Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Did you fix this? I have the same issue, it's driving me crazy.
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.
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
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:
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |