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

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