DAX Userelationship function

Question

I have this issue with the Userelationship function in DAX. I have two Tables SalesFact and DimPromoSkus like this:

Diagram View.

The 2 tables in Data View are like this:

Data View

The 2 tables are linked from SkuCampaign1, SkuCampaign2, SkuCampaign3 in the SalesFact to the SkuCampaign from the DimPromoSkus.

So basically the goal of DimPromoSkus table is to indicate if the product was in campaign C1, C2 or C3. For example, the campaign C1 happened between days 1/1/2019 to 4/1/2019 but only SKUS A1 and A2 were sold on Promotion. A2 and A3 were off promo but sold during the campaign C1 period.

My goal is to calculate Promoted Sales on each Campaign C1, C2, and C3. Of course, this could be done with a lookup-column and then calculate the Sales where that key was found BUT I would like to use USERELATIONSHIP function.

So, to calculate PromoSales for campaign C1 my measure, in Theory, should be:

PromoSalesC1:=CALCULATE(SUM(SalesFact[Sales]);USERELATIONSHIP(SalesFact[SkuCampaign1];DimPromoSkus[SkuCampaign]))

The result obtained should be 25 but is returning me 155 which is the total sum of the rows so is not doing really nothing The result keeps giving 155 if I use SkuCampaign2 or SkuCampaign3 which are inactive relations but USERELATIONSHIP should activate this..

Any idea of what is going on or am I missing something? :) Thanks 

Answer 1

•  You need a way to measure for applying a filter context to the sum. You don't need to explicitly supply a value though. You can just simply pass in the DimPromoSkus table as the filter context.

PromoSalesC1 = CALCULATE( SUM(SalesFact[Sales]), DimPromoSkus, USERELATIONSHIP(SalesFact[SkuCampaign1], DimPromoSkus[SkuCampaign]) )

• Remember that the CALCULATE function can take tables as filter arguments. Here, we provide DimPromoSkus as the filter so that we don't sum any values that don't flow through the specified relationship. So, for example, since A3 C1 isn't in DimPromoSkus, the corresponding row in SalesFact doesn't get included in the sum.