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

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.