Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
hey,
i'm trying to sort one column which has duplicate values by another one.
assume this example, where i want to have "status_category" sorted by "rank":
status | status category | rank |
new | in progress | 10 |
first review | in progress | 20 |
second review | in progress | 30 |
rejected | closed | 40 |
on hold | hold | 50 |
closed | closed | 60 |
canceled | closed | 70 |
the problem is that one category value cannot have more than one rank value. (getting a can't sort error)
so i created another column, taking the min rank value for each category:
status | status_category | rank | min_rank |
new | in progress | 10 | 10 |
first review | in progress | 20 | 10 |
second review | in progress | 30 | 10 |
rejected | closed | 40 | 40 |
on hold | hold | 50 | 50 |
closed | closed | 60 | 60 |
canceled | closed | 70 | 60 |
using this:
min_rank = VAR currentCategory = table[status_category] RETURN CALCULATE ( MIN ( table[rank] ), FILTER ( ALL ( table), table[status_category] = currentCategory) )
so now there's only one possible rank for each category value.
however, wheb ttying the sort by option i get a new error: "This column can't be sorted by a column that is already sorted, directly or indirectly, by this column".
am i to understand that it's not possible to sort a column by another one that's calcaulated by it?
can anyone find a solution to either of the sort errors?
Solved! Go to Solution.
you can create the rank in query editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRysxTKCjKTy9KLS4G8gwNlGJ1opXSMouKSxSKUssysagxgqgpTk3Oz0vBpcgYoqgoNSs1uSQ1BSiSnJNfDGaYQKTy8xQy8nNAAlDKFCIOVwdnmEElEvOSU3NQpcyBUrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [status = _t, #"status category" = _t, rank = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"status", type text}, {"status category", type text}, {"rank", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"status category", "rank"}), #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"status category"}, {{"min_rank", each List.Min([rank]), type number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"status category"},#"Grouped Rows",{"status category"},"Custom1",JoinKind.LeftOuter), #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"min_rank"}, {"min_rank"}) in #"Expanded Custom1"
you can create the rank in query editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRysxTKCjKTy9KLS4G8gwNlGJ1opXSMouKSxSKUssysagxgqgpTk3Oz0vBpcgYoqgoNSs1uSQ1BSiSnJNfDGaYQKTy8xQy8nNAAlDKFCIOVwdnmEElEvOSU3NQpcyBUrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [status = _t, #"status category" = _t, rank = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"status", type text}, {"status category", type text}, {"rank", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"status category", "rank"}), #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"status category"}, {{"min_rank", each List.Min([rank]), type number}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"status category"},#"Grouped Rows",{"status category"},"Custom1",JoinKind.LeftOuter), #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"min_rank"}, {"min_rank"}) in #"Expanded Custom1"
Thanks, that's perfect!