Measures on Unrelated Tables

[MightyMicrobe]

Hi all, I have a model with several fact tables that are only related to each other via a single dimension (Customers). 

A sample file is attached, but specifically: I need to find the total number of customers who called us within 7 days of clicking on a campaign. I can successfully implement this logic in a calculated column in the Customer table ("Calls After Campaign"), but struggling to do the same thing in a measure. 

I cannot change the relationships between the tables or implement any bridging tables in a model. 

SAMPLE FILE HERE

Thank you. 

[MightyMicrobe]

Thanks @Jihwan_Kim !
How do I apply this measure to get the total number of customers who called?

[Jihwan_Kim · Accepted solution]

Calls After Campaign Measure V2 =
SUMX (
VALUES ( Customers[Customer ID] ),
CALCULATE (
VAR LatestCampaignClick =
MAXX (
FILTER ( 'Campaign Activity', 'Campaign Activity'[Clicks] > 0 ),
'Campaign Activity'[Date]
)
VAR LatestCall =
MAXX ( 'Inbound Calls', 'Inbound Calls'[Date] )
VAR TimeGap =
DATEDIFF ( LatestCampaignClick, LatestCall, DAY )
VAR Result =
IF ( NOT ISBLANK ( TimeGap ) && TimeGap >= 0 && TimeGap <= 7, 1 )
RETURN
Result
)
)

[Jihwan_Kim]

Hi, 

Please check the below picture and the attached pbix file.

Calls After Campaign Measure =
--Latest Campaign Click
VAR LatestCampaignClick =
MAXX (
FILTER ( 'Campaign Activity', 'Campaign Activity'[Clicks] > 0 ),
'Campaign Activity'[Date]
) --Latest Call In
VAR LatestCall =
MAXX ( 'Inbound Calls', 'Inbound Calls'[Date] ) --Gap between events
VAR TimeGap =
DATEDIFF ( LatestCampaignClick, LatestCall, DAY )
VAR Result =
IF (
NOT ISBLANK ( TimeGap )
&& ( TimeGap >= 0
&& TimeGap <= 7 ),
"True",
"False"
)
RETURN
IF ( HASONEVALUE ( Customers[Customer ID] ), Result )