Forum Discussion

LMSFWork's avatar
LMSFWork
New Member
3 years ago
Solved

Fixing a Many-to-Many relationship in data model

I am creating a data model to store Budget and Forecast information. The main Fact table stores basic information: Table ID, Division ID, Item ID, Date, Value.   The Item ID field relates to an Ite...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    LMSFWork,

     

    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 )
)

     

     

LMSFWork's avatar
LMSFWork
New Member
3 years ago

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! 👍

DataInsights's avatar
DataInsights
Super User
3 years ago

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.