IF Statement with Between Values

[Anonymous]

Hi,

I'm trying to group a list of item costs using a new measure (perhaps I should be using a column?) The groups I want are between £0 and £5000, £5000 to £10000, £10000 to £25000 and over £25000

I've successfully created the measure but it takes a very long time to refresh the data now as it calculates this new measure each time. This is the statement I used in the measure:

State = Switch( true(),
MIN('pupod'[Item Cost])>0,"£0 to £5K",
MIN('pupod'[Item Cost])>5000,"£6K to £10K",
MIN('pupod'[Item Cost])>10000,"£11K to £25K",
MIN('pupod'[Item Cost])>25000,">£25K"
)

I've looked for a post which does something similar but can't find anything, can anyone suggest a more efficient way of performing this calculation?

Thanks.

[TomMartens · Accepted solution]

Hey @Anonymous ,

in regards to the performance issue you are facing, I recommend that use create a variable that stores the value of this 

MIN('pupod'[Item Cost])

outside of the SWITCH, using a variable will prevent a recalc.
Next to that, I consider the conditions in the SWITCH in the wrong order, as the first condition that is TRUE will be used.

Meaning everything is gt 0, but also values that are greater than 25k will be flagged as "£0 to £5K".

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom