topic Re: Sorting Issue in DAX Commands and Tips

Sorting Issue

wnicholl — Thu, 20 Feb 2020 20:22:16 GMT

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

This is how the data shows:

Years

11-20 Years

0-1 Years

21-30 Years

31-40 Years

6-10 Years

3-5 Years

41-50 Years

1-2 Years

51-60 Years

61-70 Years

71-80 Years

81-110 Years

I first created a formula to calculate the years. This is a calculated column.

Years = IF( ISBLANK('Lead'[Join_Date__c]), BLANK(), INT( YEARFRAC([Join_Date__c], TODAY() ) ) )

Then I created another calulated column for the year brackets which are coming from 2 separate tables:

Table 1

Year Brackets = SWITCH(TRUE(),

'Lead'[Years as Member] <= 1, "0-1 Years",

AND('Lead'[Years as Member] >= 1, 'Lead'[Years as Member] <= 2), "1-2 Years",

AND('Lead'[Years as Member] >= 3, 'Lead'[Years as Member] <= 5), "3-5 Years",

AND('Lead'[Years as Member] >= 6, 'Lead'[Years as Member] <= 10), "6-10 Years",

AND('Lead'[Years as Member] >= 11, 'Lead'[Years as Member] <= 20), "11-20 Years",

AND('Lead'[Years as Member] >= 21, 'Lead'[Years as Member] <= 30), "21-30 Years",

AND('Lead'[Years as Member] >= 31, 'Lead'[Years as Member] <= 40), "31-40 Years",

AND('Lead'[Years as Member] >= 41, 'Lead'[Years as Member] <= 50), "41-50 Years",

AND('Lead'[Years as Member] >= 51, 'Lead'[Years as Member] <= 60), "51-60 Years",

AND('Lead'[Years as Member] >= 61, 'Lead'[Years as Member] <= 70), "61-70 Years",

AND('Lead'[Years as Member] >= 71, 'Lead'[Years as Member] <= 80), "71-80 Years",

AND('Lead'[Years as Member] >= 81, 'Lead'[Years as Member] <= 110), "81-110 Years")

Table 2

Year Brackets = SWITCH(TRUE(),

'Contact'[Years as Member] <= 1, "0-1 Years",

AND('Contact'[Years as Member] >= 1, 'Contact'[Years as Member] <= 2), "1-2 Years",

AND('Contact'[Years as Member] >= 3, 'Contact'[Years as Member] <=5), "3-5 Years",

AND('Contact'[Years as Member] >= 6, 'Contact'[Years as Member] <= 10), "6-10 Years",

AND('Contact'[Years as Member] >= 11, 'Contact'[Years as Member] <= 20), "11-20 Years",

AND('Contact'[Years as Member] >= 21, 'Contact'[Years as Member] <= 30), "21-30 Years",

AND('Contact'[Years as Member] >= 31, 'Contact'[Years as Member] <= 40), "31-40 Years",

AND('Contact'[Years as Member] >= 41, 'Contact'[Years as Member] <= 50), "41-50 Years",

AND('Contact'[Years as Member] >= 51, 'Contact'[Years as Member] <= 60), "51-60 Years",

AND('Contact'[Years as Member] >= 61, 'Contact'[Years as Member] <= 70), "61-70 Years",

AND('Contact'[Years as Member] >= 71, 'Contact'[Years as Member] <= 80), "71-80 Years",

AND('Contact'[Years as Member] >= 81, 'Contact'[Years as Member] <= 110), "81-110 Years")

I’ve built this lookup table to join the 2 tables:

Years

0-1 Years

1-2 Years

3-5 Years

6-10 Years

11-20 Years

21-30 Years

31-40 Years

41-50 Years

51-60 Years

61-70 Years

71-80 Years

81-110 Years

Side Note: I can get this to work when I use the following brackets structure:

Year Brackets = SWITCH(TRUE(),

'Lead'[Years as Member] <= 10, "1-10 Years",