API calls during report use

[EpicTriffid]

Hi all,

Bit of a general query here. I'm currently using an ETL software (Alteryx) to download data from an API for one of our services. This requires the user to select various parameters such as which form or event to download. I then bundle that all up in a spreadsheet for users to peruse.

Im currently thinking about pulling this data directly into a dashboard rather than using Alteryx to download the file. But I don't think it's feasible to download all of the data from the API, which is many millions of records per table. The idea I had would be that when someone applies a filter on the report, say for the form or event, it would query the API and only return the requested data. But I don't know if PBI can do this? When people use something like a direct query connection, are they connecting directly to the data source? Does every change to filters or slicers do another call to the database? 

Just wondering about general ideas on how to connect a report to an API without having to download all the data first?

[v-venuppu]

Hi @EpicTriffid ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @EpicTriffid ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @sergej_og @Yashwant_DSP for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[sergej_og · Accepted solution]

Hey @EpicTriffid ,
in general using DQ and selecting (=applying) filter would send a new query to the source.
To avoid this load of queries you can enable the "Apply all slicers"-Button on the UI.

But DQ is still not that efficient in terms of waiting time.
Using API in this way might be a big pain. API performance?

Regards

[Yashwant_DSP]

Build an Azure Function / API gateway that accepts filters, calls upstream API, transforms results and writes to Azure SQL / Synapse. Connect Power BI to that SQL using DirectQuery so slicers become SQL queries and only return needed rows. Add caching/TTL and pagination in the proxy to protect the API. (Best UX and governance for large datasets.)