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 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'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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
      Not applicable
      6 years ago
      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.
    • wnicholl's avatar
      wnicholl
      Resolver II
      6 years ago

      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 NameYear Brackets
      57201< 1 Years
      226222 Years
      389993-5 Years
      323576-10 Years
      2855811-20 Years
      1445021-30 Years
      794331-40 Years
      569141-50 Years
      458751-60 Years
      274661-70 Years
      56271-80 Years
      12381-110 Years

       

      Table 2

      Contact NameYear Brackets
      5351< 1 Years
      26052 Years
      99813-5 Years
      210686-10 Years
      4032611-20 Years
      4788721-30 Years
      5091731-40 Years
      2336241-50 Years
      556551-60 Years
      78461-70 Years
      4771-80 Years

       

      Combined Tables

      Combined NameYears Lookup Table
      62552< 1 Years
      6888411-20 Years
      252272 Years
      6233721-30 Years
      5886031-40 Years
      489803-5 Years
      2905341-50 Years
      1015251-60 Years
      534256-10 Years
      353061-70 Years
      60971-80 Years
      12381-110 Years
      • Anonymous's avatar
        Anonymous
        Not applicable
        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