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

Matrix column custom sorting via Task finish date (MS project data field)

Hi

 

I hope someone can help

 

I have a matrix table which shows key points through my project with information whether those points are complete etc...

I would like to sort the columns by a date column (Task finish date) but I get the error "We cant sort the 'taskName' column by 'TaskFinishDate'. There can't be more than one value in 'TaskFinishDate' fr the same value in 'TaskName'".

 

I did have a work around where I used a calculation to create a sort column based on the name of the task. However this only works if the key points are consistant for all projects which unfortunately they are not.

 

Is there another way to sort by a date column?

 

Example table below

 Task NameTask NameTask NameTask NameTask NameTask Name
Task Finish Date01-Oct-1820-Jan-1719-May-1815-Jun-1918-Mar-2016-May-18
Info 1      
Info 2      

 

Thanks

 

Matt

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi

 

Turns out, the matrix table has an option to sort the columns by one of the values. sorting by finish date solved my problem straight away without the need for a complex DAX formula

 

For anyone else who has had this issue, if you click on the 3 dots at the top right of the visual it opens a list and one of the options lets you sort by

 

Thanks

 

Matt

View solution in original post

7 REPLIES 7
Highlighted
Resolver III
Resolver III

Hi @Matt22365,

 

Could you please share your sample data or pbix file.

 

 

Best Regards,

Ravi

Highlighted
Super User I
Super User I

the problem is that one date can have multiple tasks, in DAX for the custom sort to work the granularity of the involved columns has to be 1:1

would putting the date before the tasks list in columns solve the problem?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Highlighted

Hi

Its difficult to send data as it is a MS project online feed, I will see if I can find a way to replicate it without the confidential info.

Thanks

Matt
Highlighted

Hi

That's what I thought the issue may be.

I think that rearrangement would take away the functionality of the visual.

Thanks

Matt
Highlighted

Hi

 

I have managed to sort by using a unique task ID column and sorting that by finish date. This solves one problem, however, I now need to have this number in the matrix table title for the ordering to work.

 

Is there a way to hide this top tier of titles without it affecting the order of the table?

 

Thanks

 

Matt

Highlighted

Hi

 

Turns out, the matrix table has an option to sort the columns by one of the values. sorting by finish date solved my problem straight away without the need for a complex DAX formula

 

For anyone else who has had this issue, if you click on the 3 dots at the top right of the visual it opens a list and one of the options lets you sort by

 

Thanks

 

Matt

View solution in original post

Highlighted

Hi @Matt22365,

 

I'd like to suggest you create multiple sort order tables with custom index and link original columns with relationships. 

 

Then you can use these related table columns to replace original columns to apply their own sort order instead original sort order.

 

Reference link:

Custom Sorting in Power BI

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors