powerbissrs-2012business-intelligence

How do you create a parameter list drop down in Power BI?


I have always been able to create drop-down list parameters within SSRS that allow the user to dynamically filter their reports based on one or more values selected from the drop-down list box of multiple values.

Translating now over to Power BI I cannot find any documentation to achieve the same functionality.

I've found a method of turning the slicer into a drop down list box. But it only allows for the selection of 1 value at a time. So it essentially seems to me to be more of a drop-down list of radio buttons.

In SSRS I could create a stored procedure that accepted a value and returned a record set filtered on a specific value(s).

I can see how to do this with Power BI as well but the method I've seen demonstrated, forces the user to select a value before the report is loaded and subsequently loads a pre-rendered report template. Which isn't the same.

So how does one go about creating a report that implements a true drop-down list box allowing for selection of 1 or more values to dynamically generate the result in the report?

Reference links are welcome if a simple answer isn't possible.


Solution

  • Slicers visual

    There is an out-of-the-box feature in PowerBI for slicers, but mind that they can only filter the data on chosen values, you cannot change a TSQL query with them as the parameters:

    Create a slicer visual (here in a German menu):

    enter image description here

    And, as you've already discovered, set it to Dropdown type using the upside down carrot icon on the slicer header:

    enter image description here

    enter image description here

    The dropdown list is multi-select by default:

    enter image description here

    Hold down ctrl while making the multiple selections:

    enter image description here

    However, you can control Multi-Select behavior under the 'paintbrush.' When the visual is active, click the paintbrush icon under visualizations, expand 'Selection Controls' and you will see several options to manage your slicer's behaviors:

    (in German: "Slicer-Einstellungen")

    enter image description here

    Filters pane

    Additionally, you may want to explore the filters feature, which was in preview at the time of writing the answer but is now a full feature, see the article from 01/10/2023 on Format filters in Power BI reports if you follow the old link.

    Remark from the future:

    Mind that for the time of writing, in 2019, this answer means that you need new features to get this dropdown on parameter values done. And even the preview filter feature does not allow this, as we can say now in 2024 when checking the link.

    You cannot change parameter values in a dropdown like you can do in SSRS (at the time of writing the answer in 2019)

    Otherwise, the behavior in SSRS where the report loads from data sources using parameters chosen by users either as parameters in stored procedures or incorporated into dynamic SQL is not replicated in PowerBI. It's true that there are parameters that can be incorporated in that way with the data set refresh - but these are controlled on the data set, not the report, and are not convenient (or appropriate, really) for report viewers to manipulate.