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:
Pingback: Resolving Many to Many relationships leveraging DAX Cross Table Filtering | Microsoft Business Intelligence | Scoop.it
Pingback: Optimize Many-to-Many 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 filter-context 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 response-time
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
Very good example! thanks for the article. One quick question, in similar situation I am looking at
Campaign Sales = IF(ISCROSSFILTERED(Campaigns[Campaign Heirarchy Key]),
CALCULATE(sum(Sales[Amount Master]),’Product’,’Campaign Category’,’Campaigns’,),
blank())
Which works but I need to only return sales where the sales date is between the start and end of the campaign. Thanks again!
you should be able to simply add a DATESBETWEEN as a new parameter to the CALCULATE function:
DATESBETWEEN(‘Date'[Date], MIN(Campaigns[StartDate]), MAX(Campaigns[EndDate]))
this limits the sales the active dates of the campaign
-gerhard
That worked! Thanks so much!!!
Following the same example, but from a different fact table (i.e. Inventory movements) How can you get the count of products for any campaign that have a quantity Product Campaign
I tried to put a condition against the measure Inventoy OnHand (which is a TTD time pattern) but it is now working:
Thanks again!
Apologies, I meant to write
“How can you get the count of products for any campaign that have a quantity <= 0"
to get the count of products with quantity > 0 you would need to do something like COUNTROWS(FILTER(VALUES(Product[ProductID]), [Quantity] <= 0)) I am not quite sure how you want to integrate the M2M pattern there though
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! 😀
Pingback: PowerPivot - Calculate/Count Unique Row Across Multiple Tables
Pingback: many-to-many 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 CALCULATE-function 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 over-ride 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 over-ride, 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 😀
maybe you could post or send me the sample-workbook 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 over-riding 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 many-to-many 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 many-many needs.
I have 3 tables with start and end dates, connected by intermediate tables in a many-many 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 many-many 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:
http://gbrueckl.wordpress.com/2012/02/16/handling-scd2-dimensions-and-facts-with-powerpivot/
http://gbrueckl.wordpress.com/2012/03/01/handling-scd2-snowflake-schemas-with-powerpivot/
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 about-tab
-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
Many-to-many allows you to assign one fact-row to several items (targets in my example).
But this does not mean that the total changes.
little example: you have sales-data in your model. Product A is sold only once for 100$
you may have custom groupings in place using many-to-many 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: Point-In-Time Dimension Reporting In DAX | Chris Webb's BI Blog
Pingback: Point-In-Time Dimension Reporting In DAX - SQL Server - SQL Server - Toad World
Pingback: Point-In-Time Dimension Reporting In DAX - Atlas Analytics Inc.
Pingback: Many to Many and Cross-Filter | Sladescross's Blog
Pingback: Relaciones “muchos a muchos” en Power Pivot - Tecnología para todos - Site Home - TechNet Blogs
Hi,
Nice topic to solve the M2M relationship data, But I have same scenario..
Suppose one more fact table “tbl_FACT1” linked with calendar dimension table and created new measure “Measure1” into that newly fact table.
then how I can add another measure “Measure2″of audience table into newly created measure “Measure1”.
and I also would like to see in power pivot with following fields:
drag and drop column1 of fact table “tbl_FACT1” and Target column from Target table into row labels.
drag and drop the sum of 2 measures into Values of Powerpivot.
‘Audience'[Measure 3] = [Measure1] + [Measure2]
I did it….but its gives me wrong result for different dimensions..
In my case I am getting wrong data..can you help me on that..
I am not quite sure that I understood your requirements so let me rephrase it from what I understood:
You added another table “tabl_FACT1” to the model, linked it to the calendar table and created a measure “Measure1” in there which, lets say simply does a SUM over a column.
Now you would like to use columns from this new table and the Target table on rows and display the new measure togehter with the original M2M-measure
What exactly do you expect to see then?
Or what are your “wrong results”?
Do you want the original M2M Measure also to be filtered by the column of tabl_Fact1 and the new Measure1 to be filtered by the Target-column of the Target-table?
do you have a sample workbook which you could share?
this would make things a lot easier
-gerhard
Pingback: Power Pivot and Basketball Superstars: Many-to-Many and USERELATIONSHIP « PowerPivotPro
Gerhard Brueckl. Can I ask something?
sure, whats the matter?
Inactive relationships? If I use inactive relationships, can I then use UseRelatioship() to activate the relationships I need
yes, this should work. You need to wrap a CALCULATE+USERELATIONSHIP around your expression:
CALCULATE(
CALCULATE(
[MyMeasure],
‘MyM2MTable’),
USERELATIONSHIP(
‘MyM2MTable'[ProductKey],
‘MyProducts'[ProductKey])
)
kind regards,
-gerhard
I have a many to many scenario that needs to be multiplied by a value in a bridge table. The value in the bridge table is either 1 or -1.
CALCULATE(SUM(Fact(amount),Bridge)) works, but l need to extend this measure to multiply by the value of 1 or -1 in the bridge table column. Is this possible?
you would need to iterate over the values in your bridge-table to get this
like CALCULATE(SUMX(VALUES(Bridge[Multiplier]), CALCULATE(SUM(Fact[Amount])*Bridge[Multiplier]))