Forum Discussion
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?
hi, MouserMike
First, you should know that Difference between custom column and calculated column, please refer to this post:
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
6 Replies
- amitchandakSuper User
Can you try by creating a new column
Concat Name and Date in YYYYMMDD format and try using it as the sort column
- MouserMikeHelper 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?
- amitchandakSuper User
Instead of .date use .year and the same manner take month and date and append them. Also, check DateTime.ToText