Sorting Issue

[wnicholl]

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",

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")

Any help would be great. Thanks in advance! 

[Anonymous · Accepted solution]

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 · Accepted solution]

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

[wnicholl]

Thanks for your reply. I was able to properly sort the 2 sepeate tables using your sorting order process.  However when I tried to bring the tables together using a lookup table (using same brackets) I'm receiving the same error.  So for the combined totals I'm using a measure: NAME COUNTS = CALCULATE([Lead Name]+[Contact Name]), then I created the lookup table and mapped it to the both tables (Year Brackets). When I create a table the brackets are out of sync again. I tried a few things but no luck... Any suggestions with the combined effort?  Thanks again... I really appriciate everyones help.      

Table 1

Lead Name	Year Brackets
57201	< 1 Years
22622	2 Years
38999	3-5 Years
32357	6-10 Years
28558	11-20 Years
14450	21-30 Years
7943	31-40 Years
5691	41-50 Years
4587	51-60 Years
2746	61-70 Years
562	71-80 Years
123	81-110 Years

Table 2

Contact Name	Year Brackets
5351	< 1 Years
2605	2 Years
9981	3-5 Years
21068	6-10 Years
40326	11-20 Years
47887	21-30 Years
50917	31-40 Years
23362	41-50 Years
5565	51-60 Years
784	61-70 Years
47	71-80 Years

Combined Tables

Combined Name	Years Lookup Table
62552	< 1 Years
68884	11-20 Years
25227	2 Years
62337	21-30 Years
58860	31-40 Years
48980	3-5 Years
29053	41-50 Years
10152	51-60 Years
53425	6-10 Years
3530	61-70 Years
609	71-80 Years
123	81-110 Years

[Anonymous · Accepted solution]

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

[wnicholl]

Perfect!  Thank you again!!!

[Anonymous]

Or something similar for the other tables... You get the idea. Remember that a sorting column does not have to be comprised of consecutive numbers.

[Pragati11]

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]

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

[Anonymous]

If you can add an index column and use that for the sort instead of your actual column you can get it to sort how you want. If the data is already sorted correctly in power query, you can go to add columns and add an index column.

Once you've brought that into your dataset you can change which column your calculated columns are being sorted by in the Modeling tab. (Select the column you want sorted, then click on Sort By at the top and select the new index column)