cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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,

6 REPLIES 6
Frequent Visitor

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)
  • aris_dress_0-1595512768736.pngas 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

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 "|".

Super User I
Super User I

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



Feel free to connect with me:
LinkedIn

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

 

 

 

Community Support
Community Support

Hi, @aris_dress 

 

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

Table:

a1.png

 

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

a2.png

 

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'.

a4.png

 

a5.png

 

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.

Super User III
Super User III

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors