Search code examples
powerbidaxdaxstudio

How to change background color of column based of comparing values over time with DAX

I have a problem right now

I want to change the background color of a measure column in matrix table over time by comparing values I want to give a specific color if the value of % zero has a positive evolution over time , and a specific color if the value has a negation evolution over time and if the value does not change , it keeps the change color Refer to PNG to see my initial Matrix table and the Matrix table i want Thanks for your feedback

enter image description here

enter image description here

Solution

  • In order to do this, you'll need to add a few calculations. You will need to adapt these to the real table names and columns in your own model:

    EDIT: Added this calculation so that the measures can reference a base measure rather than a column:

    SumOfValue = SUM(ConditionalFormatting[value])

    EDIT updated prior value to work with a measure Find the prior value:

    PriorValue =
CALCULATE (
    LASTNONBLANKVALUE(ConditionalFormatting[Date], [SumOfValue] ),
    ConditionalFormatting[Date] < SELECTEDVALUE(ConditionalFormatting[Date])
)

    EDIT Updated the status calc to refer to the formula Calculate the color:

    StatusColor = IF (
            [PriorValue] >= [SumOfValue] || ISBLANK([PriorValue]),
            "#FFFF00",
            "#8EA9DB"
        )

    EDIT Added calc to get the last color applied:

    LastStatusColor =
CALCULATE (
    LASTNONBLANKVALUE ( ConditionalFormatting[Date], [StatusColor] ),
    ConditionalFormatting[Date] < SELECTEDVALUE ( ConditionalFormatting[Date] )
)

    EDIT added calc for the final color logic:

    FinalColor =
IF ( [PriorValue] = [SumOfValue], [LastStatusColor], [StatusColor] )

    Once you have these measures, you can configure conditional formatting in the table.

    In the value in the table, click on the arrow for more options and choose Conditional Formatting > Background Color

    enter image description here

    EDIT use final color field instead In the dialogue, select Field Value and then select the FinalColor measure

    enter image description here

    EDIT Updated image to show new complete result Once this is done, you're table will look like this:

    Final result