Dynamic Parameters from the visulisation screen into the transform area

[DaveHayter]

Hi All,

I am trying to work on a power BI dashboard, which involves pulling data from two different APIs. The forst is a list of all places that fall within our area, along with their unique ID reference numbers, and the second will take one of the id references as part of its address and bring through detailed data around that place.

I have both APIs working, however, my next issue is that I want to allow somebody to use a slicer in the report to select a specific location, and have the id number for that place be fed through as a parameter that i can use to modify the source coding for the second API to then pull through the data for that chosen location.

I am really stuck and feel that this should be possible! Is anybody able to help a very confused person so that I can head into Christmas happy? 

Many thanks in advance!

[v-achippa]

Hi @DaveHayter,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ibarrau and @tharunkumarRTK for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @DaveHayter,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[tharunkumarRTK]

@DaveHayter 

To query the source dynamically based on the slicer selection, you need to use dynamic M query parameters  

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

However, As far as I know, Dynamic M query parameters does not work with API based data sources (Web connector).

So, I believe you need to consider loading all the data into an semantic model and build the report. OR you can create a Fabric notebook and write a python script to extract and transform the data and load them into a lakehouse and then build the Power BI semantic and a report.

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ibarrau · Accepted solution]

Hi. Working with data usually means getting all you can to store it in a place you can clean and transform before the show. This means you can work better storing all the results of the API for that slicer instead of getting the api for each users interaction. Getting all once it's healthier for the API too, you are note requesting everytime but once.

When the volumne for APIs are big or they are too complex, the best practice would be doing it before Power Bi. That means, using local code to store in a DB or notebooks to store in a lakehouse/warehouse and finally use power bi to read that.

You can try using Power Bi for this and check if it doesn't take so long. If you google web request per row or paginating api for power bi, you can find articles like this one: https://blog.ladataweb.com.ar/post/188215249200/powerquery-web-content-por-fila

That shows how to handle the request for each value of an entity. That way you load it all. The slicer will just filter a big table with the result for all of them.

I hope that helps,