Forum Discussion

rksharma86's avatar
rksharma86
Frequent Visitor
2 years ago
Solved

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 ...
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    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 _result

     

    it worked like:

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
2 years ago

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 _result

 

it worked like: