Calculating column with more than one IF statement

[kjartank]

Hi there.

I basically have a column that consists of numbers from 1 to 8. I want to calculate a column with a 100 scale, where 8 is left blank.

1 = 100,

2 = 83,33,

3 = 66,66,

4 = 50,

5 = 33,33,

6 = 16,66

7 = 0

8 = null

I've tried to add a custom column, but am not sure of how to write the formula with 8 if statements.

[Anonymous]

Hi @kjartank,

I think switch function will be suitable, but if you result contains multiple result, it can only return as text format.

Calculate column:
Result = SWITCH([Column],1,"100",2,"83,33",3,"66,66",4,"50",5,"33,33",6,"16,66",7,"0",8,BLANK(),BLANK()) 

If you want to know how to split it and got the specified value, you can refer to below link:

DAX: How to Split (left) a text column on Character (space)?

Regards,

Xiaoxin Sheng

[kjartank]

hi @Anonymous

I've hopefully been trying to use that bit of code, but it states "Expressions that yield variant data-type cannot be used to define calculated columns." So unfortunately, it won't work.

[Anonymous · Accepted solution]

Hi @kjartank,

I think you also copy the "calculate column:" to your formula, right?
It means below formula is a calculate column, you don't need to copy it into the formula.

Result = SWITCH([Column],1,"100",2,"83,33",3,"66,66",4,"50",5,"33,33",6,"16,66",7,"0",8,BLANK(),BLANK()) 

Regards,

XIaoxin Sheng

[kjartank]

I had the formatting wrong. This will be so extremely useful, as I have lots of data that needs to be transformed.

Thanks a lot! 🙂

[sokg]

try this

New column = SWITCH(TRUE();

                               TABLE[COLUMN WHERE 1...8 LIVES]=1;100;

                               TABLE[COLUMN WHERE 1...8 LIVES]=2;83.33;

                               TABLE[COLUMN WHERE 1...8 LIVES]=3;66.66;

                               TABLE[COLUMN WHERE 1...8 LIVES]=4;50;

                               TABLE[COLUMN WHERE 1...8 LIVES]=5;33.33;

                               TABLE[COLUMN WHERE 1...8 LIVES]=6;16.66;

                               TABLE[COLUMN WHERE 1...8 LIVES]=7;0;

                               BLANK()

                               )

I  suppose that 1..8 are numbers.