Sort by Column does not work

[Sky]

Dear all,

I want to sort the results shown in a matrix based on Attrition Month Group:

To do that I tried to sort the data based on Attrition Month (a column that does not exist in the matrix). So I went ahead and sorted the data in Data view using that column, expecting it makes my results sorted, which didn't happen.

The records are shown sorted correctly on Data view:

I tried it several times but not working. Would you be able to let me know what's gone wrong? Thanks.

[SaritaSenapati]

Hi @Sky,

Solution given by @osoosh is a good one! This also helps you to keep your month group names neat.

Regards,

Sarita

[SaritaSenapati]

Hi @Sky,

I guess the issue is because its getting sorted based on the leftmost digit. Thats why even if 14 is greater than 2, power bi is not considering the 4 in 14 and its sorting wrongly.

To  fix the issue, 

1)In powerBI, Replace 1,2,3,4,5,6,7,8,9 in your attribution month by 01,02,03,04,05,06,07,08,09.

2) Convert the datatype of attribution month to text.

3) Recreate your visual with attribution month group and churned donors. 

4) Go to the modelling tab and select sort by attribution month.

Let me know if this solution helps!

Regards,

Sarita

[Sky]

Thanks Sarita. I followed the steps that you had mentioned but still the results shown based on 'Attrition Month Group' is not shown as sorted. If I incorporate AttritionMonthText into the result, it is shown as sorted:

But it is not what I want, I don't want to show AttritionMonthText on the matrix. So when I remove it, the sort affact disappars. It looks like regardless of sorting the records in the model based on the Attrition Month Text, when this column does not appear on the matrix, Power BI does care about it and does the sorting based on 'the column' that is shown on the matrix, which is 'Attrition Month Group'.

Any idea?

[Vvelarde]

@Sky

Did you apply this step:

4) Go to the modelling tab and select sort by attribution month.

Select Attrition Month Group From Fields Panel and Go to Modeling Sort by Column (AttritionMonthText)

[Sky]

Thanks for reply Vvelarde. I get the error below, when I try to sort by attrition month text :

We can't sort the 'Attrition Month Group" column by 'AttritionMonthText'. There can't be more than one value in 'AttriionMonthText' for the same value in 'Attrition Month Group'. Please choose a different column for sorting or update the data in 'AttritionMonthText'.

It is all about grouping, so obviously there are more than one value (month) in AttritionMonth associated to a Attrition Month Group! Not sure what can be done to get the results of group sorted ....

[SaritaSenapati · Accepted solution]

Hi @Sky,

I think you still be able to solve this issue if you change the name of your attribution month groups as 00-01 momth AR, 02-03 monthAR, 04-13 month AR, 13-25 month AR and 25+ month AR and then you can directly sort it by Attrition Month Group.

Regards,

Sarita

[Sky]

Thanks Sarita. I followed your later suggestion and sorted based on Attrition Month Group directly. It doesn't look nice to have 0 before the numbers, but at least the result is now sorted right. Thanks for your help 🙂

[osoosh]

Hi Sky,

If you want it too look as what you wanted, you can create a relationship table for the month group . Connect it to your fact table  and it will give you an outcome like this 

[bslattery]

I'm trying to solve this same problem, and followed osoosh's recommendation, but I get the error below (I replaced the column names in the error popup so it's easier to follow-- "Trying to sort" is the column of with data that I'm trying to sort a different way, and "Sort column" is the separate data I created with sort order values):

Sort by another column

We can't sort the 'Trying to sort' column by 'Sort column'. There can't be more than one value in 'Sort column' for the same value in 'Trying to sort'. Please choose a different column for sorting or update the data in 'Sort column'.

"Sort column" is set up with a one-to-many relationship with the data in "Trying to sort", and each value in "Sort column" is distinct (aka there isn't more than one value for the set of values in "Trying to sort", as the error message claims). As far as I can tell, the data I'm working with is the same type as discussed in this thread, so I'm confused as to why this solution isn't working. What am I doing wrong? I'd be happy to share screenshots if there isn't an obvious solution.

[bslattery]

I'm trying to solve this same problem, and followed @osoosh's recommendation, but I get the error below (I replaced the column names in the error popup so it's easier to follow-- "Trying to sort" is the column of with data that I'm trying to sort a different way, and "Sort column" is the separate data I created with sort order values):

Sort by another column

We can't sort the 'Trying to sort' column by 'Sort column'. There can't be more than one value in 'Sort column' for the same value in 'Trying to sort'. Please choose a different column for sorting or update the data in 'Sort column'.

"Sort column" is set up with a one-to-many relationship with the data in "Trying to sort", and each value in "Sort column" is distinct (aka there isn't more than one value for the set of values in "Trying to sort", as the error message claims). As far as I can tell, the data I'm working with is the same type as discussed in this thread, so I'm confused as to why this solution isn't working. What am I doing wrong? I'd be happy to share screenshots if there isn't an obvious solution.

[Sky]

Hi bslattery,

Mmmm it sounds strange. Are you certain that the values in the sort column is distinct? It sounds like Power BI finds more than one value to sort by for a given value in the Trying to Sort column. Could you please send some screen shots, it might help.