Forum Discussion
Filter table based on another filtered table
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.