Forum Discussion
Fixing a Many-to-Many relationship in data model
You can add additional CROSSFILTER arguments to CALCULATE. Each table with a relationship to Bridge_Table needs a CROSSFILTER argument in the measure. I'm not sure why filtering on Dim_Divisions doesn't work; the one-to-many relationship with Fact_LineItems should function normally. What exactly is the error?
Value = CALCULATE ( SUM ( Fact_LineItems[Value] ), CROSSFILTER ( Dim_PLViews[Line ID], Bridge_Table[Line ID], BOTH ), CROSSFILTER ( Dim_LineTypes[Line ID], Bridge_Table[Line ID], BOTH ) )
You can achieve this with a bridge table and DAX. Create the bridge table in either Power Query or DAX. Here's the DAX calculated table:
Bridge = DISTINCT ( DimPLViews[Line ID] )
Create relationships as shown below. It's best to avoid bidirectional relationships and use the CROSSFILTER function instead.
Create measure:
Value =
CALCULATE (
SUM ( FactLineItems[Value] ),
CROSSFILTER ( DimPLViews[Line ID], Bridge[Line ID], BOTH )
)
Result:
---
Thankyou for the help DataInsights ! For the problem at hand that worked perfectly but I've realised afterwards I may have under-explained the other parts of the model.
In addition to needing the Line IDs grouping and being able to be filtered in various ways, I also need related Dim tables to still be able to filter properly. When I slice the data up using other related tables these are now not working using the new measure. I'm sure it's an easy one but I can't see where I've gone wrong!
Below is a snap of my data model. Ive set up the bridge table to the Fact Table and also linked it to the PL Views. When using the measure and cutting the data via the Grouping Name and Category inside PLViews it works 100%. But say I wanted to cut the Fact data by a grouping in the PL Views table + a Division in the Divisions Dim table. Is this possible?
For a quick summary:
Divisions are business units
Nominals are the Nominal codes in the accounts that relate to some (not all) Line IDs
Line Types are names for the Line IDs and descriptions
Dates is just splitting the dates in different ways
Originally these were all linked using Date or Line ID to the Fact table. Some are into the Bridge table below as I thought it would fix it but sadly not.
You can add additional CROSSFILTER arguments to CALCULATE. Each table with a relationship to Bridge_Table needs a CROSSFILTER argument in the measure. I'm not sure why filtering on Dim_Divisions doesn't work; the one-to-many relationship with Fact_LineItems should function normally. What exactly is the error?
Value = CALCULATE ( SUM ( Fact_LineItems[Value] ), CROSSFILTER ( Dim_PLViews[Line ID], Bridge_Table[Line ID], BOTH ), CROSSFILTER ( Dim_LineTypes[Line ID], Bridge_Table[Line ID], BOTH ) )Thankyou so much, you were spot on with your explanation. I feel like I need to do some reasearch to understand Crossfilter a bit better now as I'm only using it functionally without properly understanding it at this stage.
Re. your confusion over the Division issue it was correct. I've only pushed fake data into these tables so far and it just so happens every division has exactly the same cost! I just assumed a relationship was broken but it was user error.
Many thanks for your support! 👍
Glad to hear that works. Think of CROSSFILTER as on "on demand" bidirectional relationship. It achieves the same crossfiltering that a bidirectional relationship would, but only in the context of the measure.