This article explains how you can embed the dashboard in your application with Row-level security and securing the reports based on user accessing the report.
It has data from customer-related information and assigned a salesperson who does the work
- Power BI Pro license and postman to test the APIs
- Data should be structured in a way which has a column with Owner that matches the filter you are trying to add.
- Postman app
To help you understand the sample code and data to download
- Customer table has Customer ID
- Fact table has the customer key which connects to Customer table via Customer ID
- FACT-BU Key connects to BU table via BUKEY and find BU-Executive-ID to find the executive assigned to the customer
- Executive-ID would give the field Executive-Name in Executive table
With the above data logic you could get the name of the executive responsible for the specific sales and to be able to work on below requirement:
Goal: Goal is to deploy a similar dashboard and add Row level Security to only show the customer information to the sales person being assigned and for admin show to all users.
Follow below steps after you Launch Power BI
Create a workspace which would store the specific report. https://app.powerbi.com/home
Login to the above link and follow below steps:
- Click on workspace
- Click on “Create a workspace”
- Add workspace name
- Add description
- Click on Save
Now open the PBIx file and click on Publish and it would pop up for you to select the workspace and select the workspace created in the step above.
Registered an App on Power BI using https://dev.powerbi.com/apps
- Enter the display name of your choice.
- Enter the application type
- Enter any home page URL of your choice.
- Enter a redirect URL
- Select checkbox “Select All” for API access.
Click on Register at the bottom of the page to get the Application ID and application secret.
Note Application ID & Application Secret.
Below steps lists down how you can publish and embed the reports on a webpage
And also how you could add the Row level security so user only sees the relevant data for them and not for others.
There are couple of steps involved on how you could do this:
- Get the access token
- Get the list of all groups i.e. workspaces and find the relevant ID for the group we are using
- Generate Embed token
- Find the ID for Specific report
- Set up Row level security for the report
- Create DAX expression to filter for specific users
- Generate Token with Row level security
- Embed the report using the token and Row level security filter as above.
Step 1 Generate Access Token
Generate access token using the client Id, secret and username/password that was generated in the last step.
If you get an error as below “ The user or administrator has not consented to use the application with ID… “
This means that either you are not the admin or you have not consented to use the application
So in order to do this you would need to go through the Azure portal as below:
- Go to Azure active directory
- App registration
- Go to CRM sales
- Go to API permissions
Click on “Grant Access….”
Active directory admin needs to provide permission for some of the API that we selected above.
Now run the generate access token again and get the Token ID:
Step 2 Get all groups
Get all powerBI group details using groups API (Group in PowerBI is same as workspace)
Pass the “access token” value from step 1 in “Authorization” header and will give you Group ID which is the workspace ID which you used for publishing the report.
Step 3 Get Report details
Get all report details within the group using the group id found in step 2
This would give you the specific report ID that is published in the group.
Step 4 Generate Embed Token
Generate embed token for the report using the report Id from step3 and groupId from step2
Remember to add the same bearer token in header and in body as below to get the embed token
This token helps you to display the report on a webpage.
Step 5 Access the report without Row Level Security
Below link can be used to access the report using the EmbedToken
- Embed Token: Pass the Embed Token from Step 4
- Embed URL: From Step 3
- Report Id: from Step 3
The above value needs to be populated to see the dashboard.
This gives you a general view of the dashboard.
Step 6 Setting up Row level security
In order to see the row level security add a new calculated column ‘UserName’ in Executive Table, to remove the spaces from name (as space is not a valid character while generating EmbedToken with RLS)
- Right click to add a new column
- Add formula below to create a new column with User Name.
Step 7 Create DAX Expression
Updated the ‘Tenant’ Role to use the new ‘UserName’ column and created a new DAX expression using function Username().
- Click on Manage roles
- Click on Role as Tenant
- Click on Create
- Click on the table executive
- Click to add DAX expression as below[UserName] = USERNAME()
- Save the report and Publish again
Username() function Returns the domain name and username from the credentials given to the system at connection time. Publish the report again.
Step 8 Generate embed token with RLS
Below is the API and the request to generate EmbedToken with Row level Security configured for user ‘TinaLassila’
- Username is one of the entries in executive table
- Roles: Tenant is the role we defined in the step 8
- Dataset ID is the value that you get from Step 3
Step 9 Access the report with Row level Security
Using the embed token the dashboard shows filtered data:
- Embed Token: Pass the Embed Token from Step 8
- Embed Url: From Step 3
- Report Id: from Step 3
Below report is now filtered for user Tina Lassila.