Forum Discussion
slicer to split data in half
I have a single table source. This table has a Date attribute. This Date spans a 6-month range. I have created a derived column in my PBI data model to identify if the given date is in the first half of the time range or the second half of the time range (i.e., 'first 3 months', 'last 3 months'). The user is now asking for the ability to select the entire time range vs just the first 3 months, as a slicer with single-select (so, something like 'first 3 months', 'all 6 months'). How can I achieve this?
3 Replies
Hi Anonymous,
Without a sample data, I can only imagine how it looks.
There are many ways to achieve your requirement and this just one.
- Create this calculated column in your table which will return the month order starting from the earliest date.
Rolling Month Number = DATEDIFF ( CALCULATE ( MIN ( 'Table'[Date] ), ALL ( 'Table' ) ), 'Table'[Date], MONTH ) + 1- Create this calculated table. This should be a disconnected table and should not have a relationship with your fact table.
Range = DATATABLE ( "Range", STRING, "MaxMonth", INTEGER, { { "first 3 months", 3 }, { "all six months", 6 } } ) Use this measure to as a visual filter. From the filters pane, select is not blank.
RowCount = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Rolling Month Number] <= MAX ( Range[MaxMonth] ) ) )- Use the Range column from the calculated table Range in a slicer. If "first 3 months" is selected, the RowCount measure will return blank for rolling month numbers 4-6 and will not be visible in your visual. If not a single item is selected, the above measure will not filter any rows. If you don't want to show any data if no item is selected, you may change <= MAX ( Range[MaxMonth] ) ) to <= SELECTEDVALUE ( Range[MaxMonth] ) ).
- Create this calculated column in your table which will return the month order starting from the earliest date.
