topic Re: Relating Multiple Fact Tables with Different IDs in DAX Commands and Tips

Relating Multiple Fact Tables with Different IDs

MightyMicrobe — Thu, 17 Feb 2022 03:28:50 GMT

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!

Re: Relating Multiple Fact Tables with Different IDs

Jihwan_Kim — Thu, 17 Feb 2022 04:31:31 GMT

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

)

Re: Relating Multiple Fact Tables with Different IDs

MightyMicrobe — Thu, 17 Feb 2022 05:49:55 GMT

This works, thank you very much!