topic Re: Power BI - RLS in Developer

Power BI - RLS

Prarav — Tue, 19 Nov 2024 21:54:34 GMT

I need assistance with the following scenario:

I have a Power BI report. In my dataset I have two tables.

Table -1: REQ table

Columns: REQ No, URL(Web-url), PrimaryID(GUID), SecondaryID(GUID)

Table-2: USER table

Columns: userid(GUID), domainname(user’s email) email

REQ table doesn’t have POCemail.
The REQ table and USER table are linked through the UserID in the REQ table and the PrimaryID in the USER table.

I have a requirement where both Primary and Secondary users should be able to see all Request Numbers (REQ No). Additionally:

For Primary and Secondary users, the Request Numbers assigned to them should appear as hyperlinks.
For other users, all Request Numbers should be displayed as plain text (non-clickable).

Re: Power BI - RLS

Anonymous — Wed, 20 Nov 2024 06:28:01 GMT

Hi, @Prarav

Firstly, I created the following sample data:

Secondly, assuming that the other users you mentioned refer to the USER[userid (GUID)] and REQ[PrimaryID (GUID)] not matching one-to-one, you should create the following measure:

Measure = VAR ww1=SELECTEDVALUE (USER[userid (GUID)]) VAR ID1 = CALCULATETABLE ( VALUES ( 'REQ'[REQ No]), FILTER ( ALLSELECTED ( 'REQ' ), 'REQ'[PrimaryID (GUID)]= ww1 ) ) RETURN IF(MAX('REQ'[REQ No]) IN ID1,MAX('REQ'[URL]),BLANK())

Then, apply this measure to the 'REQ'[REQ No] column:

Finally, make the modifications in the location indicated in the diagram below:

Here are the final results:

Due to privacy agreements, we are unable to provide a test email directly; I recommend using your own Power BI user email for testing purposes.

For further details, please refer to:

https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security#using-the-username-or-userprincipalname-dax-function

When uploading a file, please be careful to delete sensitive information.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Power BI - RLS

Prarav — Thu, 21 Nov 2024 17:13:35 GMT

I followed your steps and tried but still not working. It is showing blank report for non POC users.

Thank you.

Re: Power BI - RLS

Anonymous — Fri, 22 Nov 2024 06:39:55 GMT

HI, @Prarav
Thank you for your prompt response.

Could you please provide details on what your expected output should look like? I suspect there may be a misunderstanding on my part regarding your question, and I would appreciate it if you could clarify the following:

1.What are the results from the perspectives of Primary and Secondary users compared to those of other users?

2.How do we determine which users fall under the category of 'other users'? Am I correct in understanding that this refers to users who have a UserID but do not have a PrimaryID?

I hope you can answer my questions, as this will help me better understand your issue and propose a more suitable solution for you.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

When uploading a file, please be careful to delete sensitive information.

Best Regards,

Leroy Lu

Re: Power BI - RLS

Prarav — Fri, 22 Nov 2024 19:57:04 GMT

Hi Lu,

I appreaciate your help. Please see my answers below in blue text

1.What are the results from the perspectives of Primary and Secondary users compared to those of other users?

Primary and Secondary users should be able to see all Request Numbers as plain text. However, the Request Numbers that belong to them should appear as hyperlinks.

Something like:

1. Scenario

2. Scenario. I would like to display something like following screenshot.

2.How do we determine which users fall under the category of 'other users'? Am I correct in understanding that this refers to users who have a UserID but do not have a PrimaryID?

Users who are not assigned as Primary POCs or Secondary POCs should see the Request Numbers as plain text when they open the report.

Re: Power BI - RLS

Anonymous — Mon, 25 Nov 2024 09:50:08 GMT

Hi, @Prarav

Thank you for your prompt response.

Based on your latest reply, I have updated my solution as follows:

1.Firstly, I have created the following measures:

Measure = VAR ccc1=CALCULATETABLE ( VALUES ( 'USER'[domainname (email)]), FILTER ( ALLSELECTED ( 'USER' ), 'USER'[userid (GUID)] IN VALUES('REQ'[PrimaryID (GUID)]) ) ) VAR No1 = CALCULATETABLE ( VALUES ( 'REQ'[REQ No]), FILTER ( ALLSELECTED ( 'REQ' ), 'REQ'[PrimaryID (GUID)] IN VALUES(USER[userid (GUID)]) ) ) RETURN IF(USERPRINCIPALNAME() IN ccc1,IF(MAX('REQ'[REQ No]) IN No1,MAX('REQ'[URL]),BLANK()),BLANK())

2.Secondly, I have modified the settings in the visualization:

3.Here are the final results:

When the user is a Primary and Secondary user:

When the user is another type of user:

Please note that the USERPRINCIPALNAME() function returns the email address you are currently using.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.