Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to create range column..

Hi All, 

Is there any way to create range column like this -10-5, -5-0, 0-5 etc automatic.

I have one column which is start from -10 and highest value is 1000 i need to increments by 5 on it.

Any one can help me?

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Generate a series and then create a text column on that

    new table=

    addcolumns(generateseries(-10, 1000, 5) , "end value", [Value] +5 , "Bucket", [Value] &" - " & "Value"+5)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak Thanks for your reply, i created a new table with bucket column but now how can i use this table with the actual data table i need to count the number between this range.
      New Table

      Main Data Table

       

      And i want to count (Count Column) the values between this bucket which is given in the main data table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    I have a question. If the value of the Main data table meets the range of multiple rows in the new table [bucket] column, how should it be counted ? Like in my screenshot ?

    There are 5 values from 1 to 10 that meet the range of 0-5, so 5 is displayed, and 6 values meet the range of 5-10, so 6 is displayed, one value meets the range of 10-15, so 1 is displayed,and the rest are not satisfied, so 0 is displayed . Or is it that the two tables have unique values that correspond to each other, and then determine whether each row is satisfied ?

    If the results of the above screenshots can meet your needs, you can refer to my sample.

    Create a measure to count the numbers of values in Main Data table which can between the ranges . 

    Measure  = CALCULATE(COUNT([Column1]),FILTER(ALL('Main data'),[Column1]<=MAX('Table'[end value])&&[Column1]>=MAX('Table'[Value])))

    I have attached my pbix file ,you can refer to it .

     

    Best Regards

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Anonymous Thanks for your reply, It will help me but one more problem is that i have 5 filter column in a main data table and i don't think so this bucket will run dynamically and change the output. Isn't it?
      Because we have use x axis from the new table right.
      Please correct me if i'm wrong.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    You said that you have 5 filter column in a main data table , can you explain it with data or screenshot ?

     

    Best Regards

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Anonymous  My requirment is somthing like this

      I have 7 column in main data file filter1, filter2, filter3, filter4, A, B,
      difference (B-A) so i have created 2 other columns in data file Min and Max from difference column.
      I need 15 bucket to show in graph starting from Min values (Nearest round of value) to top highest value.
      And Min and max values may change for each day (For now i have created manully min or max value for each day (If there any way to automate this process for perticular day it will help me)
      2nd day may be it can be more then previous day max or less then previous min.
      So i created single select date filter to show only one day bucket on graph.
      And the bucket have to change according to filter selection
      Here is my data.
      Note:- Its a sample data in real one day it can be 20 to 25k rows i need 15 bucket for each day. Every day data will come with differenct values
      in A and B column we need to calculate difference, Min and max for perticular day and created automatic 15 bucket for perticular day.  
      Need Help.

      DateFilter1Filter2Filter3Filter4ABDifferenceMinmax
      30/09/20213005664165283165283119873.0710-19873.071-496617.0001260740.7999
      30/09/202130056641652831652833545137.0001320041.8-225095.2001-496617.0001260740.7999
      30/09/202130056641652831652834346182.0001606922.8260740.7999-496617.0001260740.7999
      30/09/20213005664165284165284119434.57760-19434.5776-496617.0001260740.7999
      30/09/202130056641652841652843612285.0001389400-222885.0001-496617.0001260740.7999
      30/09/202130056641652841652844425418.0001327630-97788.0001-496617.0001260740.7999
      30/09/202130056641956311956311275661.93850-275661.9385-496617.0001260740.7999
      30/09/202130056641956311956313487969.00010-487969.0001-496617.0001260740.7999
      30/09/202130056641956311956314496617.00010-496617.0001-496617.0001260740.7999
      30/09/20213005664195633195633125429.43890-25429.4389-496617.0001260740.7999
      01/10/2021300566419563319563321741291.843715067.3-1026224.543-1026224.543-572
      01/10/2021300566419593619593613160.54170-3160.5417-1026224.543-572
      01/10/202130056641959361959362973224.205684324.5-288899.705-1026224.543-572
      01/10/2021300566419593619593635720-572-1026224.543-572
      01/10/20213005664195936195936485510-8551-1026224.543-572
      01/10/20213005664197155197155122774.29220-22774.2922-1026224.543-572
      01/10/2021300566419715519715521046888.152746252-300636.1524-1026224.543-572
      01/10/202130056641985119851131130.91210-31130.9121-1026224.543-572
      01/10/2021300566419851198513361180.00010-361180.0001-1026224.543-572