Forum Discussion
Date Picker slicer with relative date
Thanks for the reply from DataNinja777 and MiquelPBI , please allow me to provide another insight:
Hi, george1
Based on my testing, parameters cannot be converted to the relative time type in a slicer. Therefore, your slicer must be derived from one of the columns in your data table. This is likely why you are encountering errors, as one of your slicers is derived from parameters, while the relative time type is from a fixed column.
I have provided an alternative solution for you:
1.Firstly, create the following measure:
MEASURE = VAR diff = 10 VAR selectid = MAX ( 'Parameter'[Parameter Order] ) VAR column1d = CALCULATE ( MAX ( 'Table'[Column1] ), ALLSELECTED ( 'Table' ) ) VAR column2d = CALCULATE ( MAX ( 'Table'[Column2] ), ALLSELECTED ( 'Table' ) ) RETURN SWITCH ( TRUE (), selectid = 0, IF ( MAX ( 'Table'[Column1] ) >= column1d - diff, 1, 0 ), selectid = 1, IF ( MAX ( 'Table'[Column2] ) >= column2d - diff, 1, 0 ), 1 )2.Secondly, apply the measure to the visual object:
3.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Of course, if you have any new ideas, you are welcome to contact us.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super Usergeorge1 ,
You can use a field parameter to allow users to select from multiple date columns and apply the selection to a date picker. Here's how you can do it:
- Enable Field Parameters: First, ensure that field parameters are enabled in Power BI. To do this, go to File > Options and settings > Options > Preview features, and check Field parameters.
- Create a Field Parameter:
- In the Modeling tab, click New Parameter > Fields.
- Select the date columns you want to include in the parameter: "Encounter Date", "Date of Service", "Post Date", "Coded Date"
- Name the parameter, such as "Date Selector."
- Use the Field Parameter:
- After creating the field parameter, you'll have a slicer in your report where users can choose which date column they want to filter.
- Create a date picker slicer in Power BI and bind it to the selected date column via the field parameter.
Now, you can switch between different date fields using the parameter, and the selected date column will dynamically apply to the date picker.
Best regards,
DataNinja777 , Thank you so much for the suggestion you provided. I tried this, and it works as you saying but only option I have is, selection of date doesn't stay when I change one date option to another.
Example: I have selected date of service and then I pick date 6/27/2023. Now if I change date from date of service to Posting date then my date changes to select all. what I want that time is my date 6/27/2023 to stay when I change from Date of Service to Posting Date.
If I change it to relative date then if for example my date selection is last 2 months and I change to Posting date then selection stays to last 2 months but my table view displays all available dates which I don't want. If my relative date slicer says last 2 months than table view also should display last 2 months of data when I change Date of Service to Posting date.
Is this something possible? How can I do that? Please help.
- DataNinja777
Hi george1 ,
You can achieve the desired output by combining field parameters with a slicer set to the relative date option, as shown below. I have generated sample data and recreated the output you’re looking for.
Even if you have multiple measures, you can use the switch function along with a disconnected table of measures to toggle between them seamlessly.
I have attached an example pbix file for your reference.
Best regards,
Thanks DataNinja777 , The only problem I see with this is when I change the date then it doesn't stick.
Like here I have selected this options, Now I change dates to last 10 days and it display correctly,
Now if I change the date option here,
It displays all the available dates when I change the date options even though I have selected last 10 days. Is there any workaround to this?
