Hi!
I'm trying to create a (impossible?) RLS model that can show almost all data for all users except a few values should be hidden.
In my case I want a user to see financial data for several units but only be able to drill it down to person level for his/her own staff.
My case: I have an employee table "Emp" that contains employee and unit values, unit is related to my organisation table and employee to my sales fact table.
I created a dublicate table of "Emp" called "Emp2" and kept it without relationships. I apply RLS on "Emp2" and then create a calculated column in "Emp" that does lookupvalue from "Emp2".
Expected result (RLS Unit=1):
"Emp2"
| Employee | Unit |
| AB12 | 1 |
BF32 | 2 |
DS16 | 2 |
| KM67 | 1 |
| ET23 | 1 |
"Emp"
| Employee | Unit | Employee (lookupvalue) |
| AB12 | 1 | AB12 |
| BF32 | 2 | |
| DS16 | 2 | |
| KM67 | 1 | KM67 |
| ET23 | 1 | ET23 |
If this would work i would hide the original Employee column and only have Employee (lookupvalue) avaliable, so users can see values from both units but when they drill down to employee the get it grouped as (Blank) for units other than their own.
The problem: RLS works fine when I look at "Emp2" table, but in "Emp" all Employees are visible in the calcualted column.
