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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Matt22365
Resolver III
Resolver III

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

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
Stachu
Community Champion
Community Champion

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 🙂

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

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

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.

Hi

That's what I thought the issue may be.

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

Thanks

Matt
Anonymous
Not applicable

Hi @Matt22365,

 

Could you please share your sample data or pbix file.

 

 

Best Regards,

Ravi

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.