Bridge Table for two fact tables which get „filtered“ by dimension table.

[John-Cena88]

Hello,

I am stuck with a problem regarding my data model and hope someone can help me with that. 

I got two fact tables which have several dimension each that "filter" these fact tables. Fact table a contains transaction details with a specific id and an amount. Fact table b also. I want to display a table visual in my report which basically lists the id and the sum of the amount for that id. Problem is, that fact table a and b don't contain the same ids. So I created a bridge table and linked that to the fact tables. 

For my table visual I use the id in the bridge table to display the sum of the amount for each id. Problem here is that I see ids which should not be in this visual because of the dimension tables which "filters" each of the fact tables. 

Any idea how to solve that problem?

[Sahir_Maharaj · Accepted solution]

Hello @John-Cena88,

Instead of relying on the automatic filtering through relationships, consider creating DAX measures that explicitly calculate the sums for each ID:

Sum Amounts with Filter = 
CALCULATE(
    SUM(FactTableA[Amount]),
    FILTER(
        BridgeTable,
        BridgeTable[ID] IN VALUES(FactTableA[ID]) && <DimensionTableFilterConditionA>
    )
)
+ CALCULATE(
    SUM(FactTableB[Amount]),
    FILTER(
        BridgeTable,
        BridgeTable[ID] IN VALUES(FactTableB[ID]) && <DimensionTableFilterConditionB>
    )
)

[Sahir_Maharaj · Accepted solution]

Hello @John-Cena88,

Instead of relying on the automatic filtering through relationships, consider creating DAX measures that explicitly calculate the sums for each ID:

Sum Amounts with Filter = 
CALCULATE(
    SUM(FactTableA[Amount]),
    FILTER(
        BridgeTable,
        BridgeTable[ID] IN VALUES(FactTableA[ID]) && <DimensionTableFilterConditionA>
    )
)
+ CALCULATE(
    SUM(FactTableB[Amount]),
    FILTER(
        BridgeTable,
        BridgeTable[ID] IN VALUES(FactTableB[ID]) && <DimensionTableFilterConditionB>
    )
)

[John-Cena88]

What would we do if we didn't have legends like you! That worked as excepted. Could you explain to me why the relationship didn't do the job in such a case? What's even the deal to do the starschema if it doesn't work properly. 

[Sahir_Maharaj · Accepted solution]

Anytime - Happy to help! 

The star schema is effective for many scenarios, but there are situations where it might not handle complex filtering or aggregation "out of the box," particularly when:

• Dealing with complex many-to-many relationships.
• Requiring custom calculations that span across multiple tables.
• Needing to enforce specific filter contexts that aren't naturally supported by the existing relationships.

Understanding when and how to extend your data model with DAX is key.

Dont hesitate to let me know if you might have any further questions!

[John-Cena88]

Hi Sahir, I have a follow-up question regarding my problem. The calculation you provided works fine and leads to the expected outcome (as mentioned earlier). The problem I have now. 

Based on the calculation we did and the output of that, I have to create another measure which assigns a source a or b. For example if the value of our calculation is 1 then a else b. But when I do that the whole filter context is broken and instead of 7k rows (which is the expected number based on the filter in the calculation measure) we have 24k (every id in the bridge table) any idea how to solve that? 

I could build this whole table in power query with merging etc which I did and it works fine but I wanted to set it up as a star schema ...

any idea?