DAX Userelationship function

Question

I have this issue with the Userelationship function in DAX. I have two Tables. The 2 tables are linked from SkuCampaign1, SkuCampaign2, SkuCampaign3 in the SalesFact to the SkuCampaign from the DimPromoSkus.

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 the USERELATIONSHIP function

Can someone tell me how to calculate that?

Answer 1

 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.