Forum Discussion
Sort column by multiple columns
Hi Anonymous ,
You could create a rank column to sort category.
Rank = RANKX ( 'Table', 'Table'[Group] * 10 + 'Table'[Sort_Order],, ASC, DENSE )
Community ChampionHi Anonymous
If I understand you correct you can create a Surrogate Order Column and try to sort by it, like
Surrogate Order Column =
CONCATENATE(Table[Group], CONCATENATE("_", Table[Sort_Order]) )Hi az38 ,
Many thanks for the quick response. The problem is the values in the 'Category' column are not unique, so even if I concatenate the 'Group' and 'Sort' columns I still get this error message: "There can't be more than one value in 'Category' for the same value in 'Surrogate Order Column'.
If I concatenate the 'Group' and 'Category' column it would work as they become unique values but I want to avoid doing that if possible.
Many thanks,
Ashley
- az38
Anonymous
Do you create measure or column?
If column, there shouldn't be such error
Hi az38 ,
I added it as a column and it certainly sorts the data correctly; However the issue I still have is that I can't specifically sort the 'Category' by this column. So if I was to add 'Category' to a slicer it still defaults the sort order alphabetically, but I would want it sorted by the new 'Surrogate Order Column'.
Thanks again for your help.
Ashley
- az38
Anonymous
try this technique https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column