Forum Discussion

MightyMicrobe's avatar
MightyMicrobe
Icon for Helper II rankHelper 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 camp...
  • 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] )
    )
MightyMicrobe's avatar
MightyMicrobe
Icon for Helper II rankHelper II
4 years ago

This works, thank you very much!