Maintain conditional formatting with RLS

[Anonymous]

I have a matrix visual that displays the following values as a hierarchy: Role, Resource, Stage, Project. The columns are Weeks, and the values are Hours.

The row values for "Resource" are conditionally formatted based on "Field Value" and based on a measure called "Utilization Rate". Utilization Rate is Calculated as: 

Utilization Rate = AVERAGEX(VALUES('Deployment'[Resource]),

DIVIDE(

    SUM('Deployment'[Hours]),

    LOOKUPVALUE('Employees'[Working Hours Per Week],'Employees'[Name],'Deployment'[Resource])))

Row-Level Security applies to 'Deployment' based on "Sector".

I would like the conditional formatting for the row to remain, despite RLS.

For example:

When I have access to all data, I see John at 60 Hours. The Utilization Rate is 150% (60/40). Due to the conditional formatting measure, this appears as RED.

However, when I "view as" a User X role, User X only sees a segment of John's hours. I only have access to 30 hours, so the Utilization Rate is calculating as 75% (30/40). Due to the conditional formatting measure, this appears as GREEN. I want this to still appear as RED and reflect the 150%.

How can I maintain the overall Utilization Rate conditional formatting system-wide despite RLS on this current table? I thought I could create a new table where no RLS table filters apply, but that did not seem to work.

[Anonymous]

This solution does not work. The conditional formatting is still applying to the filtered values, which I suppose makes sense given the row-level security.

[DataVitalizer]

Hi @Anonymous 

If you want to display the over all value despite the connected role try including your formula in a 'CALCULATE' function excluding 'ALL' applied filters on your table (Deployment or Sector)
https://learn.microsoft.com/en-us/dax/calculate-function-dax

https://learn.microsoft.com/en-us/dax/all-function-dax

Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated

[Anonymous]

Thank you. I've tried to add CALCULATE and ADD in various parts of the formula and keep getting errors. Where is the appropriate place to put these?

[DataVitalizer]

@Anonymous 

The formula should look like this

CALCULATE(whatever you want to calculate, ALL(the table used for RLS))

Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated