cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tez
Resolver I
Resolver I

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?

1 ACCEPTED SOLUTION

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.

View solution in original post

3 REPLIES 3
tez
Resolver I
Resolver I

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.

AlexisOlson
Super User
Super User

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/

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors