Forum Discussion
Row Level Filtering in DAX
Hello,
I am looking to implement row level filtering in DAX using the userprincipalname() function. I have two data tables, one with the salary data, and another with the employee access details. If an employee is allowed to have access to the data for a department, they can view the data, if not, I'd show something like "Hidden" instead of the actual value.
As an example if my salary data looks like this:
Salary Data:
EmployeeDepartmentSalaryLevel
| Emp1 | AB | 100 | 1 |
| Emp2 | AB | 200 | 2 |
| Emp3 | BC | 100 | 1 |
| Emp4 | BC | 300 | 2 |
| Emp5 | BC | 400 | 3 |
| Emp6 | DE | 500 | 4 |
| Emp7 | DE | 100 | 2 |
And the Access Data is as follows:
EmployeeDepartment
| Emp1 | AB |
| Emp2 | DE |
| Emp3 | AB |
| Emp1 | BC |
| Emp3 | BC |
| Emp3 | DE |
Now, if Emp1 is viewing the report (Username() = "Emp1"), they'd see be able to see the following:
| Employee | Department | Salary | Level |
| Emp1 | AB | 100 | 1 |
| Emp2 | AB | 200 | 2 |
| Emp3 | BC | 100 | 1 |
| Emp4 | BC | 300 | 2 |
| Emp5 | BC | 400 | 3 |
| Emp6 | DE | Hidden | Hidden |
| Emp7 | DE | Hidden | Hidden |
I tried implementing this using the following measure:
Filtering =
This gives me an error saying "Each table argument of 'Intersect' must have the same number of columns. I also tried concatenating the departments in the Access Table - so Emp1 would have departments AB, BC. Then I tried using the IN function, but that doesn't work either.
Would anyone know how I could implement a solution to flag rows that are in another table and then show the values in a column accordingly? I'd appreciate any help.
Thank you!
2 Replies
- parry2kSuper User
newpbiuser01 do you want to completely hide the row or want to show the value "Hidden" in place of salary and level?
What is the ask here?
- newpbiuser01Helper V
Hi parry2k ,
I want to show the value "Hidden" instead of the actual value for the Salary and Level columns if the user does not have access the data for that department. This way the user can see some other columns (that I haven't shown in this example) but just can't see the senstive data.