Blog

So you think a slicer can only filter data in a visual: -

Image

Right? Wrong!  Just look what I’ve done!

Image

If you want a slicer to highlight data in your visual and not filter it, then read on and I’ll explain how I did this.

My slicer had to be populated with the SALESPERSON column.  However, if I used this column from my SalesPeople dimension, it would always filter the dimension and I can’t change this behaviour. Therefore, I had to create a duplicate SalesPeople table that was not related to any other tables in the data model.  I called my duplicate SalesPeople dimension, “SalesPeople Highlight”:-

Image

You can use DAX or Power Query to duplicate tables.

Then I used the SALESPERSON column from the SalesPeople Highlight table in my slicer. 

Image

Next, I created this simple DAX measure:-

Conditional Formatting Value =
VAR MySP =
    VALUES ( 'Salespeople Highlight'[SALESPERSON] )
RETURN
    IF ( SELECTEDVALUE ( SalesPeople[SALESPERSON] ) IN MySP, 1, 2 )

This measure will return 1 or 2 depending on the following:-

1 = The value selected in the slicer, e.g. “Blanchet”.  This controls the colour of the selected value or values.

2 = The values not selected in the slicer.  This controls the colour of the unselected value or values.

Then, using the conditional formatting “Rules” option, I assigned the following colours to these conditions:-

Image

I chose the pale blue colour for the unselected values so that it mimicked the behaviour of highlighting but of course, you could select any colours here.  If you wanted a different default colour when no values were selected in the slicer:-

Image

You would need to edit the DAX measure:-

Conditional Formatting Value =
VAR MySP =
    VALUES ( 'Salespeople Highlight'[SALESPERSON] )
VAR NoOfSP =
    COUNTROWS ( 'Salespeople Highlight')
VAR AllSP =
    COUNTROWS ( ALL ( 'Salespeople Highlight') )
RETURN
    IF (
        NoOfSP = AllSP,
        3,
        IF ( SELECTEDVALUE ( SalesPeople[SALESPERSON] ) IN MySP, 1, 2)
    )

So now 3 will be returned if there is no selection in the slicer and then amend the conditional formatting conditions accordingly:-

Image

So it really is very simple to do this and you no longer need to feel that slicers can only filter data.