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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!