SWITCH STATEMENT

[icdns]

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_GRP	SEG_DESC
CORP	> 500
CORP	NATIONAL OFFICE
HOME
HOME	LOCAL OFFICE
HOME	HOME OFFICE
SMALL BUSINESS	< 500
N/A	UNDETERMINED

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_GRP	SEG_DESC
CORP	> 500
CORP	NATIONAL OFFICE
HOME	GENERAL OFFICE
HOME	LOCAL OFFICE
HOME	HOME OFFICE
SMALL BUSINESS	< 500
N/A	UNDETERMINED

Desired Result:

RESULT B
SEG_MAJ_GRP	SEG_DESC
CORP	> 500
CORP	NATIONAL OFFICE
SMALL BUSINESS	GENERAL OFFICE
HOME	LOCAL OFFICE
HOME	HOME OFFICE
SMALL BUSINESS	< 500
N/A	UNDETERMINED

Thanks a lot! 😄  

[manikumar34]

@icdns , 

Can you share some sample data or PBIX with Sample data??

Reards, 

Manikumar

[icdns]

Hi @manikumar34 ,

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

Thank you!

Table Name: Table

SEG_MAJ_GRP	SEG_DESC	CUSTOMER_COUNT
CORP	> 500	169
CORP	NATIONAL OFFICE	19
HOME	GENERAL OFFICE	3832
HOME	LOCAL OFFICE	169
HOME	HOME OFFICE	110933
MEKO USE	MEKO USE	0
N/A	UNDETERMINED	1
SMALL BUSINESS	< 500	1359

[manikumar34]

@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

[icdns]

Hi @manikumar34 ,

Very near!! 😄 But is it possible instead of HOME, GENERAL OFFICE will fall under SMALL BUSINESS? (See font in Red)

Thanks alot!!! 🙂 

[manikumar34 · Accepted solution]

@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

[icdns]

it is now working! Thank you! 

[manikumar34]

@icdns 

Awesome, you got the Answer you are looking for. 

Your Kudos will be Appreciated. 🙂

Regards, 

Manikumar

[amitchandak]

@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]

Hello @amitchandak , 

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

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

[amitchandak]

@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]

Hi, @amitchandak ,

Tried your column. But it gives me the same result 😞