Switch true multiple columns and criteria

[Saxon10]

Hi,

DATA (FROM A TO I)

COUNTORY - UK & ENG Contain three different status which is "Matched",Not Required" and "Not Matched"

COUNTORY - US,INDIA,SA,PAK,SRL,AFG & BAN Contain two different status are "Matched" and "Not Matched"

STATUS - Completed and Incompleted

RULE:

1.If status column J is "Incompleted" andcolumn A to I find "Not Matched" then return the columns headers with coma.

2.If status column J is "Completed" and column A to I find "Matched" and "Not Required" then return is "Oky"

Column K is final result (please refer the snapshot). I am looking for New calculated option.

UK	US	INDIA	SA	SRL	PAK	BAN	AFG	ENG	STATUS	RESULT
MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT REQUIRED	COMPLETED	OKY
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT REQUIRED	COMPLETED	OKY
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	COMPLETED	OKY
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	PAK,BAN
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	BAN
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED	BAN,AFG
NOT REQUIRED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED	INDIA,PAK,BAN,AFG
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT COMPLETED	BAN,AFG,ENG
NOT REQUIRED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	INDIA,PAK,BAN
NOT REQUIRED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED	US,INDIA,PAK,BAN,AFG
NOT REQUIRED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	US,INDIA,PAK
NOT REQUIRED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	INDIA,PAK
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	NOT COMPLETED	BAN,ENG
NOT REQUIRED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	US,INDIA,PAK,BAN
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED	AFG
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	SA,SRL,PAK
NOT REQUIRED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	US,INDIA,SA,SRL,PAK
NOT REQUIRED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT COMPLETED	INDIA,PAK,ENG
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	MATCHED	NOT COMPLETED	PAK
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	PAK
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT COMPLETED	ENG
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED	PAK,BAN,AFG
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	SA
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT COMPLETED	BAN
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	SA,PAK,BAN
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	SA,BAN
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT COMPLETED	PAK,AFG
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT COMPLETED	AFG
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED	SA,PAK
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED	PAK,AFG
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT COMPLETED	PAK,BAN
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	NOT COMPLETED	PAK,BAN,ENG

[pranit828]

Hi @Saxon10 

Create a new column using the below DAX, it should resolve your issue.

_Result = 
// 1.If status column J is "Incompleted" andcolumn A to I find "Not Matched" then return columns headers with coma.
// 2.If status column J is "Completed" and column A to I find "Matched" and "Not Required" then return is "Oky"
VAR _uk = IF('Table'[UK] = "MATCHED",1,0)
VAR _us = IF('Table'[US] = "MATCHED",1,0)
VAR _in = IF('Table'[INDIA] = "MATCHED",1,0)
VAR _sa = IF('Table'[SA] = "MATCHED",1,0)
VAR _sl = IF('Table'[SRL] = "MATCHED",1,0)
VAR _pa = IF('Table'[PAK] = "MATCHED",1,0)
VAR _ba = IF('Table'[BAN] = "MATCHED",1,0)
VAR _af = IF('Table'[AFG] = "MATCHED",1,0)

VAR __uk = IF('Table'[UK] = "NOT MATCHED",1,0)
VAR __us = IF('Table'[US] = "NOT MATCHED",1,0)
VAR __in = IF('Table'[INDIA] = "NOT MATCHED",1,0)
VAR __sa = IF('Table'[SA] = "NOT MATCHED",1,0)
VAR __sl = IF('Table'[SRL] = "NOT MATCHED",1,0)
VAR __pa = IF('Table'[PAK] = "NOT MATCHED",1,0)
VAR __ba = IF('Table'[BAN] = "NOT MATCHED",1,0)
VAR __af = IF('Table'[AFG] = "NOT MATCHED",1,0)
VAR __en = IF('Table'[ENG] <> "NOT MATCHED",1,0)

VAR Val = SWITCH(TRUE(),
              'Table'[STATUS] = "COMPLETED",
                    IF(_uk+_us+_in+_sa+_sl+_pa+_ba+_af > 0 || 'Table'[ENG] = "NOT REQUIRED" ,"OKY",""),
              'Table'[STATUS] = "NOT COMPLETED",
                    IF(__uk=1,"UK","") & IF(__us=1,",US","") & IF(__in=1,",IND","") & IF(__SA=1,",SA","") & IF(__SA=1,",SA","") & IF(__sl=1,",SRL","") & IF(__pa=1,",PAK","") & IF(__ba=1,",BAN","") & IF(__af=1,",AFG","") & IF(__en=1,",END","")
       )
RETURN IF(SEARCH(",",Val,1,0) > 0,SUBSTITUTE(val,",","",1),val)

[Saxon10]

Thanks for your reply.

It's almost working but some of the column not giving correct result. Please refer the snapshot.

[pranit828 · Accepted solution]

HI @Saxon10 

Us ethe below I have verified. Don't forget to "Accept it as Solution"

_Result = 
// 1.If status column J is "Incompleted" andcolumn A to I find "Not Matched" then return columns headers with coma.
// 2.If status column J is "Completed" and column A to I find "Matched" and "Not Required" then return is "Oky"
VAR _uk = IF('Table'[UK] = "MATCHED",1,0)
VAR _us = IF('Table'[US] = "MATCHED",1,0)
VAR _in = IF('Table'[INDIA] = "MATCHED",1,0)
VAR _sa = IF('Table'[SA] = "MATCHED",1,0)
VAR _sl = IF('Table'[SRL] = "MATCHED",1,0)
VAR _pa = IF('Table'[PAK] = "MATCHED",1,0)
VAR _ba = IF('Table'[BAN] = "MATCHED",1,0)
VAR _af = IF('Table'[AFG] = "MATCHED",1,0)

VAR __uk = IF('Table'[UK] = "NOT MATCHED",1,0)
VAR __us = IF('Table'[US] = "NOT MATCHED",1,0)
VAR __in = IF('Table'[INDIA] = "NOT MATCHED",1,0)
VAR __sa = IF('Table'[SA] = "NOT MATCHED",1,0)
VAR __sl = IF('Table'[SRL] = "NOT MATCHED",1,0)
VAR __pa = IF('Table'[PAK] = "NOT MATCHED",1,0)
VAR __ba = IF('Table'[BAN] = "NOT MATCHED",1,0)
VAR __af = IF('Table'[AFG] = "NOT MATCHED",1,0)
VAR __en = IF('Table'[ENG] = "NOT MATCHED",1,0)

VAR Val = SWITCH(TRUE(),
              'Table'[STATUS] = "COMPLETED",
                    IF(_uk+_us+_in+_sa+_sl+_pa+_ba+_af > 0 || 'Table'[ENG] = "NOT REQUIRED" ,"OKY",""),
              'Table'[STATUS] = "NOT COMPLETED",
                    IF(__uk=1,"UK","") & IF(__us=1,",US","") & IF(__in=1,",IND","") & IF(__SA=1,",SA","") & IF(__SA=1,",SA","") & IF(__sl=1,",SRL","") & IF(__pa=1,",PAK","") & IF(__ba=1,",BAN","") & IF(__af=1,",AFG","") & IF(__en=1,",END","")
       )
RETURN IF(SEARCH(",",Val,1,0) > 0,SUBSTITUTE(val,",","",1),val)

[Saxon10]

Thanks for your reply and help. It's working now and I like your logic.

[Saxon10]

Hi,

Thanks for your reply and help. Can you please share your output file because I am receiving error.