using Contains and multiple conditions with OR

[Anonymous]

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

[zuheirashraf17 · Accepted solution]

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]

Thanks, this worked!

[Greg_Deckler]

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]

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]

Hi @Anonymous,

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

Regards,

Daniel He