Split data based on range

Question

Hello,

I've a column with name Cure. The data inside this column as shown below.

Cure

0

10.4

25.6

30.1

50.1

- I've two tables the Cure column is from first table. Now i'm generating the new column in first table using DAX. i.e,

=CALCULATE(MIN(Cure[MinCure]),FILTER(Cure,Cure[MinCure]<=Pilot_Lens_Lineage[Cure] && Cure[MaxCure]>= Pilot_Lens_Lineage[Cure]))

- Coming to second table the data is,

Cure Range	MinCure	MaxCure
0 >= Cure <15	0	15
15>= Cure < 30	15	30
30>= Cure < 45	30	45
45>= Cure < 60	45	60
60>= Cure < 75	60	75
75>= Cure < 90	75	90
90>= Cure < 105	90	105
105>= Cure < 120	105	120
120>= Cure < 135	120	135
135>= Cure < 150	135	150
150>= Cure < 165	150	165
165>= Cure < 180	165	180
180>= Cure < 195	180	195
195>= Cure < 210	195	210
210>= Cure < 225	210	225
225>= Cure < 240	225	240
240>= Cure < 255	240	255
255>= Cure < 270	255	270
270>= Cure < 285	270	285
285>= Cure < 300	285	300
300>= Cure < 315	300	315
>=315	315

- So the idea is the values of cure should fall in this CureRange data. For example 

Cure	CalculateColumn1
0	0
10.4	0
25.6	15
30.1	30
50.1	45

- I can able to get above data. Up to here everything is fine. The problem is If I've the data as below:

Cure

0

10.4

25.6

30.1

50.1

500

1000

2000

- The output is :

Cure	CalculateColumn1
0	0
10.4	0
25.6	15
30.1	30
50.1	45
500	0
1000	0
2000	0

- I want the value which are above 315, i should get the value in CalculateColumn1 as 315. For example as shown below.

Cure	CalculateColumn1
0	0
10.4	0
25.6	15
30.1	30
50.1	45
500	315
1000	315
2000	315

- Please help me on this...

Answer 1

Just add a big number into the last cell.

270>= Cure < 285	270	285
285>= Cure < 300	285	300
300>= Cure < 315	300	315
>=315	315	1000000

Answer 2

Also, the description for your Cure Range is incorrect.

It should be: 0 <= Cure < 15

Instead of:  0 >= Cure < 15

Additionally, your formula doesn't match your description. For example if Cure value is exactly 30 the formula returns 15 but based on your description of the range it seems you would want 30. To fix this you need to use > instead of >= in the filter condition on MaxCure.

(E.g. Cure[MaxCure]> Pilot_Lens_Lineage[Cure] instead of Cure[MaxCure]>= Pilot_Lens_Lineage[Cure] )

Answer 3

Ruve1k,

Once again thanks to help me. Now i Want to create pivot table and pivot chart for that i'm taking my dimension i.e Row label as CureRange and Values as Count(CalculatedColumn1) . I can able to see the data in the charts. But it is in disorder, how can i assign sort order for the chart and pivot table.

I want to see the data in step by step range. For example,

0 >= Cure <15

15>= Cure < 30

30>= Cure < 45

45>= Cure < 60

60>= Cure < 75

75>= Cure < 90

90>= Cure < 105

105>= Cure < 120

120>= Cure < 135

135>= Cure < 150

150>= Cure < 165

165>= Cure < 180

180>= Cure < 195

195>= Cure < 210

210>= Cure < 225

225>= Cure < 240

240>= Cure < 255

255>= Cure < 270

270>= Cure < 285

285>= Cure < 300

300>= Cure <  100000000

Answer 4

Basically, you have two options:

1. Edit the CureRange descriptions so that they sort alphabetically. (Add two spaces in front of the one beginning with zero, and add one space to the ones that start with a two-digit number. Then they should sort alphabetically and align nicely as well.)
2. Create a "sort by" column for CureRange. Basically it would be a numeric index for each CureRange that you could set up in a linked table and then you tell PowerPivot to sort CureRange by CureRangeSortColumn.

Answer 5

Hello, 

Can you check the image which i've attached here. I'm using the formulas to linked table. If i change the Range value to 20 it will change the entire table based on the value.

0>=Cure <20

20>=Cure <40

40>= Cure < 60 and soon...

- I did what you said but it won't works for me. Can you please explain me clearly step by step if u don't mind.

One more thing how can we attache sample file in this blog.

Answer 6

The space(s) would have to be inside quotes.

="  "&$C5&"<=Cure<"&$D5

You can post a  file on SkyDrive or on Google Docs and make it accessible to the public.

Answer 7

I did that but no use in it..

You can get the file from this link

https://docs.google.com/spreadsheet/ccc?key=0AvOr-gN1st4jdDI2LXlqTHI3V0xtSkwtTjNrS3YwaXc#gid=0

Plz download it.

Answer 8

Use this formula for CureRange

=REPT(" ",2*(3-LEN(C5)))&$C5&" <= Cure < "&$D5

Answer 9

I want to sort the CureRange in pivot chart not in the linked table. Please the pivot chart in the excel file.

Answer 10

If you use the formula I provided for CureRange in your linked table and refresh the linked table then it will sort properly in any PivotTable or PivotChart that is based on that data model.

Answer 11

Ya i did same thing here. Can you give me sample file which you tested. B'coz there is no change in my dashboard.

Answer 12

Email me your file directly at my username at gmail.

Answer 13

Please check your mail.

Answer 14

So apparently when PowerPivot imports the data from a linked table it trims off the leading spaces. :(

But you can just create the formula for CureRange in DAX as a calculated column.

=REPT(" ",2*(3-LEN([MinCure])))&[MinCure]&" <= Cure < "&[MaxCure]

This way the CureRange will have the leading spaces and will sort correctly.

Answer 15

Thanks a lot..