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