Forum Discussion
Filter table based on another filtered table
Hi,
I have two tables:
Table1:
User | ID |
1 | |
2 | |
3 | |
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 “[email protected]” 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
3 Replies
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
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?
DAX is optimised for column-based calculations so it prefers you to have data like this in rows, rather than concatenated.
