Create range between two values with SWITCH

[rsanyoto]

Hi all,

I would like to create a new column based on the margin value where some categories  need to be created so:
 Between 0 - 10000 , 10000 - 20000, 20000-30000, >30000

How would you to write the right measure ?

[harshnathani · Accepted solution]

Hi @rsanyoto ,

Incase you want to create a new Column,

New COLUMN =
SWITCH (
    TRUE (),
    'Table'[Margin] > 30000, ">30000",
    'Table'[Margin] <= 30000
        && 'Table'[Margin] > 20000, " 20000-30000",
    'Table'[Margin] <= 20000
        && 'Table'[Margin] > 10000, " 10000-20000",
    "Between 0-10000"
)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[kentyler]

Hello,

This is an example of the pattern called Parameter Table https://www.daxpatterns.com/parameter-table/

The basic approach is to create a table that holds your categories.

I made a small sample table 

and some sample data  
and then I wrote my measures, following the Pattern
one to get the value to be categorized

Selected Value =

VAR current_value = SELECTEDVALUE('values'[value])

return current_value

One to get the Upper Boundary for each category

Upper Boundary = MAX(Categories[max])

One to get the Lower Boundary

Lower Boundary = Min(Categories[min])
And then a measure to figure out the category for the current row

In Category = CALCULATE(

MAX(Categories[category]),

FILTER(Categories,Categories[Lower Boundary] <= [Selected Value]),

FILTER(Categories,Categories[Upper Boundary] > [Selected Value])

)

because I do not have to do any calculation to get the value to be categorized I do not need to do a temporary table as is shown in the sample code for the Pattern

I created a short video explaining my solution https://www.youtube.com/watch?v=m6JJvoqLxNg

[rsanyoto]

Great YouTube video with simply explanation! 

[amitchandak]

@rsanyoto , Refer if this can help

https://www.daxpatterns.com/dynamic-segmentation/

https://www.daxpatterns.com/static-segmentation/