...
- Select the Modeling tab.
- Select Manage Roles.
- Select Create.
Select the table that you want to apply a DAX rule.
Enter the DAX expression that contains the Power the Power BI RLS Process flow. Explained below:
The view (POWERBI_SECURITY_POC_V2) in DWTEST contains the following tables:
- F_USFPG_COLLECTIONS_RVUS
- USFPG_DEPT_PROFILE_V
- L_SECURITY_POWERBI_V
...
The view (POWERBI_SECURITY_POC_V2) is joined using KEY_DEPT_PROFILE which is available in F_USFPG_COLLECTIONS_RVUS and USFPG_DEPT_PROFILE_V and KEY_GLOBAL_DEPT in L_SECURITY_POWERBI_V and USFPG_DEPT_PROFILE_V
...
.
The DAX username role in Power BI desktop à [POWERBI_ID] = LEFT(USERPRINCIPALNAME(),FIND("@",USERPRINCIPALNAME())-1) which is used to retrieve
...
the netid
...
of the person who signs-in to Power BI and maps it to the PowerBI ID which is available in the L_SECURITY_POWERBI_V
...
Once the report is published, in PowerBI service, the username role created can be assigned to individual users or AD groups. So who ever signs-in to the report will see only the rows which has their netid.[1] See below an example of an New role with the dax expression containing a POWERBI_ID and filtering by a specific Department:
To test the role and verify the data is correct just select View as Role, select the role and click OK.
...
Now you will see a yellow banner in top of the report showing the role name and the option to Stop Viewing:
When the roles are configured as needed you can click Publish and the changes will be applied.
Now we have to configure the security setings in the PowerBi web environment. We select the workplace in which we published the report and select the dataset with the same name of the report. Select the (...) and click the Security option in the ddl:
You will be able to assign different Members to the roles created so they can inherit the rules of the assigned role.
Click Add and then Save. This will assign that member to the specified role.
Now we Test as role to verify if the configuration works as we needed:
Findings:
- Workplace
If you are an Admin in the workspace you will always be able to see all data when you open a report no matter what is your security role assigned for a particular report.
...
A work around it can be creating Department row-level security roles that can have more than one department. So we can add multiple users to this role.
See below images with the role containing the Pediatrics department with Pathology:
...
...
Manage security on your model
...