topic Re: Need Help in DAX Commands and Tips

Need Help

Ar_Sh — Fri, 19 Dec 2025 10:56:20 GMT

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?

Re: Need Help

Shubham_rai955 — Fri, 19 Dec 2025 10:58:35 GMT

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.

Re: Need Help

anilelmastasi — Fri, 19 Dec 2025 11:21:38 GMT

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

Re: Need Help

cengizhanarslan — Fri, 19 Dec 2025 11:23:22 GMT

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