Sort column by another column with repeated values without changing the column being sorted

[Anonymous]

Working with data as shown below. Level5title is the column displayed on the report.  Requirement is to replicate the order elements are displayed in the source system.  Both columns come from a view that contains a ragged hierarchy.  The sort column is dynamically calculated in the view to represent the order elements are dispalyed in the source system.

Issue is when I sort Level5title using SortColumn, I get the error below.    Level5title cannot be changed and is used for report aggregations.

Level5Title	Level5Sort
Property	0
Property	0
Property	0
Property	0
Financial Services	1
Financial Services	1
Financial Services	1

 

I tried a number of approaches:

1) Create unique identifier in power bi - I created row number in power query and concetenated to level5sort using DAX. Sorted the label using the newly created unique identifier  

2.  Create unique identifier outside of power bi- Sorted level5title with a column that contains a unique row identifier created on the database view (not powerbi)  

3) This approach does not work because solution changes the column displayed on the report/being sorted (level5title). https://community.powerbi.com/t5/Desktop/Sort-column-with-repeated-values-by-another-column/m-p/1500...

The only solution I found that still gets to what's needed is to create a table with a distinct list of values. The new table contains the level 5 titles and associated sort order.  I do this by referencing the existing hierarchy table, remove columns I do not need, and remove duplicates on level 5 title. I then join to the hierarchy table on the level 5 title column and create a calculated column in the original hierarchy table that references the sort column in the new distinct table using the 'Related' function. This solution seems elaborate for what I want to do. What I do like is that changes will dynamically reflect in the report when data is refreshed since I leverage power query to transform the data. We are not dealing with a lot of data in this report so performance is not a concern.  Also use the reference capability rather than duplicate when creating the new table in power query.   I try to use as many reusable features as possible. 

Would love to know if there any other way's to achieve this or if this is the recommended approach? 

Thanks

[HotChilli]

What about my question from Wednesday's post?

[v-angzheng-msft]

Hi, @Anonymous 

I agree with @HotChilli 
For the data above, sorting the column will work.

You can create a sorted table, then establish a one-to-many relationship between the sorted table and the main table, use the RELATED function to create a sorting column in the main table, and finally make the rolling column sort by the sort column.

These two threads may be useful to you.

sort months into chronological order

Custom tooltip for table with measures - sorting columns

If it doesn't cover your case, could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here. It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

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

[HotChilli]

This problem is either being completely overthought or there's more to it than the data shown.

If Level5Sort is an integer and there are no extra spaces in Level5Title, this 'sort by column' will work.

Are there different values in Level5Sort for the same Level5Title?

[Anonymous]

Thank you for your suggestions @hotchilli and @v-angzheng-msft. 

Level5sort is a text field. I changed to an integer. There were nulls in the column. Replaced the nulls. This unfortunately did not fix it. 

Level5title does have blanks. @HotChilli are we saying that PowerBI cannot sort a column that has blanks? Want to make sure I understand.  

I'll see if I can create an example.