Forum Discussion
Dynamic API Data Loading with User-Specific Filters and Dataset Refresh in Power BI
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:
- Users interact with slicers or filters in Power BI to select parameters (e.g., user ID, date range).
- Power BI sends those parameters dynamically to the API to fetch filtered data.
- The report refreshes or loads data based on those parameters on-demand (without manual dataset refreshes or pre-loading large datasets).
- Support multi-user environment where each user sees only their filtered data.
What I have tried / know so far:
- 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.
- DirectQuery mode is not available because my data source is a REST API (non-SQL).
- Power BI’s native filtering only filters already loaded data, not the source API query dynamically per user.
- Power BI supports push streaming datasets but requires pushing data from an external service.
- I’m exploring custom solutions with Power BI REST API and Power Automate but unsure about best practices or limitations.
My questions:
- 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?
- Are there recommended patterns or workarounds for real-time or near real-time filtered API data loading for multi-user reports?
- Can Power BI Embedded or other Microsoft services help solve this scenario effectively?
- Are there any known limitations or best practices I should consider when designing such a solution?
- Any advice, links to documentation, or example implementations would be greatly appreciated!
Thank you!
Thank you Anonymous Sreeteja for you multiple suggestion. I am working with Power BI Embedded with Middleware API approach and let you know in case i am struck with anything.
8 Replies
- kushanNa
Super User
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-api/
- danextian
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.
we have onpermises MongoDB database, Is there any open source ODBC connector available for Direct Query?
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.Hi SankarMoorthy ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Hi SankarMoorthy ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you
Thank you Anonymous Sreeteja for you multiple suggestion. I am working with Power BI Embedded with Middleware API approach and let you know in case i am struck with anything.
Hi SankarMoorthy ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
