Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nisuomi
Resolver I
Resolver I

Dynamic Row Level Security with Two path?

Hello,

 

I am quite new in the world of Row Level Security in Power BI. I have managed to create few scenarios that work just fine, but now we have a problem where we should somehow go around the current RLS if the user has something on the other table as well.

 

Scenario: Dynamic Azure AD is used with USERPRINCIPALNAME() to get user's email address, we have excel-where we have the rights that needs to be given to the user. This uses the basic path() and hierarchy level RLS basics that are all over the Internet on every tutorial you can find about RLS. I have even made a separate if() clause where we can override the current RLS if there is a special case marked - there is a separate QA about this on the board. Now we have a situation, where we would need to add again a new layer to the Row Level Security: If the user has a certain client assigned to him\her, the user needs to see all the data about the client - no matter the current path things. BUT, it should also take into notice the current PATH() & hierarchy RLS, so that can't be ditched. In a way, these two needs to be working together side by side.. 

 

I have managed to create a solution on my local version where this does work - but the datamodel is a bit different on the real world scenario. On my sample data, I have created a separate access table for both path() level and to this client level. And from factual table I lookup the both values with OR-clause. And everything works like a charm. The problem comes in the real world scenario, if I try to do that same - I get an error message when trying to move from dimensional user-table (which is the access granter table in this case) to factual table.  The error is related to the earlier QA (which is linked previously), where I created the search with if maxx to the dimensional table, if those are not found in the fact table - i guess this wont work at all?

 

Is there any similiar solutions you guys that have used or faced in the situation where there is so to say "two" different RLS paths that should be working together. IDK if I should create a table with unactive relations to the factual tables or something... I guess the OR-clause is the major thing here which should be do the trick. I can try to make up some demo pbix later if I have the time.

 

edit.

Yeah and the errormessage if I try the RLS DAX on factual table:

"A single value for column 'unit_level_6' in table '(dim) user' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

This same DAX RLS works fine on the exact DIM user table. And on the local demo version, it works fine on the factual table.

 

1 ACCEPTED SOLUTION

Hi all,

 

I found a solution that worked for me, it was actually using the script that I earlier did put here but did not work as is straigth - the CONTAINS() one. I used the earlier solution here as well and comined those two solutions I got it working. And it seems at least for this point that I have tested the case, seems to be working fine. Have to add the OR-clause yet there and see how it works with the other path. But this code snippet worked with the factual table which does not have the U1,U2,U3 levels itself and checks the needed security from the dimensional table.

 

SWITCH(maxX (Filter(SecurityTable, SecurityTable[UserAccount]=USERPRINCIPALNAME()) , SecurityTable[Special_Case]) , 
1,
CONTAINS(
SecurityTable,
SecurityTable[unit_level_1],
maxX(SecurityTable, LOOKUPVALUE(SecurityTable[unit_level_1],'SecurityTable'[UserAccount],USERPRINCIPALNAME())),
SecurityTable[UserID],
FIRSTNONBLANK('(fact) Hours(3)'[User ID],1)),
2,
CONTAINS(
SecurityTable,
SecurityTable[unit_level_2],
maxX(SecurityTable, LOOKUPVALUE(SecurityTable[unit_level_2],'SecurityTable'[UserAccount],USERPRINCIPALNAME())),
SecurityTable[UserID],
FIRSTNONBLANK('(fact) Hours(3)'[User ID],1)),
3,
CONTAINS(
SecurityTable,
SecurityTable[unit_level_3],
maxX(SecurityTable, LOOKUPVALUE(SecurityTable[unit_level_3],'SecurityTable'[UserAccount],USERPRINCIPALNAME())),
SecurityTable[UserID],
FIRSTNONBLANK('(fact) Hours(3)'[User ID],1)),
FALSE())

I will edit or post a new reply if the OR-clause works also with this code. At least I got the path() thing going on here as well, if the user does not have the special_case on the column.

 

 

edit. 

Seems to be working witht he OR-clause as well, from where we can get the client level bypass for RLS as well. Superb!

 

 

 

Have a good one!

Niko

View solution in original post

5 REPLIES 5
nisuomi
Resolver I
Resolver I

I have now trying to sample this on paper how to get it working and I somehow need to fetch information from the dimensional table(s) to the factual table.

 

For example if I have the following structure of dimensional securitytable:

 

IDSpecialCaseU1U2U3email
11AAAAAAu1@sample.com
22AAAAAAu2@sample.com
3 AAAAABu3@sample.com
4 AABABAu4@sample.com


ID1 should see all the U1's with the value A, so ID's 1,2,3,4

ID2 should see all the U2's with the value AA, so ID's 1,2,3

ID3 should see only ID3

ID4 should see only ID4

 

When the user logs in with the AD login, it will fetch the corresponding row. 

If the user1@sample.com will login, the DAX would go and :

1) Which user? USERPRINCIPALNAME()

2) Does the user have SpecialCase?

3) Which SpecialCase is the number

4) Fetch the corresponding value from the U-column, which the SpecialCase is pointing to (e.g. 1 = U1, 2 = U2.. )

5) Which ID's are included within the U-column value? (e.g. A = ID's 1,2,3,4, AA = ID's 1,2,3)

6) Show the data accordingly to the logged in user (e.g. for u1@sample.com the data would show all)

 

This RLS would be on the factual table, 'cause I need to fetch other RLS also from the client level which would come as a OR-clause. That table would look something like this:

 

emailclient
u1@sample.com1
u1@sample.com2
u1@sample.com3
u2@sample.com1
u3@sample.com2
u4@sample.com3
u4@sample.com4

 

With this table it would add more visibility for example to the users u4@sample.com if their normal credentials does not have enough power to show other stuff. But they own these clients and everything related to these clients would be shown on the factual table as well. So that why OR-clause.

 

I thought that CONTAINS() would be nice DAX to use here, but only problem was that the 4th statement did not accept anything else than a hardcoded dimension - VALUES() did not help, or maybe I did something wrong. The following code was my idea to get this work on the step1 when fetching credentials from dimensional securitytable to the factual table - only problem is that the 4th and 5th DAX lines are inverted wronly here to get it working... Those should be kinda swapped around but it was not possible. Now I have to find if there is another solution for this check.

 

CONTAINS(
'SecurityTable',
 
SecurityTable[UserAccount],

USERPRINCIPALNAME(),

'SecurityTable'[unit_level_5],

LOOKUPVALUE('SecurityTable'[unit_level_5],'SecurityTable'[UserAccount],USERPRINCIPALNAME())

)

This is my datamodel on the test:

rls1.PNG

 

On the fact hours and fact hours 2, it is easy to get working cause there are the unit_level's present.

But the problem comes, if there is no unit_level's and the user should see still the data - case fact hours3.

 

 

Any help/ideas are appreciated. Smiley Tongue

 

 

 

Cheers,

Niko

Hi all,

 

I found a solution that worked for me, it was actually using the script that I earlier did put here but did not work as is straigth - the CONTAINS() one. I used the earlier solution here as well and comined those two solutions I got it working. And it seems at least for this point that I have tested the case, seems to be working fine. Have to add the OR-clause yet there and see how it works with the other path. But this code snippet worked with the factual table which does not have the U1,U2,U3 levels itself and checks the needed security from the dimensional table.

 

SWITCH(maxX (Filter(SecurityTable, SecurityTable[UserAccount]=USERPRINCIPALNAME()) , SecurityTable[Special_Case]) , 
1,
CONTAINS(
SecurityTable,
SecurityTable[unit_level_1],
maxX(SecurityTable, LOOKUPVALUE(SecurityTable[unit_level_1],'SecurityTable'[UserAccount],USERPRINCIPALNAME())),
SecurityTable[UserID],
FIRSTNONBLANK('(fact) Hours(3)'[User ID],1)),
2,
CONTAINS(
SecurityTable,
SecurityTable[unit_level_2],
maxX(SecurityTable, LOOKUPVALUE(SecurityTable[unit_level_2],'SecurityTable'[UserAccount],USERPRINCIPALNAME())),
SecurityTable[UserID],
FIRSTNONBLANK('(fact) Hours(3)'[User ID],1)),
3,
CONTAINS(
SecurityTable,
SecurityTable[unit_level_3],
maxX(SecurityTable, LOOKUPVALUE(SecurityTable[unit_level_3],'SecurityTable'[UserAccount],USERPRINCIPALNAME())),
SecurityTable[UserID],
FIRSTNONBLANK('(fact) Hours(3)'[User ID],1)),
FALSE())

I will edit or post a new reply if the OR-clause works also with this code. At least I got the path() thing going on here as well, if the user does not have the special_case on the column.

 

 

edit. 

Seems to be working witht he OR-clause as well, from where we can get the client level bypass for RLS as well. Superb!

 

 

 

Have a good one!

Niko

Hi,

 

Now I have created a new problem with this. Let's say that we have a (dim) employee and (dim) client tables and those are used for filtering data. But I can't put the same RLS code to these tables, 'cause it will filter the factual tables that are connected to these tables. Should I unconnect the dimensional tables from factual tables or make the relationships inactive? I still need to use these tables as a filters and such. 

 

Is there any other workarounds available for two way RLS? I have tried to Google, but with very thin outcomes.

 

 

 

Cheers,

Niko

v-danhe-msft
Employee
Employee

Hi @nisuomi,

Based on my research, you could refer to below blog that may help you to create dynamic RLS rules with different paths:

http://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-danhe-msft,

 

Thanks for the link, I will check it out.

 

 

 

Cheers,

Niko

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.