DAX - Comparing values in the same row and rank the columns

[IslamAbdelhalim]

I have a dataset and I would like to compare certain values per each row and rank the columns that I compared from one to three

ID	A	B	C	D	E	F	G	H
1	53	38	100	56	67	65	24	3
2	7	12	91	7	37	54	2	33
3	15	16	99	74	7	24	64	97
4	19	33	40	64	91	80	70	51
5	82	88	47	64	8	93	53	10
6	31	98	58	59	79	14	7	50
7	37	56	31	2	86	45	36	86
8	76	84	11	93	56	4	47	85
9	64	13	71	13	33	5	69	2
10	73	46	63	27	31	95	73	63
11	94	68	24	31	52	26	89	20
12	90	18	59	19	38	46	65	68
13	38	98	72	86	18	68	43	52
14	69	49	64	4	23	22	76	30
15	99	14	71	69	36	63	18	5

I would like to compare only columns A, C, E, F, and H.  the results would be like that 

ID	Rank 1	Rank 2	Rank 3
1	C	E	F
2	C	F	E
3	C	H	F
4	E	F	H
5	F	A	C

[Greg_Deckler · Accepted solution]

Thinking of something like below. See attached PBIX file.

Rank 1 = 
    VAR __Table =
        {
            ("A",MAX([A])),
            ("C",MAX([C])),
            ("E",MAX([E])),
            ("F",MAX([F])),
            ("H",MAX([H]))
        }
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "__Rank",RANKX(__Table,[Value2],,DESC)
        )
RETURN
    MAXX(FILTER(__Table1,[__Rank] = 1),[Value1])

[camargos88 · Accepted solution]

Hi @IslamAbdelhalim ,

Check this file: Download PBIX 

If you want this paste this M-code on Blank Query -> Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RVJBEsQgCPuL5x6KiMpbOv3/N9aEwl4Y0EAS9HmatKuZnqD7BLlv1POEuRDshD5w3d7raf1kOBckLl+lCAYUjjWgmCloF0xzB3Z8DZw4EXwRjFQ8mq827roGxUa5KEyIxtQNqg3NYyUalWsakptgsCvGOK6NgWIQJBVZgP9uqo08qAZodUYJMEYt1pQvxU50KttGsKdIAWRJZjSMydNJBqzQMDfBl0DWV/mwvJ2x6WDmSnc9F98V2jsVcnh4jLcDhdQyYve7GC2GEV6fgwtctQ92k3FokBE+0ssoy/wZNNFzZfppsfwb8RKSzVrGQ2R73x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID " = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID ", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}, {"F", Int64.Type}, {"G", Int64.Type}, {"H", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"B", "D", "G"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"ID "}, {{"Rows", each _, type table [#"ID "=number, A=number, C=number, E=number, F=number, H=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(Table.SelectColumns(Table.Sort(Table.Transpose(Table.SelectColumns(Table.DemoteHeaders([Rows]),
{"Column2", "Column3", "Column4", "Column5", "Column6"})),
{"Column2", Order.Descending}), "Column1"), "Index", 1, 1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Column1", "Index"}, {"Column1", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "pt-BR"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "pt-BR")[Index]), "Index", "Column1"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"5", type text}, {"4", type text}, {"3", type text}, {"2", type text}, {"1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"1", "Rank1"}, {"2", "Rank2"}, {"3", "Rank3"}, {"4", "Rank4"}, {"5", "Rank5"}})
in
#"Renamed Columns"

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[IslamAbdelhalim]

Hi @camargos88, The M-code get me the correct answer 👍

[Greg_Deckler · Accepted solution]

Thinking of something like below. See attached PBIX file.

Rank 1 = 
    VAR __Table =
        {
            ("A",MAX([A])),
            ("C",MAX([C])),
            ("E",MAX([E])),
            ("F",MAX([F])),
            ("H",MAX([H]))
        }
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "__Rank",RANKX(__Table,[Value2],,DESC)
        )
RETURN
    MAXX(FILTER(__Table1,[__Rank] = 1),[Value1])

[IslamAbdelhalim]

Thanks, @Greg_Deckler,  I used this DAX and it solved the problem. Appreciated