Dealing with multiple fact tables?

[jd8766]

We have a star schema in place currently (see screenshot example), however I have been tasked to add a new Fact table (the payments one) into our existing SSAS model.

I am planning on using the Contract Dimension as a conformed dimension to filter both Sales and Payments (joining on the ContractKey column). My only problem is I can imagine the business will eventually want to see PaymentAmount by SoldTeamKey (this would be the total payments grouped by team, however this only exists currently on the FACT.Sales table)

What would the best approach be? To add all of the conformed dimensions to my Payments table so it also contains SoldDateKey, SoldTeamKey etc.. or to move the SoldDate/SoldTeam columns to the Contract dimension table and use the fields on there to filter both? Just wondering what people think the best approach would be with these situations?

Appreciate any advice

[Jaaap]

For five ways to do this, see https://maxpowerbi.pro/facts-facts-facts-five-ways-of-combining-several-fact-tables-in-a-powerbi-rep...

[v-jayw-msft]

Hi @jd8766 ,

You just need to create relationships between these tables using [ContractKey].

Then create the visual like below:

If I misunderstood you meaning, please show the expected result.

Best Regards,

Jay

[jd8766]

From my understanding of data modelling a fact table should never directly filter a fact table, and a bi-directional relationship could create performance issues/maybe bring back incorrect data?

[v-jayw-msft · Accepted solution]

Hi @jd8766 ,

You are right.

If you want to avoid relationship, you will need to create a measure like below:

Measure = CALCULATE(SUM('Fact Payment'[PaymentAmount]),FILTER('Fact Payment','Fact Payment'[ContractKey] in VALUES('Fact Sales'[ContractKey])))

Best Regards,

Jay