If you ever had to deal with many-to-many relationships in PowerPivot then I am quite sure that you came across the blog-post Many-to-Many relationships in PowerPivot by Marco Russo and PowerPivot and Many to Many Relationships by Alberto Ferrari. Both posts describe how this issue can be solved using DAX and provide examples and also very good background information on that topic.
I recently had to struggle with many-to-many relationships again at one of my customers who complained that many-to-many relationships are just too complex. So I rethought of the problem and searched for alternatives. During my investigations I also found Jeffrey Wang’s blog-post The Logic behind the Magic of DAX Cross Table Filtering again – a must read blog for all people interested in BISM (tabular and multidimensional), DAX or MDX. In the middle of the post he describes the single operations the CALCULATE-function performs:
|
Calculate function performs the following operations:
1. Create a new filter context by cloning the existing one.
2. Move current rows in the row context to the new filter context one by one and apply blocking semantics against all previous tables.
3. Evaluate each setfilter argument in the old filter context and then add setfilter tables to the new filter context one by one and apply blocking semantics against all tables that exist in the new filter context before the first setfilter table is added.
4. Evaluate the first argument in the newly constructed filter context.
|
(the single steps are described in more details in his post)
Important for us is the fact, that you can pass tables as arguments to the CALCULATE-function and those tables are automatically filtered by the current context. Even more important is that this filtering works in both directions of a relationships. So adding a table that has an exiting relationship with any table in the current context is similar to a JOIN in terms of SQL. Filters applied to the newly joined tables are also propagated through all other tables, regardless of the direction of the relationship.
In his Question #1 Jeffrey counts the number of Subcategories for a given Product as an example (which is always 1 as there is a 1:n relationship between DimSubCategory and DimProduct). To get the correct value he uses the following calculation to extend the filter context by DimProduct and thereby also filtering DimProductSubcategory indirectly:
CALCULATE(COUNTROWS(DimProductSubcategory), DimProduct)
Knowing that we can use CALCULATE to resolve 1:n relationships in both directions we can also use this approach to solve m:n relationships pretty easy!
Alberto Ferrari uses an example where the facts are related to Individuals. Those Individuals can be assigned to 1 or more Targets. This mapping is done using a bridge table to model the many-to-many relationship:
As you can see there is no "real" many-to-many relationship in the model as it has already been split up into a bridge-table using 1:n and n:1 relationships. Adding the information from above to this model we come up with a pretty easy DAX calculations which resolves the many-to-many relationship. Here is a little example where we simply count the rows in our Audience table:
RowCount:=COUNTROWS('Audience')
This RowCount is currently not filtered by table Targets as there is no chain of 1:n relationships between Targets and Audience. Only filters applied to directly related tables (Individuals, Time, Calendar and Networks) are considered when the calculation is evaluated.
By wrapping a CALCULATE-function around our calculation and adding the tables that participate in the many-to-many relationship as parameters we explicitly extend the filter context for our calculation. As filters on those "extended tables" also impact the current filter-context, the value for our Targets also changes according to the Individuals belonging to the current Target:
RowCount_M2M:=CALCULATE(
[RowCount],
'Individuals',
'TargetsForIndividuals',
'Targets')
Finally, to resolve the many-to-many relationship for our calculation all we have to do is to explicitly add all tables of the many-to-many relationship to the filter-context of our calculation by using the CALCULATE-function. The rest is done automatically by DAX’s Cross Table Filtering Logic!
The calculation can be further extended to only apply this logic when there is a filter on table Targets, otherwise we do not have to resolve the many-to-many relationship:
RowCount_M2M:=IF(ISCROSSFILTERED('Targets'[Target]), CALCULATE( [RowCount], 'Individuals', 'TargetsForIndividuals'), [RowCount])
Doing this ensures that the more complex calculation is only executed when there is a filter on table Targets. Further we already know that Targets is already part of the current context and therefore does not have to be added again for our calculation.
In the end we come up with a solution where we only have to add the intermediate table (Individuals) and the bridge table (TargetsForIndividuals) to our CALCULATE-functions to resolve the many-to-many relationship – pretty nice, isn’t it?
I think this approach should also be very easy to understand for people that are familiar with SQL and relational databases and just switched to tabular modeling.
The PowerPivot workbook with samples for all approaches can be downloaded here: