Forum Discussion
M Query parameter binding in Power BI
Hello,
I have created a power BI report in Direct query mode against a Data bricks SQL warehouse source. I need to make use of M query parameters for optimal data rendering and was hoping to bind the parameter value, to be used in the SQL query, from a field used in the slicer. The default value that is provided manually is getting applied in the filter of the query properly but the slicer value selected is not getting bound. The datatypes of the field and the parameter are same. Source table is connected in direct query. I have been trying to identify what is missing and any suggestion to resolve this would be of great help!
The power query step that uses the parameter in the filter condition of the sql query is as below.
Thank you!
4 Replies
While M query parameters affect the semantic model, slicer values do not get passed back to the query editor. In order for a user to be able to filter a report to a desired value, those values must all become available in the data model.
Thank you danextian for your response. The parameter is bound to a field that is available in the model and the same field is used in the slicer. Not sure, what is being missed here. Please let me know if there is something else I need to be checking as well.
As mentioned, slicer selections cannot be passed back to the query editor. They stay in the semantic model and are meant to be used for data modelling. Your alternatives are:
- Use a multi-selection parameter. You can follow this route if you intend to give the pbix to users and they have access to the underlying data source. The parameter can also be edited in the service but whoever edits must be the owner of the semantic which ownership can be achieved by taking it over and granting he/she has access to the data source.
- Add all category values to the model. In your case (based on M parameter code provided), all site ids must be added to the model so users can select the ones they're interested in via a slicer. If the storage mode is via DQ, I don't see a reason why the need for a parameter to filter the site id. If loading everything affects performance, I would suggest to pre-select just a site id either from the slicer or the filter pane and add the apply slicer button.
