Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Sky
Frequent Visitor

Sort by Column does not work

Dear all,

 

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

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:

 

2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

1 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

 

 

 

View solution in original post

11 REPLIES 11

Hi @Sky,

 

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

 

Regards,

Sarita

 

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

 

 

 

 

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:

 

t1.PNG

 

 

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'.

 

t2.PNG

Any idea?

Vvelarde
Community Champion
Community Champion

@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)




Lima - Peru
Sky
Frequent Visitor

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'.

 

t3.PNG

 

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 ....

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

 

 

 

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 🙂

Hi Sky,

 

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

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.

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
Frequent Visitor

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.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.