Parameter visualization on published report

[Wresen]

Hi and thx for reading this.

I have Direct query report with a SQL query with one parameter that retrieves the data of fruits I have in the database (yes it’s a fake scenario 😊)

The advanced query looks like this in the end:

Where#(lf)#(lf)The_Fruitserver.Fruits_table in ('"&Fruits&"')

    ", null, [EnableFolding=true])
in
    Source

Then I have a parameter called Fruits and the value is set to “Banana”

In Power bi desktop (the view where vi build the graphs ect.) I have a table that shows the table Fruits and its value is Banana
I know it’s possible to change variable/Parameter under Home/transform data to change the parameter to example Apple.

My question is if there is a visualization that allows the user to add new values to the Fruits parameter in a published report, like a slicer when you do a search and past in the new parameter value there.

/Thanks

[Anonymous]

Hi @Wresen ,

Thanks for the reply from AgataJ .

Power BI does not support entering parameters using dynamic text boxes.

In Direct Query connection mode, data is not imported into Power BI Desktop. Instead, Power BI Desktop sends the query to the underlying data source to retrieve the required data.

Using the field parameter AgataJ mentioned, you can automatically generate a slicer with more than 20 fruits. When you select a fruit, a query like “Select * from Fruit where fruit = ‘Apple’” is sent to the data source to query for the data you want.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Wresen]

Hi @Anonymous 
Thanks for your answer but like i wrote , i have over 20 miljon diffrent distinct fruits and do an "offline table" in Power Bi for this is not a gooda way for the field parameters .

Thanks.

(I have alot of diffent fruits 🤣)

[Anonymous]

Hi @Wresen ,

Okay, here's another way to do it without using field parameters.

Here's the data I used to test it:

In Power bi desktop Get more visuals>> Search for Text search slicer visual object and click Add to use it.

You can add the Fruit field to the text search slicer and then enter the name of the fruit you want in it. This way, you can filter the data in another visual object and display the relevant content.

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Wresen]

Hi @Anonymous 
I really appriciate your help, i think i have not been good to explain.
I dont want to have a table where i have all the fruits (since it over 20 milj distinct fruits) in the report that the user can choose from to get a "fruits parameter"
I want the user to "free" write the fruit he wants in a "textbox lookalike". This input from this textbox  becomes the Fruits parameter in:
Where#(lf)#(lf)The_Fruitserver.Fruits_table in ('"&Fruits&"')

But from what i think i have heard this is not possible.

/Thanks 🙂

[AgataJ]

Hi @Wresen ,

If I understand it correctly, you could create separate DAX parameters for Fruit=Banana, for Fruit=Apple etc, and then create Use Modeling-New Parameter-Fields option, add your DAXes to it. Next, use this parameter for a chart and for a separate slicer. This way the user will be able to decide which fruit they want to see, as a single or combined value.

Hope this helps.

Kind regards,

Agata

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

[Wresen]

Hi @AgataJ 
Thx for your answer.

Yes its possible to do an "offline" table ( a table where i add all the fruits that i have in the database) and then link that table the to the parameter and put it in a slicer.
The problem is that i have over 20 miljon diffrent fruits 😆 in the database so i dont think that is an option.

What i am looking for is like an textbox where i can write Apple , press enter and the query uses my "new" parameter "Apple" and do the SQL query and look for a match.

/Thanks