Forum Discussion

LauraH9274's avatar
LauraH9274
Frequent Visitor
2 years ago
Solved

Dynamic M Query Date Parameter Slicer Not Working

I want a date slicer in my report to update the paramater in my query to filter my data. 

I've followed the documentation on MS, and have tested with a text value (which worked fine) but I cannot get the date to update.

Pleasee see screenshots attached, does anyone know what I might be doing wrong? The data types are both Date, the StartDate column is bound to the parameter, it just does not work.

Thanks in advanced.

 

 

 

Need Help
  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    Okay LauraH9274,

     

    Okay perfect.

    Next you need a dynamic list of your Min and Max MIN_PAYLOAD_DATE Colum. This is the Code:

    Name this query "MinPayloadDates"

    let
    // Step 1: Copy of Your Source Query
    Source = YourSourceQuery,
    
    // Step 2: Min und Max Date from Your Source Query
    MinDate = List.Min(Source[MIN_PAYLOAD_DATE]),
    MaxDate = List.Max(Source[MIN_PAYLOAD_DATE]),
    
    // Step 3: Liste der Daten erstellen
    DateListe = List.Dates(MinDate, Duration.Days(MaxDate - MinDate) + 1, #duration(1,0,0,0))
in
    DateListe

     

    Next you must edit your Parameter like this:


    Now when you save and close you cand change the parameter in Power BI Desktop:

     

    Best regards from Germany
    - Manuel

7 Replies

  • jennratten's avatar
    jennratten
    Super User
    2 years ago

    Hi LauraH9274 - I believe you need to convert the date to text in Power Query and then pass that back to your source, such as in the example below.

    Date.ToText ( ParameterStartDate, "yyyy-MM-dd" )

     

    When you it worked for you as a text value, was that for some other field or passing the date as text?  Where did you have that placed in the script? Was it in the source step or in one of the later steps like your date filter?  When you use the query as you currently have it configured, what happens?  Nothing?  An error?  What error?

  • LauraH9274's avatar
    LauraH9274
    Frequent Visitor
    2 years ago

    Hi jennratten , thanks for your response. 
    It worked on a text column, and the script was the same except for a different parameter and different column names. 
    The query at the moment is responsive to the parameter, so if i change the parameter manually to be a different date, this does filter the query, it just doesn't interact with the slicer in the report view. As soon as I add a slicer to the page, any visuals just go blank.
    Not quite sure I understand what passing the date as text would do, as the slicer is still in a date format, could you elaborate please?

    Much appreciated

  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    Hello LauraH9274,

    do you see the button "Edit parameter" (sorry german Version) from my screenshot in Power BI Desktop?

     

    I guess not, since you create your date values ​​statically via DAX. I assume you want to query and parameterize these dynamically via Power Query.

     

    Best regards from Germany
    - Manuel

    • LauraH9274's avatar
      LauraH9274
      Frequent Visitor
      2 years ago

      Hi Manuel, 

      I do see this, it allows me to edit the date

      • ManuelBolz's avatar
        ManuelBolz
        Responsive Resident
        2 years ago

        Okay LauraH9274,

         

        Okay perfect.

        Next you need a dynamic list of your Min and Max MIN_PAYLOAD_DATE Colum. This is the Code:

        Name this query "MinPayloadDates"

        let
    // Step 1: Copy of Your Source Query
    Source = YourSourceQuery,
    
    // Step 2: Min und Max Date from Your Source Query
    MinDate = List.Min(Source[MIN_PAYLOAD_DATE]),
    MaxDate = List.Max(Source[MIN_PAYLOAD_DATE]),
    
    // Step 3: Liste der Daten erstellen
    DateListe = List.Dates(MinDate, Duration.Days(MaxDate - MinDate) + 1, #duration(1,0,0,0))
in
    DateListe

         

        Next you must edit your Parameter like this:


        Now when you save and close you cand change the parameter in Power BI Desktop:

         

        Best regards from Germany
        - Manuel