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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors