# 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

• Edited by Wednesday, June 6, 2012 3:05 PM
Wednesday, June 6, 2012 2:58 PM

• 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
• Marked as answer by Thursday, June 7, 2012 8:50 AM
Wednesday, June 6, 2012 4:22 PM
• 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.

• Marked as answer by Friday, June 8, 2012 2:56 PM
Thursday, June 7, 2012 6:14 PM

### All replies

• 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
• Marked as answer by Thursday, June 7, 2012 8:50 AM
Wednesday, June 6, 2012 4:22 PM
• 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] )

Wednesday, June 6, 2012 5:03 PM
• 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

• Edited by Thursday, June 7, 2012 9:01 AM
Thursday, June 7, 2012 9:00 AM
• 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.
Thursday, June 7, 2012 1:18 PM
• 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.

Thursday, June 7, 2012 1:50 PM
• 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.

• Edited by Thursday, June 7, 2012 2:44 PM
Thursday, June 7, 2012 2:17 PM
• I did that but no use in it..

You can get the file from this link

Thursday, June 7, 2012 2:46 PM
• Use this formula for CureRange

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

Thursday, June 7, 2012 2:59 PM
• I want to sort the CureRange in pivot chart not in the linked table. Please the pivot chart in the excel file.

Thursday, June 7, 2012 3:30 PM
• 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.
Thursday, June 7, 2012 3:33 PM
• Ya i did same thing here. Can you give me sample file which you tested. B'coz there is no change in my dashboard.

Thursday, June 7, 2012 4:36 PM
Thursday, June 7, 2012 4:44 PM
Thursday, June 7, 2012 5:34 PM
• 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.

• Marked as answer by Friday, June 8, 2012 2:56 PM
Thursday, June 7, 2012 6:14 PM
• Thanks a lot..
Friday, June 8, 2012 2:56 PM