Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
2 years ago

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

Emp1AB1001
Emp2AB2002
Emp3BC1001
Emp4BC3002
Emp5BC4003
Emp6DE5004
Emp7DE1002

 

And the Access Data is as follows:

EmployeeDepartment

Emp1AB
Emp2DE
Emp3AB
Emp1BC
Emp3BC
Emp3DE

 

Now, if Emp1 is viewing the report (Username() = "Emp1"), they'd see be able to see the following: 

EmployeeDepartmentSalaryLevel
Emp1AB1001
Emp2AB2002
Emp3BC1001
Emp4BC3002
Emp5BC4003
Emp6DEHiddenHidden
Emp7DEHiddenHidden

 

 

I tried implementing this using the following measure:

Filtering = 

VAR Employee = "Emp1" // Hardcoding for testing
VAR selValTable1 = VALUES('Salary Table')
VAR selValTable2 = Filter('Access Table', [Employee] = Employee)
RETURN
IF(INTERSECT(selValTable1, selValTable2), "", "Hidden")

 

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

  • 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?

    • newpbiuser01's avatar
      newpbiuser01
      Helper 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.