USERELATIONSHIP apeears to null with Variables

[successken]

Here is an interesting one. I have been reworking my DAX to clean them up utilizing Variables. However, I have discovered (or not doing it correctly) that when I use a VAR in a Calculate that utilizes the USERELATIONSHIP DAX it ignores the DAX and defaults back to the active relationship.

Example-

$ of Gross Sales - Special =
VAR TypeFilter = FILTER( 'OrderItems' , 'OrderItems'[itemtype] = "S" )
VAR JobFilter = FILTER('Orders','Orders'[Type] = "Special" )
RETURN
CALCULATE( SUM( 'OrderItems'[Retail] ) , USERELATIONSHIP( 'OrderItems'[orderitemsdate] , 'Calendar'[Date] ) , TypeFilter , JobFilter )
vs.
CALCULATE( SUM( 'OrderItems'[Retail] ) , USERELATIONSHIP( 'OrderItems'[orderitemsdate] , 'Calendar'[Date] ) , 'OrderItems'[itemtype] = "S", 'Orders'[Type] = "Special")


The first returns incorrect vs the second that is correct. Am I doing something incorrect?

 

[Matt Allington]

The both return correct answers, but clearly one is not what you want. Variables are executed in the initial filter context. If you want them to use a different relationship, you will need to apply calculate to the variables too

 

[successken]

If you don't mind, how would you right the VAR to result in the 2nd answer?

 

[Ozeroth]

Hi successken

To get the same result using your structure with variables, you could write:

$ of Gross Sales - Special =
VAR TypeFilter =
    FILTER ( ALL ( 'OrderItems'[itemtype] ), 'OrderItems'[itemtype] = "S" )
VAR JobFilter =
    FILTER ( ALL ( 'Orders'[Type] ), 'Orders'[Type] = "Special" )
RETURN
    CALCULATE (
        SUM ( 'OrderItems'[Retail] ),
        USERELATIONSHIP ( 'OrderItems'[orderitemsdate], 'Calendar'[Date] ),
        TypeFilter,
        JobFilter
    )

A boolean filter argument for calculate like 'OrderItems'[itemtype] = "S" is a filter on the single column itemtype that ignores any existing filters. So it can be replicated with the FILTER ( ALL() ) pattern.

In this case, I'm not sure if variables would make any difference to performance.