Forum Discussion
Sorting Issue
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
- 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
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
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 |
- 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
DPerfect! Thank you again!!!