Project Botticelli

Many-to-many Relationships in DAX Get Free Access Purchase this course

20 November 2013 · 5225 views

Controlling Filter Context Propagation

Alberto shows DAX formula for handling a many-to-many relationship

Learn how to use many-to-many relationships in DAX by applying formulas which correctly handle filter context propagation over relationships in this free, 3-minute video by Alberto Ferrari. Log-in or get a free account to watch it!

Let Alberto Ferrari, BI expert from sqlbi.com, show you how to work with many-to-many relationships in DAX by using an example from a banking data model. Unfortunately, these types of relationship do not just work out-of-the-box—you need to apply correct DAX formulas to benefit from their power, because of the way DAX handles filter context propagation over relationships. This fact lead some people to believe that DAX cannot handle many-to-many relationships! Fortunately, the opposite is true: you can work with any model with many-to-many relationships by following Alberto’s suggestions shown in this short, 3-minute video.

All you need is a formula that uses the CALCULATE function, and a bridge table, as one of the filters of CALCULATE. The formula is compact, and it lets you work with many-to-many relationships easily, however, it is not the simplest one to understand, and a full explanation would require a little more time. If you are interested in using many-to-many relationships in DAX, as well as other common DAX patterns, such as banding, or how to compute new-versus-returning customers, make sure to watch Alberto’s in-depth DAX Patterns: Banding, New vs Old, Many-to-many 50-minute video.

Log in or register for free to access this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Online Courses