
I have written 3 blogposts about query parameters in the past.
- Power BI Desktop Query Parameters, Part 1, Introduction
- Power BI Desktop Query Parameters, Part2, Dynamic Data Masking and Query Parameters
- Power BI Desktop Query Parameters, Part 3, List Output
This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.
The solution is somewhat easy. I created a simple stored procedure in AdventureWorksDW2019 as below:
CREATE PROCEDURE SP_Sales_by_Date
@date int
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM [dbo].[FactInternetSales]
WHERE OrderDateKey >= @date
END
GO
In Power BI Desktop, get data from SQL Server, then:
- Enter Server name
- Enter Database name
- Select Data Connectivity Mode
- Expand the Advanced options
- Type in a SQL statement to call the stored procedure like below:
exec SP_Sales_by_Date @date = 20140101
- Click OK

- Click Transform Data

Now we need to create a Query Parameter. In my sample I create a DateKey in Decimal Number data type:
