DAX CALCULATE Trick to Pass Filters from Many to One Side of Relationship

Рет қаралды 177,378

Күн бұрын

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

Пікірлер: 115

@LeilaGharani 5 ай бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/many-to-one-file

@hi_vishy Жыл бұрын

Wow! So much information in such a small video. Very crisp and to the point.

@LeilaGharani Жыл бұрын

Glad you enjoyed it!

@BinGetBin 2 жыл бұрын

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.

@partymaschine92 2 жыл бұрын

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!!

@AverageJoe-Ohio Жыл бұрын

You've turned me into a formidable excel guru literary without even studying anything else!

@wayneedmondson1065 2 жыл бұрын

Great lesson Leila! DAX can be a bit mind bending. You always manage to make it clear. Thanks for the lesson! Thumbs up!!

@numba1druma1 2 жыл бұрын

Great! Always coming through with the hard hitters!!

@dabizness6662 2 жыл бұрын

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

@koborkutya7338 2 жыл бұрын

be careful that in larger sales tables using a complete table as a filter may become excessively slow

@vida1719 2 жыл бұрын

I've never heard that the entire table can be used as a filter modifier. A really interesting approach

@taniarabisheva8381 2 жыл бұрын

Very useful topic for Power Pivots. Thanks Leila Gharani. I learned so much from you.

@alterchannel2501 9 ай бұрын

I love your explanations, and i absolutely love the way you look up on your left when explaining. 😊

@williamarthur4801 Жыл бұрын

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.

@duszan2 Жыл бұрын

Wonderful lesson - really helps me understand the data flow in data model, and how to make it work in some scenarios.

@juandanielgonzalezchico2459 7 ай бұрын

I love this video is as well available in Spanish. Muchas Gracias Leila

@ahmetyildirim7268 2 жыл бұрын

Very clear, good job. thanks for sharing.

@therealcomment5622 3 ай бұрын

More than what I was looking for. Thanks!

@jerrydellasala7643 2 жыл бұрын

Useful, Interesting, and clear explanation! Thank you. 👍👍

@LeilaGharani 2 жыл бұрын

Glad it was helpful!

@lorenzoladejobi8701 2 жыл бұрын

Very useful! Thanks Leila.

@vijayarjunwadkar 2 жыл бұрын

Thank you Leila! Had some idea about this, but your video helped me understand better! You are a great teacher! 😊👍

@LeilaGharani 2 жыл бұрын

Much appreciated, Vijay!

@abaderefs Жыл бұрын

It's black magic 🧙‍♀🤩your video solved my big issue in data model connections 👍👍👍salute you 👏👏👏👏

@giampaolocorradi1783 6 ай бұрын

Brilliant !!! Thank you. ✌

@patrickbcox 2 жыл бұрын

Very helpful, thanks!

@matthiaswellmeyer763 2 жыл бұрын

Thank you very much! Very helpful 👍

@roycemekolle7334 2 жыл бұрын

Very useful. Thanks for sharing

@HumpendeTrussetyv3 Жыл бұрын

Thank you!

@nicopicco 2 жыл бұрын

Excellent, thank you

@ronakshah9561 7 ай бұрын

Brilliant !!!!!

@juja2819 2 жыл бұрын

Thank you for this one! :)

@Wzxxx 2 жыл бұрын

Thank U so much!

@chrism9037 2 жыл бұрын

Thanks Leila!

@mattschoular8844 2 жыл бұрын

Interesting...Yes, Useful...Yes, Going to need to watch again... Definitely. Thanks Leila

@LeilaGharani 2 жыл бұрын

Glad you enjoyed it, Matt!

@ArjunKumar-cz4qr Жыл бұрын

Really Usefull

@excelwithmark 2 жыл бұрын

Great video thanks for sharing

@LeilaGharani 2 жыл бұрын

Thanks for watching!

@poojandedhia809 2 жыл бұрын

Thanks for a wonderful knowledge sharing.!

@LeilaGharani 2 жыл бұрын

My pleasure

@santoshdevtale 2 жыл бұрын

Brain with beauty. Love u ❤️❤️

@arifurrahman3883 2 жыл бұрын

Thanks

@bioi0 2 жыл бұрын

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

@Seftehandle 2 жыл бұрын

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 :)

