Dynamic parameter/filter/slicer

[BSM1985]

Hi Team,

Below is the sample SQL that I will use in Power BI report. There is a slicer on the year. When users choose year=2010 then report should bring the data for the period 2006 to 2010 (last 5 years). Is there any way we can acheive this in SQL itself rather than doing it in DAX or M please? Becasue I have so many SQL calcluations that are dependent on the filter condition (last 5 years). I will need to perform the calculations in SQL itself, before bringing the data in Power BI.

SELECT ID, COUNTRY,TO_CHAR(DATE,'YYYY'),COUNT,VALUE
FROM TABLE
WHERE TO_CHAR(DATE,'YYYY') BETWEEN TO_CHAR(DATE,'YYYY')-5 AND TO_CHAR(DATE,'YYYY')

[edhans · Accepted solution]

Not an efficient way that I am aware of. You can use Direct query to some degree, but that is not optimal by any means.

Power BI is not a front-end for SQL. It is build on SQL Server Analysis Services (when you start Power BI, a runtime instance of SSAS is launched in the product) and SSAS is a side component of SQL Server with its own advantages. So Power BI is designed to bring the data in and be analyzed in DAX. Those filters are easy to do in DAX with the FILTER function.

[v-shex-msft · Accepted solution]

HI @BSM1985,

In fact, power bi not existed writeback features.

AFAIK, the Data view level filter and slicer not able to interact with the backend query table.
The way I know can affect the result table is config t-sql statement in the data connector and interaction with query parameters. You can't directly apply filters to your database without M query functions.

SQL Server Connector 

How do I pass parameters to my SQL statement? 

Regards,

Xiaoxin Sheng

[edhans · Accepted solution]

Not an efficient way that I am aware of. You can use Direct query to some degree, but that is not optimal by any means.

Power BI is not a front-end for SQL. It is build on SQL Server Analysis Services (when you start Power BI, a runtime instance of SSAS is launched in the product) and SSAS is a side component of SQL Server with its own advantages. So Power BI is designed to bring the data in and be analyzed in DAX. Those filters are easy to do in DAX with the FILTER function.