Forum Discussion

icdns's avatar
icdns
Post Patron
5 years ago
Solved

SWITCH STATEMENT

Hello,

 

Could someone help me in deriving result A to result B? Wherein I wanted to fall General Office in Small Business (see result B) 

I have 2 columns and I have used a SWITCH STATEMENT but it's not working 😞 

 

I'm getting below result: 

 

RESULT
SEG_MAJ_GRPSEG_DESC
CORP> 500
CORPNATIONAL OFFICE
HOME 
HOMELOCAL OFFICE
HOMEHOME OFFICE
SMALL BUSINESS< 500
N/AUNDETERMINED 

 

Here's my calculated column:

SEG_MAJ_GRP_SWITCH = IF(TABLE[SEG_MAJ_GRP] = "CORPORATE", SWITCH(TABLE[SEG_DESC], ">500",">500","NATIONAL OFFICE","NATIONAL OFFICE"),
 
IF(TABLE[SEG_MAJ_GRP] ="SMALL BUSINESS",SWITCH(TABLE[SEG_DESC], "< 500","< 500","GENERAL OFFICE","GENERAL OFFICE"),
 
IF(TABLE[SEG_MAJ_GRP] = "HOME OFFICE",SWITCH(TABLE[SEG_DESC], "LOCAL OFFICE","LOCAL OFFICE","HOME OFFICE","HOME OFFICE"),"N/A")))
 
EXAMPLE SCENARIO

 

RESULT A
SEG_MAJ_GRPSEG_DESC
CORP> 500
CORPNATIONAL OFFICE
HOMEGENERAL OFFICE
HOMELOCAL OFFICE
HOMEHOME OFFICE
SMALL BUSINESS< 500
N/AUNDETERMINED 

 

Desired Result:

 

RESULT B
SEG_MAJ_GRPSEG_DESC
CORP> 500
CORPNATIONAL OFFICE
SMALL BUSINESSGENERAL OFFICE
HOMELOCAL OFFICE
HOMEHOME OFFICE
SMALL BUSINESS< 500
N/AUNDETERMINED 

 

Thanks a lot! 😄  

 
 

 

 

 

 

 

 

Need Help
  • manikumar34's avatar
    manikumar34
    5 years ago

    icdns , 

     

    Create another calculated column for that. Liek below. 

     

    SEG_MAJ_GRP1 = IF(AND('Table (2)'[SEG_MAJ_GRP] = "HOME",'Table (2)'[SEG_DESC]="GENERAL OFFICE"),SWITCH('Table (2)'[SEG_MAJ_GRP], "HOME","SMALL BUSINESS"),'Table (2)'[SEG_MAJ_GRP])
     
    If you think this is helpful Please Accept as Solution and Leave a like.
     
    Regards, 
    Manikumar

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    icdns , Try like

     

    SEG_MAJ_GRP_SWITCH = IF(TABLE[SEG_MAJ_GRP] = "CORPORATE",
    SWITCH(true(),
    TABLE[SEG_DESC], ">500",">500"
    ,"NATIONAL OFFICE","NATIONAL OFFICE"
    ),

     

    ///and add on

    • icdns's avatar
      icdns
      Post Patron
      5 years ago

      Hello amitchandak , 

       

      When i tried to use the logic, it returns below error: 

       

      What does this mean? 😞 Thank you for the help!

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        icdns , My bad , something like this

         

        SEG_MAJ_GRP_SWITCH = IF(TABLE[SEG_MAJ_GRP] = "CORPORATE",
        SWITCH(true(),
        TABLE[SEG_DESC]= "500",">500",
        TABLE[SEG_DESC]= "NATIONAL OFFICE","NATIONAL OFFICE"
        ),

    • icdns's avatar
      icdns
      Post Patron
      5 years ago

      Hi manikumar34 ,

       

      Sorry i cant paste the file here. but this is just my dataset 

       

      Thank you!

       

      Table Name: Table

       

      SEG_MAJ_GRPSEG_DESCCUSTOMER_COUNT
      CORP> 500169
      CORPNATIONAL OFFICE19
      HOMEGENERAL OFFICE3832
      HOMELOCAL OFFICE169
      HOMEHOME OFFICE110933
      MEKO USEMEKO USE0
      N/AUNDETERMINED1
      SMALL BUSINESS< 5001359
      • manikumar34's avatar
        manikumar34
        Solution Sage
        5 years ago

        icdns , 

         

        I'm not clear but if you are looking something like below, Use the provided DAX.

        SEG_MAJ_GRP_SWITCH = IF('Table (2)'[SEG_MAJ_GRP] = "CORP", SWITCH('Table (2)'[SEG_DESC], "> 500","> 500","NATIONAL OFFICE","NATIONAL OFFICE"),
         
        IF('Table (2)'[SEG_MAJ_GRP] ="SMALL BUSINESS",SWITCH('Table (2)'[SEG_DESC],"GENERAL OFFICE","GENERAL OFFICE","< 500","< 500"),
         
        IF('Table (2)'[SEG_MAJ_GRP] = "HOME",SWITCH('Table (2)'[SEG_DESC], "LOCAL OFFICE","LOCAL OFFICE","HOME OFFICE","HOME OFFICE","GENERAL OFFICE"),"N/A")))
         
        Regards, 
        Manikumar