Forum Discussion
Helper IICalculating column with more than one IF statement
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.
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
5 Replies
sokgSolution Supplier
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.
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
kjartankhi 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.
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