...
- 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 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:
...
Basically you cannot have one member in more than one Department row-level security role. The way the method is constructed it not allows one person to participate in more than one department. Same report shows data from the MULTIPLE departments an example non-VIP user is approved to access requirement not met. See following images:
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.
...