- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- How do I return a calculated table sorted by a spe...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

SimonM

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2015
05:28 PM

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

10 REPLIES 10

Rémi

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-03-2015
12:05 AM

Hi,

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

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-03-2015
02:54 AM

Hi Rémi, thanks for getting back. Let me explain what I am ultimately trying to do, maybe there is another way around it.

Extract max value from percentage column (you helped me this on my last post so that's fine)

Identify which row the maximum value appeared in.

Extract other information from that row where the maximum value appeared.

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 measure, I have simplified it in my example.

Am I thinking through this the wrong way? As I said, I'm still learning from my mistakes!

Simon

ALeef

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-03-2015
11:06 AM

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.

SimonM

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-04-2015
02:30 PM

Hi ALeef, thanks for the comments. It is a bit frustrating that the TopN function doesn't appear to sort on a calulated column otherwise I could do that and just extract the first row but it seems that you can't. Using the code below, the percentages are just not sorting in a descending order.

TableTest = TOPN(10, ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE( TabMain, TabMain[Sector]), "Base", CALCULATE( COUNTA(TabMain[AdEval])),"CountMotivated",CALCULATE(COUNTAX(FILTER(TabMain,TabMain[AdEval]="1_Motivated"),[AdEval]))), "Percentage",[CountMotivated]/[Base]), [Percentage],DESC)

ALeef

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-05-2015
06:04 AM

Have you tried calculating the table by itself first and then sorting it? It looks like you are summarizing the sector column to calculate the initial rows for "Base." 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.

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?

In that case, I would try the following: (MSDN is down, so I can't get exact context, but logically....)

Create a new table by filtering your main table by DISTINCT ([Sector]) to get a list of sectors in source data

Add a column that COUNT or COUNTA matching your [Sector] value in the new table, for your "Base"

Add a column that COUNT COUNTA matching "Motivated" in [AdEval] && [Sector] in the new table, for your "#Motivated"

Once you have it pulling all the data correct, wrap that table in a SAMPLE. https://msdn.microsoft.com/en-us/library/mt163692.aspx

Use the OrderBy expression as [#Motivated] / [Base] and see if that works? That way it evaluates the expression and orders the table during the calculation on what it sees as static data. If it works, you might even be able to simplify it by inserting the filter/addcolumn/addcolumn as part of the <table> parameter within the SAMPLE. Then you could get rid of that one extra table you made.

Hopefully it works! I'll be back at work on Monday, and be able to actually have PowerBI to test with, instead of just providing logic help.

konstantinos

Senior Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-05-2015
06:39 AM

Not sure what you are trying to do but measures or calculated columns formulas don't affected by if or not the column is ordered.

For a calculated column in the table you can try , athough I am not sure if this is what you want

The formula can match the max pct in a column and return a value from the same row that the pct is MAX else blank

extract = IF ( CALCULATE ( MAX ( Table[pct]); ALL ( Table) ) = Table[pct)]; Table[extractcolumn] )

Konstantinos Ioannou

SimonM

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-05-2015
04:08 PM

Hi Aleef, thanks for the reply.

All your assumptions are correct about what I am trying to achieve and I will explore your suggestions fully over the next few days. Just as a starter though, I referenced the calculated table using the SAMPLE function and still, the data returns unsorted by the percentage column (as image below). I'm puzzled why it returns like that, I suspect I need to explore a bit more about evaluation and the order of which evaluation happens but logically (in my mind anyway) I feel this should return the table sorted descending by percentage!

ALeef

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-06-2015
05:25 AM

I think it would need to look like:

Table 3 = SAMPLE(10,TableTest,[Motivated]/[Base], DESC)

So you are sorting by the expression, instead of the static column. You could also try:

Table 3 = SAMPLE(1,TableTest,MAX(TableTest[Percentage], DESC)

SimonM

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-06-2015
05:58 AM

Hi ALeef

Thanks for the examples, my results based on your suggestions are below. So - example 1 is still not sorting, despite doing the calculation in the SAMPLE function.

Also, example 2 is showing some strange behaviour, even though I put 5 in the first value of SAMPLE, it returns the whole 10 rows (and again, not sorting!). I would have thought it would have returned 5 rows? I still need to try your Distinct Count solution on the Sector rather than a Group By so will do that over the weekend.

Thanks

Simon