This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Why is this difficult?
The basis of the solution
The measure to colour the rows in the table
Filling in the other bits

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?

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:

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:

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 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:

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 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:

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):

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

All clever stuff!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Colouring the table row for a selected item in Power BI

Why is this difficult?

The basis of the solution

The measure to colour the rows in the table

Filling in the other bits

Head office

London

Manchester