Sort by another column not working.

[pborah]

I'm not sure of the issue as I'm not getting any errors. However I'm unable to sort by another column. Data is in import mode and I'm using an SQL query to pull the data. My categories/bins are created by a SWITCH statement, and I created a helper column with the sort order using another switch statement that referes to the calculated bins. But when go to modeling tab and select the the field for sorting, it doesn't sort the table in any order. Following is my table after sorting it by the helper column -

What am I doing wrong? Thank you.

[PaulDBrown]

@pborah 

Oine way of solving this is to create a disconnected "Bin table" with the bin categories and an index column for sorting purposes. Sort the table by the index column
Next create the measures to refer to the bin's row context:

Measure= CALCULATE([Your value measure], FILTER(Table, [your switch defintion measure] = SELECTEDVALUE(Bin table[bin])))

Now create the visual by using the Bin Table field as rows and your measures as values. 

[Kenab22]

The only solution right now is to do the sort by column funtion from the Model Table as previously recommened.

For some reason, in the October 2020 release of Power BI Desktop, the Sort By Column feature under the Column Tools Tab is not functioning, it is greyed out all the time, regardless which column the user clicks on.

This is the solution that worked for me, Thank you @themistoklis

If this solution does not work for you, then there is some other Data probelm. (circular reference, etc.)

[pborah]

@Kenab22 @PaulDBrown @themistoklis @v-rzhou-msft  Thanks all for contributing and I will try all of the solutions one by one but right now I have a bigger problem! And I'm about to lose it on this tool now. As soon as I drop the sort column onto the table and set it to "Don't Summarize", my cumulative totals break down. 

Not sure what is going on but now I'm back to being several steps backward in preparing this report. I really don't get why something this simple is so complicated in Power BI. We used Tableau in my last job. In there, you can simply drag and rearrange the bins... it's THAT easy.

[v-rzhou-msft]

Hi @pborah 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your data model and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @pborah 

I build a sample table to have a test.

I think you can build two measures to achieve your goal.

In your screenshot I think Hold Time Cumulative has been impacted by the sort column. 

Hold Time Block = 
SWITCH (
    TRUE (),
    'Table'[Min] <= 5, "Under 5 Minutes",
    'Table'[Min] <= 8, "Under 8 Minutes",
    'Table'[Min] <= 10, "Under 10 Minutes",
    'Table'[Min] <= 11, "Under 11 Minutes",
    'Table'[Min] <= 12, "Under 12 Minutes",
    'Table'[Min] <= 13, "Under 13 Minutes",
    'Table'[Min] <= 14, "Under 14 Minutes",
    "Over 14 Minutes"
)

Hold Time Sort = 
SWITCH (
    TRUE (),
    'Table'[Hold Time Block] = "Under 5 Minutes",1,
    'Table'[Hold Time Block] = "Under 8 Minutes",2,
    'Table'[Hold Time Block] = "Under 10 Minutes",3,
    'Table'[Hold Time Block] = "Under 11 Minutes",4,
    'Table'[Hold Time Block] = "Under 12 Minutes",5,
    'Table'[Hold Time Block] = "Under 13 Minutes",6,
    'Table'[Hold Time Block] = "Under 14 Minutes",7,
    8
)

Measure:

Count of Report ID = CALCULATE(COUNT('Table'[Report ID]))

Report Cumulative Count = 
SUMX(FILTER(ALL('Table'),'Table'[Hold Time Sort]<=MAX('Table'[Hold Time Sort])),[Count of Report ID])

And you can sort the visual by Report Cumulative Count column. I try to add the sort column in this viausl and let it don't summarize, and the result shows correctly.

If this reply still couldn't solve your problem, please show me your data model(Without sensitive data), and tell your calculate logic about the Cumulative column. 

You can download the pbix file from this link: Sort by another column not working

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[v-rzhou-msft]

Hi @pborah 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[themistoklis]

@pborah Could you share the file with us?

[pborah]

Thank you for your response @themistoklis. Unfortunately I can't share the file as it contains highly sensitive data. Masking it would take a while but if I can get to it over the weekend, I will post the pbix for sure. In the meantime if you have any questions regarding what you THINK I might be doing wrong, I'd be happy answer them. Thanks!

[themistoklis]

my guess would be the helper column.

Can you add it to the table next to the first column so as to see the ordering?

Is the helper column numeric?

[pborah]

The helper column is numeric. I even checked the data type under the modeling tab to make sure it was correct. Interestingly enough, when I add it to the table, sorting is no problem! But I don't want it there.

[themistoklis]

Can you go to 'Model' view

Select the field

on Properies Pane --> Select Advanced

The sort by column (helper column)

[pborah]

@themistoklis @v-rzhou-msft  no my problem still remains. I tried the last suggestion @themistoklis suggested but still to no avail. Does it have anything to do with the fact that I'm using a report server compatible version of Power BI Desktop? Build is Jan 2020 and I cannot simply upgrade without putting a change request to my manager. 

As a workaround, I've included the helper column in the table and renamed it to something more suitable. 

Thanks.

[themistoklis]

@pborah 

Maybe it has to do that you are using an older version.

There is also a workaround. Add the helper column to the table... then hide it

There is not 'hide' option in PowerBI. You can do it by minimizing the column width.

Just move the curson over the line between the helper field heading and the column heading next to it. Then drag it to the left so as to completely hide it.

Make sure you have for column 'Headings' and 'Values' the word wrap set to 'off'. So as not to have the row height increased.

I hope it makes sense??

[pborah]

@themistoklis I already partially did what you suggested, I will go ahead and hide that column in the table. I'll kudo your responses for helping me, but please don't mind if I do not accept it as a solution. Thanks very much!

[themistoklis]

Thanks for the kudos @pborah 

The last resolution would be to share the file with us (if it doesnt contain any confidential data)