I have a situation where there are 2 facts tables representing SalesLeads / Sales. These facts are semi-independent i.e. some SalesLeads will lead to Sales but Sales can be made with no SalesLead

I need to calculate Sales/SalesLeads for measures such as count, revenue etc..

It's normally a no no to have cross fact calculations hence I'm thinking of a separate fact table containing the measures for both tables with the same keys for dimensions. This is a lot of data duplication hence I'm wondering if this is the recommended approach.

The model will be used by analysts in Power BI hence the calculations will be otherwise done with DAX which will be expensive as there will be a calculation over 2 fact tables for the numerator/denominator. If I combine the fact tables there will be less expensive DAX.

- This topic was modified 3 weeks, 3 days ago by leehbi.