Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dynamic row numbers based on Rank Column

Hell All,   I have written a calculated column to get the ranks for each sbu's employee count. Formula:-     Ranks = RANKX( FILTER('Table','Table'[Date]=EARLIER('Table'[Date])), ...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Well i got a solution on my own..but its not by using DAX..
    Its magic power query.

     

    Solution:-

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFNCoAgEIbhu8xaMJ0iW1ba3xXE+18jUSonvp08DDO8GCPNpKjXVtvOuPycKKlIi0RTcJXIBb1EWzCgyQ3t3BEeEoeCJzp0/Q7VSzWJH3VNEqMklks9wiCxTWKUxCjpxbFJYpT0Yf6QdAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SBU = _t, Date = _t, Count = _t]),
        #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}, {"Count", Order.Descending}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Count", Int64.Type}, {"Date", type date}}),
    
        Partition = Table.Group(#"Changed Type", {"Date"}, {{"Count", each Table.AddIndexColumn(_, "Index2",1,1), type table}}),
        #"Expanded Count" = Table.ExpandTableColumn(Partition, "Count", {"SBU", "Count", "Index2"}, {"SBU", "Count.1", "Index2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Count",{{"SBU", type text}, {"Count.1", Int64.Type}, {"Index2", Int64.Type}})
    in
        #"Changed Type2"

    Thanks for the help Anonymous