Power BI Not Sorting column

[yashwant101]

Hi all,

I have below data in which I am trying to sort Time Period column by Time Period sort column. but getting below issue

this is really frustrating as I have no duplicate values.

Any help is highly appreciated

[yashwant101]

Hi all,

Thank you for your responses. It might have been some transient issue. I let Power BI sleep over it and now I am able to sort without any change.😊

[PhilipTreacy]

@yashwant101 

I'm curious how sorting works as intended when the sort column is text, not numeric.  Text won't sort the periods the way you want.

Also why an answer was chosen as a solution when you say you did nothing to 'fix' the issue?

Puzzled.

Phil

[Anand24 · Accepted solution]

@yashwant101,
Here's what you can do:
1. Change the data type of column to whole number. Text will also sort it but based on ASCII ordering

2. Easiest, Quickest, Safest for long-term model development: Create a separate time period sort order table with just these 2 columns, make sure sort order column is whole number. Create a relationship between any table's time period to this table's time period and use this table in visuals. Filtering for the relationship would be uni-directional from sort order table to any other table with it.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[PhilipTreacy]

@yashwant101 

Your Time Period Sort column is text, so it won't sort properly anyway.  Change it to Whole Number 

As for the error message, it would indicate that there is a dupicate value(s) in the Time Period column.  Without seeing your data it's hard to say anymore.

Have you checked for rows with BLANKs?

Regards

Phil

[Anand24]

@yashwant101,
Do you have these 2 columns in a separate sort table or are these 2 tables part of a table larger table where each time period has one period sort mapped to it?

[yashwant101]

Hi @Anand24 ,

these two columns are present in same table where each value in Time Period is mapped to 1 value in Time Period sort

[sergej_og]

Hey @yashwant101 ,
that might be the "issue" - having unique value for each "Time Period"...btw, your "date table" is looking very special.
What if you convert your Sort column into a number (looks like it is a text formated value) and try to get your sorting in another way. Maybe directly on the visual.
We don't know your intension with that.

Regards

[MasonMA]

Hi, 

It sounds like Power BI finds more than one possible 'Time Period Sort' value for a single value in the column being sorted and it throws the error.

Under DAX Query view you can write a simple query to check if this happened 

EVALUATE
FILTER(
SUMMARIZE(
'table',
'table'[Time Period],
"DistinctSortCount", DISTINCTCOUNT('table'[Time Period Sort])
),
[DistinctSortCount] > 1
)
ORDER BY [Time Period]

[yashwant101]

Hi @MasonMA ,

thanks for the quick response, but running the query gives me no result - meaning I have unique values only. 

[MasonMA]

Can you share this part of data in a .pbix file? I tested on my Power BI with only the sample data from your screenshot and im not seeing the issues you ran into.