Need Help

You can create a Campaign Sales measure by building a virtual relationship from the disconnected CampaignMapping table to Sales using TREATAS on both CustomerID and ProductCategory.​

1. Base measure

2. Campaign Sales measure using TREATAS

3. Explanation 

• VALUES(CampaignMapping[CustomerID]) returns the distinct customers for the currently selected campaign(s).​

• The first TREATAS tells the engine to treat those values as if they came from Customers[CustomerID], so the row context on Sales is filtered by matching customers.​

• The second TREATAS does the same for ProductCategory → Products[Category], so only Sales rows where both customer and category match the campaign remain.​

• Because TREATAS creates a virtual relationship instead of a physical one, it works even though the CampaignMapping granularity (Campaign–Customer–Category) does not match the Sales fact granularity.

Please check the formula below:

Campaign Sales =
VAR SelectedCustomers =
    VALUES ( CampaignMapping[CustomerID] )

VAR SelectedCategories =
    VALUES ( CampaignMapping[ProductCategory] )

RETURN
CALCULATE (
    [Total Sales],
    TREATAS ( SelectedCustomers, Customers[CustomerID] ),
    TREATAS ( SelectedCategories, Products[ProductCategory] )
)