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

Подписаться 2,7 млн

Просмотров 179 тыс.

50% 1

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

Опубликовано:

30 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 116

@LeilaGharani 6 месяцев назад

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

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

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

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

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

@duszan2 Год назад

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

@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

@juandanielgonzalezchico2459 8 месяцев назад

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

@vida1719 2 года назад

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

@ahmetyildirim7268 2 года назад

Very clear, good job. thanks for sharing.

@hi_vishy Год назад

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

@LeilaGharani Год назад

Glad you enjoyed it!

@therealcomment5622 4 месяца назад

More than what I was looking for. Thanks!

@AverageJoe-Ohio Год назад

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

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

@lorenzoladejobi8701 2 года назад

Very useful! Thanks Leila.

@jerrydellasala7643 2 года назад

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

@LeilaGharani 2 года назад

Glad it was helpful!

@giampaolocorradi1783 7 месяцев назад

Brilliant !!! Thank you. ✌

@matthiaswellmeyer763 2 года назад

Thank you very much! Very helpful 👍

@patrickbcox 2 года назад

Very helpful, thanks!

@roycemekolle7334 2 года назад

Very useful. Thanks for sharing

@juja2819 2 года назад

Thank you for this one! :)

@nicopicco 2 года назад

Excellent, thank you

@HumpendeTrussetyv3 Год назад

Thank you!

@mattschoular8844 2 года назад

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

@LeilaGharani 2 года назад

Glad you enjoyed it, Matt!

@ronakshah9561 8 месяцев назад

Brilliant !!!!!

@chrism9037 2 года назад

Thanks Leila!

@Wzxxx 2 года назад

Thank U so much!

@excelwithmark 2 года назад

Great video thanks for sharing

@LeilaGharani 2 года назад

Thanks for watching!

@ArjunKumar-cz4qr Год назад

Really Usefull

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

@santoshdevtale 2 года назад

Brain with beauty. Love u ❤️❤️

@poojandedhia809 2 года назад

Thanks for a wonderful knowledge sharing.!

@LeilaGharani 2 года назад

My pleasure

@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

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

@alaaelrafei8907 День назад

Amazing🙂🙂

@roywilson9580 2 года назад

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

@arifurrahman3883 2 года назад

Thanks

@Akn876 2 года назад

Awesome

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

@ErikWesseling 2 года назад

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

@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 года назад

👍

@geoxtream 6 месяцев назад

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

@supipisewwandi6804 2 года назад

Thank you Lia 💐 can you make videos on Minitab

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

@DaiSinFa Год назад

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

@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

@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

@Jocedu06 2 года назад

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

@rafikfoodway5743 2 года назад

Vvvvv good

@thinkhelpservice 2 года назад

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

@LeilaGharani 2 года назад

Thank you 🤗

@juliemcg6935 10 месяцев назад

New to data models

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

@elsayedabdalla1640 2 года назад

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

@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

@msateeshkumar6902 2 года назад

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

@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

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

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

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

@zakishaikhgunners 2 года назад

Like 176 is from me 🙂 You are Superb!

@LeilaGharani 2 года назад

Thank you so much 😀

@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

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

@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

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

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

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