Forum Discussion
User input parameters for direct query
I'm flummoxed but after searching for a number of days for this, I'm becoming convinced this is an extremely simple capability that's apparently been ignored by the Power BI platform possibly.
All I want to do is provide user inputs that look like slicers on the Power BI report. I want to use the values in those input boxes in direct query data source tables in the data model.
Please do not respond with how to do this with a numeric slider parameter, nor how the "parameters" interface exists on the completely separate data model refresh settings. I shouldn't be limited to numbers for user input, I shouldn't have to allow or direct users to a completely separate "parameters" interface on the data model settings and then come back and run a report. It should be a box or calendar control on the report or dashboard screen just like nearly every other tool that exists. There are a host of very simple needs that seem like maybe they're getting ignored here such as:
- Start/end date range parameters
- String input parameters (not picked from a predetermined list)
These extremely basic capabilities are in all sorts of other reporting tools but as far as I can tell completely missing for Power BI direct query? Please someone tell me how I'm missing this here?
8 Replies
This doesn't really truly answer the question. For Example:
- In terms of datetime user inputs, I would essentially have to build a table with all necessary dates and TIMES possible to allow for it to be selected from a list rather than typed or entered via calendar control? Remember there are potential time components to this, and no one wants to scroll down at a bare minimum 90 records if they just want to get data for the last 90 days because they're forced to use a pick list instead of typing in the date or calendar control.
- This doesn't address input of things in text fields (alphanumeric work order "numbers", email addresses, other various things that would be letters and numbers) it seems to me. This appears to be all based around strictly list based selection. If you have millions or even thousands of things to pick from this presents an impossible user experience.
I would essentially have to build a table with all necessary dates and TIMES possible to allow for it to be selected from a list rather than typed or entered via calendar control?correct. That's how it is designed. If you want a different design please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com
Remember that instead of using parameters report users can make extensive use of the filter pane which has all these capabilities.
Did you find a way to make this work? Having the same problem to solve.
Not the way I really needed/wanted it to no.
It seems I need to have reference data tables existing for all possible directquery input parameter values so I can bind a column in those tables to the parameter value when one is selected (can't simply use a datetime picker to supply any value as nearly all other tools it would seem support for parameter inputs to queries).
It would also appear that limitation applies to any sort of text box input you might want to use in directquery queries (search pattern strings, etc). Basically you're picking values from a reference data table only, so it doesn't seem to me you can do any of that with Dynamic DirectQuery M parameters since in effect all of them are really based upon picking a value for a parameter bound to reference data columns.
Keep in mind that a user interacting with a slicer for a Direct Query sourced column will do this anyway - they can enter advanced search terms like "contains" and most of the time this will result in a folded query back into the data source.
