Display applied slicer values in Power BI

Most PowerBI reports include slicer components, that allow users to filter the displayed data according to data relationships in the semantic model. However, these slicers can take up a lot of the limited screen space, if they are displayed right next to the visualizations. Especially if reports have to be optimized for mobile screens, it is often a better choice to hide slicers in a slicer panel that can be shown or hidden on demand.

While this approach is superior for screen real estate, it has the disadvantage that the current slicer selections are not visible in the report. This can be confusing for end users and might require additional clicks to check the slicer panel again.

Example of a slicer, that can select a range of values

A possible solution for this problem is to show the currently selected slicer values in text form (e.g. in a card) within the report page.

Demo Setup

Financials sample data in PowerBI

Lets look at a possible approach with a simple example. Using the financials demo data in PowerBI desktop we can create a set of slicers that can filter the displayed time period according to selected years and months. In this example the year slicer is configured to only allow a single selection.

Two simple date slicers and their combined output

Our goal is to display the combination of selected filters in readable form, like in the card in the picture above. To this end we need to create a string that combines the selected month values with the selected year.

Since it is more more expressive to display the names of the selected months, we need a conversion of the numeric values to their string representation. Additionally, if all months are selected, we can use a fallback value, e.g. “All of 2014” instead of displaying “January-December of 2014”.

Required Measures

To achieve this we need a set of measures¹, which are described in the following parts: SelectedTimePeriod:

• IsYearSelected: Verifies if the year slicer has a selection
• IsMonthSelected: Verifies if the month slicer has a range selected
• SelectedYear:Returns the currently selected year value
• SelectedMonths: Returns a formatted string of the currently selected month names
• SelectedTimePeriod: Combines the SelectedMonths and SelectedYear values to the final result

IsMonthSelected

IsMonthSelected = IF(ISFILTERED(financials[Month Number]), 1, 0)

This measure verifies if the month slicer has a selection that is limited (i.e. not 1–12). It applies the function ISFILTERED to the [MonthNumber] column of the financials table. This function returns true, if a filter is applied to the given column, but does not include filters that are applied to different columns, e.g. the [Date].[Year] column. If no filters are applied to the column, it returns false. In this case the output of ISFILTERED is combined with an IF function, which converts the true value to 1 and false to 0.²

SelectedYear

SelectedYear = SELECTEDVALUE(financials[Date].[Year], BLANK())

SelectedYear uses the SELECTEDVALUE function, which returns the singular value of the given column, if all other values have been filtered. Otherwise it returns an alternative result, in this case BLANK³.

This is possible since the year slicer is configured to only allow a single selection, which in turn filters the year column to a single value.

IsYearSelected

IsYearSelected = IF(ISBLANK([SelectedYear]), 0, 1)

This measure is defined in terms of [SelectedYear] and converts the result to 0, if [SelectedYear] returns BLANK.

SelectedMonths

SelectedMonths = 
var minSelected = CALCULATE(
  MIN(financials[Month Name]), 
  INDEX(
    1, 
    ALLSELECTED(financials[Month Name], financials[Month Number]), 
    ORDERBY(financials[Month Number])))

var maxSelected = CALCULATE(
  MIN(financials[Month Name]), 
  INDEX(
  -1, 
  ALLSELECTED(financials[Month Name], financials[Month Number]), 
  ORDERBY(financials[Month Number])))

return IF(
  minSelected == maxSelected, 
  minSelected, 
  minSelected & "-" & maxSelected)

Since this measure has lots of things to digest, lets focus on the first expression for now:

var minSelected = CALCULATE(
  MIN(financials[Month Name]), 
  INDEX(
    1, 
    ALLSELECTED(financials[Month Name], financials[Month Number]), 
    ORDERBY(financials[Month Number])))

We invoke the CALCULATE function, which evaluates the given expression (first argument) in a filter context (second argument).

The filter context is given by the INDEX function, which returns a row at a position (first argument) from the table expression ALLSELECTED(..)(second argument) that has been ordered by the ORDERBY clause (ascending is the default).

ALLSELECTED creates a table from the given columns, while keeping applied filter contexts, except for row and column filters. In this example, we create a table, which only includes the values selected by our month slicer. From this table we select the first row via INDEX and then just get a scalar containing the name of the month.

The calculation for the maxSelected variable follows the same approach, but it selects the last row instead, returning the name of the last selected month.

return IF(
  minSelected == maxSelected, 
  minSelected, 
  minSelected & "-" & maxSelected)

The last expression verifies if the slicer has selected a range or a single month. For the former it concatenates both month names with a dash to signify a range of values. Otherwise only a single name is returned.

SelectedTimePeriod

SelectedTimePeriod = 
var selectedMonths = If([IsMonthSelected], [SelectedMonths] & " of ", "")
var selectedYear = IF([IsYearSelected], [SelectedYear], "all Years")
return selectedMonths & selectedYear

This measure just merges the output of our other formulas and handles a few edge cases in building the displayed text. This could be expanded for better formatting, depending on the selection e.g. writing “All Years” if no months are selected.

The output of some of the described measures for the displayed selection.

Conclusion

In this article, we have seen how to create a set of PowerBI measures, which can extract the selected values of several slicers and condense them to a human readable string.

While it takes a bit of work to implement this, it is a valuable technique to remind users of their filter configurations, if the slicers are not always visible.

1. A measure is an additional field added to tables of the semantic model of a PowerBI report. Measures can access data of all tables of the semantic model and can use functions of the Data Analysis Expression (DAX) formula language, to generate new insights.
2. This conversion allows the measure to also be used as a visual filter, e.g to hide another slicer, if month values are selected, which is not possible with a simple TRUE/FALSE result.
3. BLANK is similar to null in other languages.