cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Sorting Text in Matrix table visual

Hi everyone,

I try to sort year & month. I want to make the latest year & month show in the front. For example, Y20M06 then Y20M05 then Y20M04. But year & month datatype is in text, so i cannot sort using simple sorting method

 

 

y&m.PNG

 

So i make a table for index like below:
y&M s.PNG
The year & month in this table, i link together with the year & month in another table that i desire. But the result still the same. i cant sort the year & period. Really appreciate if anyone can help me with this problem. Thanks in advance!
link.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Sorting Text in Matrix table visual

Hi @New_be ,

 

If you want the latest date to be at the top, you should descend first, then add an index.

You can refer the following steps.

 

1. We need to create a unique Year&Month table. And sort the column.

 

so1.jpg

 

2. Then create an index column.

 

so2.jpg

 

3. Create an one-to-many relationship with Actual table.

 

so3.jpg

 

4. At last, in the matrix table visual, put the Table[Year&Month] to Column and sort it by Index.

 

so4.jpg

 

so5.jpg

 

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data and describe the relations between tables?

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

7 REPLIES 7
Highlighted
Super User V
Super User V

Re: Sorting Text in Matrix table visual

Hi,

  1. Create a year & month column in the Calendar table as well
  2. Create a relationship from the Year and Month column in the Calendar Table to the Year and Month column in the Year Month sorted table
  3. Write this calculated column formula in the Calendar table to bring over the Index column from the Year Month sorted table. Month index = related('Year Month sorted'[Inde])
  4. Click on any one cell in the Year & month column of the Calendar table (the one you create in step 1 above) and use the "Sort by" feature to sort the new column by the Month index column (the one you create in step 3 above)
  5.  Remove the relationship between the Actual and Year and Month sorted table
  6. To your visual, drag Year and Month from the Calendar Table.

Why do you have a Many to Many relationship between the Calendar and Actual tables?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Super User IX
Super User IX

Re: Sorting Text in Matrix table visual

@New_be , if you want that on row or axis. You should be able to do it with three dots. with sort descending.

 

In case you need for matrix column.

Create a desc rank on you year month

Create new columns

Month Year Rank = RANKX(ALL('Date'),'Date'[Month Year],,DESC,Dense)

 

Month Year 1 = [Month Year]

 

Sort this new Month Year 1 on Month Year Rank and use that. As Power bi might not allow you to sort Month Year on Month Year Rank .



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IX
Super User IX

Re: Sorting Text in Matrix table visual

@New_be - Are you using Sort By of your Index column for your Year & Month? You may need to reverse your ordering in your Index so that you go from 21 - 1 instead of 1 - 21


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Sorting Text in Matrix table visual

Hi @New_be ,

 

If you want the latest date to be at the top, you should descend first, then add an index.

You can refer the following steps.

 

1. We need to create a unique Year&Month table. And sort the column.

 

so1.jpg

 

2. Then create an index column.

 

so2.jpg

 

3. Create an one-to-many relationship with Actual table.

 

so3.jpg

 

4. At last, in the matrix table visual, put the Table[Year&Month] to Column and sort it by Index.

 

so4.jpg

 

so5.jpg

 

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data and describe the relations between tables?

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

Highlighted
Helper III
Helper III

Re: Sorting Text in Matrix table visual

Great explaination! Thank you so much!

Highlighted
Helper III
Helper III

Re: Sorting Text in Matrix table visual

Great answer! thanks for your time. Really appreciate that

Highlighted
Helper III
Helper III

Re: Sorting Text in Matrix table visual

Great answer! thanks for your time. Really appreciate that!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (907)