I wrote many articles and white papers about implementing many-to-many relationships in Analysis Services – for both Multidimensional and Tabular models. For the latter, the same techniques are also useful in Power Pivot and Power BI. With the recent release of Excel 2016, Analysis Services 2016, and the engine available in Power BI Desktop, there are new tools and techniques that we can use to implement this type of data models, which are more efficient and easier to manage in your DAX code.
The article Many-to-many relationships in Power BI and Excel 2016 describes how to use the bidirectional filters in Power BI and Analysis Services 2016, and also how to obtain the same performance in Excel 2016 by using the CROSSFILTER function. These techniques use a smart technique that applies the filter through the relationships in the many-to-one direction only when this is really necessary, because there is an existing filter over the bridge table in a many-to-many relationship. This is possible in the “old” DAX with a more complex conditional statement, which makes the code less readable and also suffer of another performance issue related to the usage of an IF statement in a measure (also solved in the “new” engine).
I think there are a huge number of data modeling options that are now possible thanks to these innovations, many of them will have an impact on several DAX Patterns, which we will revisit in a few months using the new techniques.
Specifies cross filtering direction to be used in the evaluation of a DAX expression. The relationship is defined by naming, as arguments, the two columns that serve as endpoints.
CROSSFILTER ( <LeftColumnName>, <RightColumnName>, <CrossFilterType> )
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
IF ( <LogicalTest>, <ResultIfTrue> [, <ResultIfFalse>] )