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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ZeDe
Frequent Visitor

Slicer doesn't update parameter

Dear all

I have an Oracle Server i'm direct query'ing from PowerBi Desktop. When I create a parameter and give it a default value it collects the data from my server. When I bind the parameter to a value from a fixed table and put it in a slicer. I simply cannot update the parameter through the slicer, as seen in the links below.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

https://www.youtube.com/watch?v=c1mezIySFf8

Isn't this possible anymore, am I doing something wrong? I tried the parameter with 'any value', 'list of values' and 'query'. When I click a rounded button (linked to a parameter), I see the loading spinners appear, but nothing changes. When I go back to 'Transform Data', the parameter still is empty or has the default-value.

 

Does someone know, where it goes wrong?

Kind regards

6 REPLIES 6
Anonymous
Not applicable

Did you fix this? I have the same issue, it's driving me crazy.

mjc543
Advocate II
Advocate II

I realize this is over a year old but I am having the same exact problem... I have a Direct Query set up via SQL statement fed to an Oracle database, parameters created and successfully bound to slicer values sitting in disconnected tables, but changing the slicers does not update the parameter values. Direct Query returns filtered results based on the default values for the parameters and changing the slicer does nothing. Been messing with it for several hours today and getting really frustrated. This is NOT a user-friendly feature.

 

Update 2023-FEB-22: I finally figured this out, and it definitely does work with a SQL query to an Oracle database. I had gotten caught up in experimenting with different variations of the M code in the Advanced Editor (which I had actually already done correctly) and totally glossed over a dumb mistake... In order for the the bounded parameter to update in response to a user's selection, the slicer being used to manipulate the parameter MUST BE ON THE SAME PAGE as the visual(s) displaying the data from a Direct Query source. Initially I had them separate because I wanted the user to 1) affirmatively select their parameters, then 2) use an action button to navigate to a hidden page to see the line item details (in order to improve query performance by delaying the visual update). This is actually still possible to do, but what I didn't realize is that I needed a second set of slicers hidden and synched to the first page. I hope this makes sense.

 

As an aside, for very simple scenarios with single text-type parameter values coming from a disconnected table, you do not need to use any of the Type.Is, Value.Type, List.Type, Text.Combine, or List.Contains nonsense that appears in the documentation for Dynamic M Query Parameters ... that's where I went down a rabbit hole and got really frustrated trying to solve the wrong problem.

 

Imporant Note: at the time of this writing, the default parameter values as can be seen in Transform Data -> Edit Parameters DO NOT update in response to the user's slicer selections (nor do they need to in order to filter the Direct Query results as expected). The offical MS documentation could definitely be a lot better about explaining how this feature actually works in practice.

As usual, PBI makes something that takes 2 minutes to implement in Tableau a multi day rabbit hole exercise. In Tableau, you create a parameter, then assign its value by clicking/selecting a visual element as part of the action options (filter, highlight, set parameter, open url, etc).

 

But do you know what will happen? Nothing. The mods are more interested in getting points for a correct answer, than working to improve the product.

v-luwang-msft
Community Support
Community Support

Hi @ZeDe ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

v-luwang-msft
Community Support
Community Support

Hi @ZeDe ,

Check if your operation involves restrictions

And to reference a query parameter in query statement Where clause, we need to modify Power Query below: 

 

vluwangmsft_0-1637289237678.png

 

refer:https://community.powerbi.com/t5/Desktop/Query-Parameters-Not-Bound-Oracle-Database/m-p/334384 

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Hi, I realize this is an old post, but I don't think that was the problem that ZeDe was trying to solve, nor do I think this extra bit of code (Number.ToText) is necessary if the parameter is correctly typed as text when originally created. It can also easily be changed by going to Parameters -> Manage Parameters -> Type: Text.

 

mjc543_0-1677083409367.png

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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