Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I created a calculated column which will bucket items into 4hr time bands. My goal is to be able to sort the time bands by their true time from AM - PM. Current problem is that this column will sort by their respective number and not in the order of time. I tried to sort by HourNum but I keep getting the following error message:
Any ideas on how I can sort?
Below is the syntax on how I created the calculated column:
Solved! Go to Solution.
@Anonymous
There are a few ways to do this, but one would be to create another column containing the first hour of each Time Band, which will have a 1:1 correspondence with 4hr Time Band.
For example
4hr Time Band Start = QUOTIENT( 'Time'[HourNum], 4 ) * 4
Then set 4hr Time Band to sort by 4hr Time Band Start
Regards,
Owen
Hi win_toeknee,
If you want to sort by time duration, you could try to create another column like below
5hr Time Band = SWITCH( TRUE(), 'Time'[HourNum] >= 0 && 'Time'[HourNum] <= 3, 1, 'Time'[HourNum] >= 4 && 'Time'[HourNum] <= 7, 2, 'Time'[HourNum] >= 8 && 'Time'[HourNum] <= 11, 3, 'Time'[HourNum] >= 12 && 'Time'[HourNum] <= 15, 4, 'Time'[HourNum] >= 16 && 'Time'[HourNum] <= 19, 5, 'Time'[HourNum] >= 20 && 'Time'[HourNum] <= 23, 6 )
Then you could [id] and [4hr Time Band] sort by [5hr Time Band] like below
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
adding an index maybe to sort the column?
@Anonymous
There are a few ways to do this, but one would be to create another column containing the first hour of each Time Band, which will have a 1:1 correspondence with 4hr Time Band.
For example
4hr Time Band Start = QUOTIENT( 'Time'[HourNum], 4 ) * 4
Then set 4hr Time Band to sort by 4hr Time Band Start
Regards,
Owen
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |