Split an interval of values to populate new column

[Anonymous]

I have a column of float values between 0 and 1. I would like to split the 0-1 interval into three parts, so the two thresholds should be 0.36 and 0.66. Each value of the column would then translate into a "low", "medium" or "high", depending on the interval in which it falls. These values would then populate a new column. The result should be something like

Value	Class
0.15	Low
0.70	High
0.41	Medium

Bonus: what if I want to change the thresholds later, so that the interval is no longer splitted into three equal parts (they would still be three, but of different size)? How can I pass a modifiable threshold parameter to the function? 

[FrankAT]

Hi @Anonymous 

you can do it like this:

Interval = 
SWITCH(TRUE(),
    'Table'[Value] < 0.36, "Low",
    'Table'[Value] >= 0.36 && 'Table'[Value] < 0.66, "Medium",
    "High"
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Thanks a lot @FrankAT , it works flawlessly.

What about my last question? Do you know if there's an easy way to, let's say, put parameters in the function and set them from a visual? This is optional but I think it would improve the user experience.

[v-lili6-msft]

hi  @Anonymous 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, you create a measure and use what if parameter in it.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

by the way, measure is a dynamic summary  value, so it couldn't used a slicer or dim values.

Regards,

Lin

[FrankAT]

Hi @Anonymous 

At the moment I don't know of any way to pass a parameter to a calculated column via visual. Calculated columns are not dynamic.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)