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:

Simple data model

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:

A table with a list of names and passwords

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:

Final version of the Passwords table with duplicated rows

2) Install Text Filter slicer:

2020-06-12_09-53-43.png
Text Filter slicer is required

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

Text Filter slicer inserted

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 ( _authorizedSELECTEDVALUE ( ‘Passwords'[Name] )“not authorized yet” )
RETURN
    _result
A card that shows either this warning or user name

5) Insert a table:

Your data table

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

Protected Value :=
VAR _authorized =
    ( SELECTEDVALUE ( Passwords[Real User], 0 ) = 1 )
VAR _result =
    IF ( _authorizedSUM ( 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.

This is what you see on the page by default

Wrong password entered:

This what you see when wrong password entered

Correct password entered by Bill:

This what you see when correct password entered

Correct password entered by John:

This what you see when correct password entered

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
2020-06-12_12-44-11[1].png
A card with empty string as a value and background color controlled by a measure
2020-06-12_12-45-43[1].png

And this is the result of the update.

Not authorized:

2020-06-12_12-46-37[1].png
This what you see by default (nothing new)

Authorized:

2020-06-12_12-45-02[1].png
This is what you see when correct password entered

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 ( _authorizedSUM ( 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]

Share the article

8 thoughts on “Simple authorization based on a slicer and DAX measures

  1. Супер! У цієї ідеї є великий потенціал для використання!

  2. The Problem was there is it doesn’t clear the search term which we search previously, so ideally the password can be viewed in the dropdown in the slicer. It should not show to anyone because it is a password!

    Is there any workaround to cover this?

Leave a Reply

Your email address will not be published. Required fields are marked *