Sorting by another column - unique id - please help

[ic23]

I'm experiencing an issue with sorting a column in Power BI. I want to sort the Reporting Category column by a Unique ID column that I created. Despite ensuring that all Unique ID values are unique for each Reporting Category, I still encounter an error.

Steps Taken:

1. Data Preparation:

   • I have a dataset with columns Reporting Category, Account ID, Reporting Category Index, Reporting Sub Group, and other related fields.

2. Verification:

   • Used DAX to ensure Unique ID values are unique for each Reporting Category
   • Verified that there are no duplicates by filtering out any values where the count is not 1.
   • Ensured data types are consistent and converted necessary columns to text.

3. Attempted Sorting:

   • Set Unique ID as the sorting column for Reporting Category in Power BI, but received the following error:
     

Seeking advice on potential causes and solutions for this sorting issue in Power BI. Any insights or suggestions are appreciated.

[kpost]

It's telling you that every value in [Reporting Category] must uniquely map to a single value in the column you're trying to sort it by, [Unique ID].   It's not saying that the values in [Unique ID] must be unique.

In the data you posted, [Gross Profit] has many many different values associated with it in the [Unique ID] column.

What exactly are you trying to do here, have [Reporting Category] sorted in a custom order in your visuals?

[ic23]

I am trying to sort the Reporting Category in a custom order but the reporting category also has a subcategory (different column) that must be in a certain order as well. For example, gross profit contains both revenue and cost of revenue and revenue must come before cost of revenue, etc. 

[kpost]

Create a new table with the category and subcategory, and add sort order columns. Follow along in this example and apply it to your situation.

For example, here are some various animals that fall into two categories (Mammal and Reptile), sorted with the Reptiles first then the mammals second, in the specific order that I want both the categories of Mammal and Reptile to be sorted, as well as the subcategories of Animal Name.  In this table, I sorted "Family" by "Family Sort Order" and "Animal Name" by "Animal Sort Order"

This will act as a dimension table.

 

Here is a fact table containing the count of each animal.

Add a many-to-one connection on the smallest subcategory in your hierarchy. In this case, Animal Name.

 

 

Now, your visual, you can use the "Animal Name" column from the dimension table with "Count" from your fact table,  then "sort by" Animal Name Ascending in the visual, and it will be in the correct order.  Snake, Lizard, Cat, Zebra, Dog, Elephant.  Note that this is not sorted alphabetically, not sorted by ascending Y axis value etc.  It is sorting using the order I dictated in my dimension table. 

 

Here is the data  in a matrix, with the Category and Subcategory both sorted correctly.

 

 See attached .pbix file.

EDIT:

If you have problems because your Subcategory values are not unique to each Category like they are in my animals example, follow these directions.  For example if you have "Revenue" subcategory within both "Net Profit" category as well as "Profit" categories.

Add a "Key" column to both tables. (I did [Family] & "-" & [Animal Name]) to uniquely idenfify each combination of Family and Animal Name.

 

Add a Key sort order column with the order you want them sorted, taking into account both the categories and subcategories.

Then use that Key column to create the relationship.

etc etc...

///Mediocre Power BI Advice, But It's Free///

[ic23]

I have tried the stated above and it makes sense but when I tried it I continue to get the same error. See sorting table attached. 

[kpost · Accepted solution]

[Reporting Category] should be sorted by [Reporting Category sort order]

[Reporting Subcategory] should be sorted by [Reporting Subgroup Sort Order]

Also, If your Reporting Subcategory column contains unique values (it looks like it does), then you can just use Reporting Subcategory column as the active relationship between this table and your fact table, instead of creating the Key column.

If you would like, you can send me a private message, or post here, a sample .pbix file with these two tables and as much data removed as you need to ensure your organizational security.  I will solve the problem and return the .pbix file with the solution implemented.

[ic23]

i didnt realize actually sorting the categories in power query would help. Thank you so much for your help!!