Conditional RLS DAX

[Anonymous]

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_id	Manager_id	Branch_id	Job_desc	Path	RLS
1	2	21	Sales rep	2 | 1	[email protected]
2	null	21	Branch Manager	2	[email protected]
3	2	21	Sales rep	2 | 3	[email protected]
4	2	21	Sales rep	2 | 4	[email protected]

Fact_table:

Customer_id	Employee_id	Branch_id	Product	Volume
655	1	21	Computer A	6
1525	3	21	Computer A	3
98	1	21	Computer B	16
327	null	21	Computer B	8
407	4	21	Computer A	11

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!

[lbendlin]

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]

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]

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]

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]

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

[Anonymous]

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