Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
3 years ago

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi Anonymous ,

        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