Forum Discussion
Exclude common values when joining two tables
Hi All,
I have two tables with Row level security applied on the table 1 based on user.
Table 1:
I have Table 2 as below :
Table 2:
The requirement is to show all the divisions from Table 2 based on the user's department from table 1.
Eg: The result should be as below :
For this, I have joined Table 1 and Table 2 based on departments.
Now, the next step is to exlude the common user-dept-div combinations in the final output.
Eg: In Table 1 - User 1 falls in Dept 1 and Div 1, and he falls in the same dept 1 and div 1 in the second table, hence this has to be excluded in the final outout.
Can someone please suggest how this can be achieved? Same file is available here.
7 Replies
I would strongly recommend you do all this further upstream. With RLS involved performance will be a critical factor, but the data is immutable so there is no reason to do this in DAX.
If you insist -
Table 3 = EXCEPT ( SELECTCOLUMNS( SUMMARIZECOLUMNS( 'Table 1'[User], 'Table 2'[Dept], 'Table 2'[Div], "CountRowsTable_2", COUNTROWS('Table 2') ), [User], [Dept], [Div] ) ,'Table 1')Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Do the join in Power Query if you aren't already, use a conditional column to identify the rows to drop, and either filter them out or apply whatever necessary logic to your RLS group rules.
