Measure - Filtered table

[RichardTam]

Measure.sickLeave = 
var
_lookupaccessLevel=LOOKUPVALUE(accessLevel[Dept],accessLevel[username],USERPRINCIPALNAME())

return

calculate(sum(Sick_Leave[Sick Leave]),Sick_Leave[Dept]=_lookupaccessLevel)

I have this measure to calculate the sum of sick days. accessLevel is granted to manager (email accounts) so that they can only view information about their department.

This works fine until I wanted to show more than one variable. ie, if I include sick leave balance, data from other staff will be shown here.

My question is how can I modify the formula so it filter and returns the names of all the employees, so the filtering can be done for all values.

Measure.sickLeave =
var
_lookupaccessLevel=LOOKUPVALUE(accessLevel[Dept],accessLevel[username],USERPRINCIPALNAME())

return

calculate(sum(Sick_Leave[Sick Leave]),Sick_Leave[Dept]=_lookupaccessLevel)

[v-yueyunzh-msft · Accepted solution]

Hi , @RichardTam 

For your dax , the LOOKUPVALUE() is the function used in the "Calculated column" not a measure .

If you want to make diffrent people to see diffrent data , you can use RLS in Power BI.

For more information, you can refer to :
Dynamic Row Level Security (Dynamic RLS) - Microsoft Power BI Community

Dynamic Row Level Security with Power BI Made Simple - RADACAD

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly