Forum Discussion

Anthony007's avatar
Anthony007
Helper I
9 years ago
Solved

Build a rating considering the duplicate values

Help please build the rating with DAX.   There is a table with three columns.In the value there are duplicates: Week Company Value 33 Company A 44 33 Company B 44 33 Company C ...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    Sorry, stupid sorting order. This will do:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZW0lFyzs8tSMyrVHAEsk1MlGJ10MSdcIg7A9mGBgaYEi5AtilU3ATNAjMs4iALjLCIgyywwCLuAlYPlTBFs8DQ0MgYU8YJ4lYsepwhhmGRcYHLxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week = _t, Company = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Company", type text}, {"Value", Int64.Type}}),
    #"Sorted Rows2" = Table.Buffer(Table.Sort(#"Changed Type",{{"Value", Order.Descending}, {"Company", Order.Descending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows2", {"Week"}, {{"MyRank", each Table.AddIndexColumn(_, "MyRank",1,1), type table}}),
    #"Expanded MyRank" = Table.ExpandTableColumn(#"Grouped Rows", "MyRank", {"Company", "Value", "MyRank"}, {"Company", "Value", "MyRank.1"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded MyRank",{{"Week", Order.Ascending}, {"MyRank.1", Order.Descending}})
in
    #"Sorted Rows1"
ImkeF's avatar
ImkeF
Community Champion
9 years ago

Sorry, stupid sorting order. This will do:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZW0lFyzs8tSMyrVHAEsk1MlGJ10MSdcIg7A9mGBgaYEi5AtilU3ATNAjMs4iALjLCIgyywwCLuAlYPlTBFs8DQ0MgYU8YJ4lYsepwhhmGRcYHLxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week = _t, Company = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Company", type text}, {"Value", Int64.Type}}),
    #"Sorted Rows2" = Table.Buffer(Table.Sort(#"Changed Type",{{"Value", Order.Descending}, {"Company", Order.Descending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows2", {"Week"}, {{"MyRank", each Table.AddIndexColumn(_, "MyRank",1,1), type table}}),
    #"Expanded MyRank" = Table.ExpandTableColumn(#"Grouped Rows", "MyRank", {"Company", "Value", "MyRank"}, {"Company", "Value", "MyRank.1"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded MyRank",{{"Week", Order.Ascending}, {"MyRank.1", Order.Descending}})
in
    #"Sorted Rows1"
Anthony007's avatar
Anthony007
Helper I
9 years ago

Thanks for the help, it works. Now I will try the second set-up - use the in-ID idifier in the measure.