Dynamic Query Parameters with SQL source

[Anonymous]

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. 

[selimovd]

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

Blog: WhatTheFact.bi

[Anonymous]

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.