Sort report by name then date

[MouserMike]

 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?

[amitchandak]

Can you try by creating a new column 

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

[MouserMike]

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?

[v-lili6-msft · Accepted solution]

hi, @MouserMike 

First, you should know that Difference between custom column and calculated column, please refer to this post:

https://community.powerbi.com/t5/Desktop/Difference-between-custom-column-and-calculated-column/td-p...

Second, from your formula we could know that this is a calculated column.

so you could add FORMAT Function in your formula to change the format to output.

New Sort = Assignments[ResourceName] & FORMAT ( Assignments[AssignmentStartDate].[Date], "yyyy/mm/dd" )

Result:

Best Regards,

lin

[MouserMike]

I have the sorting working correctly using the calculated column in the visual:

New Sort = Assignments[ResourceName] & FORMAT ( Assignments[AssignmentStartDate].[Date], "yyyy/mm/dd" )

However, I really don't want this column to be visible in the visual; the individual columns are already there, and this calculated column looks rather weird since it's multiple columns concatenated together.  I've looked for ways to sort a visual by a column that is not part of the visual, but the only "solution" I've found is to add this calculated column to the far right of the visual, then shrink the width so it's almost invisible.  Is there another way to do this?

[amitchandak]

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

[amitchandak]

If above did not help? let me know.

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