By Wyn Hopkins

Adding a password box to a Power BI report

Here’s a process that allows you to add a password input box to your Power BI report.

DO NOT, REPEAT DO NOT use this for sensitive data. We have no idea if there are ways to hack this but it’s just an interesting exercise in using some features in Power BI. This is not to be used as a substitute for proper security. See our warning at the end.

To play around with the finished report and see how it works download here.

The concept

Once you can capture a name and password you can give access to otherwise hidden pages or even set certain measures to zero by multiplying by zero if the password isn’t correct.

The end result

This is what the user would see when they open the report

We’ve published the report for you to try it out: Power BI Report

(click the Show Passwords button to see the user names and passwords to try out)

After clicking the login button 2 Text boxes are shown

The Text boxes are actually the Text Filter custom visual from the marketplace

The first text filter requires the user’s name and the 2nd requires their password.

After entering the correct user name and password a single button appears (actually it’s a pie chart with the number 1 as a value) which enables the user to then drill-through to the main report

Navigation through the “hidden” part of the report is then driven by buttons and bookmarks.

How to set it up

This is how we initially set up the User and Password table.

Table Name: USER LOGIN

(We just used Enter Data to set this up)

Then reference that query and remove the User column to just give you a password table

Table Name: USER PASSWORD

We also added a table called PieChartLabel for the “button” label to prompt the drill through

These all get loaded as disconnected tables

Now we move back to the text filter custom visuals…

The first one is set with the User Column from the User Login Table

The second search box references the Password column from the User Password Table

Time for 4 measures

Firstly, we need 2 measures to “harvest” the values entered in the search boxes

User Entered = SELECTEDVALUE ( ‘User Login'[User], “No User” )

Note: the search box is filtering this table leaving just the one name in the table

Password Entered = SELECTEDVALUE ( ‘User Password'[Password], “No Password” )

Note: the search box is filtering this table leaving just the one password in the table

We then need a measure to get the Password that was required for that User and see if it matches the password entered.

Password Required =

LOOKUPVALUE (

‘User Login'[Password],

‘User Login'[User], [User Entered]

)

The last measure needed generates a value of 1 (True) if the [Password Entered] = [Password Required]

Password Control =

IF (

OR (

[Password Entered]=BLANK (),

[Password Entered] <> [Password Required]

)

Then we can use this Password Control measure in pie chart along with a label from the Entry table

THE END RESULT / TRICK

The result of this is if the password entered doesn’t match the expected password then the measure results in the value 0 and the Pie Chart visual will not display the drill-through option

Once the drill-through option is available then you have full access to the report via buttons and bookmarks.

Not quite the whole story – KEEP READING!!!!

On the face of it, it appeared to work.

On testing we realised that if the customer name is entered then as soon as you enter the first letter of the password the screen unlocks. This is due to the nature of using the search box, it filters the table with all words beginning with what you are typing.

So a little rework is required….

We used Power Query to append a duplicate set of customer data with passwords but removing the last letter of both the customer name and the password for the duplicate part.

each Text.Start([User],Text.Length([User])-1)

This way, when the customer name and password are being typed into the search boxes the full name and full password must be entered before the search box returns a single record for customer and single record for password.

Then it works well.

BE WARNED

Just like password protection inside Excel this is not a process to rely on for proper protection. The Password you type in is obvious to anyone looking over your shoulder and it’s probably easy to hack this, so please don’t rely on it for anything important!

Read about proper ROW LEVEL SECURITY if you really want a robust solution to limit the data that users can access.