Forum Discussion
Helper IIDealing with multiple fact tables?
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
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
4 Replies
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
jd8766From 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?
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
For five ways to do this, see https://maxpowerbi.pro/facts-facts-facts-five-ways-of-combining-several-fact-tables-in-a-powerbi-report/#more-177
