Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Find UPPER except these specific values

I am using this to find improperly formatted name suffixes that are all capitalized, but I am wondering if with additional code I can filter out certain suffixes that are properly in all caps. Some examples are in the image below.

 

SF = IF (
[Suffix] = BLANK (),
BLANK (),
IF(
EXACT(UPPER('132330'[Suffix]), '132330'[Suffix])
, "yes", "no"))

 

 

Need Help

5 Replies

  • bhelou's avatar
    bhelou
    Responsive Resident
    3 years ago

    Hi , try this : 

    SF = IF (
    [Suffix] = BLANK (),
    BLANK (),
    IF(
    EXACT(UPPER('132330'[Suffix]), '132330'[Suffix])
    AND NOT(EXACT('132330'[Suffix], "JR.")),
    "yes", "no"
    )
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    bhelou I get this message.
    The syntax for 'AND' is incorrect. (DAX(IF ( [Suffix] = BLANK (), BLANK (), IF( EXACT(UPPER('132330'[Suffix]), '132330'[Suffix]) AND NOT(EXACT('132330'[Suffix], "JR.")), "yes", "no" ) )))

     

    I'm also wondering how I would add multiple results to filter out in addition to the "JR." you have in the sample.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    bhelou I tried changing the "AND" to & and now I get this error message.

     

  • bhelou's avatar
    bhelou
    Responsive Resident
    3 years ago

    hi , try to work with switch : 

    SF = SWITCH(
    TRUE(),
    [Suffix] = BLANK(), BLANK(),
    EXACT(UPPER('132330'[Suffix]), '132330'[Suffix]) && NOT(CONTAINS("JR,SR,III,IV,V", UPPER('132330'[Suffix]))), "yes",
    "no"
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      bhelou I get this message: Too few arguments were passed to the CONTAINS function. The minimum argument count for the function is 3.