Return string if values exists in column

Question

I have one column that contains text such as,

column1
    3
    4
    5
    6
    7
    8
    9.2
    10
    11
    txt1
    txt2

I want to create a new column2 that gives me the following output.

column1         column2    
    3          3-6
    4          3-6
    5          3-6
    6          3-6
    7          7-10
    8          7-10
    9.2        7-10
    10         7-10
    11         11
    txt1       txt1
    txt2       txt2

I have tried with the following Dax function but i dont get it to work as it only returns "value if false". My format on Column1 is text.

 column2  = IF(CONTAINS(Table1;Table1[column1];"3";Table1[Column1];"4");"3-8";"9.5-10").........

I have tried with the FIND function aswell without luck. Someone have any tips? If someone nows how to do this in Excel perhaps it could be figured out that way?:D /D

Answer 1

I'm not sure exactly what your logic for bucketing values is, but you should be able to write something along these lines:

Column2 = SWITCH(TRUE(),
                 ISERROR(VALUE(Table1[Column1])), Table1[Column1],
                 VALUE(Table1[Column1]) >= 3 && VALUE(Table1[Column1]) <= 6, "3-6",
                 VALUE(Table1[Column1]) >= 7 && VALUE(Table1[Column1]) <= 10, "7-10",
                 Table1[Column1])

This SWITCH function will return the first thing that evaluates to true, otherwise, it returns the last argument. The first pair checks if the value can be converted to a number and if not returns the original value. The next two pairs check if the number is in certain ranges and returns specified strings for those ranges.

---

Here's a link that explains the SWITCH(TRUE()...) construction in more detail:

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/