Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MightyMicrobe
Helper II
Helper II

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:

MightyMicrobe_0-1645068126106.png

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

MightyMicrobe_1-1645068451621.png

Thank you!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
MightyMicrobe
Helper II
Helper II

This works, thank you very much!

Jihwan_Kim
Super User
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.