If statement only working when I put a new measure?

[ningty09]

Hi,

So I have a calculated column where I want to categorize data based on size:

Size =
IF([SA M2]<1, "Small",
IF([SA M2]<5,"Medium",
"Large"))

where [SA M2] = SELECTEDVALUE(Surface_Area[SurfaceArea_m2]).

However, when I'm attempting to combine them, it doesn't work any more (shows everything as small).

Size 2 =
IF(SELECTEDVALUE(Surface_Area[SurfaceArea_m2])<1, "Small",
IF(SELECTEDVALUE(Surface_Area[SurfaceArea_m2])<5,"Medium",
"Large"))

Why would this be?

Any comment would be greatly appreciated

[Mariusz · Accepted solution]

Hi @ningty09 

Every Measure implies CALCULATE function automatically that preforms context transition, so...

 [SA M2] = SELECTEDVALUE( Surface_Area[SurfaceArea_m2] )

in reality, is...

 [SA M2] = CALCULATE( SELECTEDVALUE( Surface_Area[SurfaceArea_m2] ) )

therefore, your calculated column should look like that.

Size 2 = 
VAR x = CALCULATE( SELECTEDVALUE( Surface_Area[SurfaceArea_m2] ) )
RETURN 
SWITCH(
    TRUE(),
    x < 1, "Small",
    x < 5, "Medium",
    "Large"
)

this article will explain the concept of context transition.

https://www.sqlbi.com/articles/understanding-context-transition/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.