Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello, I'm working on a Report which has as a data source a query to SQL which is actually a stored procedure which receives a parameter (in this case it is a line of text that refers to a date: 202412), what I need is to know if it can (and if it can, know how to do it) I have already created a parameter with the list of suggested values to be able to change this parameter from the Data Transformation view, but I want to know if this parameter can be handled from the same report, that is, give a drop-down list to the user that is configured as a single selection that has the same values from the list of suggested values that I configured in the parameters or something similar, so that the user can select the date with which they want to work the data.
Solved! Go to Solution.
Create a parameter in Power Query and provide a list of suggested values (202401, 202402...) and set a default value like 202412.
Then replace the hardcoded parameter in your SQL query or stored procedure call with the parameter reference. For example:
EXEC dbo.YourStoredProcedure @DateParameter = '" & DateParameter & "'
The parameters created in Power Query are not directly editable by report viewers in the report view so if you want to provide a dropdown for the user in the report create a seperate table :
DateOptions = DATATABLE( "DateParameter", STRING, { {"202401"}, {"202402"}, {"202403"}, {"202404"}, {"202412"} }
)
Drag the DateParameter column from the DateOptions table into a slicer visual and configure the slicer to Single Select.
Create a measure to capture the selected value from the slice :
SelectedDate = SELECTEDVALUE(DateOptions[DateParameter])
Then modify your query to use the SelectedDate dynamically by creating a conditional binding or dynamic M query through query parameters.
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameter
https://youtu.be/pXU_SpiBWUA?si=c2pE_ZAWzE2DL6b3
https://blog.crossjoin.co.uk/2020/11/08/handling-multi-select-in-power-bi-dynamic-m-parameters
Create a parameter in Power Query and provide a list of suggested values (202401, 202402...) and set a default value like 202412.
Then replace the hardcoded parameter in your SQL query or stored procedure call with the parameter reference. For example:
EXEC dbo.YourStoredProcedure @DateParameter = '" & DateParameter & "'
The parameters created in Power Query are not directly editable by report viewers in the report view so if you want to provide a dropdown for the user in the report create a seperate table :
DateOptions = DATATABLE( "DateParameter", STRING, { {"202401"}, {"202402"}, {"202403"}, {"202404"}, {"202412"} }
)
Drag the DateParameter column from the DateOptions table into a slicer visual and configure the slicer to Single Select.
Create a measure to capture the selected value from the slice :
SelectedDate = SELECTEDVALUE(DateOptions[DateParameter])
Then modify your query to use the SelectedDate dynamically by creating a conditional binding or dynamic M query through query parameters.
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameter
https://youtu.be/pXU_SpiBWUA?si=c2pE_ZAWzE2DL6b3
https://blog.crossjoin.co.uk/2020/11/08/handling-multi-select-in-power-bi-dynamic-m-parameters
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |