Resolving Many to Many relationships leveraging DAX Cross Table Filtering
May 8, 2012 29 Comments
I recently moved my blog to a new host. It is now hosted at
http://blog.gbrueckl.at/?feed=rss2
If you ever had to deal with manytomany relationships in PowerPivot then I am quite sure that you came across the blogpost ManytoMany 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 manytomany relationships again at one of my customers who complained that manytomany relationships are just too complex. So I rethought of the problem and searched for alternatives. During my investigations I also found Jeffrey Wang’s blogpost 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 CALCULATEfunction 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 CALCULATEfunction 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 manytomany relationship:
As you can see there is no "real" manytomany relationship in the model as it has already been split up into a bridgetable 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 manytomany 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 CALCULATEfunction around our calculation and adding the tables that participate in the manytomany relationship as parameters we explicitly extend the filter context for our calculation. As filters on those "extended tables" also impact the current filtercontext, 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 manytomany relationship for our calculation all we have to do is to explicitly add all tables of the manytomany relationship to the filtercontext of our calculation by using the CALCULATEfunction. 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 manytomany 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 CALCULATEfunctions to resolve the manytomany 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:
Pingback: Resolving Many to Many relationships leveraging DAX Cross Table Filtering  Microsoft Business Intelligence  Scoop.it
Pingback: Optimize ManytoMany Calculation in DAX with SUMMARIZE and Cross Table Filtering  SQLBI
Pingback: Resolving Many to Many relationships leveraging DAX Cross Table Filtering  Enterprise Data  Scoop.it
Hello!
Why in measure “Value M2M FilterCount” is used …values(Individuals[Individual])
=CALCULATE(SUM(Audience[Value]); FILTER(Values(Individuals[Individual]); COUNTROWS(RELATEDTABLE(Bridge))>0))
what is the difference if it used without …values() just like this
=CALCULATE(SUM(Audience[Value]); FILTER(‘Individuals'; COUNTROWS(RELATEDTABLE(Bridge))>0))
Thank you very much for your blog
I’m just trying to copy for people like you!
Mikhael
and if it’s possible
why your measure “Value M2M FilterCount”
=CALCULATE(SUM(Audience[Value]); FILTER(Values(Individuals[Individual]); COUNTROWS(RELATEDTABLE(Bridge))>0))
and measure
=CALCULATE(SUM(Audience[Value]); FILTER(‘Individuals';[Cnt_Bridge] ))
where [Cnt_Bridge]=COUNTROWS(Bridge)
produce the same result?
while if latter measure to write like
=CALCULATE(SUM(Audience[Value]); FILTER(‘Individuals';COUNTROWS(Bridge)))
the result will be wrong/different?
if you use the measure [Cnt_Bridge] then there is a CALCULATE wrapped around it internally whenever it is used in any other measure. this is done to get the correct filtercontext automatically
if you use COUNTROWS(Bridge) in the main expression the context is not propagated correctly so you would have to wrap the CALCULATE around it on your own to make it work:
=CALCULATE(SUM(Audience[Value]); FILTER(‘Individuals';CALCULATE(COUNTROWS(Bridge) )))
hth,
gerhard
Hi, sorry for the (very) slow responsetime
VALUES() is only used to limit the number of rows that FILTER() has to iterate over as VALUES() only returns distinct values. It does not really make a difference in this example as Individuals only contains distinct values – and yes, the results are of course the same
Pingback: Unexpected relationship/measure behavior Can some one explain
Great post! :) Such an elegant solution for a complex situation… Just used it in front of our client and boy, was he pleased! :D
Pingback: PowerPivot  Calculate/Count Unique Row Across Multiple Tables
Pingback: manytomany relationship or filtering problem
If this technique is used where pivot filtering is happening, by virtue of the row in question say, how do you override such an implicit filter. In a ‘normal’ scenario, you create a measure wrapped in CALCULATE and ALL, such as
=CALCULATE(
SUM( InternetSales[SalesAmount] ),
ALL( Date[Year]
)
but I tried that with this technique and it didn’t alter the numbers, no override took place.
I am not quite sure if I understood your question correctly but in general you can extend the CALCULATEfunction by any other filters that you want to change or remove:
RowCount_M2M:=IF(ISCROSSFILTERED(‘Targets'[Target]),
CALCULATE(
[RowCount],
‘Individuals’,
‘TargetsForIndividuals’,
ALL(Calendar)),
[RowCount])
That is what I thought Gerhard, but when I added the ALL function to override a filter in this technique, that measure calculated as though the ALL was just not there, it did not override the filter as I expected. ALL works fine in normal models, but in the M2M technique it just doesn’t work for me.
could you run your query on the model I provided in the post?
just download the excel workbook and adopt the query accordingly and then post the desired and the actual results
I have to modify the model (quite) a bit to demonstrate it. Just for arguments sake, let’s introduce the year into the model.
I added a Year column to the Audience table and also to the Bridge table. In the first instance I populated all rows with a year of 2011, and I added another column with a concatenated value for Individual and Year (I did it in the Excel table, it could just as easily be done in the PowerPivot model). I also removed the link between Bridge and Individual and joined Bridge to Audience via the concatenated IndividualYear column (I did this so that I could factor Year in).
At this point, the pivot works as before, and I can then introduce Year as a column header, and it is still the same.
Next I added some data for 2012.
Audience[Individual] = 1
Audience[Year] = 2012
Audience[Value][ = 510
Audience[IndividualYear] = 12012
Bridge[Individual] = 1
Bridge[Year] = 2012
Bridge[Target] = a
Bridge[IndividualYear] = 12012
Now my pivot shows values of 2996 for 2011 and 510 for 2012. So far so good!
But suppose I also want to show the average over all years (Just humour me on the why, it makes sense in my model :)), which should be 500.857 (2996 (2011) + 510 (2012) divided by the 7 instances of ‘a’). I added this measure
Audience[Average M2M CrossTable]: =CALCULATE(
AVERAGE( [Value] ),
Bridge,
ALL( Audience[Year] )
)
Instead of getting the values I spell out above, I get 499.333 for 2011 and 510 for 2012. In other words no filter override, the Year filter is still in play even for this measure.
Apologies for the length of the post, but I hope it explains what I am trying to achieve, and what I am seeing.
I am afraid I still dont get your problem :D
maybe you could post or send me the sampleworkbook you are working with?
to get the average over all years you may also want to try this calculation:
MyAverageOverYear:=CALCULATE(AVERAGEX(ALL(Audience[Year]), [Value]))
where [Value] is the measure created using the approach described in this blogpost
My model has funds that are managed by an individual, each fund can have many managers, each manager can manage many funds, and it can change by year, that is the M2M. In the pivot I am showing the managers within each fund. That extra measure sort of works, in that it gives me a measure that shows the average overriding my filter, but it now shows every manager against every fund, and as I have a many hundreds of managers that just doesn’t work.
I might add that I get exactly the same situation if i ditch my fact table and add the values to one of my other table using LOOKUPVALUE.
I can provide a test workbook that shows everything, where shall I post it?
just drop me a mail at gerhard@gbrueckl.at
Hi Gerhard,
I have an issue with designing powerpivot setup for the following case. I hope you could help me. I have a table with list of car accidents (Q_ZDARZENIE_03). ID is a primary key. It indicates accident’s ID.
There are 4 other tables (QA_SSWA_KOD_01, QA_GEOD_KOD_01, QA_SSUP_KOD_01, QA_STNA_KOD_01). Every of those tables contain additional attributes of an accident. There may be more than 1 attribute value for 1 accident in every table.
The data model looks as here:
https://docs.google.com/file/d/0B8qG_JlwEIG0YldUTFVLSlowbGs/edit?usp=sharing
The join is between ID – ZSZD_ID. There may be accidents which have attribute values in table #1, but do not have attribute values in table #2 etc.
I would like to create a pivottable with 4 slicers. Every slicer would be for every QA_ table. The pivottable would count number of accidents depending on selected values from slicers.
My problem is that I do not know how to do it. If I add Q_ZDARZENIE_03.ID into values, then any selection in slicers does not change numbers in pivottable. It works if I put ZSZD_ID into Values, however I have 4 columns with ZDZD_ID.
Please help. Thanks in advance. Kuba
sorry for the late answer but it’s quite busy times here
I think it should be possible to use cross table filtering as I described in my post about manytomany relationships
The problem is that you have relationships from QA_xxx to Q_ZDARZENTE_03 which means Q_ZDARZENTE_03 would filter all other tables but not the other way round
To achieve this you have to add the other tables to your CALCULATE() like this:
AccidentCount:=CALCULATE(COUNTROWS(‘Q_ZDARZENTE_03’), ‘QA_SSWA_KOD_01’, ‘QA_GEOD_KOD_01’, ‘QA_SSUP_KOD_01’, ‘QA_STNA_KOD_01’)
Unfortunately this does not work if an accident is not found in any of the tables and the table is not filtered
So I created cascading measures to solve this:
Cnt:=COUNTROWS(‘Accidents’)
Cnt_Att1:=IF(ISFILTERED(Attribute1[Attribute1]), CALCULATE([Cnt], ‘Attribute1′), [Cnt])
Cnt_Att2:=IF(ISFILTERED(Attribute2[Attribute2]), CALCULATE([Cnt_Att1], ‘Attribute2′), [Cnt_Att1])
Cnt_Att3:=IF(ISFILTERED(Attribute3[Attribute3]), CALCULATE([Cnt_Att2], ‘Attribute3′), [Cnt_Att2])
‘Accidents’ would be your ‘Q_ZDARZENIE_03′
‘AttributeX’ would be your QA_SSWA_KOD_01, QA_GEOD_KOD_01, QA_SSUP_KOD_01, QA_STNA_KOD_01
‘AttributeX'[AttributeX] would be your ‘QA_…'[OPIS]column
it worked quite well for the little example i built up on my own – can you test it on your example and give us the feedback – thanks!
Hi,
I have a model in test with similar, but more complex manymany needs.
I have 3 tables with start and end dates, connected by intermediate tables in a manymany system.
so we must filter all these 3 tables in a between mode (start < last date of selected period < end date)
so, in your model, what's the best way to filter the targetsforindividual table? (which is our model)
and to add a layer of complexity the middle table of this cascading manymany model, I have to get the first row matching the keys.
if I reflect this in your model, add a column "Priority" and "campain target" into the targetsforindividuals table, and foreach distinct individual and target identified for the date, I can have multiple priorities and I have to get the MIN one to identify the campain associated to the llowest priority only.
how to do this in DAX?
Hi jerome,
did you take a look at my other post about handling SCD 2 Dimensions and Facts in PowerPivot?
the are probably more related to your problem:
https://gbrueckl.wordpress.com/2012/02/16/handlingscd2dimensionsandfactswithpowerpivot/
https://gbrueckl.wordpress.com/2012/03/01/handlingscd2snowflakeschemaswithpowerpivot/
about the issue with “get the first row matching the keys” – do you have a little sample workbook that you could share?
you can find my eMail in the abouttab
gerhard
Thank you for your post on the M2M relationships. I was wondering if you could explain the reason why the Grand Total in the pivot table does not work? Just wondering as in some cases, we may want the totals to show correctly.
well, it does work, at least in terms of how it is supposed to work :)
but I guess you are referring to the fact that the total is different from the sum of the single rows.
thats “by design” and is the similar behavior also for multidimensional model
Manytomany allows you to assign one factrow to several items (targets in my example).
But this does not mean that the total changes.
little example: you have salesdata in your model. Product A is sold only once for 100$
you may have custom groupings in place using manytomany where Product A is assigned to ProductGroup X and also ProductGroup Y
this does not mean that you have total sales of 200$ just because Product A is assigned to 2 groups
in fact you have sold Product A only once and therefor the total sales are only 100$
to get the behaviour you are talking about you may use this calculation:
Value M2MSUM:=SUMX(VALUES(Targets[Target]), [Value M2M CrossTable])
Pingback: PointInTime Dimension Reporting In DAX  Chris Webb's BI Blog
Pingback: PointInTime Dimension Reporting In DAX  SQL Server  SQL Server  Toad World
Pingback: PointInTime Dimension Reporting In DAX  Atlas Analytics Inc.
Pingback: Many to Many and CrossFilter  Sladescross's Blog