cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Sorting problem

I have a visualisation which is a league table.

 

The columns in view include Player, Points, Wins, 2nd, 3rd etc.

 

In the event that Points are tied I have created a calculated column in my table called "PointsForSorting" which is essentially Points + (Wins*0.0001)+(2nd*0.0000001) etc.

 

The PointsForSorting column is more accurate for sorting purposes as the points column will divide those tied on points alphabetically which is incorrect in this case.

 

I'm trying to follow the advice via the link below, but it's not working. I select the PointsForScoring field in my fields pane, select my visual, select the modelling tab -> Sort by column, select PointsForScoring but the visual doesn't update. In fact whatever I select under "Sort By Column" doesn't affect the visual.

 

Could decimilisation be the issue? I've noticed if I drag the PointsForScoring column onto the visual it is set at 2dp (which isn't sufficient)

 

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Sorting problem

Thanks for your help. I couldn't do this in Power Query as my Results table contained the following data:

 

Tournament - Player - Result

22/12/2018 - Danny - 1st

22/12/2018 - Janet - 2nd

 

I had another table called Points as follows:

 

Result - Points

1st - 16

2nd - 12

 

The Points Measure was modelled with the formula:

Points = RELATED('Points'[Points])

 

I attempted In Power Query Editor to include a custom column with an IF statement (If Results = 1st, 16, If Result = 2nd, 12 ...) but it complained that the term "IF" was unrecognized.

 

In the end I decided there was no need for a separate Points and PointsForSorting columns, and recreated the Points column as follows:

Points = RELATED('Points'[Points])+(Results[1st]*0.0001)+(Results[2nd]*0.0000001)+(Results[3rd]*0.0000000001)

 

This seems to have worked.

View solution in original post

4 REPLIES 4
jthomson New Contributor
New Contributor

Re: Sorting problem

You need to select the points column and have that sorted by pointsforscoring

Re: Sorting problem

Unfortunately that's not working for me. I select the Points column from the fields list, select my visualisation, then modelling tab -> sort by column, select the PointsForSorting column but I receive the error message "This column can't be sorted by a column that is already sorted, directly or indirectly, by this column"

 

I checked the PointsForSorting column (selected it in the fields pane, selected my visualisation -> sort by column) and the PointsForSorting column is sorted by itself (as the default).

 

I'm assuming the error message is referencing the fact that the PointsForSorting calculated column uses the Points column in its formula (which it has to), so I'm not sure what else I can do??

Super User
Super User

Re: Sorting problem

@PetyrBaelish you are right, pointforsorting is referring to point column and that's why it is not allow to sort. you can create this "pointforsorting" column in power query instead using dax.

 

although I'm mot sure it will stil work because you have tied point and try to sort the tied point with non-tied sort column , and it will give error (i think so)

 

for example , if you have following tied points

 

Point PointSorted

1       1.01

1       1.02

1       1.03

 

and if you try to sort point by pointsorted, it will give error message.

 

I think first and foremost is to create "pointsorted" in power query and go from there.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Highlighted

Re: Sorting problem

Thanks for your help. I couldn't do this in Power Query as my Results table contained the following data:

 

Tournament - Player - Result

22/12/2018 - Danny - 1st

22/12/2018 - Janet - 2nd

 

I had another table called Points as follows:

 

Result - Points

1st - 16

2nd - 12

 

The Points Measure was modelled with the formula:

Points = RELATED('Points'[Points])

 

I attempted In Power Query Editor to include a custom column with an IF statement (If Results = 1st, 16, If Result = 2nd, 12 ...) but it complained that the term "IF" was unrecognized.

 

In the end I decided there was no need for a separate Points and PointsForSorting columns, and recreated the Points column as follows:

Points = RELATED('Points'[Points])+(Results[1st]*0.0001)+(Results[2nd]*0.0000001)+(Results[3rd]*0.0000000001)

 

This seems to have worked.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,660 guests
Please welcome our newest community members: