Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a case where we have a colum what tells if the person has a special rights to read data with a certain level or not.
And we have 6 different levels of data in our hierarchy. Example data:
Userid | level1 | level2 | level3 | level4 | level5 | level6 | RIGHTS |
1 | A | AA | AAA | AAAA | AAAAA | AAAAAA | 1 |
2 | A | AA | AAB | AABA | AABAA | AABAAA | 2 |
3 | A | AB | ABA | ABAA | ABAAA | ABAAAA |
Userid 1 would see all that have the same level1 that he does, so in this cse userid1 would see all 3 lines
Userid 2 would see the second level data and all that are having the same level2 value than he, so he would see 2 lines.
Userid 3 does not have any special rights and this would be proceed to normal hierarchy level with path.
I tried to create a RLS DAX, but for some reason I don't see any data. And Power BI is not giving me any error feedback.
Do you know what is wrong, or should I look this on a completely different angle?
SWITCH(LOOKUPVALUE(RLSTable[RIGHTS],'RLSTable'[User],USERPRINCIPALNAME()),1,
LOOKUPVALUE(RLSTable[level1],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level1],2,
LOOKUPVALUE(RLSTable[level2],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level2],3,
LOOKUPVALUE(RLSTable[level3],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level3],4,
LOOKUPVALUE(RLSTable[level4],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level4],5,
LOOKUPVALUE(RLSTable[level5],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level5],6,
LOOKUPVALUE(RLSTable[level6],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level6]
))
Any help is much appreciated!
edit:
And there is a f_data table which has the same level-columns with extra data, to summarize values.
Cheers,
Niko
Solved! Go to Solution.
Hello @v-jiascu-msft,
Yeah I know it would be good practice to share the files always, but they are not so anonymized yet.
Actually when I started to make it more random data, it started to work for some reason ... ?
Alltho' I changed my DAX a little bit to this:
IF(maxX (Filter(RLSTable, RLSTable[User]=USERPRINCIPALNAME()) , RLSTable[RIGHTS]) = 1, LOOKUPVALUE(RLSTable[level1],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level1], IF(maxX (Filter(RLSTable, RLSTable[User]=USERPRINCIPALNAME()) , RLSTable[RIGHTS]) = 4, LOOKUPVALUE(RLSTable[level4],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level4]
I have to check this more deeply if it actually now works. And I try to anonymize the data also for others to use.
Cheers,
Niko
You need to create a table like the below
Where the last column is a calculated column with this formula:
UserPath = PATH( Users[UserID],Users[Manager] )
And then the Row Level Security Formula is:
PATHCONTAINS(
Users[UserPath],
LOOKUPVALUE(
Users[UserID],
Users[Email],
USERPRINCIPALNAME()
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello @LivioLanzo,
Thank you for your answer, but this quite isn't the solution I am looking for. I already have this kind of a hierarchy path solution in place for the current hierarchy RLS. But the case I had is related to special cases that overrides the normal path-based RLS. So if in the special case column there is no number, it will use the path-based solution. If there is a number on the special case column, it will get the value from the right column and use the right column from the other.
IDK if I need to create "special case" column for each of the unit_level -columns. Or should I create a summarized table that somehow switches all the separate columns into one. I will post here if I found any solutions on my own.
Hi @nisuomi
what should happen then when you have RIGHTS 1 and 2 ?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo,
The DAX should look the RIGHTS column for access rigth level, for 1 it meas column level1 and for 2 it means column level2.
If it is empty, it will use the PATH() solution, which isnt present in this sample that I provided, but there is userid + managerid columns also for that and uses that solution that you presented on your first reply.
But in this case, we want to override this security for few users witht he RIGHTS column and from there we should be able to check the needed level access and then find from the needed level column the value of the access rights. For example RIGHTS 1 for user 1 it would find the column value from level1 and it would be A. So, this userid1 would see all the data who have the value A on the column level1 - and so on.
We have the same columns also in the fact table, level1, level2 and so on. And I have tried to do a lookup between these two table. The DAX did not say any erros, but I don't see any data, so something is wrong.
Did you get anything out from this? 😄 I can try to make up some dummy pbix ...
edit.
For RIGHTS 2 for userid 2 it would search column level2 and find the value from there, which would be AA.
The userid2 would see all the data who have the level2 and value AA on there. So userid2 would see userid1 and userid2 data.
Cheers,
Niko
Does your table look like the below? try the below RLS rule:
=
SWITCH (
LOOKUPVALUE ( Users[Rights], Users[Email], USERPRINCIPALNAME () ),
1, Users[Level1]
= LOOKUPVALUE ( Users[Level1], Users[Email], USERPRINCIPALNAME () ),
2, Users[Level2]
= LOOKUPVALUE ( Users[Level2], Users[Email], USERPRINCIPALNAME () ),
PATHCONTAINS (
Users[Path],
LOOKUPVALUE ( Users[UserID], Users[Email], USERPRINCIPALNAME () )
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo,
Not quite, but almost. All the levels had some information in those columns, there are no empty values. It is hierarchy so do speak as well.
As seen on the image below, where is the access table (not affected by the RLS)
usersaccounts 1,2,3 all have special_case on them, but not the 4.
All of these will have the rights determined by that unit_level_X that they have been assigned on the special_case column and what calue is on their row on that column.
So as example user1@sample.com has special_case as 4. What happens:
Did this make this situation more clear :)?
It actually got worked with the code I proved earlier, I got a little help from one site. Which I have already lost 'cause I cleared system memory etc. today... Maybe have to find it again and I can link it here.
Cheers,
Niko
as far as I can see then you just need to account for the extra levels in my previous formula, because I only accounted for two
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi Niko,
Please share a dummy pbix file. You can upload it to the cloud drive like OneDrive, GoogleDrive, then share the download link here.
Best Regards,
Dale
Hello @v-jiascu-msft,
Yeah I know it would be good practice to share the files always, but they are not so anonymized yet.
Actually when I started to make it more random data, it started to work for some reason ... ?
Alltho' I changed my DAX a little bit to this:
IF(maxX (Filter(RLSTable, RLSTable[User]=USERPRINCIPALNAME()) , RLSTable[RIGHTS]) = 1, LOOKUPVALUE(RLSTable[level1],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level1], IF(maxX (Filter(RLSTable, RLSTable[User]=USERPRINCIPALNAME()) , RLSTable[RIGHTS]) = 4, LOOKUPVALUE(RLSTable[level4],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level4]
I have to check this more deeply if it actually now works. And I try to anonymize the data also for others to use.
Cheers,
Niko
Thanks for sharing, Niko. You can post further questions anytime.
Best Regards,
Dale
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |