How to Replicate ccomplicated Dynamic Row Level Security from Tableau to Power BI

[cruncher]

We are currently using Tableau and exploring things to switch to Power BI. The major problem we are facing to replicate the row level security model from Tableau to Power BI.

We have a sales transaction table in format below.

Region	Vertical	Business Unit	Quarter	Sales
East	Vert_1	BU1	Q1 24	3529
East	Vert_1	BU2	Q1 24	3702
East	Vert_1	BU3	Q1 24	3182
East	Vert_2	BU1	Q1 24	4934
East	Vert_3	BU2	Q1 24	1257
West	Vert_1	BU1	Q1 24	545
West	Vert_1	BU2	Q1 24	3602
West	Vert_1	BU3	Q1 24	2233
West	Vert_2	BU1	Q1 24	3485
West	Vert_3	BU2	Q1 24	1995
East	Vert_1	BU1	Q2 24	7392
East	Vert_1	BU2	Q2 24	8531
East	Vert_1	BU3	Q2 24	3379
East	Vert_2	BU1	Q2 24	1462
East	Vert_3	BU2	Q2 24	5004
West	Vert_1	BU1	Q2 24	2872
West	Vert_1	BU2	Q2 24	255
West	Vert_1	BU3	Q2 24	5231
West	Vert_2	BU1	Q2 24	9632
West	Vert_3	BU2	Q2 24	7786

We have User Entitlement table in below format.

Region	Vertical	Business Unit	USERS LIST 1	USERS LIST 2	USERS LIST 26
East	Vert_1	BU1	anna|arvy|anton	bicar|brandy	zeptr
East	Vert_1	BU2	anna|arvy|anton	brandy	zeptr
East	Vert_1	BU3	anna|arvy|anton	brandy	zeptr
East	Vert_2	BU1	arvy|anton	bicar|brandy	zeptr
East	Vert_3	BU2	arvy|anton	brandy	zeptr
West	Vert_1	BU1	anna|anton	bicar|brandy	zene
West	Vert_1	BU2	anna|anton	brew|brandy	zene
West	Vert_1	BU3	anna|anton	brandy	zene
West	Vert_2	BU1	anton	bicar|brew|brandy	zene
West	Vert_3	BU2	anton	brew|brandy	zene

Row level security is  based on Region, Vertical and Businees Unit.

For example:

a. anton and brandy can see all region, Verticals and BU

b. zeptr and Zene can see all verticals and Business Units of their respective Region.

c. bicar can see only BU1 from both the Regions

d. anna can access all BU and regions but only with Vertical 1

THere are 26 User List columns and usernames are available in one of the 26 columns based on first alphabet character. In tableau we are joining this entitlement table with transactions table and filtering the data based on user logged in and his first alphabet character so that his userid can be searched in one of the 26 user list columns.

There are 10000 users in our org and entitlement table contains 10 million unique values of combination of Region, Verticals and Business Units. If we convert the entitlement table from concatenated values to individuals rows so that each user has their respective row for each Region , Vertical and BU then data will explode to Hundred of Millions or even billions rows.

How to approach this Power BI so that there won't be any impact on performance and security will be dynamic ?

Many thanks in advance for reading all the way .

[Anonymous]

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

[cruncher]

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 ?

[_AAndrade]

Hi @cruncher,

I think you could find a solution for your issue in on of this links:
https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multipl...

https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multipl...

Reza has a lot of a good blog post and videos talking about a lot of type of RLS. I think this two links could be match your needs.

[cruncher]

Thank you @_AAndrade . Give me some time. I will go through these links and let you know if that helps