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:
|2||21||Sales rep||2 | email@example.com|
|3||2||21||Sales rep||2 | firstname.lastname@example.org|
|4||2||21||Sales rep||2 | email@example.com|
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:
Any help would be greatly appreciated!
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.
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?