Create a range with Switch function

[Anonymous]

Im trying to create a range with the Switch function but can't really get it done.

The range coming from a value column so I want to range the value like that

0-10

10-20

20-30

30-40

etc......

is that a IF or switch I should use? And how can I create it?

[Anonymous · Accepted solution]

@Anonymous 

If you're creating a calculated column it looks like it would be

Group = SWITCH(TRUE(),'Table'[Value]>=100 && 'Table'[Value]<=110,"100-110")

[judspud]

Hi @Anonymous 

If i understand correctly you could use the following formula for a column. This takes the value and shows which range it is in

Range = if(ROUNDDOWN(Table[Value],-1)=Table[Value],ROUNDDOWN(Table[Value],-1) & "-" & ROUNDUP(Table[Value],-1)+10,ROUNDDOWN(Table[Value],-1) & "-" & ROUNDUP(Table[Value],-1))

Hope this helps

Thanks,

George

[Anonymous]

I would go the way of creating a Range table that is not related to any other table.  You can do a few ways, but an easy way is to use generate series function

RangeTable = GENERATESERIES(0, MAX(FactTable[RandomData]),10)

then add a column for the max

Max = [Min] +10

then create a label based on those two columns

Range Label = [Min]& " to " & [Max]

Then you can create a calculated colum :

Range= 
CALCULATE(
    VALUES( RangeTable[Range Label]),
    FILTER(
        RangeTable,
        FactTable[RandomData] >= RangeTable[Min]
        && FactTable[RandomData] < RangeTable[Max]
    )
)

[Anonymous]

I think I know what you are talking about here, but any chance you can post and example of what you would want the expected outcome to be?

[Anonymous]

I have a column with values like:

Values

116

144

234

146

254

221

etc.....

I wana create a range from that column so all between 100 to 110 values in that column should be in the new column 100-110.

[Anonymous]

I use a switch to group times into hour periods as follows which you could apply to your scenario

	"Time (Hour Start)",
		SWITCH(
			TRUE(),
        			Table[time]>=2300,"23:00",
        			Table[time]>=2200,"22:00",
        			Table[time]>=2100,"21:00",
        			Table[time]>=2000,"20:00",
        			Table[time]>=1900,"19:00",
        			Table[time]>=1800,"18:00",
        			Table[time]>=1700,"17:00",
        			Table[time]>=1600,"16:00",
        			Table[time]>=1500,"15:00",
        			Table[time]>=1400,"14:00",
        			Table[time]>=1300,"13:00",
        			Table[time]>=1200,"12:00",
        			Table[time]>=1100,"11:00",
        			Table[time]>=1000,"10:00",
        			Table[time]>=900,"09:00",
        			Table[time]>=800,"08:00",
        			Table[time]>=700,"07:00",
        			Table[time]>=600,"06:00",
        			Table[time]>=500,"05:00",
        			Table[time]>=400,"04:00",
        			Table[time]>=300,"03:00",
        			Table[time]>=200,"02:00",
        			Table[time]>=100,"01:00",
        			Table[time]>=0,"00:00"
		)

In your case you could also do

	"Group",
		SWITCH(
			TRUE(),
        			Table[Value]>=100 && Table[Value]<=110,"100-110"
		),

[Anonymous]

I tried this code

"Group",
		SWITCH(
			TRUE(),
        			Table[Value]>=100 && Table[Value]<=110,"100-110"
		),

It doesn't work.  Did you write the code correct?

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

By my tests, the suggestion of @Anonymous  should be helpful.

Here is my test.

group =
SWITCH (
    TRUE (),
    'Table1'[Values] >= 110
        && 'Table1'[Values] <= 120, "110-120",
    'Table1'[Values] >= 120
        && 'Table1'[Values] <= 130, "120-130",
    'Table1'[Values] >= 130
        && 'Table1'[Values] <= 140, "130-140",
    'Table1'[Values] >= 140
        && 'Table1'[Values] <= 150, "140-150",
    ">150"
)

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Anonymous · Accepted solution]

@Anonymous 

If you're creating a calculated column it looks like it would be

Group = SWITCH(TRUE(),'Table'[Value]>=100 && 'Table'[Value]<=110,"100-110")