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

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

      • LauraH9274's avatar
        LauraH9274
        Frequent Visitor
        2 years ago

        Thank you Manuel! I have implemented this.

         

        Do you know if it would still possible to use a slicer to update this parameter?

         

        Thanks