Loading the player...
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 beneﬁt from their power, because of the way DAX handles ﬁlter 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 ﬁlters 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.