How do I return a calculated table sorted by a specific column?

Question

Hi there, I'm fairly new to DAX and I have a simple question I can't seem to find an answer to. In the image below I am adding a percentage column to a table in Power BI Desktop, What is the syntax for returning the table sorted in descending order by the percentage column? So, the equivilent of an Order By clause. I have searched but found little information on this topic, I would have expected it to be quite intuitive!

Many thanks

Simon

Table Results

aleef · Answer

Have you tried calculating the table by itself first and then sorting it? &nbsp;It looks like you are summarizing the sector column to calculate the initial rows for "Base." &nbsp;I think the issue is calculating the table at the same time you are trying to rank it - and it doesn't know what the end result will be.&nbsp;&nbsp;To define the problem, you have a table with multiple [sectors] like geographic regions or demographic profiles, and are trying to figure out which [sector] had the highest percentage of people that were "Motivated" correct?&nbsp;In that case, I would try the following: (MSDN is down, so I can't get exact context, but logically....)&nbsp;Create a new table by filtering your main table by DISTINCT ([Sector]) to get a list of sectors in source dataAdd a column that COUNT or COUNTA &nbsp;matching your [Sector] value in the new table, for your "Base"Add a &nbsp;column that COUNT COUNTA matching "Motivated" in [AdEval] &amp;&amp; [Sector] in the new table, for your "#Motivated"&nbsp;&nbsp;Once you have it pulling all the data correct, wrap that table in a SAMPLE. &nbsp;https://msdn.microsoft.com/en-us/library/mt163692.aspxUse the OrderBy expression as [#Motivated] / [Base] and see if that works? &nbsp;That way it evaluates the expression and orders the table during the calculation on what it sees as static data. &nbsp;If it works, you might even be able to simplify it by inserting the filter/addcolumn/addcolumn as part of the &lt;table&gt; parameter within the SAMPLE. &nbsp;Then you could get rid of that one extra table you made.&nbsp;Hopefully it works! &nbsp;I'll be back at work on Monday, and be able to actually have PowerBI to test with, instead of just providing logic help.&nbsp;&nbsp;

aleef · Answer

I think it would need to look like:&nbsp;Table 3 = SAMPLE(10,TableTest,[Motivated]/[Base], DESC) &nbsp;&nbsp;So you are sorting by the expression, instead of the static column. &nbsp;You could also try:&nbsp;Table 3 = SAMPLE(1,TableTest,MAX(TableTest[Percentage], DESC) &nbsp;

rémi · Answer

Hi,&nbsp;You can't sort columns in this data view.Do a table with [Sector] and [Percentage] then click on the three dots top/right of the visualisations, you can choose your sort.Otherwise, if you want a specific order for a column (ex with months : jan / feb / ... ), you have to create a sort column with the number of the order (same ex : Sort Column = MONTH([Date]) ) and you can do a sort by column.

simonm · Answer

Hi Rémi, thanks for getting back. &nbsp;Let me explain what I am ultimately trying to do, maybe there is another way around it.&nbsp;Extract max value from percentage column (you helped me this on my last post so that's fine)&nbsp; &nbsp; &nbsp;Identify which row the maximum value appeared in.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Extract other information from that row where the maximum value appeared.&nbsp;My 'quick fix' in my head was to just sort the table descending on the percentage column thus having the max value always on row 1. Bear in mind, this table will be calculated inside a&nbsp;measure, I have simplified it in my example.&nbsp;&nbsp;Am&nbsp;I thinking through this the wrong way? &nbsp;As I said, I'm still learning from my mistakes!&nbsp;Simon&nbsp;

aleef · Answer

The first issue is that you can't sort highest to lowest with the "sort by" button, because it isn't available in PowerBI (hopefully they are working on it).

ORDER BY is part of the query builder, so if your data model works with it, you could sort in your query - although I'm assuming your percentage is a calculate column, so it won't work in that case: https://msdn.microsoft.com/en-us/library/gg492156.aspx

As a potential solve for your issue, you could use TOPN and only return the first row.

https://msdn.microsoft.com/en-us/library/gg492198.aspx

I *think* that will give you the desired result, but the documentation says it doesn't guarantee the sorting of the results -but since you are only returning one row, it should be the right one, assuming your orderby expression is right.

Forum Discussion

How do I return a calculated table sorted by a specific column?

10 Replies

Related content

How to search specific word in table filter Calculations

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

Calculating Days late with specific target times

Wrap text in specific columns?