Sort by Column - There can't be more than one value...

[Namestaken]

I am trying to sort a text column by another index column. I've set up a unique ID for every value but I'm getting an error:

The data summary below, with no filters applied at all, shows there is only 1 value of the Sort column for every value in the subgroup column:

I have no idea what's causing the problem because the error doesn't describe the data.

[freginier]

Hello @Namestaken  did you create unique ID in a new table or in the same table ?  It won't work if it's in the same table. The displayed rows should not be repeated. 

[Namestaken]

Thanks for the reply, @freginier. I had a separate (manual entry) table with a unique ID for each group and subgroup. Here's what my table looks like:

Unique ID	Income Statement Group	Income Statement Sub-group	Income Statement Group Sort	Income Statement Subgroup Sort
1	Revenue	Revenue	0	0
2	Cost of Goods Sold	Cost of Goods Sold	1	1
3	Expenses	Selling	2	2
4	Expenses	Manufacturing	2	3
5	Expenses	Administrative	2	4
6	Exclude	Exclude	99	99

Sorting Income Statement Group by Income Statement Group Sort is working fine. Sorting Income Statement Sub-Group by Income Statement Subgroup Sort is giving me the error.

I also just tried another manual table for just the subgroup names, excluding the groups, and it's giving me the same error.

I am merging the manual table with the cost data in Transform Data. The Group sort is working well using this method, with no errors; however, subgroup is throwing the error. I tried using LOOKUPVALUE, instead of merging, and got a circular reference when selecting the sort column despite the lookup table being input by hand. Should I be doing this a different way?

[Namestaken · Accepted solution]

If I use the DAX SWITCH function, I get the same errors as above. However, if I use M if/else if statements to accomplish the same goal, I am able to sort by the new column. This isn't a great solution but it is interesting it works even though the resulting column of IDs is identical to every other solution.

Also, if I link the manually created subgroup table with the cost data in model view and then sort the subgroup name by the unique subgroup ID in that manual table, sorting works fine when adding the manual tables subgroup name to a visual. This also isn't a good solution because I can't link tables on multiple columns in model view and I want to avoid creating a lookup column. If I can't figure out what's happening, this will probably be the solution I go with.