Filter table based on another filtered table

[biAD]

Hi,

I have two tables:

Table1:

User	ID
aaaa@contoso.com	1
bbbb@contoso.com	2
cccc@contoso.com	3
dddd@contoso.com	4

Table2:

Locations	Enabled Users ID
Location 1	;1;3;
Location 2	;2;4;
Location 3	;1;3;4;
Location 4	;2;3;

With Row Level security, Table1 is filtered based on logged user, so logged user can see only one row in Table1

For example i am user “cccc@contoso.com” and when i open my report i can see only third row in Table1.

Ok, but now i want to add Table2 in the same report and i want to see only Location1, Location 3 and Location 4 because my User ID is 3 in Table1

Important: i use Direct Query to connect to tables

Regards

[Phil_Seamark]

HI @biAD

Ideally, you should pivot (extract) your location data so you have the information over rows as per the example below.  Yes, it is possible to do this in DAX but will be fiddly, and if you are using RLS for good reason, you don't want to make an error and reveal data to users they shouldn't see.  If you format your Table2 as suggested and create a standard relationship between the two tables on ID, then it will be a much simpler model to maintain.

eg.

Table 2

Locations , ID
--------------------
Location 1 , 1
Location 1 , 3
Location 2 , 2
Location 2 , 4
Location 3 , 1
Location 3 , 3
Location 3 , 4
Location 4 , 2
Location 4 , 3

[biAD]

Thank you for help

Yes, I did so in other reports with small tables

But in this case Table1 contains 40 users and Table2 contains 2 millions of rows. After relationship I have about 30 millions rows

That's why I created a function to concatenate users for every row of Table2

So how can I do it in DAX?

Or what is the correct approach in this scenario?

[Phil_Seamark]

DAX is optimised for column-based calculations so it prefers you to have data like this in rows, rather than concatenated.