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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors