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

      • ManuelBolz's avatar
        ManuelBolz
        Responsive Resident
        2 years ago

        Hallo LauraH9274,

        I think what you're trying to do isn't possible. But maybe you can explain again why you want to parameterize your query. Maybe there is another solution?

        • Do you have too much data?
        • Is your dataset too slow?
        • Maybe you need RLS (Row Level Security)?


        Best regards from Germany
        Manuel Bolz

        If this post helped you, please consider Accept as Solution so other members can find it faster.

        🤝Follow me on LinkedIn