Exclude common values when joining two tables

[POSPOS]

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.

[j_ocean]

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.

[lbendlin]

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')

[POSPOS]

@lbendlin  - thank you for your response. I tried to implement the same DAX in the actual report and it does not emilimate the same records from the table. Please find details below :

Can you please suggest

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[POSPOS]

@lbendlin  - I tried to export data from the actual report and tried to replicate the issue. But the DAX you provided works fine in the sample file. I am trying to recreate a sample file with all the tables I have in the originial report and will share it. Thanks

[Anonymous]

Hi @POSPOS ,
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[POSPOS]

@Anonymous  - Thank you for reaching out.
There were issues with the DAX as records with same  data was showing up. As a workaround, we had to handle this differently by creating relationships between tables and then doing table2 difference table 1 with full data.