topic Conditional RLS DAX in DAX Commands and Tips

Conditional RLS DAX

Anonymous — Thu, 18 Mar 2021 13:03:33 GMT

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	1@email.com
2	null	21	Branch Manager	2	2@email.com
3	2	21	Sales rep	2 \| 3	3@email.com
4	2	21	Sales rep	2 \| 4	4@email.com

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!

Re: Conditional RLS DAX

lbendlin — Thu, 18 Mar 2021 19:25:29 GMT

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

Re: Conditional RLS DAX

Anonymous — Thu, 18 Mar 2021 21:34:48 GMT

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

Re: Conditional RLS DAX

lbendlin — Thu, 18 Mar 2021 22:24:48 GMT

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.

Re: Conditional RLS DAX

Anonymous — Fri, 19 Mar 2021 08:09:45 GMT

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?

Re: Conditional RLS DAX

lbendlin — Fri, 19 Mar 2021 11:09:28 GMT

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.

Re: Conditional RLS DAX

Anonymous — Fri, 19 Mar 2021 12:56:26 GMT

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.