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:

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” )
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 () )

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” )
A card with empty string as a value and background color controlled by a measure

And this is the result of the update.

Not authorized:

This what you see by default (nothing new)


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:

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] )“” )

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 ( and row level security 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:

[8/3/2020: v2 uploaded]

Share the article

11 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?

  3. Hi i have tried this and it works but the problem is that when i backspaced the search in text filter and clicked on enter, the results of the first person is being shown. is there any way to tackle this?

  4. I’m working on a project that displays student dashboards at the moment. I want to include a slicer in the dashboard, but I don’t want the students to be able to see it. Only the tutor, who has entered his or her password in the text search slicer, will be able to see the slicer with the names of their students in it. I have an excel file containing each student’s name, role (tutor/student), and password for that purpose. Neither do I want RLS to be performed nor do I want the password to be hardcoded inside the measure. I want DAX metrics to be used for this. Additionally, there will be various student names for different tutors because I am doing this for multiple classes. I want the tutors to be limited to knowing the names of their students and nothing else.

  5. hi, i have a question. I tried using this method, but the proble arises when i type in the password with only two characters. meanign to say two users have passwords such as password1, passw0rd2, and when i were to only type in pa but the users name show up. how to resolve this

Leave a Reply

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