Need Help

[Ar_Sh]

I have a disconnected CampaignMapping table with columns (CampaignName, CustomerID, ProductCategory) where the granularity is a many-to-many combination, while Sales is at the Order/Product level related only to standard dimensions (Customers, Products, Date).

This granularity mismatch makes physical relationships impossible. How can I write a Campaign Sales measure in DAX so that selecting CampaignName on a slicer virtually filters Sales by both CustomerID and ProductCategory matches?

[Shubham_rai955 · Accepted solution]

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

Sales Amount := SUM ( Sales[Amount] )

2. Campaign Sales measure using TREATAS

Campaign Sales := CALCULATE ( [Sales Amount], TREATAS ( VALUES ( CampaignMapping[CustomerID] ), Customers[CustomerID] ), TREATAS ( VALUES ( CampaignMapping[ProductCategory] ), Products[Category] ) )

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.

[cengizhanarslan · Accepted solution]

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

[anilelmastasi]

My advice is creating a CampaignBridge table at the same grain as Sales.

[Shubham_rai955 · Accepted solution]

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

Sales Amount := SUM ( Sales[Amount] )

2. Campaign Sales measure using TREATAS

Campaign Sales := CALCULATE ( [Sales Amount], TREATAS ( VALUES ( CampaignMapping[CustomerID] ), Customers[CustomerID] ), TREATAS ( VALUES ( CampaignMapping[ProductCategory] ), Products[Category] ) )

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.