@roywilson9580 2 жыл бұрын

Of the solutions presentedI think the first would be my goto.

@supipisewwandi6804 2 жыл бұрын

Thank you Lia 💐 can you make videos on Minitab

@Akn876 2 жыл бұрын

Awesome

@chh8860 2 жыл бұрын

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.

@thinkhelpservice 2 жыл бұрын

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

@LeilaGharani 2 жыл бұрын

Haha, I understand. Not something you need every day 😊

@petr6617 2 жыл бұрын

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.

@emirtuncer 2 жыл бұрын

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.

@umangdbz 2 жыл бұрын

I guess it's more to do with ref integrity than blank

@ErikWesseling 2 жыл бұрын

Hi Leila, from a performance point of view, between option 1 or 2, which one would you recommend?

@kishanthacker9999 Жыл бұрын

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

@pravinr197 2 жыл бұрын

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.

@LeilaGharani 2 жыл бұрын

👍

@DaiSinFa Жыл бұрын

Hi Leila, do you know which of the 3 solutions has the best performance? Thanks

@elsayedabdalla1640 2 жыл бұрын

Great video thanks, The Grand Total not reflect the correct No, Please clarify

@juliemcg6935 9 ай бұрын

New to data models

@GeertDelmulle 2 жыл бұрын

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.

@LeilaGharani 2 жыл бұрын

Thanks for the feedback, Geert!

@rafikfoodway5743 2 жыл бұрын

Vvvvv good

@msateeshkumar6902 2 жыл бұрын

Hi madam, did you more videos on DAX for power BI

@Jocedu06 2 жыл бұрын

Thanks for this nicely contextualized demonstration for the CROSSFILTERS function!!

@thinkhelpservice 2 жыл бұрын

like 104 is from me :) thank you Leila another very interesting guide

@LeilaGharani 2 жыл бұрын

Thank you 🤗

@geoxtream 5 ай бұрын

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

@naomifagan2885 2 жыл бұрын

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

@rosesmary 2 жыл бұрын

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.

@jrobinson8898 2 жыл бұрын

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!!

@vishalbhati912 2 жыл бұрын

Convert them into text format

@jrobinson8898 2 жыл бұрын

@@vishalbhati912 Thank you. I will try this. It is currently a number format. Thanks so much!👍🏾

@vishalbhati912 2 жыл бұрын

@@jrobinson8898 thank me later mam. 1st apply this

@DW_Korell 2 жыл бұрын

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.

@LeilaGharani 2 жыл бұрын

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.

@ronnyidris 2 жыл бұрын

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

@zakishaikhgunners 2 жыл бұрын

Like 176 is from me 🙂 You are Superb!

@LeilaGharani 2 жыл бұрын

Thank you so much 😀

@Wzxxx 2 жыл бұрын

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.

@baibiarugula544 Жыл бұрын

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 ? 🙏🙏

@usman_oz 2 жыл бұрын

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.

@Seftehandle 2 жыл бұрын

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 :)

@partymaschine92 2 жыл бұрын

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.

@Seftehandle 2 жыл бұрын

@@partymaschine92 i use xlookup in relation to costs analysis. I cannot see how could i apply this other then building a db in excel

@partymaschine92 2 жыл бұрын

@@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

@Seftehandle 2 жыл бұрын

@@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.

@jerseyd7131 2 жыл бұрын

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?

@LeilaGharani 2 жыл бұрын

Advanced knowledge in DAX and data models maybe.

@arifurrahman3883 2 жыл бұрын

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.

@adityadeepakghadge5738 2 жыл бұрын

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 .

@mohamedsalah-mm8ex Жыл бұрын

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

@ricebowl8393 2 жыл бұрын

Good video. But i got lost by the measure with distinctcount 😥

@LeilaGharani 2 жыл бұрын

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.

@chrismast2790 2 жыл бұрын

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.

@mohanadmohamed5501 2 жыл бұрын

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

@badonggarcia1639 2 жыл бұрын

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!

@karimmajerbi8394 2 жыл бұрын

😉

@truonghoanghaminh4839 2 жыл бұрын

🇻🇳🇮🇳🇬🇧

@shineking4035 2 жыл бұрын

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 KZfaq it will be helpful for many students out here ..