locked
Split data based on range RRS feed

  • 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...





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

Answers

  • 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 Jagannalla 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 Jagannalla 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 Jagannalla 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 Jagannalla 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 ruve1k 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

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

    Plz download it.

    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
  • Email me your file directly at my username at gmail.
    Thursday, June 7, 2012 4:44 PM
  • Please check your mail.
    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 Jagannalla Friday, June 8, 2012 2:56 PM
    Thursday, June 7, 2012 6:14 PM
  • Thanks a lot..
    Friday, June 8, 2012 2:56 PM