How to rank 6 values from the 6 different columns in the same row (DAX)

15 Views Asked by At

I have 6 columns of values, I want a final column that can give me the 3rd ranked value of those 6 columns meaning (R0W 1= 1,2,3,4,5,6)=3

Baseline KW = 
VAR Rank1 = RANKX(ALLSELECTED('2_grouped_data'),'2_grouped_data'[7_Days_Prior_KW] , , DESC)
VAR Rank2 = RANKX(ALLSELECTED('2_grouped_data'), '2_grouped_data'[14 Days Prior KW], , DESC)
VAR Rank3 = RANKX(ALLSELECTED('2_grouped_data'), '2_grouped_data'[21 Days Prior KW], , DESC)
VAR Rank4 = RANKX(ALLSELECTED('2_grouped_data'), '2_grouped_data'[1 Year Prior KW], , DESC)
VAR Rank5 = RANKX(ALLSELECTED('2_grouped_data'), '2_grouped_data'[1Y1W Prior KW], , DESC)
VAR Rank6 = RANKX(ALLSELECTED('2_grouped_data'), '2_grouped_data'[1Y2W Prior KW], , DESC)

RETURN
    MAXX(
        VALUES('2_grouped_data'),
        SWITCH(
            TRUE(),
            Rank1 = 3, '2_grouped_data'[7_Days_Prior_KW],
            Rank2 = 3, '2_grouped_data'[14 Days Prior KW],
            Rank3 = 3, '2_grouped_data'[21 Days Prior KW],
            Rank4 = 3, '2_grouped_data'[1 Year Prior KW],
            Rank5 = 3, '2_grouped_data'[1Y1W Prior KW],
            Rank6 = 3, '2_grouped_data'[1Y2W Prior KW]
        )
    )---- colunn only gives me blanks
0

There are 0 best solutions below