Forum Discussion

Alta88's avatar
Alta88
Icon for Helper IV rankHelper IV
4 years ago
Solved

Highlighting Fields - formula

I have this very complex formula I'm using to highlight fields of data containing 'U's, 'E's , or blanks. I went into Conditional Formatting and tried highlighting the corresponding fields but no hig...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Alta88 

     

    You can use "Related" in a variable before those IF conditions. 

    Highlighting Flag =
VAR vAppointmentType =
    RELATED ( 'Scheduled Appointments'[AppointmentType] )
RETURN
    IF (
        ISBLANK ( 'Customer Data'[AccountNumber] ) = TRUE,
        "#F7DC6F",
        IF (
            LEFT ( 'Customer Data'[AccountNumber], 1 ) IN { "U", "E" },
            "#F7DC6F",
            IF (
                vAppointmentType
                    IN {
                        "Install - Builder - Sold Homes - LVL 1",
                        "Install - Builder - Sold Homes - LVL 2"
                    }
                        && LEFT ( 'Customer Data'[Accountnumber], 1 ) = "1",
                "#F7DC6F",
                IF (
                    vAppointmentType
                        IN { "Service - Builder - Builder Awareness" }
                            && LEFT ( 'Customer Data'[AccountNumber], 1 ) = "5",
                    "#F7DC6F"
                )
            )
        )
    )

    When referencing a column, it is recommended to have its table name before the column name to indicate which table it is from. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

Hi Alta88 

 

You formula is for creating a calculated column. Did you add this column to the data table? If so, you can create the following measure with a color code you want to set. For example,

color = IF ( SELECTEDVALUE ( 'TableName'[Highlighting Flag] ) = "Yes", "#F7DC6F" )

 

Then use this measure for conditional formatting. Select Format style by Field value based on above color measure. 

 

Note that when you format by Field value, the field you choose to base on must return color strings (color code e.g. "#F7DC6F" or color name e.g. "Yellow") as the results. The field can be either a column or a measure. So if you want to base it on a column, you can change your original column code into below one. Relace "Yes" with the color code directly. 

Highlighting Flag =
IF (
    ISBLANK ( [AccountNumber] ) = TRUE,
    "#F7DC6F",
    IF (
        LEFT ( [AccountNumber], 1 ) IN { "U", "E" },
        "#F7DC6F",
        IF (
            [AppointmentType]
                IN {
                    "Install - Builder - Sold Homes - LVL 1",
                    "Install - Builder - Sold Homes - LVL 2"
                }
                    && LEFT ( [Accountnumber], 1 ) = "1",
            "#F7DC6F",
            IF (
                [AppointmentType]
                    IN { "Service - Builder - Builder Awareness" }
                        && LEFT ( [AccountNumber], 1 ) = "5",
                "#F7DC6F"
            )
        )
    )
)

 

Hope this helps. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.