Forum Discussion

jasonyeung87's avatar
jasonyeung87
Helper V
1 month ago
Solved

creating drill-down drop-downs in Power BI reports (while data source is web API)

Hi,
 
I have a Power BI report which has 2 data sources. Both will be from web URL's (API's) and the data turned is JSON. Initially the report will be a drop-down (slicer) filter, populated by the first API. When the value changes, the report will invoke the second API (passing the value of the first). The second API will return the results of any records that matches the parameter. This is similar to drilling down from product to product details and is common for web applications.
 
I was wondering if something similar could be done with Power BI reports? I did some research and some people menttioned that I couldn't do this with data sources that uses import mode. It can be done with DirectQuery, but web API sources are generally not available with DirectQuery,
 
Any help is appreciated.
 
Jason
 
Need Help
  • Shai_Karmani's avatar
    Shai_Karmani
    1 month ago

    Standard Power BI reports in Import mode cannot re-call a web API in response to a slicer selection, so a live drill from API 1 to API 2 is not natively supported.

     

    The usual workaround is to pre-load all combinations at refresh time. In Power Query, loop through every possible first-API value, call the second API for each, and union the results into one table. Then the slicer just filters the pre-loaded table like a normal filter.

     

    If the parameter space is too large to fully pre-load, use Paginated Reports (Report Builder) with a parameterized REST/OData dataset. Paginated Reports resolve their query at render time, so the selected value is passed straight into the second API call.

     

    If this helped, a thumbs up and accepting the solution would be appreciated.

     

    Best,

    Shai Karmani

     

    Let's connect in LinkedIn

2 Replies

  • Shai_Karmani's avatar
    Shai_Karmani
    Super User
    1 month ago

    Standard Power BI reports in Import mode cannot re-call a web API in response to a slicer selection, so a live drill from API 1 to API 2 is not natively supported.

     

    The usual workaround is to pre-load all combinations at refresh time. In Power Query, loop through every possible first-API value, call the second API for each, and union the results into one table. Then the slicer just filters the pre-loaded table like a normal filter.

     

    If the parameter space is too large to fully pre-load, use Paginated Reports (Report Builder) with a parameterized REST/OData dataset. Paginated Reports resolve their query at render time, so the selected value is passed straight into the second API call.

     

    If this helped, a thumbs up and accepting the solution would be appreciated.

     

    Best,

    Shai Karmani

     

    Let's connect in LinkedIn

    • jasonyeung87's avatar
      jasonyeung87
      Helper V
      1 month ago

      Hi Shai_Karmani ,

       

      Thanks for your insights. Your solution in the second paragraph makes sense. Web API data could only be imported, so I'll try this out. Currently there isn't too much data so I'll grab all the data while it loops. If there's too much data, I'll try the third paragraph.

       

      Jason