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

[aroyappan]

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

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.

[v-sihou-msft]

@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,

[saujanyae]

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

[aroyappan]

@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?

[dataviznz]

Does Power BI support this by now?