Forum Discussion
FILTER() makes USERELATIONSHIP() superfluous?
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?
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.
3 Replies
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/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?
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.
