Forum Discussion
Slicer initializer
Good morning
I have the following question
How to initialize a slicer so that it always starts at the same value when a report is opened. For example, that whenever we open the CUSTOMERS report the segmenters of years are always in the largest year for which there is data in the CALENDAR, for example, 2021, next year in 2022 ect.
Thank you very much in advance.
Here's a more performant solution. Create a calculated table as follows (you could also do this in Power Query):
FactTableMaxYear = { YEAR ( MAX ( FactTable[Date] ) ) }The result is a one-row, one-column table. Then, refer to this table in the calculated column:
Slicer Year = VAR vMaxYear = MAX ( FactTableMaxYear[Value] ) VAR vResult = SWITCH ( TRUE (), 'Calendar'[Year] = vMaxYear, "Current Year", 'Calendar'[Year] < vMaxYear, 'Calendar'[Year] & "" ) RETURN vResult
2 Replies
Try this solution.
1. In the Calendar table, add this calculated column. The table FactTable is your data table.
Slicer Year = VAR vMaxYear = YEAR ( MAX ( FactTable[Date] ) ) VAR vResult = SWITCH ( TRUE (), 'Calendar'[Year] = vMaxYear, "Current Year", 'Calendar'[Year] < vMaxYear, 'Calendar'[Year] & "" ) RETURN vResult2. Create a slicer based on this calculated column. Set the visual filter to "is not blank". Sort descending.
Here's a more performant solution. Create a calculated table as follows (you could also do this in Power Query):
FactTableMaxYear = { YEAR ( MAX ( FactTable[Date] ) ) }The result is a one-row, one-column table. Then, refer to this table in the calculated column:
Slicer Year = VAR vMaxYear = MAX ( FactTableMaxYear[Value] ) VAR vResult = SWITCH ( TRUE (), 'Calendar'[Year] = vMaxYear, "Current Year", 'Calendar'[Year] < vMaxYear, 'Calendar'[Year] & "" ) RETURN vResult