Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
7 years ago
Solved

Measure check

Dear community,

 

Below is my measure, is there a way i can simplify this?

 

BDE check = IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Valencia, Gonzalo","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Webster, Julianne","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="DiBlasio, Wendy","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Maliepaard, Yolanda","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Geyer, Jody","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Croft, Ben","Yes-BDE",IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive]="Gardner, Toby","Yes-BDE",if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Valencia, Gonzalo", "Yes-PL",if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Webster, Julianne","Yes-PL",if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="DiBlasio, Wendy","Yes-PL", if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Maliepaard, Yolanda","Yes-PL", if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Geyer, Jody","Yes-PL", if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Croft, Ben","Yes-PL", if('Sales and Pipeline Full Data Ex'[Pursuit Leader]="Gardner, Toby","Yes-PL",if('Sales and Pipeline Full Data Ex'[Identified By]="Valencia, Gonzalo","Yes-Identifed By", if('Sales and Pipeline Full Data Ex'[Identified By]="Webster, Julianne","Yes-Identified By", if('Sales and Pipeline Full Data Ex'[Identified By]="DiBlasio, Wendy","Yes-Identifiedy By", if('Sales and Pipeline Full Data Ex'[Identified By]="Maliepaard, Yolanda","Yes-Identified By", if('Sales and Pipeline Full Data Ex'[Identified By]="Geyer, Jody","Yes-Identified By", if('Sales and Pipeline Full Data Ex'[Identified By]="Croft, Ben","Yes-Identified By", if('Sales and Pipeline Full Data Ex'[Identified By]="Gardner, Toby","Yes- Identified By",if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Valencia, Gonzalo","Yes-PT",if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Webster, Julianne","Yes-PT", if('Sales and Pipeline Full Data Ex'[Pursuit Team]="DiBlasio, Wendy","Yes-PT",if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Maliepaard, Yolanda","Yes-PT", if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Geyer, Jody","Yes-PT", if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Croft, Ben","Yes-PT", if('Sales and Pipeline Full Data Ex'[Pursuit Team]="Gardner,Toby","Yes-PT","No"))))))))))))))))))))))))))))

Need Help
Show and Tell
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, vjnvinod 

    For your requirement, you could use IN Function in your formula,

    and "Pursuit Team" coloumn has more than 1 names in it, you need to use SEARCH Function in it.

    BDE check =
