Post reply

Calculations from 2 fact tables

  • August 29, 2019 at 11:59 am

    #3675379

    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 5 years, 3 months ago by  leehbi.

  • August 30, 2019 at 12:10 pm

    #3675684

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 30, 2019 at 4:00 pm

    #3675776

    I would leave the tables separate. You can write a query/function in DAX to do the counts. If they're separate, it makes creating basic measures much easier.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply