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

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.

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

🛠️ 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.

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:

🚩Let’s connect on social:

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

[Biin_there]

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.

[hi_vishy]

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

[TylehurstXL]

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

[abaderefs]

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

[juandanielgonzalezchico]

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

[jerrydellasala]

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

[alterchannel]

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

[taniarabisheva]

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

[numbadruma]

Great! Always coming through with the hard hitters!!

[Noble.Hustler]

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

[partymaschine]

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

[wayneedmondson]

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

[vijayarjunwadkar]

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

[vida]

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

[williamarthur]

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.

[emirtuncer]

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.

[duszan]

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

[mattschoular]

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

[therealcomment]

More than what I was looking for. Thanks!

[bioi]

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