DAX CALCULATE Trick to Pass Filters from Many to One Side of Relationship
Вставка
- Опубліковано 6 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Dive deep into the world of data modeling with our comprehensive guide on managing many-to-one relationships in Excel and Power BI. Perfect for intermediate to advanced users, this video is packed with practical examples and advanced DAX techniques.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/many-to-one...
🔑 What You'll Discover:
▪️ Essentials of Many-to-One Relationships: Learn the fundamentals and their application in data models.
▪️ Practical Excel & Power BI Strategies: Gain insights on how to effectively navigate these relationships in both platforms.
🎓 Get access to the full course here: www.xelplus.com/course/excel-...
🛠️ Techniques Explored:
▪️ Distinct Count Case Study: A step-by-step guide on counting distinct data entries.
▪️ DAX Formulas Unveiled: Master the use of CALCULATE and CROSSFILTER for dynamic data analysis.
▪️ Alternatives & Best Practices: Discover different approaches and when to use them for optimal results.
Relationships in Microsoft Power BI and Power Pivot give us the ability to report on fields from multiple tables in the data model. The most common type of relationship used in data models is a One-to-Many Relationship. But sometimes you have to go from the Many side to the One-Side of the Relationship. I'll show you 3 different solutions you can apply in DAX. You can use these techniques in Excel Power Pivot and in Power BI as well. The first technique takes you through the DAX CALCULATE function and shows a trick you can use to get the filter flow the other way. I'll also demonstrate how the many to one side calculation works by breaking down the measure in simpler calculation steps so you can visualize and understand the process and how DAX performs the final calculation.
🌍 My Online Excel Courses ► www.xelplus.com/courses/
00:00 Calculate with Many-One Relationships
00:22 Many-to-One Relationships In data models
02:11 The Wrong Approach
03:41 Solution 1: Using DAX CALCULATE
06:36 Solution 2: Adding a Column On Many-Side RELATED
07:36 Solution 3: Using CROSSFILTER
09:28 Wrap Up
🎬 LINKS to related videos:
- Excel Productivity for Lazy (but Smart) People: • Excel Productivity for...
- DAX CALCULATE Function Made Easy to Understand: • DAX CALCULATE Function...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #powerbi #dax
Grab the file I used in the video from here 👉 pages.xelplus.com/many-to-one-file
Wow! So much information in such a small video. Very crisp and to the point.
Glad you enjoyed it!
I have been intuitively developing excel macro codes for the past few years. Now as i rewind and try to refine my methods, your videos have really been very helpful. I appreciate your simple and concise presentations. Thank you.
I really love this video! 😊 Sometimes it can be frustrating when filters don‘t work the way as they should!
I liked the first way most, because it is so handy and quick. Thanks Leila!!
You've turned me into a formidable excel guru literary without even studying anything else!
Great lesson Leila! DAX can be a bit mind bending. You always manage to make it clear. Thanks for the lesson! Thumbs up!!
Great! Always coming through with the hard hitters!!
Excellent video, really precise in its content! Ive googles this topic many times and come out more confused than before . All i had to do was use the table as a filter in the calculate function. Neat trick. Googling is over on this topic. Thanks Laila
be careful that in larger sales tables using a complete table as a filter may become excessively slow
I've never heard that the entire table can be used as a filter modifier. A really interesting approach
Very useful topic for Power Pivots. Thanks Leila Gharani. I learned so much from you.
I love your explanations, and i absolutely love the way you look up on your left when explaining. 😊
Great video, I have always found many to one side filtering confusing, and still do but thanks for pushing me a bit further along the road to understanding.
Wonderful lesson - really helps me understand the data flow in data model, and how to make it work in some scenarios.
I love this video is as well available in Spanish. Muchas Gracias Leila
Very clear, good job. thanks for sharing.
More than what I was looking for. Thanks!
Useful, Interesting, and clear explanation! Thank you. 👍👍
Glad it was helpful!
Very useful! Thanks Leila.
Thank you Leila! Had some idea about this, but your video helped me understand better! You are a great teacher! 😊👍
Much appreciated, Vijay!
It's black magic 🧙♀🤩your video solved my big issue in data model connections 👍👍👍salute you 👏👏👏👏
Brilliant !!! Thank you. ✌
Very helpful, thanks!
Thank you very much! Very helpful 👍
Very useful. Thanks for sharing
Thank you!
Excellent, thank you
Brilliant !!!!!
Thank you for this one! :)
Thank U so much!
Thanks Leila!
Interesting...Yes, Useful...Yes, Going to need to watch again... Definitely. Thanks Leila
Glad you enjoyed it, Matt!
Really Usefull
Great video thanks for sharing
Thanks for watching!
Thanks for a wonderful knowledge sharing.!
My pleasure
Brain with beauty. Love u ❤️❤️
Thanks
Good timing! I was looking for a proper solution for this problem today at work. I solved it with an additional column. But the measure is a much nicer way to do that!😃 thanks for the video, I will change it tomorrow
May i ask what kind of job requires this knowledge. I work w excel bu cannot see how and where to apply this , certainly i could somehow :)
Of the solutions presentedI think the first would be my goto.
Thank you Lia 💐 can you make videos on Minitab
Awesome
At the 9:30 minute mark, Leila comments; " ... this was a bit of an advanced dive into the world of DAX ..." A 'bit'??? For me it was a deep dive, ... really deep. Unfortunately, I am still only swimming on the surface with a snorkel ... 😏. But as always, beautifully presented.
yeh i was thinking the same !, but I certainly learnt something new and interesting - just have to get the courage to try it for myself
Haha, I understand. Not something you need every day 😊
Measures are something I can't get my head into using. I would also be quite interested in what made you use a separate Calendar table. Thanks, Leila.
Hi, in Crossfilter option you can use Distintcountnoblank function o avoid wrong result in total row.
I think using Crossfilter has best performance when compared to other 2 option.
Thanks for the video.
I guess it's more to do with ref integrity than blank
Hi Leila, from a performance point of view, between option 1 or 2, which one would you recommend?
I think add a summary slide in the start with bullet list of approaches used.. also by changing to bidirectional we can have same thing
Leila Mam, I studied something called one to one, one to many, many to one and many to many relationships in RDBMS. during my college days. I also read about primary key and foreign key constraints. I recalled those concepts while you were explaining data modelling.
👍
Hi Leila, do you know which of the 3 solutions has the best performance? Thanks
Great video thanks, The Grand Total not reflect the correct No, Please clarify
New to data models
Nice video on the concept of ‘Table Filters’ (where you use an entire table as a filter).
Any bridge table will do, here we only have one: the factTable itself.
BTW: I do not recommend adding a calculated column to the factTable as an alternative, because factTables usually are very large.
Thanks for the feedback, Geert!
Vvvvv good
Hi madam, did you more videos on DAX for power BI
Thanks for this nicely contextualized demonstration for the CROSSFILTERS function!!
like 104 is from me :) thank you Leila another very interesting guide
Thank you 🤗
Hi thanks for the video. I have a + between two columns - =CALCULATE(COUNT(Pay_23[Full Name]),Pay_23)+CALCULATE(COUNT(Pay_23[Full Name]),Pay_23) what can I add so that on the pivot table when I double click the measure it drills down the right data on another sheet? thank you. I am using a bridge table for a relationship between two tables
Hi, I’ve recently watched your videos I was wondering if you could point me to the correct videos or post a video explaining some of the below tasks
I understand how to create a task list what I’m struggling with is understanding how to create reoccurring tasks that need to be completed monthly etc
How to filter todays due tasks in a list to keep on top of them
How to move completed tasks to a different worksheet and remove from the main task list
Tips for creating a training matrix for employees and it be dynamic
Attendance or absence trackers
Is there anyway possible say if I have a daily report that I need to fill in and this is a word document to be able to generate a new blank template and save in a particular folder for every new day
Creating slicer buttons to access different worksheets or data
I’m looking for any tips on an employee dashboard so you could click on employee it would have supervision notes basic information their training information all linked from data in other worksheets
How to create a dynamic calendar that you can add events too/ reoccurring events / and changes and updates automatically and I can use continuously
Will you ever plan to put your Power Pivot course on Udemy? I bought quite a few of your other "power" courses there and love to have them in one place.
Thanks Lia!! Very helpful!! I have an issue where I concatenated 2 columns to create a unique column on 2 tables to create a relationship. However, for some reason when I try connecting I get a many to many error. But when I look at the data in excel there are no duplicates. I don't know what the issue is🤦🏾♀️ Let me know if you have any tips. Thanks!!
Convert them into text format
@@vishalbhati912 Thank you. I will try this. It is currently a number format. Thanks so much!👍🏾
@@jrobinson8898 thank me later mam. 1st apply this
In the data model, could the relationship between the customer and sales tables be altered to allow data to flow in both directions, in order to solve the issue? Only I recently saw this same example in a Power BI report and they made the relationship allow data to flow in both directions to do this exact type of report. It was stated by the trainer/demonstrator that this does come with a performance cost, so care should be taken, but I would expect it to be a similar performance cost as using the sales table as a filter in the DAX calculation.
In Excel it‘s not possible to have bidirectional relationships. In Power BI it is. But it‘s generally not recommended because it can cause a lot of other issues. It‘s usually best to go via measures.
Hi my macro cannot run in the edge browser, can you give me some tips to make it work, previously I work use IE since IE will be demise, I try to use EDGE..thank you
Like 176 is from me 🙂
You are Superb!
Thank you so much 😀
I am curious is there a way to somehow use summarize to accomplish this? I did summarize (sales tbl, months, customer's city) but now i am stuck.
I’ve been working on a dashboard and I’m coming across an error. I know it’s gonna be an easy fix but I can’t seem to figure it out. Do you give one on ones ? 🙏🙏
In 1st solution, I believe it's important to understand the concept of "Expanded Tables" coz that's what I think is responsible for the correct calculation. "Usually" Fact table indirectly contains all the columns of the dimension tables linked to it.
May i ask what kind of job requires this knowledge. I work w excel bu cannot see how and where to apply this , certainly i could somehow :)
I am not sure if it is always useful to keep all fields in one fact table. Of course it makes it easier to build a data model. However, if you have to answer different business questions that require different centered data models, translation tables might be more efficient.
@@partymaschine92 i use xlookup in relation to costs analysis. I cannot see how could i apply this other then building a db in excel
@@Seftehandle I use DAX and PowerQuery always when I need to repeat certain actions in order to update a workbook. The big advantage of loading external data into the workbook is to keep the actual size of the workbook as small as possible. But I aggree, the usage of DAX is more or less the beginning of a bigger journey which has PowerBI on its path
@@partymaschine92 last thing, how do you keep the workbook as small as possible.for example if i load 10 excel in one with power query, the data is still loaded in the workbook.
If I was to hire someone who could do this, what would I be looking for on a cv to recognise they can do this?
Advanced knowledge in DAX and data models maybe.
I don't have a computer/ laptop. I want to use Microsoft surface book. It's not available in my country. Now what can I do?? Please help me, if possible.
Please help 🥺
I want to make a script
That converts Google sheets data
In pdf
But problem is i want to arrange data landscape and horizontal way and also want to set margin and set different page set-up A4,A3, legal size .
Please I need Problem Solved for Date table , I tray create relationship between Date Table to two Table but 1 Relation Active & other relation inactive Please Support
Good video. But i got lost by the measure with distinctcount 😥
No worries. It's not an easy one to follow. If you don't use data models and DAX at work, then you will not need this.
I like to imagine Leila making the funny faces at her camera for these thumbnails. It makes me chuckle. I bet she's gotten some odd looks from her husband over the years.
Grate job, but simply you can create a measure to distinctly count the SalesCity (=DISTINCTCOUNT(Sales[SalesCity]) and you filter it out based on any other dimension linked to the Sales table, and that all you need to do.
but if your intention to introduce CROSSFILTER that is another topic you should bring a complex example to best practice its usage
Hi Ms Leila, i've been posting the same query few times, but seems you did not notice me. 🙂
Can you please show us how to copy cells from filtered table and paste it in the same filtered cells but a different file location? Thank you!
😉
🇻🇳🇮🇳🇬🇧
Hello madam,,
It is a request from my side .. I am not able to pay and watch your lectures on Excel (macros & VBA) ..
Can you please upload the paid videos in UA-cam it will be helpful for many students out here ..
Why not just set the relationship to birectional?
Little difficult
From performance point of view- Not a good idea to pass an entire fact table (sales) as a filter....
Wouldn't this be better tackled as an Access database and queries set up
If this data was in microsoft access everything will be simple. Why on earth people use excel to keep data?
Awsome! ... and not intuitive at all!
Hi Leila, from a performance point of view, between option 1 or 2, which one would you recommend?