Update SQL query from a changing data in the dashboard

[alicemntt]

Hello all, 

I'd like to change my SQL query in function of a data that I would change directly in the dashboard. It seems impossible but i'm still trying, hoping someone knows how to do it. 

Here is a bit more details : 
I have an SQL query that imports my data from a data base : 

= Sql.Database("database", "DB", [Query="WITH    TABLESELECT   #(lf)  AS (select [$Table1].[firstdata] [...] #(lf)        where [$Table1].[Date on which the record was created] = CHOSEN_DATE #(lf))#(lf)SELECT * FROM DB    #(lf)#(lf)"])

So the date of the imported datas will depend on the 'CHOSEN_DATE'. I know how to change this chosen date in function of a parameter. However, what I'd like to do is to be able to change it from within the dasboard. So i'd like to change the date in the green rectangle and so it updates my native query 

Is this possible ? If yes, how ? thank you 

[amitchandak]

@alicemntt , not very clear. But this direct query, refer Dynamic M parameters

[alicemntt]

Basically I import my datas from an SQL database so i prefilter my datas according to a specific date: 

So my datas are already prefiltered on the date. However, I'd like to make this filter dynamic and be able to change the date according to whats in the dashboard. So if someone changes the date in the dashboard in the green rectangle in the next screenshot and enter for example march 9th, I'd like the query to be filtered on the 9th of march. Is there any way this is possible ? 

thank you ! 

[Thejeswar]

Hi @alicemntt ,

I doubt if this is possible. Changing of parameter in the Report Viewer Screen is way downstream from the place where you import the data. The Data selection that you make here will be available only for any downstream operations that you might do like adding a new column or new measure to the original dataset.

You can instead use Parameters inside Power Query to do the filtering and display the parameters as read only values in Data Card.

For the Users to be able to edit the parameter, you can use the approach that is used in the below video by Guy in a Cube

https://guyinacube.com/2017/05/24/dynamic-power-bi-reports-using-parameters/

Regards,

[alicemntt]

Thank you for your response ! I did use the parameters even though it was not an ideal solution ... In the end I actually didn't need to change the date anymore so 🤷🏼‍♀️