Forum Discussion
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:
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.
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.
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
10 Replies
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.
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
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
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?