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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Saniat
Helper V
Helper V

Only show filtered data for subordinates

I have a table like this:

Saniat_1-1662046335027.png

 


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

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Saniat ,

According to your description, here's my solution. 

Create this DAX expression when manage roles.

CONTAINSSTRING ( [Username_Username_2], USERNAME () )

vkalyjmsft_0-1662540230404.png

Get the correct result.

vkalyjmsft_1-1662540467630.png

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.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

Hi @Saniat ,

According to your description, here's my solution. 

Create this DAX expression when manage roles.

CONTAINSSTRING ( [Username_Username_2], USERNAME () )

vkalyjmsft_0-1662540230404.png

Get the correct result.

vkalyjmsft_1-1662540467630.png

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

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 

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Yeah, eventually yes.

Hi again, 

 

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

onurbmiguel__0-1662049326318.png

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 :  

 

onurbmiguel__1-1662049542131.png

onurbmiguel__2-1662049623215.png

 

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"

onurbmiguel__3-1662050001924.png

 

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

 

 

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.