Custom sorting for calculated table (circular error)

[Joris_NL]

Hi,

I have a calculated table, in which I want the NAME column (all unique) to sort by CATEGORY first, and then sorted alphabetically second. Like this:

NAME	CATEGORY Nr.	*My custom sorting column*	(The desired sorting result, based on custom sorting column )
A	1	1001	first position
B	2	2003	third
C	1	1002	second
D	3	3004	fourth
E	4	4006	sixth
F	3	3005	fifth

I managed to make the output for the custom sorting column:

My custom sorting column = 
RANKX ( 'Table' ; 'Table'[NAME] ; ; ASC )
+
( 1000 * 'Table'[CATEGORY Nr.] )

But I can't use this column to sort NAME! This gives a circular dependancy error. So the order of the names is used for the RANKX function, which is then used to order the names. 

Any suggestions?

[Joris_NL]

Wow thank you, that must help a lot of people. Unfortunately I'm working with a calculated table. But I'll try to add the column to the original source table and get back to you.

[Jihwan_Kim · Accepted solution]

Hi,

If possible, one of ways is to create sort column in power query editor.
Please check the below picture and the attached pbix file.

let
    Source = source,
    #"Sorted Rows" = Table.Sort(Source,{{"CATEGORY Nr.", Order.Ascending}, {"NAME", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CATEGORY Nr."}, {{"Count", each Table.AddIndexColumn( _, "index",1,1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"NAME", "index"}, {"NAME", "index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "sort_order", each [#"CATEGORY Nr."]*1000 + [index]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"NAME", type text}, {"sort_order", Int64.Type}})
in
    #"Changed Type"