cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Conditional RLS DAX

Hello!

 

I have an interesting problem that I've come some way towards solving but a complete solution eludes me, I was hoping that someone better than me would stumble upon this and find the solution laughably simple.

 

I have 2 tables, an RLS table and a Fact table like below:

 

RLS_table:

Employee_idManager_idBranch_idJob_descPathRLS

1

221Sales rep2 | 11@email.com
2null21Branch Manager22@email.com
3221Sales rep2 | 33@email.com
4221Sales rep2 | 44@email.com

 

Fact_table:

Customer_idEmployee_idBranch_idProductVolume
655121Computer A6
1525321Computer A3
98121Computer B16
327null21Computer B8
407421Computer A11

 

I have successfully created a path based RLS so that the manager (2) can see his/her own sales and any sales of the persons down the path function, but as is the case with customer (327) the manager cannot see this sale as the customer doesn't belong to any sales rep. I was wondering if anyone knows if there is a way to create a DAX so that if a sales happens outside of the path (i.e no Employee_id) then it goes to the branch manager of the branch in question, based on the job_desc column. It shouldn't say that the branch manager was responsible for the sale, it should be that it says "null" as employee_id for the sales total. My data set is far too big and volatile to manually add a dummy Employee_id and put that into the hierarchy.

 

My current DAX:

 

 

 

================================================

 

PATHCONTAINS(RLS_table[Path],

MaxX(

Filter(

RLS_table,

[RLS]=USERPRINCIPALNAME()

)

,RLS_table[Employee_id]

)

)

 

================================================

 

Any help would be greatly appreciated!

6 REPLIES 6
lbendlin
Super User
Super User

Different RLS rules can be applied in different tables. As long as you take care not to make them conflicting or useless you will be fine.

Anonymous
Not applicable

Sounds lovely, do you think you could possibly show me what some DAX like that would look like? I really cannot understand how I would do it.

lbendlin
Super User
Super User

As I said, create another role, and give it a rule that computes to TRUE() when the Employee_id in your fact table is BLANK(). Then add your managers to that role.

Anonymous
Not applicable

This sounds really good, but I must be too stupid to see how I would do that. Do you mean that the role cannot be the job description? and that the role should be in the RLS table?

lbendlin
Super User
Super User

You can create a separate role that sees all "dangling"  data and assign your managers to that role.

Anonymous
Not applicable

Yes, but how :)? To work alongside the existing RLS path function

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.