Browse 549 attributed reviews, viewable separately for our classroom and online training
Colouring the table row for a selected item in Power BI
How can you highlight the row in a table corresponding to the slicer value you've chosen? With a disconnected slicer and a clever measure!

Posted by Andy Brown on 22 March 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Colouring the table row for a selected item in Power BI

There's a bit more to this blog than the title suggests, but the main point is: how can you get this to work?

Colouring a row

When you click on (for example) 2019, the chart at the top should change to show sales relative to this year and the relevant year number should be highlighted in the table at the bottom.

If you want to see the solution yourself (or follow along) you can download the files you'll need here.

Why is this difficult?

This seems at first glance straightforward, until you realise that this isn't a visual interaction:

Selected row in red

Power BI isn't filtering the table to show the year selected in the slicer, it's just highlighting it.

 

The basis of the solution

The solution is to have two copies of the calendar table:

Model diagram

The calendar table has been loaded twice. One of the versions has been linked to the table of purchases in the usual way, but the other one (Live slicer table) is disconnected from any other table.

You can easily achieve this effect by removing all other columns apart from the year number from the second instance of the Calendar table in Query Editor, then removing duplicates from the remaining column:

Removing duplicates

Removing duplicates from the remaining YearNumber column will give a list of the years in our model (this will be the basis for the slicer).

You can now create a slicer based on the YearNumber column in the Live slicer table:

Slicer for year number

The only problem now is that when you select a year number in the slicer, nothing updates!

 

The measure to colour the rows in the table

Create this measure (if you're not sure what a measure is, consider booking onto our two-day DAX course instead!):

Selected year = IF(

// test if the year number shown in the calendar is the

// same as the one selected in the slicer

SELECTEDVALUE('Calendar'[YearNumber]) =

SELECTEDVALUE('Live slicer table'[YearNumber]),

// if it is, colour the row red; otherwise, white

"Red",

"White"

)

Now set the background colour of each of the columns of your table to be this measure:

Set the background colour ... ... measure used.
Set the back colour ... ... to be your measure.

Filling in the other bits

There are a couple of other measures used in this report.  The table's title has been set to the value of this measure:

Chart Title =

-- show the selected year within the title

"Year sales less " &

VALUES('Live slicer table'[YearNumber]) &

" sales"

You can then get it this measure to be the source of your table's title:

Custom table title

Set the Title property of the table to this Chart Title measure.

 

Finally, the chart is showing this measure:

Comparison to selected =

 

VAR SelectedValue =

 

-- get the currently selected slicer year

VALUES('Live slicer table'[YearNumber])

VAR OriginalColumnValue =

-- get total sales for the current filter context

-- (ie for each year shown in chart)

Sum(Purchase[Quantity])

VAR TheSlicerCalculation =

-- replace the row/column filter with the year chosen by the user

CALCULATE(

SUM(Purchase[Quantity]),

'Calendar'[YearNumber] = SelectedValue

)

-- subtract the sales for the year chosen by the user away

-- from the current column/row sales

Return OriginalColumnValue - TheSlicerCalculation

Setting this to be the value showin in your column chart should give this (again assuming you've selected 2019 as your slicer year):

Slicer year comparison

Obviously the figure for the chosen slicer year - in this case 2019 - will always be 0.

 

All clever stuff! 

This blog has 0 threads Add post