IF (
    'Sales and Pipeline Full Data Ex'[Global Business Development Executive]
        IN {
        "Valencia, Gonzalo",
        "Webster, Julianne",
        "DiBlasio, Wendy",
        "Maliepaard, Yolanda",
        "Geyer, Jody",
        "Croft, Ben",
        "Gardner, Toby"
    },
    "yes bde",
    IF (
        'Sales and Pipeline Full Data Ex'[Pursuit Leader]
            IN {
            "Valencia, Gonzalo",
            "Webster, Julianne",
            "DiBlasio, Wendy",
            "Maliepaard, Yolanda",
            "Geyer, Jody",
            "Croft, Ben",
            "Gardner, Toby"
        },
        "yes pl",
        IF (
            'Sales and Pipeline Full Data Ex'[Identified By]
                IN {
                "Valencia, Gonzalo",
                "Webster, Julianne",
                "DiBlasio, Wendy",
                "Maliepaard, Yolanda",
                "Geyer, Jody",
                "Croft, Ben",
                "Gardner, Toby"
            },
            "Yes-Identified By",
            IF (
                SEARCH (
                    "Valencia, Gonzalo",
                    'Sales and Pipeline Full Data Ex'[Pursuit Team],
                    1,
                    0
                ) > 0
                    || SEARCH (
                        "Webster, Julianne",
                        'Sales and Pipeline Full Data Ex'[Pursuit Team],
                        1,
                        0
                    ) > 0
                    || SEARCH (
                        "DiBlasio, Wendy",
                        'Sales and Pipeline Full Data Ex'[Pursuit Team],
                        1,
                        0
                    ) > 0
                    || SEARCH (
                        "Maliepaard, Yolanda",
                        'Sales and Pipeline Full Data Ex'[Pursuit Team],
                        1,
                        0
                    ) > 0
                    || SEARCH ( "Geyer, Jody", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0
                    || SEARCH ( "Croft, Ben", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0
                    || SEARCH (
                        "Gardner, Toby",
                        'Sales and Pipeline Full Data Ex'[Pursuit Team],
                        1,
                        0
                    ) > 0,
                "Yes-PT",
                "No"
            )
        )
    )
)

    By the way, you could use this tool to format your dax formula.

    https://www.daxformatter.com/#

    Note:  Please understand that this tool is provided “AS IS” with no warranties or guarantees , and confers no rights.

     

    Best Regards,

    Lin

7 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    7 years ago

    vjnvinod 

     

    Hi, you can start to reduce using IN

     

    Example:

     

    IF (Table1[NameColumn] IN {"Angela", "Betty", "Karl"} , "TOP",     Continue

     

     

    Regards

     

    Victor

    • vjnvinod's avatar
      vjnvinod
      Impactful Individual
      7 years ago

      Vvelarde 

       

      thanks, super helpful

       

      my new measure is below

       

      BDE check = IF('Sales and Pipeline Full Data Ex'[Global Business Development Executive] IN {"Valencia, Gonzalo","Webster, Julianne","DiBlasio, Wendy","Maliepaard, Yolanda","Geyer, Jody","Croft, Ben","Gardner, Toby"},"yes bde",IF('Sales and Pipeline Full Data Ex'[Pursuit Leader] IN {"Valencia, Gonzalo","Webster, Julianne","DiBlasio, Wendy","Maliepaard, Yolanda","Geyer, Jody","Croft, Ben","Gardner, Toby"},"yes pl",IF('Sales and Pipeline Full Data Ex'[Identified By] IN {"Valencia, Gonzalo","Webster, Julianne","DiBlasio, Wendy","Maliepaard, Yolanda","Geyer, Jody","Croft, Ben","Gardner, Toby"},"Yes-Identified By",IF('Sales and Pipeline Full Data Ex'[Pursuit Team] IN {"Valencia, Gonzalo","Webster, Julianne","DiBlasio, Wendy","Maliepaard, Yolanda","Geyer, Jody","Croft, Ben","Gardner, Toby"},"Yes-PT"))))

       

      there is one more issue here

      i see above measure is not reading my "Pursuit Team" coloumn

       

      below is my pursuit team coloumn, possibly because i have more thn 1 names in the field,

      is there a way we can sort this?

       

       

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    vjnvinod  :smileysurprised:

     

    You have simplified your question. But to provide any suggestion, Without any background of what exactly you are trying to do with this or provide with some sample data. Noone will have a scooby doo about this massive measure.

    • vjnvinod's avatar
      vjnvinod
      Impactful Individual
      7 years ago

      PattemManohar 

       

      :)

       

      below is my 4 coloumns

      and my table name is

      Sales and Pipeline Full Data Ex'[Global Business Development Executive.

       

       

       

       

       

       

      i want to create a new coloumn, with  the below condition

       

      1) if my coloumn

      "Global Business Development Executive" contains ,
      Valencia, Gonzalo, 
      Webster, Julianne,
      DiBlasio, Wendy,
      Maliepaard, Yolanda,
      Geyer, Jody then my new coloum should show me "Yes-BDE"
       
      and if my coloumn
      Pursuit Leader contains,
      Webster, Julianne, then my new coloum should show me "
      Yes-PL
       
      and similarly for other 2 fields as well
       
      the above measue works fine for me, except for coloumn Pursuit team because it has name combined something like this
       
      Jones, Colin;Chen, Cecile;
       
       
      • PattemManohar's avatar
        PattemManohar
        Community Champion
        7 years ago

        vjnvinod  Thanks for this. Is it possible to have some sample data to test on. Also, why can't you have a calculated column to derive this logic by having all these values into different list variables and then flagging them appropriately.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, vjnvinod 

    For your requirement, you could use IN Function in your formula,

    and "Pursuit Team" coloumn has more than 1 names in it, you need to use SEARCH Function in it.

    BDE check =
IF (
    'Sales and Pipeline Full Data Ex'[Global Business Development Executive]
        IN {
        "Valencia, Gonzalo",
        "Webster, Julianne",
        "DiBlasio, Wendy",
        "Maliepaard, Yolanda",
        "Geyer, Jody",
        "Croft, Ben",
        "Gardner, Toby"
    },
    "yes bde",
    IF (
        'Sales and Pipeline Full Data Ex'[Pursuit Leader]
            IN {
            "Valencia, Gonzalo",
            "Webster, Julianne",
            "DiBlasio, Wendy",
            "Maliepaard, Yolanda",
            "Geyer, Jody",
            "Croft, Ben",
            "Gardner, Toby"
        },
        "yes pl",
        IF (
            'Sales and Pipeline Full Data Ex'[Identified By]
                IN {
                "Valencia, Gonzalo",
                "Webster, Julianne",
                "DiBlasio, Wendy",
                "Maliepaard, Yolanda",
                "Geyer, Jody",
                "Croft, Ben",
                "Gardner, Toby"
            },
            "Yes-Identified By",
            IF (
                SEARCH (
                    "Valencia, Gonzalo",
                    'Sales and Pipeline Full Data Ex'[Pursuit Team],
                    1,
                    0
                ) > 0
                    || SEARCH (
                        "Webster, Julianne",
                        'Sales and Pipeline Full Data Ex'[Pursuit Team],
                        1,
                        0
                    ) > 0
                    || SEARCH (
                        "DiBlasio, Wendy",
                        'Sales and Pipeline Full Data Ex'[Pursuit Team],
                        1,
                        0
                    ) > 0
                    || SEARCH (
                        "Maliepaard, Yolanda",
                        'Sales and Pipeline Full Data Ex'[Pursuit Team],
                        1,
                        0
                    ) > 0
                    || SEARCH ( "Geyer, Jody", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0
                    || SEARCH ( "Croft, Ben", 'Sales and Pipeline Full Data Ex'[Pursuit Team], 1, 0 ) > 0
                    || SEARCH (
                        "Gardner, Toby",
                        'Sales and Pipeline Full Data Ex'[Pursuit Team],
                        1,
                        0
                    ) > 0,
                "Yes-PT",
                "No"
            )
        )
    )
)

    By the way, you could use this tool to format your dax formula.

    https://www.daxformatter.com/#

    Note:  Please understand that this tool is provided “AS IS” with no warranties or guarantees , and confers no rights.

     

    Best Regards,

    Lin