Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago
Solved

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...
  • DataInsights's avatar
    DataInsights
    5 years ago

    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

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

rociosanantolin,

 

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
    vResult

 

2. Create a slicer based on this calculated column. Set the visual filter to "is not blank". Sort descending.

 

 

 

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    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