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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

RLS if-statement on USERPRINCIPALNAME

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?

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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:

RLS1.png

 

RLS2.png

 

RLS3.png

 

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.

View solution in original post

8 REPLIES 8
v-yingjl
Community Support
Community Support

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:

RLS1.png

 

RLS2.png

 

RLS3.png

 

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!