Dynamic web request on opening the report, use response as filter data

[lars_hei]

Hey there!

in our company we have lots of offices spread over the country.

Our reporting is currently being built in PowerBI, but are encountering one challenge:

We have an external service that determines a persons assignment to one or many of these offices.

If the person opens the report, they should be able to see all the data for their assigned offices only.

What we could do
1. Create a table or file somewhere,

   Mirror our external system into it periodically,

   then use it in the report.

2. Create Entra-Groups for every office and sync people into the Groups.

But, to be honest, both of those solutions are pretty bad, because we need to mirror stuff around

(how often do we do that? For how long does an employee need to wait for changes to appear?)

What we want to do

- Person X opens the Report.

- Web request to our service "What can Person X see?"

- Use the request response as a dynamic data source / table

Is that possible?

[Anonymous · Accepted solution]

Hi @lars_hei,

I think you can consider using row level security to achieve your requirement.

Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn

You need to prepare a user table to mapping username with locations and use location as key to link other tables.

Then you can create a user role based on username/userprincipalname and use expression to get the current user location value and use this as condition to filter other table records based on relationship.

USERNAME, USEROBJECTID, USERPRINCIPALNAME, CUSTOMDATA, USERCULTURE – DAX Guide - SQLBI

Regards,

Xiaoxin Sheng

[lars_hei]

Hello @Anonymous 

thanks for your reply 🙂 

So this means the request based approach is not possible, corret?

Our MS users in Entra already have a custom field which would identify the user with an ID we know,

and our user -> office table would contain this ID and the ID of the office.

No mapping should be required here, no?

Why do I need a user role or custom functions in this scenario?

[Anonymous · Accepted solution]

HI @lars_hei,

Yes, my suggestion required your table inlcude relationship that can link user id and corresponding locations.
The username are used the find out user id and get the locations, then RLS filter should applied to the location field and applied this filter to different tables based on table relationships.

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @lars_hei,

I think you can consider using row level security to achieve your requirement.

Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn

You need to prepare a user table to mapping username with locations and use location as key to link other tables.

Then you can create a user role based on username/userprincipalname and use expression to get the current user location value and use this as condition to filter other table records based on relationship.

USERNAME, USEROBJECTID, USERPRINCIPALNAME, CUSTOMDATA, USERCULTURE – DAX Guide - SQLBI

Regards,

Xiaoxin Sheng