March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am having a complicated sorting issue.
Here are some samples of tables I have:
Time Intelligence Selection Sort:
Selection | Sort
------------------
Week | 0
Month | 1
Quarter | 2
Year | 3
Time Intelligence Selection:
Selection | Value
---------------------
Week | 01/04/2020
Week | 01/11/2020
.....
Week | 05/16/2020
Month | 2020 Jan
Month | 2020 Feb
....
Month | 2020 May
Quarter | 2020 Q1
....
Quarter | 2020 Q2
Year | 2020
Month | 2020 Apr
(My actual table goes back to 2016.)
The above two tables are related by the 'Selection' columns.
My date table has columns corresponding to the "Selection" columns, so "Week", "Month", "Quarter" and "Year" with the values being equivalent to that in the "Value" column of the above table. I have that same "Value" column connected to the "Week", "Month", "Quarter", and "Year" columns in my date table with only one of them being active, obviously. I should mention that these columns in my date table are of text type and so is the 'Value' column in the 'Time Intelligence Selection' table above.
My actual data tables have a 'Week Ending Date' column which is related to a 'Week Ending' column in my date table which is of an actual date type.
My achieved intention is to have a slicer with the values of "Week", "Month", "Quarter", and "Year" in it. When the user selects one of those values, all of my visuals dynamically change to aggregate by the corresponding date period. I know I can have a drill down on my visuals using a date hierarchy. However, my client does not want that. They wanted a period selection slicer. So please do not respond with, "Just use a date hierarchy drill down on your visuals."
I have it working correctly. So, when the user chooses "Month" in the period selection slicer, the time intelligence selection table gets filtered by ['Selection'] = "Month" and only the month values (2020 Jan, 2020 Feb, 2020 Mar, 2020 Apr, etc.) show up on my visuals as the axis or column headings (for the particular visuals I am using). The data is correctly aggregated by month--in this case and by the appropriate period in the other cases, as well. My issue is the dates are not sorted.
The dates being shown in my visuals are those from the 'Time Intelligence Selection'[Value] column. This is a text column with multiple formats as shown above. I did add a sort column to this table but when I try to select it as the 'Sort By' column for the 'Value' column, I get an error saying "You cannot sort by a column that is already sorted, directly or indirectly." and my date values obviously show in the wrong order.
How can I get them sorted properly?
Thanks in advance.
Solved! Go to Solution.
Since no one came forward with a better solution, I have found one.
To reiterate the issue, I am wanting to sort a column which has multiple groupings, and so I want a sort order for each grouping. The only way I have found to get this working correctly is to embed zero-width spaces into my data. Spaces come alphabetically before the letter 'a'. Two spaces come alphabetically before one space, and so on and so forth. So, for each grouping, I calculated how many values were in the group and put that many spaces in front of the item I want first, one less space in front of the next item, etc. And I did that for each grouping. Power BI automatically sorts your values alphabetically, so this fixed my issue.
To add the zero-width spaces in front of my items, I used the UNICHAR function. Unichar code 8203 is a zero-width space. To get the number of spaces that I needed in front of my values, I used the REPT function.
REPT(UNICHAR(8203), NUM_OF_SPACES) & [MyValueColumn]
I concatenated this in front of my values for each group of values.
I now have a slicer which dynamically changes the x-axis for a year, quarter, month, and week and measures which act off of this slicer to calculate each time-intelligence value needed.
This took a lot of searching to figure out.
Since no one came forward with a better solution, I have found one.
To reiterate the issue, I am wanting to sort a column which has multiple groupings, and so I want a sort order for each grouping. The only way I have found to get this working correctly is to embed zero-width spaces into my data. Spaces come alphabetically before the letter 'a'. Two spaces come alphabetically before one space, and so on and so forth. So, for each grouping, I calculated how many values were in the group and put that many spaces in front of the item I want first, one less space in front of the next item, etc. And I did that for each grouping. Power BI automatically sorts your values alphabetically, so this fixed my issue.
To add the zero-width spaces in front of my items, I used the UNICHAR function. Unichar code 8203 is a zero-width space. To get the number of spaces that I needed in front of my values, I used the REPT function.
REPT(UNICHAR(8203), NUM_OF_SPACES) & [MyValueColumn]
I concatenated this in front of my values for each group of values.
I now have a slicer which dynamically changes the x-axis for a year, quarter, month, and week and measures which act off of this slicer to calculate each time-intelligence value needed.
This took a lot of searching to figure out.
@SignorSoprano , Not very clear to me
if you plan to change axis using slicer then bookmark is the option
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
If you want to change measure then
If their issue with sort order has sort column
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
@amitchandak I do not want to use bookmarks. I want to use this slicer to dynamically change the x axis of my visual. And I have that part working, correctly. I just want to sort the x axis values correctly. This is what I cannot figure out. I have made a sort column and it doesn't work. The related columns are also sorted, so I figured this column would automatically sort, as well, since a relationship exists. But that is not the case.
@SignorSoprano - This seems like a very nuanced issue and I think will need to be recreated in order to be solved. Any way you can speed up that process by sharing the PBIX or some additional sample data to recreate?
I can't share the pbix file because it has sensitive information in it. But here is more complete data:
'Time Selection Sort'
Sort | Selection |
0 | Week |
1 | Month |
2 | Quarter |
3 | Year |
'Time Selection'
Selection | Value |
Week | 01/04/2020 |
Week | 01/11/2020 |
Week | 01/18/2020 |
Week | 01/25/2020 |
Week | 02/01/2020 |
Week | 02/08/2020 |
Week | 02/15/2020 |
Week | 02/22/2020 |
Week | 02/29/2020 |
Week | 03/07/2020 |
Week | 03/14/2020 |
Week | 03/21/2020 |
Week | 03/28/2020 |
Week | 04/04/2020 |
Week | 04/11/2020 |
Week | 04/18/2020 |
Week | 04/25/2020 |
Week | 05/02/2020 |
Week | 05/09/2020 |
Week | 05/16/2020 |
Week | 05/23/2020 |
Month | 2020 Jan |
Month | 2020 Feb |
Month | 2020 Mar |
Month | 2020 Apr |
Month | 2020 May |
Quarter | 2020 Q1 |
Quarter | 2020 Q2 |
Year | 2020 |
Both columns are of type Text
'Dates'
Date | Week Ending | Week | Month | Quarter | Year |
01/01/2020 | 01/04/2020 | 01/04/2020 | 2020 Jan | 2020 Q1 | 2020 |
01/02/2020 | 01/04/2020 | 01/04/2020 | 2020 Jan | 2020 Q1 | 2020 |
01/03/2020 | 01/04/2020 | 01/04/2020 | 2020 Jan | 2020.Q1 | 2020 |
01/04/2020 | 01/04/2020 | 01/04/2020 | 2020 Jan | 2020 Q1 | 2020 |
01/05/2020 | 01/11/2020 | 01/11/2020 | 2020 Jan | 2020 Q1 | 2020 |
... | ... | ... | ... | ... | ... |
05/16/2020 | 05/16/2020 | 05/16/2020 | 2020 May | 2020 Q2 | 2020 |
05/17/2020 | 05/23/2020 | 05/23/2020 | 2020 May | 2020 Q2 | 2020 |
05/18/2020 | 05/23/2020 | 05/23/2020 | 2020 May | 2020 Q2 | 2020 |
05/19/2020 | 05/23/2020 | 05/23/2020 | 2020 May | 2020 Q2 | 2020 |
[Week Ending] is of type Date. [Week] is of type Text, as is [Month], [Quarter], and [Year]
'Hours'
Project Number | Phase | Task | Resource ID | Week Ending | Type | Hours |
10123421 | 01 | 01 | 1112345 | 05/16/2020 | B | 23 |
10132144 | 03 | 01 | 1112345 | 05/16/2020 | B | 10 |
11111111 | 01 | 01 | 1112345 | 05/16/2020 | O | 7 |
... | ... | ... | ... | ... | ... | ... |
'Time Selection Sort'[Selection] is in the value field of a chiclet slicer. The [Sort] column makes it display in the slicer like such:
| Week | Month | Quarter | Year |
'Time Selection Sort'[Selection] has a relationship to 'Time Selection'[Selection]
'Time Selection'[Value] has a relationship to 'Dates'[Week], 'Dates'[Month], 'Dates'[Quarter], and 'Dates'[Year]. Only the relationship to 'Dates'[Week] is active. The remaining are inactive.
'Hours'[Week Ending] has a relationship to 'Dates'[Week Ending].
I have measures which look at the current time selection and sum up the hours like such:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
99 | |
90 | |
72 | |
62 |
User | Count |
---|---|
140 | |
121 | |
106 | |
98 | |
94 |