cancel
Showing results for 
Search instead for 
Did you mean: 
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
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.