Sort by two columns in DAX

[Cogidubnus_Rex]

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!

[v-juanli-msft · Accepted solution]

Hi @Cogidubnus_Rex 

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.

[Cogidubnus_Rex]

Maggie, thank you so much for your solution, it's beautiful.  I think I've fallen in love...

[Mariusz]

Hi @Cogidubnus_Rex 

Witch one of the columns in your sample are you trying to recreate?

Thanks

Mariusz

[Cogidubnus_Rex]

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.

[Mariusz]

Hi @Cogidubnus_Rex 

Please see the attached file with the solution. 

To sort Due Status I've added an extra dimension with dueStatusID that is used for sorting in the measure so it will have to be recreated in your model.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Greg_Deckler]

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.

[Cogidubnus_Rex]

Thanks, Greg.

I'm not quite sure I was clear enough before, therefore, as the table I provided was just for illustrative purposes.  The real data has 20k rows, so what I'm after is the DAX to order the rows correctly.

Or am I missing something from the file you shared?