M Query parameter binding in Power BI

[shivakumardr1]

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!

[danextian]

Hi @shivakumardr1 

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.

[shivakumardr1]

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.

[danextian]

Hi @shivakumardr1 

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.

[shivakumardr1]

Hi @danextian 

Thank you again, for your prompt response.  

You are right there is no need to include site_id as a parameter as slicer works fine with Direct query. Our actual use case involves fetching data from an event based fact table based on the  between condition applied from what users have selected in the from and to date slicers. We have a Startdate and EndDate calendar tables and are trying to use parameter binding to fetch only the data that is filtered based on datetimes selected by the users. The slicers are getting values from the calendar table fields. We were first trying to achieve this with the Site_Id column.