Forum Discussion
How to append tables that are being filtered by RLS
I have 3 identic tables being filtered by Row-Level Security DAX expressions. Each table has its own RLS coding.
For example:
RLS 1 - is filtering all the cases where [Column A] = "yellow" + [Column C] = "big" + USERPRINCIPALNAME()
RLS 2 - is filtering all the cases where [Column A] = "red" + [Column B] = "far" + [Column C] = "small" + USERPRINCIPALNAME()
RLS 3 - is filtering all the cases where [Column D] = "today" + USERPRINCIPALNAME()
The RLS is running smoothly. All the conditions are good!!
However, I'm struggling to find a way to dynamically append the three tables into a final table containing only the results of the RLS filters.
Can anyone help me to solve that puzzle?
*Important: The conditions used in the RLS (red, yellow, big, small, today) come from a live-connected Microsoft List in my data modeling.
*Important2: I cannot use the OR statement because I need a list with all results together, not one or the other.
I've found a solution for that puzzle.
1 - I kept the data model 1 and 2 without any RLS2 - Put the RLS 1 and 2 together with the RLS 3, and then added a UNION statement within the RLS to have all the results together.
*The reason for keeping the structures 1 and 2 was because the DIM tables and FACT tables were using different primary keys in their relations. It worked perfectly!
3 Replies
Syndicate_AdminAdministrator
Why dynamically? Why can't this be a calculated table? What's the purpose of that table ?
"live connected Microsoft List" - please elaborate. I am not aware of that option.
Dynamically because the RLS is using the USERPRINCIPALNAME as one of the multiple conditions within it.
I'm sharing a mock up of the data modelling so you can visually understand what I'm trying to do.I've found a solution for that puzzle.
1 - I kept the data model 1 and 2 without any RLS2 - Put the RLS 1 and 2 together with the RLS 3, and then added a UNION statement within the RLS to have all the results together.
*The reason for keeping the structures 1 and 2 was because the DIM tables and FACT tables were using different primary keys in their relations. It worked perfectly!