topic Re: Custom sorting for calculated table (circular error) in DAX Commands and Tips

Custom sorting for calculated table (circular error)

Joris_NL — Mon, 17 Jun 2024 15:20:12 GMT

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?

Re: Custom sorting for calculated table (circular error)

Jihwan_Kim — Mon, 17 Jun 2024 17:37:08 GMT

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"

Re: Custom sorting for calculated table (circular error)

Joris_NL — Tue, 18 Jun 2024 06:59:44 GMT

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.