Only show filtered data for subordinates

[Saniat]

I have a table like this:

I now want to generate the Show/NoShow field dynamically based on the user logged in.

If for example, user 405 logs in, he is present in all the username1_username2 field, hence he will get Show for all the rows, and will be able to see all the records.
If user 102 logs in, he can only see his own record, as his username is not present in any other record than himself.

Question is how do I generate this Show/NoShow field/measure ?

Many thanks

[v-yanjiang-msft · Accepted solution]

Hi @Saniat ,

According to your description, here's my solution. 

Create this DAX expression when manage roles.

CONTAINSSTRING ( [Username_Username_2], USERNAME () )

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[onurbmiguel_]

Hello Saniat

 Do you want to use RLS , so when the user open de report in power bi service it will automatically filter only what he can see?

BR 

Bruno 

[Saniat]

Yeah, eventually yes.

[onurbmiguel_]

Hi again, 

First you need to bild a table in power bi desktop with the username email, ID and the hierarchy :

in the Hierachy column you need to have xxx|zzz|.... because you will use the function  PATHCONTAINS()

this table will not be connect to others 

i´m assuming that you have the ID user in the fact table, so you can filter bi user ID 

Go to Manage Roles and creat a role :  

Give a name for the role e go to the fact table thar need to be filter. 

on Tabel filter DAX expression put this: 

VAR vUsername = UserPrincipalName()

VAR vUser = LOOKUPVALUE('newTable'[Hierarchy], 'newTable'[username], vUsername)

VAR vCheck1 = PATHCONTAINS(vUser, 'fact_Table'[user_id])

RETURN vCheck1

then make some teste with "View as"

remember to add the users in the power bi service , link -> 

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC