Forum Discussion

zurab4000's avatar
zurab4000
Frequent Visitor
3 years ago

Dynamically Change Data Source Using Parameter and Slicer

Hi, 

 

I have Data Source: https://example.com/v1/{city}/api=1.0 .

I created parameter (list_param) "List of Value" with City names.

I changed the source : Source = Json.Document(Web.Contents(Text.Combine({"http://example.com/v1/",list_param,"/api=1.0"}))),

 

The data is generated based on the parameter value, works good.

But I want to use Slicer with values of parameter to select City name on the table. How to achive it ?

 

Thanks

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi zurab4000,

    AFAIK, slicer and query parameter are work on different level, you can’t use the front side slicer to affect the background query parameters.

    Notice: the data level of power bi(from parent to child level)

    Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

    Regards,

    Xiaoxin Sheng

  • zurab4000's avatar
    zurab4000
    Frequent Visitor
    3 years ago

    Hi Anonymous 

    Is there a way to change the data source from a report view?

     

    Regards,

    Zurab

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      HI zurab4000,

      Query parameter is possible to achieve dynamic change the data source. You only need to parameterize the connection string with query parameters.

      Current the slicer and filter not able to directly interact with the backend query tables connection string. I'd like to suggest you submit an idea for these requirements.

      Microsoft Power BI ideas
      Regards,

      Xiaoxin Sheng

    • Farhad88's avatar
      Farhad88
      Helper I
      2 years ago

      Hi zurab4000 

      It is possible to define a paramateric SQL query which can be updated in real-time based on the selected value in a slicer. But it seems that it is limited to the data sources that supports DirectQuery. In this ragard, these two links should be useful:

      https://youtu.be/L-wqwzj0-Xo

      https://learn.microsoft.com/en-us/shows/mvp-azure/pass-parameter-to-sql-queries-statement-using-power-bi

       

      However, I am still looking for a way to apply the same scenarion on the WEB (RestAPI) data source. Please let me know if you find any solution.

      Best regards, Hossein.

  • Adamboer's avatar
    Adamboer
    Responsive Resident
    3 years ago

    To achieve this, you can create a slicer with the values from the "list_param" parameter. Follow these steps:

    1. Go to the "Visualizations" pane in Power BI Desktop.
    2. Click on the "Slicer" visual to add it to your report page.
    3. Select the "list_param" parameter from the "Fields" pane.
    4. The slicer will automatically be populated with the values of the parameter.
    5. Select the values from the slicer to filter the data in the table visual.

    This way, you can use the slicer to dynamically filter the data based on the selected city name.