Many-to-many Relationships in DAX

20 November 2013 · 2108 views

Loading the player...

If this message stays on, check that you have the latest Flash Player installed (http://get.adobe.com/flashplayer) and that your browser supports JavaScript.

Controlling Filter Context Propagation

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 (0:31), as one of the filters of CALCULATE (2:24). 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.