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.

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

Ok, I ran into an issue with the formula you posted b/c the 'Appointment Type' comes fom a different table than the account number. Would I have to create a 'Related' column in my main table to get the formula to work? The two tables are 'Customer Data' (which is the main one) and 'Scheduled Appointments'.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
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.