Forum Discussion
Rank Text-Based Column, ignore blanks and double-count duplicates
The following code does a sort on the column "Level 3" and ignores all blanks, however I want it to give the same rank for values which are the same. i.e. 'Consumables' would be 236 for all 5 entries instead of increasing for each - shown in the picture.
Note: The ranking starts at 1 as per an Index column which goes from 1 to 4000.
Level 3 Sort = IF (
NOT ( ISBLANK ( accountlist[Level 3] ) ),
RANKX (
FILTER ( accountlist, NOT ( ISBLANK ( accountlist[Level 3] ))),
accountlist[Index],
,
ASC,
DENSE
),BLANK()
)
Hi Jered
Why don't you do it in Power Query instead of DAX? By the way, you've posted this question on a forum about Power Query and M, not DAX. In Power Query this would be dead simple. Here's an example how you'd go about it in M:
// T let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZyzs8rLs1NTMpJLaYJH0wEFOWXZRZn5udh4WEjXHMLcvIrU1OLFXQVwhPToUYNnKh/SUYq0LWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level 3", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Level 3"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Level 3"}), #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Level 3"}, {{"AllRows", each _, type table [Index=number, Level 3=nullable text]}}), #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 235, 1, Int64.Type), #"Reordered Columns1" = Table.ReorderColumns(#"Added Index1",{"Index", "Level 3", "AllRows"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Index", "Lvel 3 Sort"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Level 3"}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"Index", "Level 3"}, {"Index", "Level 3"}), #"Reordered Columns2" = Table.ReorderColumns(#"Expanded AllRows",{"Index", "Level 3", "Lvel 3 Sort"}), #"Sorted Rows" = Table.Sort(#"Reordered Columns2",{{"Index", Order.Ascending}}), #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Level 3 Sort", each if [Lvel 3 Sort] = 235 then null else [Lvel 3 Sort], type number), #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Lvel 3 Sort"}) in #"Removed Columns1"Create a blank query and paste this code into it (right-click on the left-hand side -> New Query -> Blank Query) in Power Query and see how to do this step by step.
1 Reply
Hi Jered
Why don't you do it in Power Query instead of DAX? By the way, you've posted this question on a forum about Power Query and M, not DAX. In Power Query this would be dead simple. Here's an example how you'd go about it in M:
// T let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZyzs8rLs1NTMpJLaYJH0wEFOWXZRZn5udh4WEjXHMLcvIrU1OLFXQVwhPToUYNnKh/SUYq0LWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level 3", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Level 3"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Level 3"}), #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Level 3"}, {{"AllRows", each _, type table [Index=number, Level 3=nullable text]}}), #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 235, 1, Int64.Type), #"Reordered Columns1" = Table.ReorderColumns(#"Added Index1",{"Index", "Level 3", "AllRows"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Index", "Lvel 3 Sort"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Level 3"}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"Index", "Level 3"}, {"Index", "Level 3"}), #"Reordered Columns2" = Table.ReorderColumns(#"Expanded AllRows",{"Index", "Level 3", "Lvel 3 Sort"}), #"Sorted Rows" = Table.Sort(#"Reordered Columns2",{{"Index", Order.Ascending}}), #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Level 3 Sort", each if [Lvel 3 Sort] = 235 then null else [Lvel 3 Sort], type number), #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Lvel 3 Sort"}) in #"Removed Columns1"Create a blank query and paste this code into it (right-click on the left-hand side -> New Query -> Blank Query) in Power Query and see how to do this step by step.