RLS if-statement on USERPRINCIPALNAME

[Anonymous]

Hi!

In my table of employees, the columns are [email], [IsTeamlead] and [country]. 

If a non-teamlead employee logs in, RLS should be [email] = USERPRINCIPALNAME (only see information about himself).

If a teamlead employee logs in, d, he should see all employees in England,  [country] = "England". 

Is this if-statement possible in DAX?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

In Manage roles, you can create new roles and try this DAX pression to solve your problem:

IF (

    CALCULATE (

        COUNTROWS ( 'Table' ),

        FILTER ( 'Table', 'Table'[email] = USERPRINCIPALNAME () && [IsTeamlead] )

    ) > 0,

    [Country]

        = CALCULATE (

            MAX ( 'Table'[Country] ),

            FILTER ( 'Table', 'Table'[email] = USERPRINCIPALNAME () )

        ),

    [email] = USERPRINCIPALNAME ()

)

When you use View as roles, you will get the following result:

Here is the demo , please try it:

PBIX 

Best Regards,

Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Everything is possible in DAX 🙂 My guess for the RLS expression is:

'Table'[email] = USERPRINCIPALNAME() || COUNTROWS(FILTER(ALL('Table'); 'Table'[country] = EARLIER('Table'[country]) && 'Table'[email] = USERPRINCIPALNAME() && 'Table'[isteamlead]=TRUE())) > 0

[Anonymous]

Thank you for your suggestion.

Every row (employee) has [email]. Your solution is a type Option A || Option B. Will Option B ever be evaluated if Option A is true? 

[Anonymous]

The statement will evaluate once for each row in the table. But if the first part evaluates to TRUE the second part is not evaluated for that row (since all users should have access to the row that contains their own email, right?).

[Anonymous]

I tried your code, and it was as I expected. The first statement will evaluate true, since whenever someone logs in with the USERPRINCIPALNAME(), it will match a row. I believe the second part is never evaluated. 

[Anonymous]

I tried it (on my own table with other columns) and it looked like it worked for me. You must understand that when the user logs on the statement will be evaluated once for each row in the table. If there is a row with a non-matching email column, the first part of the expression will ecaluate to FALSE. The second part will then look for rows where email is the same as USERPRINCIPLENAME, IsTeamLead = TRUE and the same value in the country column as the current row being evaluated. If a row like that exists (COUNTROWS returns > 0) the expression will return TRUE.

[amitchandak]

Refer if these can help

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

https://community.powerbi.com/t5/Desktop/RLS-at-the-Team-Level-Data-Model-and-DAX/m-p/911797/highlig...

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Marcin8888]

Are You here only to post links which are 99% irrelevant? Sorry, but it starts to annoys me. No solutions, just spamming with links which are 99% useless for more advanced problema which people ask here