Forum Discussion

MightyMicrobe's avatar
MightyMicrobe
Helper II
4 years ago
Solved

Relating Multiple Fact Tables with Different IDs

Hi everyone, I have a model with multiple fact tables related via the Customer dimension and a hidden bridging table (UUID). The model schema is below:

Digital Campaigns fact table stores campaign activity: impressions and clicks. It is related to customers via SBLID. It also has a campaign list related to it as a dimension. 

Digital Events fact table has logins, orders and payments. It is related to customers via UUID. 

I need to create measures calculating digital events by each campaign, e.g. how many orders each campaign fetched. 

The expected outcome is below, the sample file is attached here. 

Thank you!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi, 

    I am not sure if I understood your question correctly, but please check the below measures and the attached pbix file.

     

    Login Count =
    VAR UUIDfiltertable =
    CALCULATETABLE (
    VALUES ( Customers[UUID] ),
    CROSSFILTER ( 'Digital Campaigns'[SBLID], Customers[SBLID], BOTH )
    )
    RETURN
    CALCULATE (
    SUM ( 'Digital Events'[Login] ),
    TREATAS ( UUIDfiltertable, 'Digital Events'[UUID] )
    )
     
    Order Count =
    VAR UUIDfiltertable =
    CALCULATETABLE (
    VALUES ( Customers[UUID] ),
    CROSSFILTER ( 'Digital Campaigns'[SBLID], Customers[SBLID], BOTH )
    )
    RETURN
    CALCULATE (
    SUM ( 'Digital Events'[Orders] ),
    TREATAS ( UUIDfiltertable, 'Digital Events'[UUID] )
    )
     
    Payment Count =
    VAR UUIDfiltertable =
    CALCULATETABLE (
    VALUES ( Customers[UUID] ),
    CROSSFILTER ( 'Digital Campaigns'[SBLID], Customers[SBLID], BOTH )
    )
    RETURN
    CALCULATE (
    SUM ( 'Digital Events'[Payment] ),
    TREATAS ( UUIDfiltertable, 'Digital Events'[UUID] )
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi, 

    I am not sure if I understood your question correctly, but please check the below measures and the attached pbix file.

     

    Login Count =
    VAR UUIDfiltertable =
    CALCULATETABLE (
    VALUES ( Customers[UUID] ),
    CROSSFILTER ( 'Digital Campaigns'[SBLID], Customers[SBLID], BOTH )
    )
    RETURN
    CALCULATE (
    SUM ( 'Digital Events'[Login] ),
    TREATAS ( UUIDfiltertable, 'Digital Events'[UUID] )
    )
     
    Order Count =
    VAR UUIDfiltertable =
    CALCULATETABLE (
    VALUES ( Customers[UUID] ),
    CROSSFILTER ( 'Digital Campaigns'[SBLID], Customers[SBLID], BOTH )
    )
    RETURN
    CALCULATE (
    SUM ( 'Digital Events'[Orders] ),
    TREATAS ( UUIDfiltertable, 'Digital Events'[UUID] )
    )
     
    Payment Count =
    VAR UUIDfiltertable =
    CALCULATETABLE (
    VALUES ( Customers[UUID] ),
    CROSSFILTER ( 'Digital Campaigns'[SBLID], Customers[SBLID], BOTH )
    )
    RETURN
    CALCULATE (
    SUM ( 'Digital Events'[Payment] ),
    TREATAS ( UUIDfiltertable, 'Digital Events'[UUID] )
    )