Forum Discussion
Customized Date Slicer by using text values
hi,
So, so far I have a simple Table. Containing all dates between the 07.12.2019 and 01.04.2023 in the date column the Weeknumber and year as numbers and a combined column that shows a combination of the weeknumber (either e.g. 01 or 49) a dot and the respective year as a text.
No I created a slicer tha gives me the option to choose dates between the 07.12.2019 and 01.04.2023.
So far so good. That is the visual I would like to use. But I would like to display the column "FinalFormat" rather then the date. So the slicer should display the values in this format "49.2019" to "13.2023". This I could not get to work.
Is there a way to tisplay text in this slicer or another way to display this formatted week year combination rather then the actual dates?
Hi Anonymous ,
How about this?
1. Create a column.
FinalFormat 2 = 'Calendar'[Year] & FORMAT ( 'Calendar'[WeekNumber], "00" )2. Sort [FinalFormat] by [FinalFormat 2].
3. Create two tables and Repeat step 2 in each table.
From = SUMMARIZE('Calendar','Calendar'[FinalFormat],'Calendar'[FinalFormat 2])To = SUMMARIZE('Calendar','Calendar'[FinalFormat],'Calendar'[FinalFormat 2])4. Create a Measure.
Measure = VAR From_ = SELECTEDVALUE ( 'From'[FinalFormat 2] ) VAR To_ = SELECTEDVALUE ( 'To'[FinalFormat 2] ) RETURN SWITCH ( TRUE (), From_ = BLANK () && To_ = BLANK (), 1, From_ = BLANK () && To_ <> BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) <= To_, 1 ), From_ <> BLANK () && To_ = BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) >= From_, 1 ), From_ <> BLANK () && To_ <> BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) >= From_ && MAX ( 'Calendar'[FinalFormat 2] ) <= To_, 1 ) )5. Create two slicers.
6. Create other visuals with "Filters on this visual": [Measure] is 1.
7. Test.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- Icey
Community Support
Hi Anonymous ,
How about this?
1. Create a column.
FinalFormat 2 = 'Calendar'[Year] & FORMAT ( 'Calendar'[WeekNumber], "00" )2. Sort [FinalFormat] by [FinalFormat 2].
3. Create two tables and Repeat step 2 in each table.
From = SUMMARIZE('Calendar','Calendar'[FinalFormat],'Calendar'[FinalFormat 2])To = SUMMARIZE('Calendar','Calendar'[FinalFormat],'Calendar'[FinalFormat 2])4. Create a Measure.
Measure = VAR From_ = SELECTEDVALUE ( 'From'[FinalFormat 2] ) VAR To_ = SELECTEDVALUE ( 'To'[FinalFormat 2] ) RETURN SWITCH ( TRUE (), From_ = BLANK () && To_ = BLANK (), 1, From_ = BLANK () && To_ <> BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) <= To_, 1 ), From_ <> BLANK () && To_ = BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) >= From_, 1 ), From_ <> BLANK () && To_ <> BLANK (), IF ( MAX ( 'Calendar'[FinalFormat 2] ) >= From_ && MAX ( 'Calendar'[FinalFormat 2] ) <= To_, 1 ) )5. Create two slicers.
6. Create other visuals with "Filters on this visual": [Measure] is 1.
7. Test.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- TomMartens
Super User
Hey Anonymous ,
unfortunately, this is not possible.
Regards,
Tom- TomMartens
Hey Anonymous ,
this article describes how to create custom formats: https://blog.gbrueckl.at/events-in-progress/
And here is also a video: https://www.youtube.com/watch?v=72qQrPw4RuE
I'm not sure if the format will be honored inside a slicer, make sure that you check also the list view inside the slicer.
Regards,
Tom
