Forum Discussion

KamilGH's avatar
KamilGH
Frequent Visitor
3 years ago
Solved

Dynamic M query parameter for Start & End Date

Hi,

I want to use M parameters and slicers to allow users to select start and end dates to filter the result of the report.

I created two DateTime parameters "StartDate" and "EndDate" and passed them into the advanced editor using DateTime.ToText in two different ways as the following:

 

Method 01:

 

 

let
Source = Sql.Database("**.***.**.***", "******", [Query="SELECT MEMBERSHIP_ID, BEG_DATESTAMP #(lf)FROM MEMBERSHIP_HISTORY#(lf)WHERE BEG_DATESTAMP >= '"& DateTime.ToText(StartDate,"MM-dd-yyyy") &"'#(lf)AND BEG_DATESTAMP <= '"& DateTime.ToText(EndDate,"MM-dd-yyyy") &"'"])
in
Source

 

Method 02:

 

 

let
Source = Sql.Database("**.***.**.***", "******", [Query="SELECT * #(lf)FROM MEMBERSHIP_HISTORY#(lf)WHERE BEG_DATESTAMP >= '"& DateTime.ToText(StartDate,"MM-dd-yyyy") &"'#(lf)AND BEG_DATESTAMP <= '"& DateTime.ToText(EndDate,"MM-dd-yyyy") &"'"])
in
Source

 

Both methods are updating the query based on the dates I enter in the parameters.

 

Then, I created two calendar tables for "Start Date" and "End Date" using the following syntax and bound them to the parameters.

Start Date = CALENDAR(DATE(2019,01,01),TODAY())
End Date = CALENDAR(DATE(2019,01,01),TODAY())
 
The issue I'm having is that when I create slicers using the date tables and select one of the dates, the data in the other visual disappear and I get the error message in the screenshots below
 

 

 

 

Does anyone know where I made mistakes?

 

Thanks in advance.

 

Need Help
Tutorial Requests
  • KamilGH's avatar
    KamilGH
    3 years ago

    Hi everyone,

     

    an update.

    I upgraded the Power Bi to the latest version and the issue is solved.

     

    Thanks,

     

    Kamil

5 Replies

  • KamilGH's avatar
    KamilGH
    Frequent Visitor
    3 years ago

    Hi everyone,

     

    an update.

    I upgraded the Power Bi to the latest version and the issue is solved.

     

    Thanks,

     

    Kamil

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi KamilGH ,

      Thanks for your feedback and sharing the solution here. Could you please mark your post as Answered since the problem has been resolved? Thank you.

      Best Regards

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    KamilGH , has StartDate some default value. 

     

    Also, can you click on Copy details on clipboard and share that

    • KamilGH's avatar
      KamilGH
      Frequent Visitor
      3 years ago

      Hi amitchandak 

      thanks for responding.

       

      the parameters StartDate and EndDate are have "current Value"

      Below are the details of the error message I'm getting:

      Feedback Type:
      Frown (Error)

      Timestamp:
      2022-11-01T15:35:10.6783826Z

      Local Time:
      2022-11-01T11:35:10.6783826-04:00

      Release:
      July 2021

      Product Version:
      2.95.983.0 (21.07) (x64)

      Error Message:
      Query (2, 14) The M parameter 'StartDate' not defined in model or M parameter type is not valid.

      Workbook Package Info:
      1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

      Telemetry Enabled:
      True

      Snapshot Trace Logs:
      C:\Users\*****\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot519e8ee3-ff05-42bf-b065-9c8d1dbf0fd2.zip

      Model Default Mode:
      Composite

      Model Version:
      PowerBI_V3

      Performance Trace Logs:
      C:\Users\******\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

      Enabled Preview Features:
      PBI_JsonTableInference
      PBI_NewWebTableInference
      PBI_ImportTextByExample
      PBI_ExcelTableInference
      PBI_dynamicParameters
      PBI_rdlNativeVisual

      Disabled Preview Features:
      PBI_shapeMapVisualEnabled
      PBI_SpanishLinguisticsEnabled
      PBI_qnaLiveConnect
      PBI_azureMapVisual
      PBI_dataPointLassoSelect
      PBI_compositeModelsOverAS
      PBI_enhancedTooltips
      PBI_enableWebView2

      Disabled DirectQuery Options:
      TreatHanaAsRelationalSource

      Cloud:
      GlobalCloud

      DPI Scale:
      100%

      Supported Services:
      Power BI

      Formulas:


      section Section1;

      shared StartDate = #datetime(2022, 9, 1, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true];

      shared EndDate = #datetime(2022, 9, 30, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true];

      shared #"Simple (ToText)" = let
      Source = Sql.Database("*******", "*****", [Query="SELECT MEMBERSHIP_ID, BEG_DATESTAMP #(lf)FROM MEMBERSHIP_HISTORY#(lf)WHERE BEG_DATESTAMP >= '"& DateTime.ToText(StartDate,"MM-dd-yyyy") &"'#(lf)AND BEG_DATESTAMP <= '"& DateTime.ToText(EndDate,"MM-dd-yyyy") &"'"])
      in
      Source;

      shared #"Simple (filterquery)" = let
      filterquery="SELECT MEMBERSHIP_ID, BEG_DATESTAMP #(lf)FROM MEMBERSHIP_HISTORY#(lf)WHERE BEG_DATESTAMP >= '"& DateTime.ToText(StartDate,"yyyy-MM-dd") &"'#(lf)AND BEG_DATESTAMP <= '"& DateTime.ToText(EndDate,"yyyy-MM-dd") &"'",
      Source = Sql.Database("*****", "*******", [Query=filterquery])
      in
      Source;

      Thanks,

       

      Kamil

       

  • Thennarasu_R's avatar
    Thennarasu_R
    Responsive Resident
    3 years ago

    Try this  manage paraneters in Querry Editor

    Thanks,
    Thennarasu R