Blogs

Power BI > Using slicer for just highlighting values without filtering

WebSiteAdmin, December 20, 202110582 Views

The general purpose of slicer visual in Power BI is to filter the data points in the report and view only the filtered information. They provide options (single or multiple) to a user, to click on & filter the visuals on the page. 

Recently, in one of our projects, we were tasked with a requirement to highlight the bars of a bar chart instead of filtering the visual based on the selected value of the slicer. It sounded odd at first but after spending some time on it, we could come up with an approach to handle this requirement. We thought that to do this, we could use the conditional formatting feature for the visual plotted. And then, this could be achieved by incorporating some logic inside a measure, which can then be used as a Field Value while applying conditional formatting to the visual. 

Let’s see how our final output looked like: 

Here, we can see the color of the bar chart changing according to the selections made in the slicer which gives us impression of the bar getting highlighted. 

Now let’s look on how we achieved the same. 

For illustration, we have ‘Sales’ table with the following fields: 

Here, the user will select the Person from the slicer and the data bar for that particular person will be highlighted. 

First, we need to make a copy of the ’Sales’ Table – the one that we want in the slicer, and make sure there is no relationship between the two tables, because by doing so, the visual will get filtered which is against our requirement. And let’s call this newly copied table as ‘Sales Person’. 

Next, in the bar chart, plot Total Amount against Person Name from ‘Sales’ table and plot the Person field from the ‘Sales Person’ table in a slicer. As there is no relationship between the two tables, the slicer won’t be filtering the data points of the bar chart. 

Now, we will create a measure named ‘Highlight’ which will be used for conditional formatting of the data color of the bar chart. 

Highlight =
VAR SelectedName =
    ALLSELECTED ( 'Sales Person'[Person] )
RETURN
    IF (
        ISCROSSFILTERED ( 'Sales Person'[Person] ),
        IF ( MAX ( 'Sales'[Person Name] ) IN SelectedName, 1, 0 ),
        0
    )

Let’s understand how this works.  

The variable ‘SelectedName’ stores the value/s selected from the slicer. Then, if the selected value/s from the slicer matches the value/s of the plotted Person Name data field from the visual, then we return value 1 else we return 0. If there is no selection from the slicer, then the measure always returns 0. 

Now we will use this logic for applying conditional formatting to the data colours of the bar chart as below. If the value is 1, then we will use a dark colour (for the highlighting effect) and if it is 0 then it will be light colour.  

This is how our output would look like after applying the conditional formatting!! 

Happy DAX-ting!