Forum Discussion

MouserMike's avatar
MouserMike
Helper I
6 years ago
Solved

Sort report by name then date

 I created a report to be used with Project Online that includes resource names and task assignments.  I want to sort the report first by the name, then the task (assignment) start date.  I've read several different posts about how to setup sorting based on two columns; below is the closest solution I've been able to find, created as a Measure and added to a Table visual:

Sort = 
VAR t =
    SUMMARIZE (
        ALLSELECTED ( Assignments ),
        Assignments[ResourceName],
        Assignments[AssignmentStartDate]
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            ISONORAFTER (
                    Assignments[ResourceName], SELECTEDVALUE ( Assignments[ResourceName] ), DESC,
                    Assignments[AssignmentStartDate], SELECTEDVALUE ( Assignments[AssignmentStartDate] ), DESC
            )
        )
    )

This works in that it doesn't generate any errors and does provides a value I can sort by and get the result I want.  However,  it takes an extremely long time for the report to open/refresh when working with the PBIX file (as in 5+ minutes) and this happens anytime I make a change to the visual displaying this data.  It also takes several minutes for the report to open when viewing on-line, and it's so bad it's unusable. 

 

I tried using the same formula as a custom column in the Advance Query Editor, thinking that might have better performance.  However, the syntax seems to be wrong for that scenario, and I'm not familiar enough the differences between measures and columns to troubleshoot it.

 

Any thoughts on a better way to sort this report by Name then Start Date?  Alternatively, any thoughts on how to increase the performance to an acceptable level?

6 Replies

  • Can you try by creating a new column 

    Concat Name and Date in YYYYMMDD format and try using it as the sort column

    • MouserMike's avatar
      MouserMike
      Helper I

      I created this column:

      Sort = Assignments[ResourceName]&Assignments[AssignmentStartDate].[Date]

      It defaults to a date format of mm/dd/yyyy so it doesn't sort correctly.  How do you specify a different format as part of the concatenation?

       

      • amitchandak's avatar
        amitchandak
        Super User

        Instead of .date use .year and the same manner take month and date and append them. Also, check DateTime.ToText