Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dataset date as parameter

Hi

 

Im trying to add a parameter for narrowing a a dataset.

 

= Sql.Database("xxxserver.database.windows.net", "DB", [Query="Declare @DateLoadFromMonthMinus1 as Date;
Declare @DateLoadFromYearMinus10 as Date;
set @DateLoadFromMonthMinus1=DATEADD(MONTH,-1,GETDATE());
set @DateLoadFromYearMinus10=DATEADD(YEAR,-10,GETDATE());
select * from [schema].[table] where THE_DATE > " & paramDateLoadFrom & ""])

 

The PowerBI parameter paramDateLoadFrom is text.

 

The reason why I try to do this is to reduce the imported dataset so its possible to soucecontrol the pbix file since we are using imported mode

Is this approch not valid? 

 

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    how are you defining the parameter value? is it coming from the result of another query, or is it static?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I set the parameter on the dataset in the service to "@DateLoadFromYearMinus10". The scheduled refresh succeeds but its not using the expected parameter. Its seem like it using the other defined parameter from the example which is the default parameter

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ，

     

    Would you please try the following m query:

     

     

    = Sql.Database("xxxserver.database.windows.net", "DB", [Query="Declare @DateLoadFromMonthMinus1 as Date;
Declare @DateLoadFromYearMinus10 as Date;
set @DateLoadFromMonthMinus1= Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1);
set @DateLoadFromYearMinus10=Date.AddYears(DateTime.Date(DateTime.LocalNow()),-10);
select * from [schema].[table] where THE_DATE > " & paramDateLoadFrom & ""])

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai