Forum Discussion

aroyappan's avatar
aroyappan
Frequent Visitor
9 years ago

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:

 

 

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

    • aroyappan's avatar
      aroyappan
      Frequent Visitor

      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?

    • Anonymous's avatar
      Anonymous
      Not applicable

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