Need help in DAX to Apply RLS using DAX

[Anonymous]

Hi Everyone,

Please help me writing DAX

I've user table like below image attached

The user table has joined with the transaction table based on email. Here ClientId is Parent Id 

Below is my requirement. 

• If any of the users get logged in with DataAccessRole='USER' or NULL, then DAX's responsibility is to filter respective users plus their child.
  • For example, UserID: 142
• If any of the users get logged in with DataAccessRole='ALL', then DAX responsibility is to filter respective users plus their child data plus other user data with the same ClientId
  • For example, UserID: 110 logged in then the table should be filtered with all users with 82 clientID and 151 users with 110 client Id.

I need DAX to fulfill the above requirements. Any DAX help will be appreciated.

I've been followed below link but don't seem to fulfil my requirement.

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

Thanks

[parry2k]

@Anonymous if you followed the link what didn't work?

[Anonymous]

Hello @parry2k 

I'm getting an error on Path function

"Each value in 'User'[userID] must have the same value in 'User'[MainUserId]. The value '110' has multiple values."

Please find the attachment for the Power BI Sample file. If you can please correct the logic in Power BI DAX and share it back to me.

I've added a base User Table that I've tried to convert in the form of hierarchy table data as guided in the above tutorial.

You can create some calculated table from this user table or any idea, will be appreciated. 

File Link

Thanks

[Anonymous]

Any one here?