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.

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Cogidubnus_Rex 

 

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

 

Thanks

Mariusz

 

 

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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

      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?

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