Forum Discussion
The impossible Slicer...
Hi All,
I have two columns, On Hire Date start date and Off Hire date.
From this I have created a table which looks like the following;
| Hire Duration |
| 12 Weeks |
| 6 Months |
| 1 Year |
| 2 Years |
I have added the Hire Duration table into a slicer and want to be able to select each button in the slicer and when selected show items that are greater than or equal to 12 weeks old, 6 months, 1 year or 2 years.
Is this possible within Power Bi?
Many thanks,
E
7 Replies
- Greg_Deckler
Community Champion
Anonymous - Yes, see Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
Hi Greg_Deckler ,
Unfortunately, after staring at the below link for 15 minutes, I have come to the conclusion that I have no idea how to implement this into my own Power Bi to achieve the original request.
- Greg_Deckler
Anonymous - Download the PBIX in that post and take a look at what is going on. If you can provide some sample raw data I can probably whip up an example of it.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi Anonymous,
I'd like to suggest you create a new calculated table to expand your selection table to mapping these ranges with real date ranges.
Then you can link the new table date field to your calendar table to use the selection on the duration field to control the records displayed.Expand = VAR _calendar = ALL ( 'Calendar'[Date] ) VAR maxDate = MAXX ( _calendar, [Date] ) RETURN FILTER ( CROSSJOIN ( ALL ( 'Table'[Hire Duration] ), _calendar ), SWITCH ( [Hire Duration], "1 Year", [Date] >= DATE ( YEAR ( maxDate ) - 1, MONTH ( maxDate ), DAY ( maxDate ) ) && [Date] <= maxDate, "12 Weeks", [Date] >= DATE ( YEAR ( maxDate ), MONTH ( maxDate ), DAY ( maxDate ) - 12 * 7 ) && [Date] <= maxDate, "2 Years", [Date] >= DATE ( YEAR ( maxDate ) - 2, MONTH ( maxDate ), DAY ( maxDate ) ) && [Date] <= maxDate, "6 Months", [Date] >= DATE ( YEAR ( maxDate ), MONTH ( maxDate ) - 6, DAY ( maxDate ) ) && [Date] <= maxDate ) )Regards,
Xiaoxin Sheng
Anonymous Hi, thanks for your response. Please find some sample data below;
The table that will be implemented into the slicer drop-down box (For example, when 6 months is selected I want to see everything that has been on hire for longer than 6months)
Sample data for On Hire and Off Hire date
Sample column for hire duration (days between on hire and off-hire date)
Hope this is all clear.
Many thanks,
E
