Forum Discussion
Security: RLS & columns
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
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
10 Replies
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()
)
)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.
