Simple authorization based on a slicer and DAX measures

I decided to start a blog about Power BI. And while my web site is not complete yet (need some design changes, SSL certificate has to be installed and some other tuning is required). I’m starting it with this simple solution of a problem that had no solution. I think it will be useful for many Power BI developers. For those of you who are willing to have some basic protection of the data shared via ‘Publish to web’ feature.

One of Power BI Community users asked a question. Is it possible to use a slicer to enter a password and then show different data in a table depends on the password entered by a user?

First thought – no, you need to use row level security. You can find multiple similar questions on the community forums with the same answer. But never say no if you didn’t try! So, my second thought was – I need to try!

And here we are!

Enter either ‘power‘ or ‘dserw‘ password into the search field:

How I built it? Just a few tricks and workarounds:

1) Create data table and passwords table, link them by user name:

Passwords table should contain 3 columns [Name[, [password] and [Real User]. Use any data source to get a list of names and passwords, for example ‘Enter Data’ button:

Then duplicate every row of the passwords table (using Power Query). Replace last character of each duplicate password with “_” and add “_after” to each duplicate user name. Do the same once again but replace first character of each duplicate password with “_” and add “_before” to each duplicate user name [updated: 8/3/2020]. Add ‘Real User’ column with 1 for original rows and 0 for duplicate rows:

2) Install Text Filter slicer:

3) Insert ‘Text filter’ slicer on a page and use your Passwords[password] field as value:

4) Insert a card and use the following measure as value for the card:

User Name :=
VAR _authorized =
    ( SELECTEDVALUE ( Passwords[Real User], 0 ) = 1 )
VAR _result =
    IF ( _authorized, SELECTEDVALUE ( ‘Passwords'[Name] ), “not authorized yet” )
RETURN
    _result

5) Insert a table:

Use Name, Month columns and Protected Value measure for values.

Protected Value :=
VAR _authorized =
    ( SELECTEDVALUE ( Passwords[Real User], 0 ) = 1 )
VAR _result =
    IF ( _authorized, SUM ( Data[Value] ), BLANK () )
RETURN
    _result

Really simple. The only way to select a value using ‘Text Filter’ slicer is to type in one of the passwords.

6) Publish the report. Then ‘Publish to web’.

Testing. Nothing entered.

Wrong password entered:

Correct password entered by Bill:

Correct password entered by John:

And one more idea is here!

Cover your headers with a white card that shows empty string.

Use this measure for card’s background conditional formatting:

Background :=
VAR _authorized =
    ( SELECTEDVALUE ( Passwords[Real User], 0 ) = 1 )
VAR _result =
    IF ( _authorized, “#FFF0”, “#FFF” )
RETURN
    _result

And this is the result of the update.

Not authorized:

Authorized:

You can hide any visual (a chart for example) or the entire page now!

But there is cost. No interactivity, no tool-tips available for visuals hidden behind the card that has transparent background. Do anyone know a solution? Let me know! Leave a comment here or email me: a@avatorl.org

Anyway, it’s possible to use first method (‘Protected Value’ DAX measure) to hide a chart (without loosing interactivity). Just use ‘Protected Value’ as a value for the chart. But depends on used visual you’ll need to use additional conditional formatting (e.g. to hide a title) or white/transparent cards to hide only some elements (e.g. a legend) of the chart (instead of hiding the entire chart) that doesn’t disappear automatically when Protected Value is BLANK() or “”. And try both blank value and empty string, they provide different results).

Protected Value :=
//alternative version that returns empty string instead of BLANK()
VAR _authorized =
    ( SELECTEDVALUE ( Passwords[Real User], 0 ) = 1 )
VAR _result =
    IF ( _authorized, SUM ( Data[Value] ), “” )
RETURN
    _result

Take into account that you can’t sync Text Filter slicers, so a user will need to enter a password on each page (if there are multiple pages in the report).

To access data hidden using this method a user need to know page URL and a password. Do not use passwords like I used for testing. Use longer password, random letters, numbers and other characters. Share page URL only with trusted users, don’t make it public. If page URL and password will become known to an unauthorized user you won’t even know that someone is using the report without your permission. I can’t guarantee that there is no way to get an access without a password. Test the solution well before the use.

I do not recommend the use of ‘Publish to web’ feature for sharing reports that contains really sensitive and confidential information. But if all parties using this kind of sharing understand pros and cons and accept risks then you’re free to do whatever you want if it’s technically possible. I believe this solution will provide you a basic level of security which is (in some cases) better than no security at all. But I take no responsibility for any loses. Use secure embedding (https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-embed-secure) and row level security https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls if you’re not ready to accept risks.

Use only secure computer to work with the report. Your password probably will be insecurely saved by a browser together with a page URL (in browser history).

Let me know if it worked for you or if you found any flaws. Before this morning I had no idea something like this is possible. Let’s test it out!

If you’re member of Power BI Community, kudos appreciated here:
https://community.powerbi.com/t5/Power-Query/write-unique-password-to-see-the-data-of-that-partucal-person/m-p/1154949

[8/3/2020: v2 uploaded]