Userelationship with filter not displaying correctly

DAX DAX Calculations
6

Hello @ysherriff,

Firstly, in this case, since you wanted to see the same results, you were not required to use the “FILTER()” function inside the filter argument of “CALCULATE()” since the expression which you wrote inside the “FILTER()” function is this -

'Contacts Table'[Became an MQL Date] >= DATE( 2022, 1 , 1 )

So the measure could’ve been simply written as -

# of MQLs without Filter =
CALCULATE( COUNTROWS( 'Contacts Table' ) ,
     'Contacts Table'[Became an MQL Date] >= DATE( 2022 , 1 , 1 ) ) ,
      USERELATIONSHIP( 'Contacts Table'[Became an MQL Date] , 'Date'[Date] ) )

Because in this case, at the back-end of the engine, it would’ve evaluated the measure in the same context as this one -

# of MQLs without Filter =
CALCULATE( COUNTROWS( 'Contacts Table' ) ,
        FILTER( ALL( 'Contacts Table'[Became an MQL Date] ) ,
                'Contacts Table'[Became an MQL Date] >= DATE( 2022 , 1 , 1 ) ) ,
        USERELATIONSHIP( 'Contacts Table'[Became an MQL Date] , 'Date'[Date] ) )

Now, coming to the usage of “ALL”. Since you wrote an expression like this -

# of MQL with Filter =
VAR _Become_an_MQL_Date =
FILTER( 'Contacts Table' ,
   'Contacts Table'[Became an MQL Date] >= DATE( 2022 , 01 , 01 ) )

RETURN
CALCULATE( COUNTROWS( 'Contacts Table' ) ,
    _Become_an_MQL_Date ,
    USERELATIONSHIP( 'Contacts Table'[Became an MQL Date] , 'Date'[Date] ) )

You explicitly removed the context from the column i.e., non-usage of “ALL()”. Because in this case, when “ALL()” is used, it’s used in the form of CALCULATE modifier and not simply/only as a table function. So by using “ALL()” in the form of this -

ALL( 'Contacts Table'[Became an MQL Date] )

It retains all the context of that particular column. So the formula would’ve been like this -

# of MQL with Filter =
VAR _Become_an_MQL_Date =
FILTER( ALL( 'Contacts Table'[Became an MQL Date] ) ,
    'Contacts Table'[Became an MQL Date] >= DATE( 2022 , 01 , 01 ) )

RETURN
CALCULATE( COUNTROWS( 'Contacts Table' ) ,
    _Become_an_MQL_Date ,
    USERELATIONSHIP( 'Contacts Table'[Became an MQL Date] , 'Date'[Date] ) )

Lastly, I won’t say it as a best practice that one needs to use “ALL” because if we eliminate the usage of “FILTER()” function inside the filter argument then by default there’ll always be a presence of “ALL()” at the back-end.

So for example, if you’ve slicers on your report, then we explicitly mention the use of “ALLSELECTED()” that’s because we want the figures to change in accordance with the selection made into the slicer. But if that’s not the case, then “ALL()” will be predicated by default.

The usage of these functions i.e., context modifiers depend upon the context that you’re trying to insert and the results that you’re trying to achieve.

Thanks and Warm Regards,
Harsh

2 Likes