“No way – I don’t want any sensitive data, especially salary info, stored in the Microsoft Cloud!”
That was the very clear directive from a client at the start of a Power BI report project. “I don’t trust Microsoft and I’m afraid of a data breach!”
That’s how it all started. But it quickly became clear that this stance clashed with operational needs: “But I still need to be able to identify employees in the report.”
At the same time, there was also a third requirement: “But not every report user should see the same data – only some users should see names!”
So here we are – three completely different and seemingly contradictory requirements for one single report:
No real names in the cloud, meaning not stored in the dataset (Client)
But the need to identify employees in the report using real names (Report Owner)
And selective visibility: Not all report users should see employee names – all within the same report!
That’s a real challenge. And these demands can’t be fulfilled using standard Power BI features:
You can’t show real names if they’re not even in the dataset
You can’t use the same report page to show or hide names depending on the user’s role – Power BI just doesn’t support that yet
Sure, Power BI offers row-level security. You can define roles and restrict data by filter – like User 1 only sees “Sales”, User 2 sees “Administration”, and Power User 3 sees “everything”.
But what Power BI doesn’t offer is page-level security: You can’t show one version of a page to Power Users (with full names) and another version to regular users (with anonymized data).
So, no out-of-the-box solution here.
That’s why I built a custom one – tailored to meet all three requirements in one integrated setup.
Here’s a screenshot of the final result. (Heads-up: it’s not interactive!)
“HR-Manager”
Sees full employee details – including real names – without anonymization.
“Business-Controller” -> Gleiche Berichtsseite (Werte, Strukturen)
Individual employee identities remain hidden
Take a look!
On the left, you see employee names and personnel numbers – fully visible for the Power User “HR Manager”.
On the right, the same data is completely anonymized for the regular user “Business Controller”.
How does it work?
First requirement: No real names in the Microsoft Cloud!
That means employee names and personnel numbers must only exist in encrypted form within the dataset.
So, the data must be encrypted in the source system – before it even touches the cloud.Then we need controlled decryption – based on the user’s role or username.
Some users get to see the real data, others don’t.
This is made possible by a custom DAX measure that decrypts the values only if the user is authorized – otherwise, it stays scrambled. 😊
Mmmh … so how does that actually work in practice?
<now it’s getting technical>
From a technical point of view, you need a matching encryption-decryption setup – with the encryption happening in the source system and a corresponding decryption mechanism in the reporting layer.
To keep things simple (yet secure), I went with a custom Caesar-style encryption method – lightweight, but with a few solid enhancements.
Here’s the basic idea:
First, the employee’s full name is converted to a clean ASCII string.
(For example, “René” becomes “Rene”. This avoids headaches caused by different character sets and encoding mismatches across data systems.)Then, using a simple four-digit code, I apply character-wise shifts, and sprinkle in random values at different positions.
The decryption is handled live in Power BI via a custom DAX measure.
Now, here’s a potential catch:
What if an employee recognizes their own encrypted name and tries to reverse-engineer the logic?
To prevent that, I added two extra layers:
A fixed key that’s tied to the employee record – so even repeated letters don’t result in the same encrypted character
A dynamic key that changes with every dataset refresh – so the encryption pattern evolves over time
The result? I’d say it’s pretty secure 😎
<and now back to non-tech talk>
Of course, the expert might point out:
If the decryption logic is part of the semantic model (the dataset), then technically, someone with admin access could find the measure and use it to decrypt the data.
And that’s true.
Which means there are a few things you absolutely have to take care of:
Protect the dataset:
No downloads, no linking to external models, no external reuse (✅ handled!)And/or:
Trust your admin
(Realistically, if they wanted to, they could already access sensitive data through other means. Alternatively, let the business department manage the report themselves.)
Do you need support or assistance with your reports? You are unsure about how to solve your business task with BI? Feel free to contact me!
