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.

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?