Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
SignorSoprano
Helper I
Helper I

Sort Order

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. 

 

 

1 ACCEPTED SOLUTION
SignorSoprano
Helper I
Helper I

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.

View solution in original post

5 REPLIES 5
SignorSoprano
Helper I
Helper I

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.

amitchandak
Super User
Super User

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I can't share the pbix file because it has sensitive information in it. But here is more complete data:

 

 

'Time Selection Sort'

SortSelection
0Week
1Month
2Quarter
3Year

 

 

'Time Selection'

SelectionValue

Week

01/04/2020
Week01/11/2020
Week01/18/2020
Week01/25/2020
Week02/01/2020
Week02/08/2020
Week02/15/2020
Week02/22/2020
Week02/29/2020
Week03/07/2020
Week03/14/2020
Week03/21/2020
Week03/28/2020
Week04/04/2020
Week04/11/2020
Week04/18/2020
Week04/25/2020
Week05/02/2020
Week05/09/2020
Week05/16/2020
Week05/23/2020
Month2020 Jan
Month2020 Feb
Month2020 Mar
Month2020 Apr
Month2020 May
Quarter2020 Q1
Quarter2020 Q2
Year2020

 

Both columns are of type Text

 

 

'Dates'

DateWeek EndingWeekMonthQuarterYear
01/01/202001/04/202001/04/20202020 Jan2020 Q12020
01/02/202001/04/202001/04/20202020 Jan2020 Q12020
01/03/202001/04/202001/04/20202020 Jan2020.Q12020
01/04/202001/04/202001/04/20202020 Jan2020 Q12020
01/05/202001/11/202001/11/20202020 Jan2020 Q12020
..................
05/16/202005/16/202005/16/20202020 May2020 Q22020
05/17/202005/23/202005/23/20202020 May2020 Q22020
05/18/202005/23/202005/23/20202020 May2020 Q22020
05/19/202005/23/202005/23/20202020 May2020 Q22020

 

[Week Ending] is of type Date. [Week] is of type Text, as is [Month], [Quarter], and [Year]

 

'Hours'

Project NumberPhaseTaskResource IDWeek EndingTypeHours
101234210101111234505/16/2020B23
101321440301111234505/16/2020B10
111111110101111234505/16/2020O7
.....................

 

 

'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:

 

DTI Billable Hours =
  VAR selectedValue = SELECTEDVALUE('Time Selection'[Selection], "Week")
  RETURN
    SWITCH(
      TRUE()
      , selectedValue = "Week"
      , CALCULATE(
          SUM('Hours'[Hours])
          , FILTER('Hours', [Type] = "B")
          , USERELATIONSHIP('Time  Selection'[Value], 'Date Table'[Week])
        )
      , selectedValue = "Month"
      , CALCULATE(
          SUM('Hours'[Hours])
          , FILTER('Hours', [Type] = "B")
          , USERELATIONSHIP('Time Intelligence Selection'[Value], 'Date Table'[Month])
        )
      , selectedValue = "Quarter"
      , CALCULATE(
          SUM('Resource Utilization Forecast'[Total Absence Hours])
          , FILTER('Hours', [Type] = "B")
          , USERELATIONSHIP('Time Intelligence Selection'[Value], 'Date Table'[Quarter])
        )
      , selectedValue = "Year"
      , CALCULATE(
          SUM('Resource Utilization Forecast'[Total Absence Hours])
          , FILTER('Hours', [Type] = "B")
          , USERELATIONSHIP('Time Intelligence Selection'[Value], 'Date Table'[Year])
        )
    )
 
One visual I have is a bar chart with 'Time Selection'[Value] as the x-axis and 'DTI Billable Hours' for the value field. When the user clicks on "Week" in the slicer, the 'Time Selection' table is filtered by "Week" and correctly displays all of the week ending dates on the x axis. My measure also correctly shows the values on the bars. However, the week ending dates are not sorted correctly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.