Forum Discussion
unable to dynamically change parameters when user clicks on a selectedvalue in a table
Hi, I have a parameter with a default value that I pass into a query and it works as expected. Doing it this way because if I don't filter the records I don't want out first, the report gets about 90 million records, and just that table alone increases the size of the report to about 3/4 of a gig. Unaceptable.
So I created a parameter and I'm using htat to pass a particular URL so only records connected to that URL appear. Using the default in the parameter, it works.
But: I can't figure out how to get the parameter to get reassigned to the SelectedValue if the user clicks on a different record of a difference choice in a slicer. I've looked a number of the potential solutions out there (Chris Webb's for example) but can't seem to emulate the part where I can pass a value back to that parameter.
This is the parameter
The portion of the query where I’m using this parameter below
and Page_URL = '" & @Page & "'"
It works!
Any help is appreciated! Thanks, Steve
Only works for Direct Query data sources, and only via field bindings.
7 Replies
- lbendlin
Super User
Only works for Direct Query data sources, and only via field bindings.
Hi, Anonymous
As Ibendlin said, make sure that the DirectQuery mode, since the dynamic parameter updates for slicer selection are only available for DirectQuery data sources, confirm that the data source is in DirectQuery mode. This is essential for the dynamic parameter feature to work as intended.
Bind the field correctly to the parameter, and in the Model view in Power BI Desktop, make sure that the field you want the user to select from is correctly bound to the parameter. Ensure that the use of slicers or interactive elements is properly configured to allow the user to select a value that is then passed to the parameter. This typically involves setting up the slicer with the correct fields bound to the parameter.
Double-check the syntax in the M query that references the parameter. It should be formatted correctly to accept dynamic input from parameters. The correct syntax should be something like , making sure to replace it with the actual name of the parameter.Note actions that aren't supported by dynamic M query parameters, such as certain filter types, which may not update parameters as expected. For a more detailed guide to creating and using dynamic M query parameters, you can check out the following links:
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI, I've followed your recommendations and so far unable to get the ability to pass different values into the query when I click on a slicer.
Here's what I did so far.
Created a text parameter, put a default value in.
Changed the query string in the query to add the parameter I just created.
Ran it, the correct data appeared. Changed the paramter value, ran it again and it worked.
Created a duplicate of the query in direct mode (both queries are in direct mode, not linked to each other
Removed references to the parameter in the new one, so i can choose anything in the slicer.
Set the slicer up so it shows same field name as the one I'm trying to filter with the parameter.
Clicked on an item in the slicer. the table with the binded field did nothing. It ignored the slicer selection.
So that's where I am. It feels like there's a disconnect between how I get the selectedvalue from the slicer, into the query that contains the parameter- lbendlin
This documentation has an example of how to handle multi value scenarios
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
OK, I'm back. I am at the point where everything seems to be connected properly but no results. The only data that is returned is what is in the parameter.
This is what I did .
Created the parameter (Named it Directory)
Bound the parameter to the field called SiteName
Changed the SQL code to add in the parameter
ws.SiteName = '" & Directory &"'.
Ran the query, the correct data appeared in the table.
For test, changed the default value in the parameter, the correct data appeared in the query
So this is where I'm stuck. How do now click on a slicer with value that match the field, and basically set up so that when the user clicks on a different record, that record is passed to the query?
That's the disconnect I'm having right now.
Simply put, when I click on a slicer that contains a table of values, that value I click on isn't being sent to the parameter.- lbendlin
Is that a single select slicer or can the report user select multiple items?