Forum Discussion
Advocate IIDate Picker in slicer
Hello all,
I have an dynamic M query with a date parameter on Birth Date as 'Param_StartDate'. Created a blank query using power query listing all dates from 2004-2010 with 'Date_Col' as column. Later in the data model on the 'Date_Col' did a Bind to Parameter on 'Param_StarDate'.
Now on the report, using 'Date_Col' as the field in the slicer I do not get to do a date picker. I followed what Patrick mentioned in his blog but the option to choose 'between' doesnt work. I can only do the dropdown, vertical list, Tile only. Any ideas ?
Appreciate any help.
What is I observe is, if the column is 'Bind to Parameter' you only see Vertical List, Tile and Dropdown.
But when it is NOT binded to Parameter you see Between, before, After Relative date and Time.
The Parameter 'Param_StartDate' is defiend as 'Date' data type.
the column EMPL.BIRTH_DT is a 'Date' data type in the database.
And the where clause in the M code reads as
"where EMPL.BIRTH_DT = ' "&Date.ToText(Date.From(Param_StartDate),"yyyy-MM-dd")&" '
Hi rnola16 ,
Thank you for sharing the details and your valuable observations about date slicers in Power BI with dynamic M query parameters.
You're right, When a field like Date_Col is linked to a parameter such as Param_StartDate, the slicer options become limited. You can only use basic types like Dropdown, Tile, or Vertical List, while advanced options like Between, Before, After, or Relative Date/Time are no longer available.
This behavior is intentional and is a known limitation of dynamic M query parameters in Power BI, as mentioned in the official Microsoft documentation.
Link:Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
Also, thank you kushanNa , for referencing the helpful video and prior blog post which demonstrated this limitation and offered practical guidance.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
13 Replies
- Deku
Super User
Is the field set a date type?
rnola16Yes.
- kushanNa
If you're trying to do the same thing this developer is doing in here https://www.youtube.com/watch?v=pXU_SpiBWUA&t=1291s&ab_channel=DataPlatformCentral
then When you bind the column to a parameter, the date slicer will change into a dropdown and will no longer give you the option to use it as a regular calendar-style date picker. note that this video is 3 years old
- rnola16
Yes, been through this from Data Platform. I believe its a flaw in the application to not let Between, Relative Date/time, Before , After when the field itself is set to 'Date'. Microsoft should provide its own visual like 'Date Picker' in its visual List.
- rnola16
Alternatively, created a dynamic date prompts where user can enter dates in the field along with other parameters and run it live. Avoids the hassle of scrolling down for the dates.
- rnola16
What is I observe is, if the column is 'Bind to Parameter' you only see Vertical List, Tile and Dropdown.
But when it is NOT binded to Parameter you see Between, before, After Relative date and Time.
The Parameter 'Param_StartDate' is defiend as 'Date' data type.
the column EMPL.BIRTH_DT is a 'Date' data type in the database.
And the where clause in the M code reads as
"where EMPL.BIRTH_DT = ' "&Date.ToText(Date.From(Param_StartDate),"yyyy-MM-dd")&" '
- kushanNa
Correct it stopped working as a usual between, before, After Relative date and Time as soon as you bind it . Not sure why it happens like that , I have tried it last year , if you are still having this issue then it's a limitation & they have not change it yet 🙂
V-yubandi-msftCommunity Support
Hi rnola16 ,
Thank you for sharing the details and your valuable observations about date slicers in Power BI with dynamic M query parameters.
You're right, When a field like Date_Col is linked to a parameter such as Param_StartDate, the slicer options become limited. You can only use basic types like Dropdown, Tile, or Vertical List, while advanced options like Between, Before, After, or Relative Date/Time are no longer available.
This behavior is intentional and is a known limitation of dynamic M query parameters in Power BI, as mentioned in the official Microsoft documentation.
Link:Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
Also, thank you kushanNa , for referencing the helpful video and prior blog post which demonstrated this limitation and offered practical guidance.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- V-yubandi-msft
Hi rnola16 ,
Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.
- HarishKM
rnola16 Hey,
Kindly follow below steps
Step 1: Set 'Date_Col' to Date type- In Power Query, ensure the column is explicitly set to Date (right-click column → Change Type → Date).
Step 2: Use a separate unbound date table for slicer- Don’t use the parameter-bound version of the date table in the slicer. Create a standalone date table (e.g., SlicerDateTable) for the slicer.
Step 3: Mark as Date Table- Go to Model View, select the SlicerDateTable, then on the ribbon choose "Mark as Date Table", and pick the 'Date_Col' column.
Note : This enables full date slicer functionality, including the "Between" option.Step 4: Use DAX to capture slicer values:
Create DAX measures to read the selected date range from the slicer:
something like this
SelectedStartDate = MIN('SlicerDateTable'[Date_Col])
SelectedEndDate = MAX('SlicerDateTable'[Date_Col])Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query - V-yubandi-msft
Hi rnola16 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- V-yubandi-msft
Hi rnola16 ,
We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.
Your feedback is valuable to us, and we look forward to hearing from you soon.
