Project Botticelli

Status message

See the new version of this article on Tecflix.com! Why? Read our announcement.

Many-to-many Relationships in DAX Purchase this course

20 November 2013 · 5477 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.