Dynamic Query Parameters in SQL query.

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.