Filter out using USERPRINCIPALNAME()

[harshagraj]

Hello all,

Please see the below data. When i publish the below view in the service if i login with "harsha@ab.com" i dont want to see 1st record and If an user login with "gururaj@ab.com" we dont need 2nd record.

Name	Email	Scores
Harsha	harsha@ab.com	1
Gururaj	gururaj@ab.com	2
King	king@ab.com	3

I tried creating a column as 
If(email=USERPRINCIPALNAME(),1,0)

But since i connected my dashboard as directquery i get an error.

Please help me to filter out the data.

[Anonymous]

HI @harshagraj,

You can refer to the following link to know how to set a dynamic RLS based on username:

Solved: RLS with UserName() - Microsoft Power BI Community

Notice: please create a measure with USERNAME and USERPRINCIPALNAME functions to confirm which one is suitable for your user table information.

Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax - SPGuides

Regards,

Xiaoxin Sheng

[AlexisOlson · Accepted solution]

You can create an RLS role with the rule

[email] <> USERPRINCIPALNAME()

Once you have created a role, anyone you want to view the report must be assigned to a role. This is the intended functionality and there isn't a way to avoid assigning roles if you want to use RLS.

See the documentation and prior discussion here:
https://community.powerbi.com/t5/Desktop/Row-Level-Security-All-Users/m-p/253154

[amitchandak]

@harshagraj , You can use USERPRINCIPALNAME only in a measure. I doubt you can use that in a column

[harshagraj]

Hello can someone help on this topic
@Greg_Deckler @AlexisOlson @PhilipTreacy @v-zhangti 

[harshagraj]

Also if i have to create a measure then how do i aggregate Name column?

If(???(Name)=USERPRINCIPALNAME(),1,0)

Kindly help.

[harshagraj]

Thanks for the reply but how do i solve this problem?

I also tried creating a role like NAME<>LOOKUP(NAME,EMAIL,USERPRINCIPALNAME())

This is also not working properly because i dont have a speicific AD gropu to assign.