Procurement Dashboard, designed by Teniloluwa Sogbesan and published in Power BI.

Hi, it’s me again!

Have you ever run into this? You select a value from a filter and apply it to a visual, for example a bar chart, and suddenly all the other bars disappear and you’re left with just one.

Now what if you actually want to see all the bars but have the selected value in the slicer highlighted instead, like in the image below?

Side-by-side view comparing how visuals respond to a selected slicer value, with and without the highlight workaround.

I’m sure a lot of us have been in this situation before. I’ve seen a few different ways people try to tackle it, but in this edition of Inside My Dashboard, I’ll be sharing a little trick I use that allows the slicer to affect your entire report and not just the chart you want to highlight.

We’ll be using a sales dataset to build a clustered column chart that shows the monthly trend for total sales over a year. But instead of letting the month slicer hide all the other bars when a month is selected, we’ll highlight just the selected month in a different color.

We start by creating a duplicate of the table where the Month field comes from. In this case, that’s the Calendar table.

Step 1: Duplicate the table that contains your filter column

I typically create a calendar table when I’m going to be doing any granular time comparisons. You can also do this directly on your fact table, but since my Month field is on the Calendar table, I created a duplicate of that table. To do this:

Click on New Table in the Table Tools panel and enter the table definition like this:

new date = ‘Calendar’

Creating a duplicate of the Calendar tableStep 2: Add the Filter Slicer

Drag a slicer visual onto your report canvas and add the Month column from your original Calendar table. This slicer will control which month is selected, and later on, we’ll use it to highlight that selection in our bar chart without hiding the other months.

Step 3: Build the Column Chart

Next, we’ll create a clustered column chart that displays Total Sales by Month and highlights the selected month from the slicer.

The key to this is using the Month column from the duplicated calendar table as the X-axis in the visual.

Since this new table is disconnected from our data model, we’ll use the TREATAS function in our DAX calculations. This function lets us mimic a relationship between the new calendar table and the original calendar table without actually creating one.

To achieve this:

Start by creating a basic measure for total sales:

Total Sales = SUM(financials[Sales])

Now, write a measure that returns total sales across all months, using the TREATAS function:

Total Sales No Highlight =
CALCULATE(
[Total Sales],
TREATAS(VALUES(‘new date'[Month]), ‘Calendar'[Month])
)

Next, write a measure that checks if the current month matches the selected month in the slicer and returns the same sales value if it does:

Total Sales Highlight =
IF(
SELECTEDVALUE(‘Calendar'[Month]) = SELECTEDVALUE(‘new date'[Month]),
[Total Sales No Highlight]
)

Lastly, add both measures to the Y-axis of the clustered column chart.

Column chart showing both highlight and base bar side by side for the selected month.

In the visual above, you’ll notice that for the selected month (March, in this case), there are two bars displayed side by side. This happens because we’re plotting two separate measures on the Y-axis:

Total Sales No Highlight (which shows all months)Total Sales Highlight (which only returns a value for the selected month)

Power BI treats them as separate series in a clustered column chart and places them next to each other. The Total Sales Highlight measure returns blank for the other months not selected, so only the base value from Total Sales No Highlight is shown.

This setup works perfectly because it allows us to overlay a highlight bar in a different color without hiding the context of the other months.

Step 4: Overlay the Highlighted Bar on the Base Bar

To achieve this:

In the Format pane, select the visual and go to the Columns section.Under Apply settings to, choose All series.Expand the Layout dropdown and set Series spacing to 100%.

Turn on Overlap:

If your No Highlight measure is listed above the Highlight measure on the Y-axis, also enable Flip overlap.If the Highlight measure is listed first, you can leave Flip overlap turned off.

Column layout settings showing overlap and spacing adjustments

Set your column colors:

Under Apply settings to, choose the Total Sales Highlight measure and assign a color for the highlighted month.Then choose the Total Sales No Highlight measure and set the base bar color of your choice.