Custom Sort on Straight Table

[scottc00]

I am having problems with a custom sort

I am using a straight table with 2 Dimensions.  [Trend Year] and [Trend Month]

I am trying to show the last 12 months of data (in order).

To do this I have the dimensions set up with these expressions

=IF(Match([Trend Year],'2020','2021','2022','2023','2024','2025'),[Trend Year])

 =IF(MATCH([Trend Month],'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),[Trend Month])

I use these expressions in the sort order

MATCH([Trend Year],'2020', '2021','2022','2023','2024','2025') 

MATCH([Trend Month],'Jan', 'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

The Sort by for every Expression has nothing checked.

My straight table looks fine as long as I don’t make any selections in the dashboard.  Meaning, As long as every dimension has data.  As soon as the filters cause blank columns it’s chaos.  Columns started disappearing so I added a hidden column that ignores the selections.  Now the columns don’t disappear, but they are no longer in the specified order. To be clear, this sort order issue is only when selections are made.  For example, if I select John Doe and he didn’t start until Dec2021, then Aug2021 – Nov2021 are in a random order (because there is no data), Dec2021 – Jul2022 are in the correct order.

[Rockstar7 · Accepted solution]

@scottc00  try below one

PICK(MATCH([Trend Year],'2020', '2021','2022','2023','2024','2025') ,1,2,3,4,5,6)

PICK(MATCH([Trend Month],'Jan', 'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),1,2,3,4,5,6,7,8,9,10,11,12)

[luizcdepaula]

I think the one above can work, and will improve performance as it will get rid for IF statements which can really drag performance.

Luiz

[scottc00]

Thanks Rockstar and Luiz. 

Rockstar, It looks like your suggestion should be used in the Sort Expression.  

But Luiz response suggests it should be used in the Dimension Expression.

When I use it in the Sort Expression it doesn't seem to fix the issue.

When I use it in the Dimension Expression it also doesn't seem to fix the issue, but it additionally changes all my years to 1,2,3,4,5,6 and my months to 1,2,3,4,5,6,7,8,9,10,11,12.

I am obviously using  it incorrectly.  Can  you be a bit more specific on how to use your suggestion.

Thanks

[scottc00]

Thanks for the help, Rockstar.  I created a calculated Dimension for both PICK(MATCH([Trend Month],’Jan’,’Feb’,…),1,2,…) and PICK(MATCH([Trend Year],’2020’,’2021’,…),1,2,…)

Then I sorted by those dimensions Num Value Only.  This solved the dimensions out of order problem. 

To fix the fields disappearing when no data, I added an expression that always has data when no selection is made and used set analysis to ignore the selections =Count({1<[Field]…  and then hid all he columns I don’t want to display using the Hide Column radio button on the Presentation tab