Dynamic API Data Loading with User-Specific Filters and Dataset Refresh in Power BI

[SankarMoorthy]

Hi Power BI Community,

I’m building a Power BI report that sources data from a REST API (which provides data based on URL parameters such as user ID, date range, or other filters). My goal is to enable multiple users to view the report filtered dynamically by their own parameters, and have the report load data accordingly from the API in (near) real-time.

What I want to achieve:

1. Users interact with slicers or filters in Power BI to select parameters (e.g., user ID, date range).
2. Power BI sends those parameters dynamically to the API to fetch filtered data.
3. The report refreshes or loads data based on those parameters on-demand (without manual dataset refreshes or pre-loading large datasets).
4. Support multi-user environment where each user sees only their filtered data.

What I have tried / know so far:

1. Using Power Query parameters to build the API URL works, but these parameters are static once the dataset is published and scheduled refreshes use saved parameters only.
2. DirectQuery mode is not available because my data source is a REST API (non-SQL).
3. Power BI’s native filtering only filters already loaded data, not the source API query dynamically per user.
4. Power BI supports push streaming datasets but requires pushing data from an external service.
5. I’m exploring custom solutions with Power BI REST API and Power Automate but unsure about best practices or limitations.

My questions:

1. Is there a supported way in Power BI to dynamically pass API parameters based on user interaction and have the dataset refresh or query the API accordingly?
2. Are there recommended patterns or workarounds for real-time or near real-time filtered API data loading for multi-user reports?
3. Can Power BI Embedded or other Microsoft services help solve this scenario effectively?
4. Are there any known limitations or best practices I should consider when designing such a solution?
5. Any advice, links to documentation, or example implementations would be greatly appreciated!

Thank you!

[v-sshirivolu]

Hi @SankarMoorthy ,

Thanks for reaching out to the Microsoft fabric community forum.

Power BI can't send slicer selections to a REST API during runtime. Parameters in Power Query are fixed after publishing, and DirectQuery isn't available for REST APIs. Slicers and filters only work on loaded data, and refreshes happen at the dataset level. There are, however, several alternative strategies you can try.

1. Power BI Embedded with Middleware API

Your application gathers user filters (such as User ID or date range).

A middleware service uses these parameters to call the REST API and returns the resulting JSON in a tabular format.

Power BI Embedded presents this filtered data to the user.

Advantages: Enables per-user, on-demand API calls; secure and scalable.

Reference: https://learn.microsoft.com/en-us/javascript/api/overview/powerbi/

2. Push Dataset Using Power Automate or Azure Function

Power Automate or Azure Function can be used to trigger API calls as needed.

The returned data is pushed into a Power BI push dataset.

The report updates in near real-time for all users.

Advantages: No embedding required; supports near real-time refresh.

Reference: https://learn.microsoft.com/en-us/rest/api/power-bi/push-datasets

3. Import Mode with Dynamic Row-Level Security (RLS)

Preload a manageable subset of data from the API.

Apply Dynamic RLS to restrict users to their respective data.

Advantages: Straightforward and works well in multi-user scenarios.

Limitations: Not real-time; limited to preloaded data.

Reference: https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

Key Considerations

Power Query parameters are static after publishing.

DirectQuery is unavailable for REST APIs.

Dataset refresh limits: 8 times per day for Pro, 48 times per day for Premium.

Secure API authentication is essential (OAuth/Azure AD recommended).

Recommendation: For per-user, near real-time API data, the Embedded with Middleware approach is most robust. If near real-time updates without embedding are sufficient, the Push Dataset with Power Automate method is a simpler alternative.

References for REST API connections:
https://learn.microsoft.com/en-us/power-bi/report-server/rest-api

Regards,
Sreeteja.

[danextian]

Hi @SankarMoorthy 

What you’re trying to do isn’t possible. You can’t send a slicer selection back to the Query Editor. The Query Editor shapes the data for the report, but not the other way around. The closest workaround would be if your source were a database then, using DirectQuery, each interaction in the report would cause Power BI to generate a new SQL (or equivalent) query based on the visual’s definition, filters, and current context. Your other option now is to load all possible options (dates, category, etc) to the model so the user can just select the values they want with a slicer and use RLS to limit the rows visible to them.

[kushanNa]

Hi @SankarMoorthy 

Maybe you will need 3rd party connectors to get this done 

please check this article : https://blog.crossjoin.co.uk/2021/04/25/can-i-build-a-power-bi-directquery-dataset-on-top-of-a-rest-...