Forum Discussion

BSM1985's avatar
BSM1985
Helper IV
5 years ago
Solved

Dynamic parameter/filter/slicer

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...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
edhans
Community Champion
5 years ago

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.