Forum Discussion

Cogidubnus_Rex's avatar
Cogidubnus_Rex
Frequent Visitor
6 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
Cogidubnus_Rex
Frequent Visitor
6 years ago

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's avatar
Mariusz
Community Champion
6 years ago

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.