Sorting error - can't be more than one value

[Anonymous]

Hi,

i try to sort the Slicer from Modeling pane >sort by column >sort by YearFinMonthDay (which is the year the month and the day) so there cannot be a duplicate.

But its giving me this error 'there can't be more than one value in yearfinmonthday for the same value in workweek.

Can a kind person please assist ?"

cheers 

[parry2k]

@Anonymous which column you are trying to sort? Does that is same as sort column?

For example,

you have 3 years dates in your calendar table and then you have month name column jan, feb march etc. so each month will repeat 3 times (1 for each year) and now sort column with 1, 2, 3, 4 (month number), so i this case you can sort month name with month number. Point here is that it doesn't need to be unique, but column you are sorting and column you are sorting by much have same value. hope I able to explain.

[Anonymous]

Hi, sorry can i please have some assistance ?

i've clicked on the column that i want sorting (workWeek) then i click 'sort by column', i chose YearFinMonthDay'.

YearFinMonthDay is in number format.

[HotChilli · Accepted solution]

It's a little tricky because we can't see the YearFinMonthDay values you have.

However, the error is caused because there are more than 1 value for YearFinMonthDay for one WorkWeek e.g. 29/ Mar/21 - 04/Apr/21 will have 5 values of YearFinMonthDay.  Powerbi doesn't know which to use.

You need to find something to order by that has the same granularity as WorkWeek, something like Year and weeknum combined OR Year and 1st Day of workweek combined.

That's my theory.

[Yvoro]

It didnt work for me. I have unique items from an Excel sheet. In two PowerBI Report I am using this Excel sheet. In one PowerBI report the Sorting is working and the other one gives me an error. Also if I add an Index and sort it based on the Index.

[Anonymous]

thanks alot. that really clears things up.

i've manage to do it !

cheers,

[Anonymous]

Hi,

I'm sorry i dont understand.

I am trying to sort the Workweek, its data type is text.

i have concatenate a 'YearFinMonthDay' column which have the year/month/day.

so there should not be any duplicate value.

can you please assist ?