Forum Discussion
Calculated column with switch expression is not returning what I need.
Problem : I want to create a calculated column by checking the value comparing the value of a cases column of the current year(2023) and return the same value for thhat given ID for all year.
My Approach: I am trying to create a calculated column (Segment) with the below expression and having a trouble to get the desired outcome of the calculated column
Segment =
SWITCH (
TRUE(),
Table[cases] >= 100, "Very High",
Table[cases] >= 50, "High",
Table[cases] >= 20, "Medium",
"Low"
)
I am geeting the result as below
ID Cases Fiscal Year Segment
1 785.55 2023 Very High
1 315.1 2022 Very High
1 7.23 2021 Low
1 1.37 2020 Low
2 5.73 2023 Low
2 3.93 2022 Low
2 7.05 2021 Low
2 6.01 2020 Low
3 35.58 2023 Medium
3 19.57 2022 Low
3 21.96 2021 Medium
3 4.36 2020 Low
Desired Outcome I want is below
ID Cases Fiscal Year Segment
1 785.55 2023 Very High
1 315.1 2022 Very High
1 7.23 2021 Very High
1 1.37 2020 Very High
2 5.73 2023 Low
2 3.93 2022 Low
2 7.05 2021 Low
2 6.01 2020 Low
3 35.58 2023 Medium
3 19.57 2022 Medium
3 21.96 2021 Medium
3 4.36 2020 Medium
I am not sure how can I get the desired column.
hi rksharma86 ,
not sure if i fully get you, try like:
column = VAR _total = SUMX( FILTER( data, data[ID]=EARLIER(data[ID]) &&data[Fiscal Year]<MAX(data[Fiscal Year]) ), data[cases] ) VAR _result = SWITCH( TRUE(), _total>=100, "Very High", _total>=50, "High", _total>=20, "Medium", "Low" ) RETURN _resultit worked like:
1 Reply
FreemanZSuper User
hi rksharma86 ,
not sure if i fully get you, try like:
column = VAR _total = SUMX( FILTER( data, data[ID]=EARLIER(data[ID]) &&data[Fiscal Year]<MAX(data[Fiscal Year]) ), data[cases] ) VAR _result = SWITCH( TRUE(), _total>=100, "Very High", _total>=50, "High", _total>=20, "Medium", "Low" ) RETURN _resultit worked like: