Forum Discussion

Matt22365's avatar
Matt22365
Icon for Resolver III rankResolver III
7 years ago
Solved

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

Need Help
  • Matt22365's avatar
    Matt22365
    7 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Matt22365,

     

    Could you please share your sample data or pbix file.

     

     

    Best Regards,

    Ravi

    • Matt22365's avatar
      Matt22365
      Icon for Resolver III rankResolver III
      7 years ago
      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
  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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?

    • Matt22365's avatar
      Matt22365
      Icon for Resolver III rankResolver III
      7 years ago
      Hi

      That's what I thought the issue may be.

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

      Thanks

      Matt
    • Matt22365's avatar
      Matt22365
      Icon for Resolver III rankResolver III
      7 years ago

      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

      • Matt22365's avatar
        Matt22365
        Icon for Resolver III rankResolver III
        7 years ago

        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