Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamic Query Parameters with SQL source

Hi! 

 

We are migrating reports from SQL Reporting Services to Power BI. Our reports have parameters in the SQL Query that are used for making calculations, stored procedures and so on. Is there anyway we can make end users change these parameters using a slicer or filter, besides making the query M-based (according to this article Dynamic M Query Parameters) because re-writing our querys will take too much time, as they are really complex.

 

Here are some examples of what our querys do with parameters.

 

 

declare Anonymous varchar(10) = 'AA'

SELECT Column
FROM Table(@Data)

CASE WHEN Anonymous = '*' THEN '%' ELSE Anonymous+'%' END

 

 

I will appreciate any idea or suggestions you have. 

Need Help

2 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    with Dynamic M Query you are on the right path.

    Maybe I don't see your point, but why don't you want to use them?

     

    You can add your SQL Query:

     

    Then add the parameters:

     

    And at the end change the static part in the M-Code to the parameter: 

     

     

    Then whenever the user is changing the parameter, the query will be changed to the selection of the user.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I want the users to change the parameter using a slicer or filter, as stated here Dynamic M Query Parameters but using SQL as my source.

       

      End users can't get into the configurations and change parameters.