How to create range column..

[Anonymous]

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?

[Anonymous]

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]

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

Date	Filter1	Filter2	Filter3	Filter4	A	B	Difference	Min	max
30/09/2021	3005664	165283	165283	1	19873.071	0	-19873.071	-496617.0001	260740.7999
30/09/2021	3005664	165283	165283	3	545137.0001	320041.8	-225095.2001	-496617.0001	260740.7999
30/09/2021	3005664	165283	165283	4	346182.0001	606922.8	260740.7999	-496617.0001	260740.7999
30/09/2021	3005664	165284	165284	1	19434.5776	0	-19434.5776	-496617.0001	260740.7999
30/09/2021	3005664	165284	165284	3	612285.0001	389400	-222885.0001	-496617.0001	260740.7999
30/09/2021	3005664	165284	165284	4	425418.0001	327630	-97788.0001	-496617.0001	260740.7999
30/09/2021	3005664	195631	195631	1	275661.9385	0	-275661.9385	-496617.0001	260740.7999
30/09/2021	3005664	195631	195631	3	487969.0001	0	-487969.0001	-496617.0001	260740.7999
30/09/2021	3005664	195631	195631	4	496617.0001	0	-496617.0001	-496617.0001	260740.7999
30/09/2021	3005664	195633	195633	1	25429.4389	0	-25429.4389	-496617.0001	260740.7999
01/10/2021	3005664	195633	195633	2	1741291.843	715067.3	-1026224.543	-1026224.543	-572
01/10/2021	3005664	195936	195936	1	3160.5417	0	-3160.5417	-1026224.543	-572
01/10/2021	3005664	195936	195936	2	973224.205	684324.5	-288899.705	-1026224.543	-572
01/10/2021	3005664	195936	195936	3	572	0	-572	-1026224.543	-572
01/10/2021	3005664	195936	195936	4	8551	0	-8551	-1026224.543	-572
01/10/2021	3005664	197155	197155	1	22774.2922	0	-22774.2922	-1026224.543	-572
01/10/2021	3005664	197155	197155	2	1046888.152	746252	-300636.1524	-1026224.543	-572
01/10/2021	3005664	19851	19851	1	31130.9121	0	-31130.9121	-1026224.543	-572
01/10/2021	3005664	19851	19851	3	361180.0001	0	-361180.0001	-1026224.543	-572

[Anonymous]

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]

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

[amitchandak]

@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]

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