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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
userdata
Helper IV
Helper IV

RLS with overriding userprincipal dax

Hi all,

I need to create a user which is a manager for RLS based on three fields.  ID, customers and type. What the rls needs to do is, based on the id which is userprincipal it has to filter out the type and customers that are applicable and then it needs to show all fields that the selected user can see without the id, so show also ids from others who can see the same customers or type.

 

As below example, we have Bart as an id. So if Bart logs in he only should see in customers fruit, banana or retail, but based on the userprincipalname knowing what he is allowed to see the dynamic rls should now show all the customers and types he can see independent from id

 

userdata_0-1654877879399.png

userdata_1-1654879051664.png

Now he has to be able to see also other ids who have the same customer and type as him : meaning now he can also see elena and kraus as an id. I tried to configure it based on userprincipalname to find out which fields Bart should see like banana, fruit and retail and then it needs to override the id and filter only the fields he was allowed to see as he is a manager. Anyone ideas how to set up that rls? Many thanks!

userdata_2-1654879110729.png

 

 

 

 

 

 

You can find the link to the pbix in here : https://github.com/userdata21/test12

1 ACCEPTED SOLUTION

Hi @userdata ,

 

Please try this expression to do that.

[customers] 
in
CALCULATETABLE (
    VALUES ( 'Table'[customers] ),
    FILTER ( ALL ( 'Table' ), [id] = USERNAME () )
)

 

vchenwuzmsft_0-1655195830595.png

 

Result:

vchenwuzmsft_1-1655195846023.png

 

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

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

3 REPLIES 3
vapid128
Solution Specialist
Solution Specialist

emmmmmmmmmm,

you need a table contains 2 colnums

1 user id , 2 power bi log-in email

 

link this table to whatever you get, both way.

modeling --> manage role -->new table

[Email]=username()

vapid128_0-1654912589919.png

 

 

if somehow you can't make it both way, and it many to one then one to many

like below

 

vapid128_1-1654912691870.png

 

we can use code:

 

[A_ID] in SELECTCOLUMNS(
FILTER('member','member'[members_area.email] = username()),
"areas"
,'member'[members_area.a_id])

Thanks, I tried but it doesnt work because it just filters based on user but I need to filter based on user to find out the brand and customer and then it has to list all the brands and customers it can see.

The rls should filters out like below, that should be the end solution but it only filters on bart?

userdata_0-1654926781142.png

 

Hi @userdata ,

 

Please try this expression to do that.

[customers] 
in
CALCULATETABLE (
    VALUES ( 'Table'[customers] ),
    FILTER ( ALL ( 'Table' ), [id] = USERNAME () )
)

 

vchenwuzmsft_0-1655195830595.png

 

Result:

vchenwuzmsft_1-1655195846023.png

 

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.