Forum Discussion
How to Replicate ccomplicated Dynamic Row Level Security from Tableau to Power BI
Thanks for the reply from _AAndrade , please allow me to provide another insight:
Hi cruncher ,
Here are the steps you can follow:
1. Create calculated columns in both tables to combine [Region], [Vertical], [Business Unit].
Table1_Con =
[Region]&"-"&[Vertical]&"-"&[Business Unit]Table2_Con =
[Region]&"-"&[Vertical]&"-"&[Business Unit]
2. Modeling – Manage Roles – Filter data -- Place the following formula .
When applying to multiple users, you can replace " bicar " with USERPRINCIPALNAME().
var _name="bicar"
var _user1=
SELECTCOLUMNS(
FILTER(ALL('Table2'),
CONTAINSSTRING('Table2'[USERS LIST 1],_name)=TRUE()),"user1",[Table2_Con])
var _user2=
SELECTCOLUMNS(
FILTER(ALL('Table2'),
CONTAINSSTRING('Table2'[USERS LIST 2],_name)=TRUE()),"user2",[Table2_Con])
var _user26=
SELECTCOLUMNS(
FILTER(ALL('Table2'),
CONTAINSSTRING('Table2'[USERS LIST 26],_name)=TRUE()),"user3",[Table2_Con])
RETURN
IF(
'Table1'[Table1_Con] in _user1 ||
'Table1'[Table1_Con] in _user2 ||
'Table1'[Table1_Con] in _user26,
TRUE(),FALSE())
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- cruncher1 year agoHelper II
Thank you Anonymous for detailed explanation. Will go through it and let you know how it performs with real data with 100 Millions row in transactions table.
QQ:
1. Do I have to create one to many relationship from users to transaction table between these two tables on basis on newly created concatenated columns.
2. Right now you created 3 variables based on 3 users list columns. Do i have to created 26 variables for all 26 user list columns. Can it be done it in optimized way by looking at the ascii value of first character of logged in user so that we already know in which column out of 26 we have to search the userprincipal name ?