Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Syndicate_Admin
Administrator
Administrator

Parameters with SQL

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.

1 ACCEPTED SOLUTION
AmiraBedh
Super User
Super User

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

https://community.fabric.microsoft.com/t5/Power-Query/Dynamic-Stored-Procedure-Call-based-on-user-in...

 

 

 

 

 

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

1 REPLY 1
AmiraBedh
Super User
Super User

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

https://community.fabric.microsoft.com/t5/Power-Query/Dynamic-Stored-Procedure-Call-based-on-user-in...

 

 

 

 

 

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.