Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lars_hei
Regular Visitor

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

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?

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
lars_hei
Regular Visitor

Hello @v-shex-msft 

 

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?

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.