Handling one-to-many and many-to-many relationships

One of the most important data modeling features of Power BI, which is shared with SQL Server Analysis Services Tabular 2016 and later versions, is the control the modeler has over defining the filtering behavior through relationships. In addition to one-to-many single direction relationships, Power BI models can contain bidirectional relationships as well as DAX measures that contain their own relationship filtering logic via the new CROSSFILTER() function. These relationship tools, along with modifying the filter context of measures through DAX, can be used to support many-to-many modeling scenarios and provide alternative model behaviors for multiple business requirements.

In this recipe, we look at the primary use cases for bidirectional relationships and DAX-based cross filtering. The first example uses a bidirectional relationship and the CROSSFILTER() function to support analytical needs at different scopes--the data model and specific measures. The second example model uses a bidirectional relationship with a bridge table to enable a filter through a many-to-many relationship. Examples of related DAX filter context approaches are also included for reference.