Dynamic M Query Date Parameter Slicer Not Working

[LauraH9274]

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.

 

[ManuelBolz]

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]

Hi Manuel, 

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

[ManuelBolz · Accepted solution]

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]

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]

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

[LauraH9274]

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

[jennratten]

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?