Color data that has been changed in Power BI

[pko997]

Hello, I have a question about the report that I want to show in Power BI. The report looks like this from the picture, there is the union of two tables created by Power queries in Power Query Editor:

Is there any possibility in Power BI to compare rows let's say for the same car_id, and to change the background color or to bold data that has changed, like on the second picture?

Example: for car_id=10, in the second row change occurred on result='Updated', date, car_value, and new_car_value in relation to the previous row. Then in relation to the second-row, in the third-row change occurred on the date, car_value, and new_car_value. 
I would like to color those changed fields.

[pko997]

Hi, 
Is there any way to show only those data with a background color for a particular car_id in a linear graph or something else?
I am asking this, in case I have a large number of columns and rows in the table, and only in certain fields there is a change, I would have to scroll to see those changes through the table and it would take some time to see what has changed.
Is it possible to extract only the changes?

[v-yanjiang-msft · Accepted solution]

Hi @pko997 ,

According to your description, here's my solution. Create five measures.

result_color =
VAR _previous =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[car_id] = MAX ( 'Table'[car_id] )
                && 'Table'[date] < MAX ( 'Table'[date] )
        ),
        'Table'[result]
    )
RETURN
    IF (
        MAX ( 'Table'[result] ) <> _previous
            && _previous <> BLANK (),
        "light green"
    )

status_color =
VAR _previous =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[car_id] = MAX ( 'Table'[car_id] )
                && 'Table'[date] < MAX ( 'Table'[date] )
        ),
        'Table'[status]
    )
RETURN
    IF (
        MAX ( 'Table'[status] ) <> _previous
            && _previous <> BLANK (),
        "light green"
    )

date_color =
VAR _previous =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[car_id] = MAX ( 'Table'[car_id] )
                && 'Table'[date] < MAX ( 'Table'[date] )
        ),
        'Table'[date]
    )
RETURN
    IF (
        MAX ( 'Table'[date] ) <> _previous
            && _previous <> BLANK (),
        "light green"
    )

carvalue_color =
VAR _previous =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[car_id] = MAX ( 'Table'[car_id] )
                && 'Table'[date] < MAX ( 'Table'[date] )
        ),
        'Table'[car_value]
    )
RETURN
    IF ( MAX ( 'Table'[car_value] ) <> _previous, "light green" )

newcar_color =
VAR _previous =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[car_id] = MAX ( 'Table'[car_id] )
                && 'Table'[date] < MAX ( 'Table'[date] )
        ),
        'Table'[new_car_value]
    )
RETURN
    IF (
        MAX ( 'Table'[new_car_value] ) <> _previous
            && _previous <> BLANK ()
            && MAX ( 'Table'[new_car_value] ) <> BLANK (),
        "light green"
    )

In visualizations formatting pane>Cell elements, select the columns in the Series and turn on the backgroud color option.

Select corresponding measure in the dislog.

Get the correct result:

I attach my sample below for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Community Support Team_yanjiang

[pko997]

Hi @v-yanjiang-msft , appreciate your response. You helped a lot. Just one more additional question. What can be approached if I would have 30+ columns? Would I have to create measures for all of them or just maybe another approach? What is your opinion?

[v-yanjiang-msft]

Hi @pko997 ,

By my test, we can't use one measure for all the columns, if so, all the columns will render the same color, because context is considerd in measures.

Best regards,

Community Support Team_yanjiang

[pko997]

Hi @v-yanjiang-msft, I was testing your solution, but in some cases, it does not color the right field. In these cases when I added a couple of 'latest' in the result columns, and also when I change the content of the result column (from 'Updated' to 'Changed Value'). Screenshots are below:

[v-yanjiang-msft]

Hi @pko997 ,

Do you mean result of section in the red box are incorrect?

Could you please send me the sample, or if the sample data is entered, paste the code in Advanced Editor here.

Best regards,

Community Support Team_yanjiang

[pko997]

Yes, I mean on that section and also for historical values in the "status" column. There is a sample code for the result and status column (Sheet1 is the name of the table):

resultSheet1_color = 
var _previous=maxx(filter(all('Sheet1'),'Sheet1'[car_id]=max('Sheet1'[car_id])&&'Sheet1'[date]<max('Sheet1'[date])),'Sheet1'[result])
return
if(max('Sheet1'[result])<>_previous&&_previous<>BLANK(),"light green")

statusSheet1_color = 
var _previous=maxx(filter(all('Sheet1'),'Sheet1'[car_id]=max('Sheet1'[car_id])&&'Sheet1'[date]<max('Sheet1'[date])),'Sheet1'[status])
return
if(max('Sheet1'[status])<>_previous&&_previous<>BLANK(),"light green")

[pko997]

There is data that I've used.

car_id	result	status	date	car_value	new_car_value
10	New Record	historical	04/06/2021 00:00		10carnewcreated
10	Latest	lastest	05/08/2021 00:00	10carnewcreated	1010carnewvalue
12	New created	historical	06/08/2021 00:00	10carnewcreated	1010carnewvalue
12	Latest	historical	15/08/2022 00:00	1010carnewvalue	101010carnewval
14	New created	lastest	20/08/2021 00:00	101010carnewval
14	Latest	historical	04/06/2022 00:00		12carnewcreated
16	New created	lastest	05/08/2020 00:00	12carnewcreated
16	Changed Record	historical	11/01/2021 00:00		14carnewcreated
16	Changed Record	historical	05/08/2021 00:00	14carnewcreated	1414carnewvalue
16	Changed Record	historical	08/08/2021 00:00	14carnewcreated	1414carnewvalue
16	Latest	historical	09/08/2022 00:00	14carnewcreated1	1414carnewvalue
18	New Record	historical	15/08/2021 00:00	1414carnewvalue
18	Latest	Latest	17/08/2022 00:00	1414carnewvalue11
18	Latest	Latest	19/08/2022 00:00	1414carnewvalue123

[v-yanjiang-msft]

Hi @pko997 ,

Modify the formula to:

resultSheet1_color = 
var _predate=maxx(filter(all('Sheet1'),'Sheet1'[car_id]=max('Sheet1'[car_id])&&'Sheet1'[date]<max('Sheet1'[date])),'Sheet1'[date])
var _previous=maxx(filter(all('Sheet1'),'Sheet1'[car_id]=max('Sheet1'[car_id])&&'Sheet1'[date]=_predate),'Sheet1'[result])
return
if(max('Sheet1'[result])<>_previous&&_previous<>BLANK(),"light green")

Best regards,

Community Support Team_yanjiang

[pko997]

Hi @v-yanjiang-msft , it is working now, thanks a lot.