Simple authorization based on a slicer and DAX measures

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.

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.

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

Warning! This is not a real security. This is security through obscurity.

I take no responsibility for your loses and 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 all pros and cons and accept risks then you’re free to do whatever you want if it’s technically possible. I highly recommend to 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.

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!

Read the new post (new methods used, critical issues fixed, other changes, new PBIX):

Share the article