I am developing power bi reports. We want to share this report to specific users which will be maintaining the users in SQL DB table.
If any unauthorized person clikcking on the shared link, It is possible to show the page, "You are unauthorized person to view this report. pls. contact Adminsintrator"..?
.The Shared link and load the reports based on my tables data list. For example below is the sample user and only those people must open that page.
SQL Table (Table Name : Emp_Table)
The above sql table is the list of authorized person. Apart from this, if anyone can access the link, the page should thorow the error link ""You are unauthorized person to view this report. pls. contact Adminsintrator"....?
How to make this in power bi report....
There are a few ways to achieve this but they all start off at a specific base which is returning the current user's alias or any representation of that unique user.
CurrentUser = VAR _UPN = USERPRINCIPALNAME() VAR _Alias = PATHITEM(SUBSTITUTE(_UPN, "@", "|"), 1) RETURN _Alias
Next, you'll need to check if current logged in user is within the employee table
isAuthorized = VAR _CurrentUser = [CurrentUser] VAR _isAuthorized = IF(_CurrentUser IN VALUES(Emp_Table[Name]), 1, 0) RETURN _isAuthorized
Method 1 (longest to set up)
Have an overlay of a specific visual with the unauthorized text behind all your visuals. Modify the properties of all your visual elements to transparent if user is not authorized. A measure can be made in similar fashion:
CondiAuth = VAR _isAuthorized = [isAuthorized] RETURN IF(_isAuthorized = 0, "#FFFFFF00")
Of course, the opposite must be true if a user is authorized then don't make elements transparent.
Method 2 (faster to set up)
For your base measures, you can check if user is authorized and if not, return an error.
TotalAmount = VAR _isAuthorized = [isAuthorized] RETURN IF(_isAuthorized = 1, SUM('Fact'[Value]), ERROR("You are unauthorized person to view this report. pls. contact Adminsintrator"))
All your visuals will break and if user selects "See details" an error pop up message will show based on your message:
Method 3 (fastest to set up)
Create a landing page in which all users land on. Similar to method 1, you'll only have 1 visual instead and a button. The visual will be your unauthorized text and set it's transparency accordingly if authorized/unauthorized.
The button will have an expression to allow or disallow an action. A sample measure is supplied:
NavigateToReport = VAR _isAuthorized = [isAuthorized] RETURN IF(_isAuthorized, "Page 2")
And again, you can make this transparent or not based on current user's unique name. You can even go further by showing a lock icon if user is unauthorized.
Alternatively, the button can conditionally take you to specific page navigation that returns that message if user is unauthorized.
There may be other methods but these should definitely help you move towards your intended goal.
I am trying the above steps to solve the issue.
In Page-1 i have more than 10 visuals. So i cannot do specific visual lock.
In Page-2, I can set the label like, "You are not Authorized user. Pls. contact administrator".
When user logging to the reports, based on the table access, report should display else, error page should be display..
Is it possible..?
You are really awesome..Can we have test file if you have already..?
If you are providing test file, it will be helpful for easy reference.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.