Dynamic Query Parameters in SQL query.

[rnola16]

Hi all,

I ran across tons of videos, forums and reddit, couldnt find a soultion to this still. I still wonder if PBI desktop has the functionality to do a basic SQL statement with user defined inputs. Assuming that that tables are huge, I'd like to query on the database first with user input paramaters.  and then filtering/slicing etc in the report. Can this even be executed ?

DB: Teradata.

PBI Desktop cloud app.

SQL:

Select

A.car,

B.price,

C.zip,

Sum(b.totalsales)

from

Auto A

inner join Sales A on A.city = B.city

inner join Region C on B.zip = C.zip

where

c.zip = '?Parameterzip' and a.yearbuilt in ( select p.year from invent p where p.date between '?StartParam' and '?EndParam')

group by 1,2,3

Thank you

[divyed]

Hello @rnola16 ,

Yes , using parameters you can control what data you want to load and can utilize additional slicers/filters for your report. 

I would suggest to create a procedure with inputs (Case_ID in your case ). You can pass Min_Case_ID and Max_Case_ID parameters  to filter your query in stored procedure and use this stored procedure to load data in power bi. This way you have better control and you can apply additional logics and performance optimization if required.

Steps :

1. Create procedure with parameters to filter data 

2. Create power bi parameter and use in this procedure

Example :

EXEC GetFilteredData @Min_Case_ID = 1000, @Max_Case_ID = 500000

I hope this helps.

Did I answer your query ? Mark this as solution if this helps , Kudos are appreciated.

Warm Regards,

Neeraj

[rohit1991]

Hi @rnola16 ,
Yes, Power BI does allow executing SQL queries with user-defined parameters before bringing data into the report, but it requires Power Query (M Language) and Native Query Parameters to function properly, especially when dealing with large datasets in Teradata. Since you are using Power BI Desktop Cloud App, you will need to utilize DirectQuery mode to ensure that filtering happens at the database level before data enters Power BI. The best way to implement this is by defining parameters in Power Query, binding them to your SQL query, and ensuring that the query executes efficiently before loading data into Power BI. Below is an example SQL query where the user-defined parameters (@ParameterZip, @StartParam, and @EndParam) are dynamically applied. This ensures that the dataset retrieved is already filtered, allowing for smoother performance and more efficient report interactions.

SELECT
    A.car,
    B.price,
    C.zip,
    SUM(B.totalsales) AS TotalSales
FROM
    Auto A
INNER JOIN Sales B ON A.city = B.city
INNER JOIN Region C ON B.zip = C.zip
WHERE
    C.zip = @ParameterZip
    AND A.yearbuilt IN (
        SELECT P.year
        FROM Invent P
        WHERE P.date BETWEEN @StartParam AND @EndParam
    )
GROUP BY A.car, B.price, C.zip;

This approach ensures that your filters are applied before data enters Power BI, reducing unnecessary data load and optimizing performance.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.
Connect with me on LinkedIn.

[ToddChitt]

Yes, this can be done. You need to start with a Direct Query data source. Investigate "Query Parameters" in Power BI. 

Create a basic Parameter in Power Query. 

Edit the Advanced Query of your main SQL statement to inject that Parameter into the SELECT statement, much like you have done in your post.

On the Home tab. click Enter Data and create a table that will hold the valid values of the paramter.

Now, in the Model view, for the table you just entered, under the Advanced options, "Bind to parameter" and select the Power Query parameter.

Hope that helps. 

Microsoft introduced this a few years ago. 

[rnola16]

Really appreciate your quick inputs Todd. The paramaters I use are the fields from a databse table ( eg: case_id which runs into billion records) So in this case I cannot create a table and list the values to be used in parameter, the values of it are user inputs ( something like a prompt when the user runs the query)