Sort by column does nothing

[Anonymous]

Hi!

I have a workbook with multiple tables that all connects to the same date-table. This date-table contains a special column which is used as axis for all the visuals. However, this axis/column is string so the sorting is not really working as I would like. To solve this I created a sort-column in the date-table but when sorting by this under Modeling > Sort by column nothing happens.

Below is a minimalized example of my file.

Date-table:

Date               special_axis                            sort_column

2016-12-03	2016	2016
2016-12-04	2016	2016
2016-12-05	2016	2016
2017-01-01	January - November 2017	2017
2017-08-01	January - November 2017	2017
2017-11-30	January - November 2017	2017
2017-12-01	December2017	3000
2017-12-02	December2017	3000

The date-column are simply dates imported from excel.

Then the special_axis is created using the DAX below:

special_axis = if(YEAR('date'[Date]) < 2017; FORMAT('date'[Date];"YYYY"); // if before 2017 > year only
    if(MONTH('date'[Date]) = 12; Format('date'[Date];"MMMM") & FORMAT('date'[Date];"YYYY"); // elif december 2017 > December 2017
        "January - November " & FORMAT('date'[Date];"YYYY"))) // else > January - November 2017

The sort column:

sort_column = if(len('date'[special_axis]) = 4; VALUE('date'[special_axis]); // if only year > year
    if(len('date'[special_axis]) > 16; VALUE(year('date'[Date])); // elif multiple monhts > year
        3000) ) // else sole month > 3000 (will always be last until year 3001..)

Then I have a simple value-table which is connected to the date-table on Date

Date               Value

2016-12-03	82
2016-12-04	47
2016-12-05	21
2017-01-01	33
2017-08-01	39
2017-11-30	36
2017-12-01	53
2017-12-02	82

A simple bar chart looks like this with special-axis on axis and Value in Value.

As you can see the Axis is sorted by the strings in special_axis even though I have selected to sort by sort_column under Modeling > Sort By Column.

How to use Sort By Column so it actually sorts by the column I have selected. (As you probably understood the December 2017 should be to the right in the chart.)

[Greg_Deckler]

Try clicking the ellipses in the visual and changing the sort to the Date column? Is your Sort By on the Date column or the Special Axis column?

[Anonymous]

By ellipses in the visual you mean the three small dots in the upper right corner of the visual? If so there is not the option sort by  date, only be special axis or Value. 

Regarding the second question this is where I get a bit confused. If marking the column Date in the date table on the right and then select Sort By Column it shows a tick next to Date (default). See below:

If marking the column special_axis in the date table on the right and then select Sort By Column it is a tick next to axis_special (default). The same goes for when marking sort_column(order_column). But nothing happens with the visual. 

I have also tried to add the Date-column and the order column to the visual´s axis cell, below the axis special. But this doesn´t change anything.

[v-ljerr-msft]

Hi @Anonymous,

In your scenario, you should firstly select [special_axis] column, and sort [special_axis] column by [sort_column]. Then it should work as expected like below.

However, as both [special_axis] column and [sort_column] are created with DAX and rely on [Date] column, you'll receive an circle dependency error when trying sorting [special_axis] column by [sort_column]. So you'll need to create the two columns in Query Editor with M. 

Regards