Forum Discussion

wnicholl's avatar
wnicholl
Resolver II
6 years ago
Solved

Sorting Issue

Sorting Issue   I’m having issues with sorting the yearly brackets in my column chart.  I tried to create a conditional column in query editor, however the supporting built off a few calculated col...
  • Anonymous's avatar
    Anonymous
    6 years ago
    Year Brackets Order =
SWITCH(TRUE(),
	'your_table'[Years as Member] <= 10, 10,
	AND('your_table'[Years as Member] >= 11, 11,
	AND('your_table'[Years as Member] >= 21, 21,
	AND('your_table'[Years as Member] >= 31, 31,
	AND('your_table'[Years as Member] >= 41, 41,
	AND('your_table'[Years as Member] >= 51, 51,
	AND('your_table'[Years as Member] >= 61, 61,
	AND('your_table'[Years as Member] >= 71, 71,
	AND('your_table'[Years as Member] >= 81, 81
)

    In both tables create such a column and you're done. You'll use the column as the sorting column (hidden).

     

    Best

    D

  • Anonymous's avatar
    Anonymous
    6 years ago
    Instead of looking up, which by the way is a slow process, create one table with all the combinations, or relevant combinations, and then you'll have just one column which you can sort any way you want. Sometimes the answer does not lie in DAX but in the data model. Just change the model.

    There is a way to arbitrarily sort a measure's values but that requires stunts in the code, namely, attaching non-breaking invisible zero-width spaces to the values returned by the measure. You don't want to go that route.

    Best
    D
Pragati11's avatar
Pragati11
Super User
6 years ago

Hi wnicholl ,

 

You can create an index column for your Year buckets as follows: (taking only 3 buckets as an example, can be modified to more buckets as in your case)

 

SortYear = IF([Years] = "0-1 Years", 1, IF([Years] = "1-2 Years", 2, 3))

 

 

Then go to "Data Modelling", select the "YEAR" column and choose "Sort By Column" option, further selecting the new column that is created above using DAX - "SortYear".

This will sort your YEAR column with this new index column created.

 

NOTE: The screenshot is shown from a different sample data.

 

If the solution helps, give kudos and if it solves your issue please mark it as a solution! 🙂

 

Thanks,

Pragati

wnicholl's avatar
wnicholl
Resolver II
6 years ago

Thanks for your reply!  Will let you know how it goes...