Forum Discussion

SamOvermars's avatar
SamOvermars
Helper I
3 years ago
Solved

How to create conditional formatting based on another measure value

I have created a column called "Goal Color" which can be either red or green. However, when setting up conditional formatting, I am asked to specify a summarization method. Here is the DAX formula I'm using for the conditional formatting column:

Goal Color =
VAR Value_From_Table1 = Table1[Column1]
VAR Category_From_Table2 = RELATED(Table2[Category])
RETURN
SWITCH (
TRUE(),
AND(Category_From_Table2 = "Category_A", Value_From_Table1 <= 10), 1,
AND(Category_From_Table2 = "Category_B", Value_From_Table1 <= 12), 1,
AND(Category_From_Table2 = "Category_C", Value_From_Table1 <= 55), 1,
AND(Category_From_Table2 = "Category_D", Value_From_Table1 <= 10), 1,
AND(Category_From_Table2 = "Category_E", Value_From_Table1 <= 14), 1,
2
)

When I choose the "Minimum" option, the color is always displayed as red, representing 1. If I choose the "Maximum" option, it will always show green. How to 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi SamOvermars ,

     

    I suggest you to create a measure and use Field value in conditional formatting.

    Goal Color = 
VAR Value_From_Table1 =
    SUM ( Table1[Column1] )
VAR Category_From_Table2 =
    CALCULATE (
        MAX ( Table2[Category] ),
        FILTER ( Table2, Table2[Related Column] = MAX ( Table1[Related Column] ) )
    )
RETURN
    SWITCH (
        TRUE (),
        AND ( Category_From_Table2 = "Category_A", Value_From_Table1 <= 10 ), "Red",
        AND ( Category_From_Table2 = "Category_B", Value_From_Table1 <= 12 ), "Red",
        AND ( Category_From_Table2 = "Category_C", Value_From_Table1 <= 55 ), "Red",
        AND ( Category_From_Table2 = "Category_D", Value_From_Table1 <= 10 ), "Red",
        AND ( Category_From_Table2 = "Category_E", Value_From_Table1 <= 14 ), "Red",
        "Green"
    )

    Use Field value as Format style and use this measure.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

3 Replies

  • nvprasad's avatar
    nvprasad
    Solution Sage
    3 years ago

    Hi SamOvermars,

     

    This is default behavior of DAX. The output of measure is scaller value (single value). Hence you need ot summarization instead of calling entire column in variable.

     

    Appreciate a Kudos! ‌‌
    If this helps and resolves the issue, please mark it as a Solution! ‌‌

    Regards,
    N V Durga Prasad

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi SamOvermars ,

     

    I suggest you to create a measure and use Field value in conditional formatting.

    Goal Color = 
VAR Value_From_Table1 =
    SUM ( Table1[Column1] )
VAR Category_From_Table2 =
    CALCULATE (
        MAX ( Table2[Category] ),
        FILTER ( Table2, Table2[Related Column] = MAX ( Table1[Related Column] ) )
    )
RETURN
    SWITCH (
        TRUE (),
        AND ( Category_From_Table2 = "Category_A", Value_From_Table1 <= 10 ), "Red",
        AND ( Category_From_Table2 = "Category_B", Value_From_Table1 <= 12 ), "Red",
        AND ( Category_From_Table2 = "Category_C", Value_From_Table1 <= 55 ), "Red",
        AND ( Category_From_Table2 = "Category_D", Value_From_Table1 <= 10 ), "Red",
        AND ( Category_From_Table2 = "Category_E", Value_From_Table1 <= 14 ), "Red",
        "Green"
    )

    Use Field value as Format style and use this measure.

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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