Fixing a Many-to-Many relationship in data model

[LMSFWork]

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 Item table which specifies what this row contains. I.e it could be a staff costs line, or it could be Headcount numbers for staff costs.

Our FP&A Manager needs to be able to sumarise the data in the Fact table in various ways. This will be for financial reporting. An example could be to summarise all staff costs in one line so Item ID's 4, 5 and 8 will be in there. Another business report may be more high level and simply have all costs in one line so ID 4, 5, 6, 7, 8 are in this grouping.

My initial idea was to make a groupings table which maps out which IDs relate to different views of the data. However this will not work because Line ID to Line ID in the Fact table and the Dim Groupings table are Many to Many. I'm not 100% sure the best practice way to achieve this. An example of my Fact table with itemised costs/numbers is below as well as the Dim Groupings table I'm trying to join to. I appreciate it's not really a true dim table as it has several line IDs repeated, so I'm trying to understand how to reshape the overall to make this work please.

In terms of data volume, we want to include one line in our Fact table per day, per item. We will need to Budget for a rolling 500 days, across 10 divisions. This means that over the course of a year we're expecting c. 15million rows to accumulate.

[DataInsights]

@LMSFWork,

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:

---

[LMSFWork]

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.

[DataInsights · Accepted solution]

@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]

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]

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.