Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Color data that has been changed in Power BI

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:

pko997_0-1684758758684.png

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?

pko997_1-1684759171974.png

 

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.

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

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.

vyanjiangmsft_0-1684917134750.png

Select corresponding measure in the dislog.

vyanjiangmsft_1-1684917213377.png

Get the correct result:

vyanjiangmsft_2-1684917239487.png

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

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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
Community Support
Community Support

Hi @Anonymous ,

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.

vyanjiangmsft_0-1684917134750.png

Select corresponding measure in the dislog.

vyanjiangmsft_1-1684917213377.png

Get the correct result:

vyanjiangmsft_2-1684917239487.png

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

Anonymous
Not applicable

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?

Hi @Anonymous ,

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

Anonymous
Not applicable

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:
Capture.PNG

Hi @Anonymous ,

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

vyanjiangmsft_0-1686043049983.png

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

 

Anonymous
Not applicable

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

 

Anonymous
Not applicable

There is data that I've used.

car_idresultstatusdatecar_valuenew_car_value
10New Recordhistorical04/06/2021 00:00 10carnewcreated
10Latestlastest05/08/2021 00:0010carnewcreated1010carnewvalue
12New createdhistorical06/08/2021 00:0010carnewcreated1010carnewvalue
12Latesthistorical15/08/2022 00:001010carnewvalue101010carnewval
14New createdlastest20/08/2021 00:00101010carnewval  
14Latesthistorical04/06/2022 00:00 12carnewcreated
16New createdlastest05/08/2020 00:0012carnewcreated  
16Changed Recordhistorical11/01/2021 00:00 14carnewcreated
16Changed Recordhistorical05/08/2021 00:0014carnewcreated1414carnewvalue
16Changed Recordhistorical08/08/2021 00:0014carnewcreated1414carnewvalue
16Latesthistorical09/08/2022 00:0014carnewcreated11414carnewvalue
18New Recordhistorical15/08/2021 00:001414carnewvalue  
18LatestLatest17/08/2022 00:001414carnewvalue11  
18LatestLatest19/08/2022 00:001414carnewvalue123  

Hi @Anonymous ,

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors