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 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] )
    )
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] )
)