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 highlights show up. What am I missing? 

 

Highlighting Flag = VAR Highlighting = SELECTEDVALUE('vw_strax_1ScheduledAppointments'[AccountNumber])

RETURN
IF(
ISBLANK([AccountNumber])=true,"Yes",
IF(LEFT([AccountNumber],1) IN
{"U","E"},"Yes",
IF([AppointmentType] IN
{
"Install - Builder - Sold Homes - LVL 1",
"Install - Builder - Sold Homes - LVL 2"
}
&& LEFT([Accountnumber],1)="1", "Yes",
IF([AppointmentType] IN
{"Service - Builder - Builder Awareness"}
&& LEFT([AccountNumber],1)="5", "Yes"
))))
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.

5 Replies

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

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