Forum Discussion
Helper VDAX/Group(Bins) for running buckets
Hi,
My data set has a column named "Time" with time stamp values.
When I right click the field "Time" & create new group, it provides me an way to create groups/bins/buckets of equal specifed durations. Apparently, it creates a column in the table with corresponding bucket value matching "Time" column. When I create a continuous bar chart/histogram, the bucktets falls in X axis and count / total for some other fields falls in Y axis. So far everyone aware.
My requirement is that I need the X axis to be a running bucket.
Instead of X axis value as follows,
00:00:00 AM, 3:00:00 AM, 6:00:00 AM, 9:00:00 AM, 12:00:00 PM, 3:00:00 PM, 6:00:00 PM, 9:00:00 PM & 12:00:00 AM.
I need the X axis value to be as follows.
00:00:00 AM - 03:00:00 AM, 01:00:00 AM - 04:00:00 AM, 02:00:00 AM - 05:00:00 AM, and so on till 09:00:00 PM - 12:00:00 AM.
I aware a histogram cannot be created with such X axis. But I can create a clustered/stack column chart with the values as X axis.
Any workaround would help me a lot.
Thanks
Vishvesh R
This is a sample of the table again (simplified):
Time Time Bucket BucketSortOrder 01:10:00 23-2 23 01:10:00 0-3 0 01:10:00 1-4 1 06:20:22 4-7 4 06:20:22 5-8 5 06:20:22 6-9 6 What you have to do is this:
1. Create a table with all your buckets in it. It has to look like this:
Time Bucket StartInclusive EndExclusive BucketSortOrder 00:00:00-03:00:00 00:00:00 03:00:00 0 01:00:00-04:00:00 01:00:00 04:00:00 1 02:00:00-05:00:00 02:00:00 05:00:00 2 03:00:00-06:00:00 03:00:00 06:00:00 3 04:00:00-07:00:00 04:00:00 07:00:00 4 and so on… up to 23:00:00-02:00:00 23:00:00 02:00:00 23 2. Once you have it, you cross-join it with the table that stores your unique times. Cross-joining in PQ (M) is easy: just create a column and type "=Your_Table_Name".
3. After that, you expand the table, so that the expanded columns appear.
4. Then you add another column that will now return TRUE if
[Time] >= [StartInclusive] and [Time] < [EndExclusive].
5. Then you filter the table leaving only the rows where the above is TRUE.
6. Then you remove all the columns apart from [Time], [Time Bucket] and [BucketSortOrder].
7. Now join the [Time] to the original column with the times and do all the things I've explained before...
Best
Darek
6 Replies
Mate, you have to create a table (in Power Query) of this structure:
Time Buckets (table)
Time|Time Bucket|BucketSortOrder
01:10:00|00:00:00 AM - 03:00:00 AM|1
01:10:00|01:00:00 AM - 04:00:00 AM|2
...
where
[Time] is a (hidden) column with all the times in your table,
[Time Bucket] is the bucket into which the Time falls (one time will fall into many buckets),
[BucketSortOrder] is a (hidden) column that you'll be sorting the buckets by.
Remember that if a bucket A appears in the table many times, it has to have the same BucketSortOrder.
Once this table's been created, you have to join it to your table on the [Time] column and the relationship will be M2M. Filtering will be one-way only, from [Time Buckets] to your table.
Then you can take the [Time Bucket] column from the Time Buckets table, drop it onto your visual and add your measures. They should aggregate correctly for the time periods/categories.
Best
Darek
- vissvess
Anonymous ,
Thanks for the inputs.
As per your advise, I need to create a table as shown by you.
For column 1 [Time], from my table I'll reference my existing query, remove other columns & remove duplicate in the specified column.
For column 2 & 3, I was working out the code.
As I have 3 hours bucket, for every timestamp, I'll have three buckets, if I am not wrong.
So If I create 3 columns with column name as sort order (1,23) so that I'll unpivot to create it as specified.
But If I use conditional column, I was not able to achieve the result.
Zubair_Muhammad & Anonymous ,
Need your help for the power query stuff.
Thanks
This is a sample of the table again (simplified):
Time Time Bucket BucketSortOrder 01:10:00 23-2 23 01:10:00 0-3 0 01:10:00 1-4 1 06:20:22 4-7 4 06:20:22 5-8 5 06:20:22 6-9 6 What you have to do is this:
1. Create a table with all your buckets in it. It has to look like this:
Time Bucket StartInclusive EndExclusive BucketSortOrder 00:00:00-03:00:00 00:00:00 03:00:00 0 01:00:00-04:00:00 01:00:00 04:00:00 1 02:00:00-05:00:00 02:00:00 05:00:00 2 03:00:00-06:00:00 03:00:00 06:00:00 3 04:00:00-07:00:00 04:00:00 07:00:00 4 and so on… up to 23:00:00-02:00:00 23:00:00 02:00:00 23 2. Once you have it, you cross-join it with the table that stores your unique times. Cross-joining in PQ (M) is easy: just create a column and type "=Your_Table_Name".
3. After that, you expand the table, so that the expanded columns appear.
4. Then you add another column that will now return TRUE if
[Time] >= [StartInclusive] and [Time] < [EndExclusive].
5. Then you filter the table leaving only the rows where the above is TRUE.
6. Then you remove all the columns apart from [Time], [Time Bucket] and [BucketSortOrder].
7. Now join the [Time] to the original column with the times and do all the things I've explained before...
Best
Darek
