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.
Helper II believe you can accomplish this with a combination of measures and a field parameter. This is what the model would look like.
1. Create relationships between a calendar table and the three dates in a fact table. Note, one relationship will be active and two will be inactive. Note the image of model above.
2. Write measures for each date column you want to calculate. To my previous point you'll write measures to look at the inactive relationships. That said, measures you write will respect the active relationship unless you tell the measure to look at the inactive.
3. Create a field parameter that uses the measures you created. Field parameters are a wonderful technique to create dynamic displays and filters. This table is disconnected in the model. Note the model image at beginning of post.
4. Drop the date column from your calendar table in slicer for the date slicer. Drop the field parameter column into a slicer. Drop the same field paramter column in your table visual.
I hope that helps!
Hi MiquelPBI , Is it possible to attach pbix file here? If you can it will help a great deal.
Do you think with this selection of date does 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.
If you can attach pbix file it would be great. Please
- MiquelPBI
Yes, the dates will remain as selected and the user can select and show multiple date columns.
I dont see a way to attach a file. I'm assuming I'll have to add it to my Git repo and share a link. I can do that but will have to wait until later. Sorry.
