Show column contents in a measure, only if the current User is allowed (no RLS)


#1

Hello Forum Team!
The main question:

How can I show in a MEASURE (not a calc column) the contents of another column (row context) and keep all relevant other filters (filter context and evaluation context).

Background:

I am creating an HR Report for a company. All department managers should see all rankings etc. of the whole company and its other departments and cost centers.

There are 2 hierarchies, as a cost center can affect multiple departments:

Business Unit ® Business Area ® Department
Business Unit ® Cost Centers

Challenge:

The report view:


I want to offer the functionality, that whenever user hover over the numbers or bars, that they only see the relevant personnel of their own line of responsibility.

Example:

I assigned my user the rights of being a QM-Manager:

My Measure

MASK_Namekpl =
VAR currentUsername = USERNAME()
VAR currentDepartmentID=MAX(Personalstamm_DYN[Department-ID])
VAR currentName=MAX(Personalstamm_DYN[Name Kpl.])
VAR mayBeShown = CALCULATE(countrows(Dim_RightsMatrix);Dim_RightsMatrix[email]=currentUsername;Dim_RightsMatrix[Department-ID]=currentDepartmentID)
VAR result= if(MayBeShown>=1;currentName)
return
result

delivers what I want when I hover over “my” Quality-Department.

Issue:

  • If I have multiple colums with masked out measures the performance is really poor - 20s refreshtime on a List that shows a lot of raw data Colums AND the masked measures
  • Just for understanding: If I move the measures to another table, they don’t work anymore…

Question:
Is there any better/mor performant way to show a raw data value list with all masked names

Relevant tables & structure (rearranged for offering a compact screenshot):

Thank you very much!