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

DAX DAX Calculations
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:

11
11.jpg1441×594 359 KB

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):

1_4
1_4.jpg904×1079 248 KB

Thank you very much!

2

First thing I would really recommend here is to work on the setup in your model.

Having your tables like this can make things very confusing. In my view it becomes very difficult to create any complex calc when you have you model setup like you currently have.

Check out this module here where I go over all my best practice tips.

Regarding your actual core question…

I don’t believe that this is actually possible to complete in Power BI. There is no way to create a measure (just by itseld) that can understand which user is currently looking at the report.

You can combine measures with RLS.

RLS functionality has been specifically enable this.

I’ve research some further details on how to do this. Check out the below which runs through a decent example of this

Unfortunately it’s not super simple, but certainly can be done.