I have 4 columns are A,B,C and D. Those 4 columns are contain number and text or number or text.

If column A&B and C&D are matched then return "Ok" and if column A&B and C&D are not matched then return "No" and if column A&B or C&D are blanks then return "Blanks".

I am achieved my desired result by using duplicate column (A&B , C&D) but I want the same result without duplication columns.

A&B = 'DATA2'[A]&"-"&'DATA2'[B]

C&D = 'DATA2'[C]&"-"&'DATA2'[D]

RESULT = IF(DATA2[A&B]="-","NO",IF(DATA2[C&D]="-","NO", IF(DATA2[A&B] = DATA2[C&D],"OK","NO")))

I am attempted one more DAX for formula but that one also giving error "Cannot convert value 'AA1' of type Text to type Number".

RESULT 1 = CALCULATE( IF ( SUMX( 'DATA2', 'DATA2'[A] 'DATA2'[B] 'DATA2'[C] 'DATA2'[D])>0,0,1 ))

I am looking for New calculated column option.

Any help much appreciated.

Data:

A   B   C   D   DESIRED RESULT
AA1 TT1 DD1 XX1 NO
AA1 TT1 DD1 XX1 NO
AA1 TT1 DD1 XX1 NO
AA1 TT1 DD1 XX1 NO
AA1 TT1 DD1 XX1 NO
AA1 TT1 DD1 XX1 NO
AA1 TT2 DD1 XX2 NO
AA1 TT2 DD1 XX2 NO
AA1 TT2 DD1 XX2 NO
AA1 TT2 DD1 XX2 NO
AA1 TT2 DD1 XX2 NO
BB1 RR1 DD1 XX3 NO
BB1 RR1 DD1 XX3 NO
BB1 RR1 DD1 XX3 NO
BB1 RR1 DD1 XX3 NO
BB1 RR2 GG1 HH1 NO
BB1 RR2 GG1 HH2 NO
BB1 RR2 GG1 HH3 NO
                NO
                NO
                NO
                NO
BB1 RR2 BB1 RR2 OK

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

CodePudding user response：

This is how I would solve it:

Result = 
var AB = [A]&[B]
var CD = [C]&[D]

return  
SWITCH(
    TRUE(),

    // Blank return if both AB and CD are blank/null
    (ISBLANK(AB) || AB = "") && (ISBLANK(CD) || CD = ""), BLANK(), 

    // A1 return if AB or CD (but not both) are blank
    (ISBLANK(AB) || AB = "") || (ISBLANK(CD) || CD = ""), "A1",

    // OK return if AB = CD
    AB = CD, "OK",

    // Catch all remaining cases with NO
    "NO"
)