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 th...
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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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