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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aroyappan
Frequent Visitor

How to pass values to variables declared in Power BI SQL Statement

I'm connecting to SQL server using direct query connectivity mode and diretly entering the query into SQL statement input field as shown below:

 

Capture.PNG

 

Here is the SQL statement:

 

DECLARE @TestId1 INT
DECLARE @TestId2 INT
SELECT
IType.Name AS IType
,COUNT(*) AS CNT
FROM 
dbo.Table1
INNER JOIN dbo.Table2 ON *****
LEFT JOIN dbo.Table3 ON *****
LEFT JOIN dbo.Table4 ON *****
LEFT JOIN dbo.Table5 ON ****
WHERE
Table2.Id IN (1,2)AND
TestId1 IN (@TestId1) AND
Table1.Id IN (@TestId2)
GROUP BY
IType.Name

I couldn't find a way to pass values for variables TestId1 and TestId2.

Please advise. Thanks in advance.

4 REPLIES 4
v-sihou-msft
Employee
Employee

@aroyappan

 

Try not to use variable in your SQL statement. It will not prompt out any window for input value for variables. In this scenario, you can use Query Parameter in the generated Power Query ( just go to "Advanced Editor" in Query Editor). For more details, see: Deep Dive into Query Parameters and Power BI Templates

 

Regards,

 @v-sihou-msft  what is a good reference article for choosing multiple values in a single parameter in Power BI? 

 

@v-sihou-msft

 

I have 10 million records in production. So I want to filter out before loading the data in power BI.

Is there a way for filtering data before loading?

Does Power BI support this by now?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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