Forum Discussion

Helper 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

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

Helper 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

Community 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.