Relating Multiple Fact Tables with Different IDs

[MightyMicrobe]

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!

[MightyMicrobe]

This works, thank you very much!

[Jihwan_Kim · Accepted solution]

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

)