Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

assign multiple values to single parameter

Good morning,

 

we are trying to move some reports from an old software to Power BI with DirectQuery... The old software gives the possibility to use multiple values in a single parameter (i.e.: worker_param = p;d). Is it possible to achieve the same result in Power Bi?

 

In details, the table contains a column with values p or d and I would like to have worker_param taking both p and d as default values, but the user could filter the single value if needed. Let me know if you need additional details!

 

Kind regards,

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

6 Replies

  • Smauro's avatar
    Smauro
    Solution Sage
    6 years ago
    Hi Ari,

    What you're asking for is certainly possible, but requires some extra steps.

    1) Add a parameter as text.
    2) Instruct users that they can enter multiple values by using `;` as a separator and no spaces.
    3) create a new query, using Text.Split(parameter_name, ";")
    4) Filter anything you want using List.Contains(#"new query", Value)

    Regards,
    Spyros
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      to have a more adherent answer to your needs, you should illustrate in more detail the context in which to apply this "feature"

       

      but for the momento, here it is an idea ..

       

      let
    fnChoice=(optional inp)=>
    let
    par=[p="p",d="d"],
    out=if inp= null then Record.FieldNames(par) else inp 
in
    out
in
fnChoice

       

       

       

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    in Power Query, split the column by delimiter and in the advanced option select "split into rows"

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may go to 'Query Editor', click 'Manage Parameters' to create a parameter as below.

     

    Then you may right-click 'Changed Type' to create a new step as below.

    Custom1 = Table.ReplaceValue(#"Changed Type",each [Column1],each Parameter1,Replacer.ReplaceText,{"Column1"})

     

    Finally you may change the parameter to modify the value in 'Column1'.

     

     

    For further information, please refer to the following links.

    Deep Dive into Query Parameters and Power BI Templates 

    Power BI Desktop Query Parameters 

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you for the suggestions.

     

     I will try to be more specific:

    • I am using DirectQuery mode, so Power Query Editor functionalities are limited and to split columns I have to use measures or new columns in the Tables/Visual Editor (with already extracted data)
    • as shown here, the values P and D are in the same column, but one for row. So, I need to create a parameter (i.e.: param = "P";"D") able to filter all the rows of the table. In details, the parameter by default will select all the rows (imported via DirectQuery with Advanced Editor), but the user could select a single value if needed ("P" or "D" only)

    Let me know if you need additional details.

    Kind regards for your help,

    Aris Dressino

    • lbendlin's avatar
      lbendlin
      Super User
      6 years ago

      You posted in the Power Query section of the forum hence we assumed it was a Power Query question.

       

      In DAX you can use the PATH() functions.  Before you can do that you will want to replace ";" with "|".