Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Sort table: Function SUMMARIZE not accepting the newly defined Column in next Names & Expressions

Hi, I'm practicing writing DAX function in PBI, but there is the problem of sorting table created in function SUMMARIZE.

SuNguyen_1-1630039918593.png

 
Measure 'DescendingTable' = SUMMARIZE(Sales_2017,Sales_2017[CustomerKey],"TotalOrderQty",SUM(Sales_2017[OrderQuantity]),[TotalOrderQty],DESC)

 

The Name argument requires the name of the newly created column: e.g. "TotalOrderQty"

However, this Name argument does not accept existing columns, so the next name & expression [TotalOrderQty],DESC in the same SUMMARIZE function do not work.

So, is there a way for me to add the sorting in Ascending or Descending order to the table being created in SUMMARIZE function?

This is really useful for the combination with TOPN to find the right records matching the criteria.

Regards,

Su

1 ACCEPTED SOLUTION

SUMMARIZE does not include sorting. It just creates a table. If you want to create a physical table, you can sort the actual table using the table interface and/or the " Sort column by" in the ribbon. 
HOWEVER, this sorting order of a physical table is only relevant to sorting fields in visuals. Measures are oblivious to this, since they return scalar values based on row/filter context and not tables. 
To establish an order relevant to measures, you either create a calculated column in the physical table (which will however remain static) using RANK.EQ or RANKX, or you create a measure to rank values using RANKX (which can be dynamic)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@Anonymous , You can get in one of the two ways

 

Measure 'DescendingTable' = SUMMARIZE(Sales_2017,Sales_2017[CustomerKey],"TotalOrderQty",SUM(Sales_2017[OrderQuantity]), "TOQ",sum([TotalOrderQty]))

 

Measure 'DescendingTable' = SUMMARIZE(Sales_2017,Sales_2017[CustomerKey],[TotalOrderQty],"TotalOrderQty",SUM(Sales_2017[OrderQuantity]))

 

also in a measure table can be in a VAR

 

Measure=

var _tab

 = SUMMARIZE(Sales_2017,Sales_2017[CustomerKey],"TotalOrderQty",SUM(Sales_2017[OrderQuantity]), "TOQ",sum([TotalOrderQty]))

return

<>

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

SUMMARIZE does not include sorting. It just creates a table. If you want to create a physical table, you can sort the actual table using the table interface and/or the " Sort column by" in the ribbon. 
HOWEVER, this sorting order of a physical table is only relevant to sorting fields in visuals. Measures are oblivious to this, since they return scalar values based on row/filter context and not tables. 
To establish an order relevant to measures, you either create a calculated column in the physical table (which will however remain static) using RANK.EQ or RANKX, or you create a measure to rank values using RANKX (which can be dynamic)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

OMG, Paul!

You're so snarkily intelligent! You're a life saver! I initially looked around for many formulas, but was too lazy to try Rank functions. After seeing your suggestions of those 2 functions, I started improving my CustomCol table. I had created a CustomCol table with 2 fields, Customer Key & TOQ Sum of Qty. Now, I have added another column called RankingTOQ and applied RANK.EQ in DESC.

 

Now, to find the #1 Customer with highest Transactions to display his/her name in the Card visual, I don't need to nest the SUMMARIZE inside the TOPN function anymore (because SUMMARIZE was defined in a way that prohibits calling out newly created columns). Instead, applying the FILTER function where RankingTOQ = 1 will produce the satisfactory result.

 

It's a bummer to know that SUMMARIZE does not allow SORTing, but the workaround RANK is still good. By the way, I tried MIN inside FILTER function, and the FILTER does not return the right table for me.

Eg. This works:

FILTER(CustomCol,CustomCol[RankingTOQ]=1)

SuNguyen_2-1630078206102.png

Eg. This doesn't work:

FILTER(CustomCol,MIN(CustomCol[RankingTOQ]))

SuNguyen_3-1630078242344.png

It would be great if you can help me understand the working of these 2 contradicting examples, Paul. Thanks for your life-saving tips so far :).

Regards,

Su

Anonymous
Not applicable

Hi Amit Chandak,

Thanks for replying, but I think you misunderstood the problem in this thread. The problem is how to add Sort in Ascending or Descending to the SUMMARIZE function or to any Dax formula.

 

E.g. Function A:

SUMMARIZE(Sales_2017,Sales_2017[CustomerKey],"TotalOrderQty",SUM(Sales_2017[OrderQuantity]))

This function A works perfectly and returns a table with 2 columns, which are Customer Key and TotalOrderQty, given so many rows matching the formula criteria.

 

However, when I added 2 more arguments to the above SUMMARIZE function: Name, Expression = [TotalOrderQty], DESC to sort the newly created column TotalOrderQty in descending order (Largest to Smallest), the SUMMARIZE function did not accept this. It told me the error lied in the Name argument [TotalOrderQty]. Apparently, my PBI version only accepts column Name in this format "ColName" (aka. name in double quotations), not [ColName] (aka. name in 2 square brackets)!

Hence, is there any way to add Sort command to the Dax formula? Without this Sort command, the table results will not be sorted and the TopN function will not give me to correct record having the smallest (sorted ASC) or largest (sorted DESC) value.

Regards,

Su

@Anonymous , Summarize create table and it can be ordered in the result as you want in power bi.

 

If you are using DAX studio, order by outside the summarize should work

 

https://dax.guide/st/order-by/

 

SUMMARIZE(Sales_2017,Sales_2017[CustomerKey],"TotalOrderQty",SUM(Sales_2017[OrderQuantity]))

order by [TotalOrderQty]

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi Amit Chandak,

I've tried your link and unfortunately nothing worked. I think the DAX-expression formula in Power BI does not allow this function EVALUATE, so the keyword ORDER BY (a part of the EVALUATE statement) cannot be used. Pls take a look at this image below:

SuNguyen_0-1630059040656.png

CustomCol = EVALUATE SUMMARIZE(AW_Sales_2017,AW_Sales_2017[CustomerKey],"TOQ",SUM(AW_Sales_2017[OrderQuantity])) ORDER BY [TOQ]

In the above line of codes, EVALUATE, ORDER BY, and [TOQ] are not allowed to be used in Power BI. The table can be seen sorted in tab Data View, but in fact, without any clear allowed functions to sort in the above line, any Measures created will not produce the result that has the column [TOQ] sorted!

I'm starting to guess there is no way to write a Dax expression in Power BI that can sort a table. All users are allowed to do is to click on visual button on this software to sort and see the temporary result in Data View.

Regards,

Su

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors