Rank Text-Based Column, ignore blanks and double-count duplicates

[Jered]

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()
)

[daXtreme · Accepted solution]

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.