In tabular cubes and PowerPivot models, it is not currently possible to establish many-to-many relationships between tables. The nature of business data often requires this type of relationship, so we must design our calculations in order to recreate them.
Consider this scenario with two many-to-many fact tables:
They are both joined to an Opportunity table, a Partner Table, and a Product Table:
Typically, we would simply use the code below as a Revenue calculation, but this calculation will not slice by Partner:
By using a FILTER function, we can create a single column table of the Opportunities with Partners and take the sum of the Revenue for that list:
If the user filters the Partner table, it will be reflected in the column produced by the FILTER function. However, using a COUNTROWS function nested in a FILTER function will make the calculation very slow.
Instead of having to repeatedly count rows in the Partner table, we can obtain the same functionality using a SUMMARIZE function:
As with the FILTER function, the SUMMARIZE function produces a table that will filter our SUM function, and this table will be altered by filtering the Partner table.
While this will improve the speed of our calculation, it is actually possible to get the same functionality and performance with a much simpler syntax:
CALCULATE will accept the OpportunityPartner table as a filter for the sum, and filtering down the Partner table will filter the rows being summed. This provides us with a calculation that is both efficient and concise.
Posted by admin