Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

using Contains and multiple conditions with OR

I have below formula and I have tried DAX contains and search functions, nothing is giving desired output, please suggest.

 

Job Category = 

IF
contains([Job Group],'Materials Management')
OR contains([Job Group],'Logistics')
OR contains([Job Group],'Distribution')
then 'Distribution'
ELSEIF
contains([Job Group],'Procurement')
OR contains([Job Group],'Categories')
OR contains([Job Group],'Geographic')
OR contains([Job Group],'Processes')
then
'Procurement and Sourcing'
ELSEIF
contains([Job Group],'Engineering & Construction')
then
'Facilities'
ELSE 'N/A'
END

Need Help
Tips and Tricks
  • zuheirashraf17's avatar
    zuheirashraf17
    7 years ago

    Hi Shilpi,

     

    I tested it and this should be working fine.

    Do let me know if you need further explanation for this;

     

    Category = SWITCH(TRUE(),
                    IF(SEARCH("Materials Management", [Job Group], 1, 0) > 0 || SEARCH("Distribution", [Job Group], 1, 0) > 0 || SEARCH("Distribution", [Job Group], 1, 0) > 0
                        ,TRUE()
                        ,FALSE()
                        ),"Distribution",
                    IF(SEARCH("Procurement", [Job Group], 1, 0) > 0 || SEARCH("Categories", [Job Group], 1, 0) > 0 || SEARCH("Geographic", [Job Group], 1, 0) > 0 || SEARCH("Processes", [Job Group], 1, 0) > 0
                        ,TRUE()
                        ,FALSE()
                        ),"Procurement and Sourcing",
                    IF(SEARCH("Engineering & Construction", [Job Group], 1, 0) > 0 
                        ,TRUE()
                        ,FALSE()
                        ),"Facilities",
                    "N/A"                  
)

5 Replies

  • zuheirashraf17's avatar
    zuheirashraf17
    Icon for Advocate II rankAdvocate II
    7 years ago

    Hi Shilpi,

     

    I tested it and this should be working fine.

    Do let me know if you need further explanation for this;

     

    Category = SWITCH(TRUE(),
                    IF(SEARCH("Materials Management", [Job Group], 1, 0) > 0 || SEARCH("Distribution", [Job Group], 1, 0) > 0 || SEARCH("Distribution", [Job Group], 1, 0) > 0
                        ,TRUE()
                        ,FALSE()
                        ),"Distribution",
                    IF(SEARCH("Procurement", [Job Group], 1, 0) > 0 || SEARCH("Categories", [Job Group], 1, 0) > 0 || SEARCH("Geographic", [Job Group], 1, 0) > 0 || SEARCH("Processes", [Job Group], 1, 0) > 0
                        ,TRUE()
                        ,FALSE()
                        ),"Procurement and Sourcing",
                    IF(SEARCH("Engineering & Construction", [Job Group], 1, 0) > 0 
                        ,TRUE()
                        ,FALSE()
                        ),"Facilities",
                    "N/A"                  
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, this worked!

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    That looks like a mix of DAX and M. And CONTAINS takes 3 parameters, Table, Column, Value. I would use a SWITCH though like:

     

    Job Category = 
SWITCH(TRUE(),
  CONTAINS('Table',[Job Group],'Materials Management') 
        || CONTAINS('Table',[Job Group],'Logistics')
        || CONTAINS('Table',[Job Group],'Distribution')
      ,'Distribution',
      CONTAINS('Table',[Job Group],'Procurement') 
        || CONTAINS('Table',[Job Group],'Categories')
        || CONTAINS('Table',[Job Group],'Geographic')
        || CONTAINS('Table',[Job Group],'Processes')
      ,'Procurement and Sourcing',
      CONTAINS('Table',[Job Group],'Engineering & Construction')
      ,'Facilities',
      'NA'
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Greg, unfortunately, this is not giving the desired output, its only passing the last condition, below is the snapshot. when I used it the if and contains, it gave me almost the same result, it only passes the last condition.

       

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous,

        Could you please offer some sample file to have a test and post your desired result if possible?

         

        Regards,

        Daniel He