Forum Discussion
Sort by two columns in DAX
I've spent hours with one of my Power BI consultants trying to do in DAX what is a two-second operation in Query Editor, but unfortunately this isn't quite happening!
All we want to do is to sort low to high value within the categories of another column. Unfortunately this has to be in DAX.
Below is a simplified Excel table to illustrate:
| Due status | Value | Invoice | Cumulative # invoices | On time | Cumulative On time # invoices | Cumulative on-time % | Rank |
| Past due | 20 | 1 | 1 | 0 | 0 | 0% | 1 |
| Past due | 26 | 1 | 2 | 0 | 0 | 0% | 2 |
| Past due | 52 | 1 | 3 | 0 | 0 | 0% | 3 |
| Past due | 420 | 1 | 4 | 0 | 0 | 0% | 4 |
| Due | 13 | 1 | 5 | 1 | 1 | 20% | 5 |
| Due | 52 | 1 | 6 | 1 | 2 | 33% | 6 |
| Due | 249 | 1 | 7 | 1 | 3 | 43% | 7 |
| Due | 953 | 1 | 8 | 1 | 4 | 50% | 8 |
| Due | 220854 | 1 | 9 | 1 | 5 | 56% | 9 |
| Due | 234923753 | 1 | 10 | 1 | 6 | 60% | 10 |
| Not yet due | 4 | 1 | 11 | 1 | 7 | 64% | 11 |
| Not yet due | 7 | 1 | 12 | 1 | 8 | 67% | 12 |
| Not yet due | 24 | 1 | 13 | 1 | 9 | 69% | 13 |
| Not yet due | 744 | 1 | 14 | 1 | 10 | 71% | 14 |
| Not yet due | 4545 | 1 | 15 | 1 | 11 | 73% | 15 |
| Not yet due | 24577 | 1 | 16 | 1 | 12 | 75% | 16 |
| Not yet due | 5675474 | 1 | 17 | 1 | 13 | 76% | 17 |
| Not yet due | 567567567 | 1 | 18 | 1 | 14 | 78% | 18 |
I don't understand why asceding ordering by value and then by due status doesn't give the right order (it seems to shuffle the invoices around), but getting the right answer here seems to be very complicated, and all other examples in the community seem to do something different.
Ultimately we then want to add the 'On-time %' calculation (as a measure, to be most efficient) but hopefully the above is clear.
Thank you in advance to the superstars who help us simple folk!
In Edit queries,
Add conditional column
sort the "status rank" column first, then sort the "value" column, add an index column,
Close &&apply, create columns
clc invoice = CALCULATE(SUM('Table'[Invoice]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index]))) on time = IF([Due status]="Past due",0,1) clc on time invo = IF([on time]=0,0,CALCULATE(SUM('Table'[Invoice]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])&&[on time]=1))) clc on time% = [clc on time invo]/[clc invoice]Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
In Edit queries,
Add conditional column
sort the "status rank" column first, then sort the "value" column, add an index column,
Close &&apply, create columns
clc invoice = CALCULATE(SUM('Table'[Invoice]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index]))) on time = IF([Due status]="Past due",0,1) clc on time invo = IF([on time]=0,0,CALCULATE(SUM('Table'[Invoice]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])&&[on time]=1))) clc on time% = [clc on time invo]/[clc invoice]Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Maggie, thank you so much for your solution, it's beautiful. I think I've fallen in love...
Witch one of the columns in your sample are you trying to recreate?
Thanks
Mariusz
Thanks for the question.
The 'Due status' column must be ordered first by 'Past due', then 'Due', then 'Not yet due', but the 'Value' column needs to show from low value to high value within each of those 'Due status' subsets.Hopefully that then aligns with what is in the table.
Once the invoices are ordered correctly, which is what I haven't been able to do, I can add a rank (1, 2, 3, etc.) that will enable the user to correctly order the invoices easily, before then going on to show what % of invoices will be paid on-time.
Some of the above is for context (unless people want to jump ahead and give a solution to everything), but my question is simply to order the invoices by 'Value' within each 'Due status', which must also be in order.
I hope that clarifies everything.
Thanks once again.
Check out Page 2, Table 3 and see if that is what you want.
I added a column called Due Sort that is used as a Sort By column. You should be able to add that as a column in your query very easily.
