Forum Discussion
Impactful IndividualRestrict Report or Pages in Report based on user persona
can it be possible to provide report or pages access based on information stored in database table like below
| Group Name | Access |
| Director | All reports of all departments |
| Sales MD | All reports of Sales Department |
| Sales Team Lead | Only Summary Page of Sales Report |
| Salesman | Only Detail Page of Sales Report |
Imagine,
You have a talbe access like this one:
Then, you just have to create measure like:
CheckPageAccess =
VAR CurrentUser = USERPRINCIPALNAME()
VAR SelectedPage = AccessiblePage (one value from your table)
VAR HasAccess =
CALCULATE (
COUNTROWS ( 'SecurityTable' ),
'SecurityTable'[UserEmail] = CurrentUser,
'SecurityTable'[AccessiblePage] = SelectedPage
)
RETURN
IF ( HasAccess > 0,SelectedPage, Blank())If you use this approche, you have to be sure that people do not have access to download the report, or they could unhidde your page
8 Replies
- Cookistador
Super User
Hello powerbiexpert22
There are two ways to achieve that:
-The first and easiest one, would be to create an app with the right audience
If this solution doesn't fit for you, I had a similar request for a customer, I create a button for each pages, and then the access of the button was set up with the command USERPRINCIPALNAME() and a dataflow which extrced the user from all groups and stored them in a secured table
But definitely, if I was you, I would have a look on Power BI app
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-create-distribute-apps
- KarinSzilagyi
Cookistador I came for the same solution, but rather than using a dataflow and userprincipalname() I would recommend a table with the roles and apply RLS and assign either the individual users or whole AD-groups to those roles.
powerbiexpert22Hi Cookistador ,
can you explain below the second option or provide any documentation or video for this option
- Cookistador
Imagine,
You have a talbe access like this one:
Then, you just have to create measure like:
CheckPageAccess =
VAR CurrentUser = USERPRINCIPALNAME()
VAR SelectedPage = AccessiblePage (one value from your table)
VAR HasAccess =
CALCULATE (
COUNTROWS ( 'SecurityTable' ),
'SecurityTable'[UserEmail] = CurrentUser,
'SecurityTable'[AccessiblePage] = SelectedPage
)
RETURN
IF ( HasAccess > 0,SelectedPage, Blank())If you use this approche, you have to be sure that people do not have access to download the report, or they could unhidde your page
- powerbiexpert22
Hi Cookistador , KarinSzilagyi
the access information is stored in database table, how to acheive same in this use case?
- Cookistador
What kind of information do you have ?
The name/email adress and to what they can access? If yes, you just need to import the table and use this table to set up the rules?
If it is not the case, can you share a dummy sample of what the data looks like ?
v-sgandrathiCommunity Support
Hi powerbiexpert22,
Thank you for the update. To give a clear example of how to link database-stored access details to report or page visibility, could you share a small dummy sample of your access table? as Cookistador mentioned. For example, 4–5 rows with the exact column names (such as UserEmail, Role, Department, PageName, AccessLevel, etc.) and some sample values.
Thank you.
