Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
4 years ago
Solved

Switch multiple condition for multiple columns

 

I have 5 columns are A,B,C,D and type. Those 5 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".

If AB is blank and CD is not blanks in this case I need a result "A1". If CD is blank and AB is not blanks then I need a result "A1" as well.

 

I am applying blow mentioned DAX formula and I would like to add type column in same DAX code to get the final result.

Any advise how can I added type column in exciting DAX code or alternative way.

   

Result = var AB = [A]&[B]var CD = [C]&[D] return  SWITCH(    TRUE(),        (ISBLANK(AB) || AB = "") && (ISBLANK(CD) || CD = ""),

BLANK(),          (ISBLANK(AB) || AB = "") || (ISBLANK(CD) || CD = ""), "A1",         AB = CD, "OK",         "NO")

 

Fillter range and result:

If type is TTT then XX

if type is AA then X1

If type is BB then X2

If type is PR then X3

 

 

TYPEABCDDESIRED RESULTResult

DDAA1TT1DD1XX1NONO
DDAA1TT1DD1XX1NONO
DDAA1TT1DD1XX1NONO
TTAA1TT1DD1XX1XXNO
TTAA1TT1DD1XX1XXNO
TTAA1TT1DD1XX1XXNO
TRAA1TT2DD1XX2NONO
TRAA1TT2DD1XX2NONO
AAAA1TT2DD1XX2X1NO
AAAA1TT2DD1XX2X1NO
AAAA1TT2DD1XX2X1NO
BBBB1RR1DD1XX3X2NO
BBBB1RR1DD1XX3X2NO
BBBB1RR1DD1XX3X2NO
BBBB1RR1DD1XX3X2NO
BBBB1RR2GG1HH1X2NO
BBBB1RR2GG1HH2X2NO
BBBB1RR2GG1HH3X2NO
PR NO  X3A1
PR NO  X3A1
PR NO  X3A1
PR NO  X3A1
PRBB1RR2BB1RR2OKOK

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    Saxon10 Give this a try

     

    Result2 = 
VAR AB = [A] & [B]
VAR CD = [C] & [D]
RETURN
SWITCH (
    TRUE(),
    AB = CD, "OK",
    [TYPE] = "TT","XX",
    [TYPE] = "AA","X1",
    [TYPE] = "BB","X2",
    [TYPE] = "PR","X3",
    (ISBLANK ( AB ) || AB = "" ) && ( ISBLANK ( CD ) || CD = "" ), BLANK (),
    (ISBLANK ( AB ) || AB = "" ) || ( ISBLANK ( CD ) || CD = "" ), "A1",
    "NO"
    )

     

    All my results match your deisred result column.

     

     

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    Saxon10 Give this a try

     

    Result2 = 
VAR AB = [A] & [B]
VAR CD = [C] & [D]
RETURN
SWITCH (
    TRUE(),
    AB = CD, "OK",
    [TYPE] = "TT","XX",
    [TYPE] = "AA","X1",
    [TYPE] = "BB","X2",
    [TYPE] = "PR","X3",
    (ISBLANK ( AB ) || AB = "" ) && ( ISBLANK ( CD ) || CD = "" ), BLANK (),
    (ISBLANK ( AB ) || AB = "" ) || ( ISBLANK ( CD ) || CD = "" ), "A1",
    "NO"
    )

     

    All my results match your deisred result column.

     

     

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      4 years ago

      Thank you so much for your help and solution.