topic Re: FILTER() makes USERELATIONSHIP() superfluous? in DAX Commands and Tips

FILTER() makes USERELATIONSHIP() superfluous?

tez — Thu, 03 Mar 2022 15:35:51 GMT

I have two tables (simplified):

Table A: StartDate, EndDate, ...

Table B (Datetable): Date, ...

Table A StartDate has an active relationship to Table B Date, Table A EndDate has an inactive relationship to Table B Date.

Additionally I have two calculated columns in Table B:

X = COUNTROWS(FILTER('Table A', 'Table A'[StartDate] = 'Table B'[Date]))

Y = COUNTROWS(FILTER('Table A', 'Table A'[EndDate] = 'Table B'[Date]))

It looks like the result is correct. However, I would have guessed that for the second statement (calculation of Y) I would have actually needed the USERELATIONSHIP() function; apparently that is not necessary. Can anyone judge or explain this?

Re: FILTER() makes USERELATIONSHIP() superfluous?

AlexisOlson — Thu, 03 Mar 2022 21:34:48 GMT

There are multiple ways to propagate filters in DAX. That doesn't mean one or the other is "superfluous".

The biggest difference is that FILTER is flexible but virtual whereas USERELATIONSHIP is more limited but physical.

I recommend this article which covers multiple filter propagation techniques and compares their performance:
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Re: FILTER() makes USERELATIONSHIP() superfluous?

tez — Fri, 04 Mar 2022 07:22:57 GMT

Thanks for the feedback. I worded that in a misleading way: I didn't mean that USERELATIONSHIP was generally redundant. I just had the impression that it would seemingly not be necessary in this context of the FILTER function. Is that correct?

Re: FILTER() makes USERELATIONSHIP() superfluous?

AlexisOlson — Fri, 04 Mar 2022 15:54:20 GMT

The relationships are not being used with the way your calculated columns are written. Since there isn't a CALCULATE transforming the row context to filter context, Table A isn't filtered by Table B except as you explicitly define the filter, so which relationship is active doesn't affect the calculation.

This wouldn't work (and couldn't be written) quite the same way if these were measures instead of calculated columns.