Sort Order

[SignorSoprano]

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. 

[SignorSoprano · Accepted 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.

[amitchandak]

@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

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

If their issue with sort order has sort column

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[SignorSoprano]

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

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

[SignorSoprano]

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:

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.