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:

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.

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.

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!

[8/3/2020: v2 uploaded]