I have this issue with 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 selled on Promotion. A2 and A3 were off promo but sold during 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 lookupcolumn 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

2 Answers

@StelioK is correct that you need a way for the measure to apply a filter context to the sum. You don't need to explicitly supply a value though. You can 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.

The DAX is doing exactly what it is intended to do. By calling the USERELATIONSHIP function you are 'activating' the relationship but you still need to provide a filter context either through a slicer or in the DAX itself.

Use the code below and see if it helps

PromoSalesC1 := CALCULATE ( SUM ( SalesFact[Sales] ), USERELATIONSHIP ( SalesFact[SkuCampaign1], DimPromoSkus[SkuCampaign] ), DimPromoSkus[Campaign] = 'C1' ) 

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobG5paGZ%2Fd36OnZixZaWosrOxy5qroqeeqLWqvIyfrKebpJ68rw%3D%3D