Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MRUry7
Resolver II
Resolver II

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

MRUry7_0-1708701847053.png
The portion of the query where I’m using this parameter below

and Page_URL = '" & @Page & "'"

It works!

Any help is appreciated! Thanks, Steve

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Only works for Direct Query data sources, and only via field bindings.

View solution in original post

7 REPLIES 7
MRUry7
Resolver II
Resolver II

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.

Is that a single select slicer or can the report user select multiple items?

 

v-yohua-msft
Community Support
Community Support

Hi, @MRUry7 

 

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

This documentation has an example of how to handle multi value scenarios

 

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

lbendlin
Super User
Super User

Only works for Direct Query data sources, and only via field bindings.

Thanks very much.  So far that's what I've discovered as well.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.