DAX to get largest 3 numbers in a series of columnby row

[KarthikeyanS]

Hello

I am trying to find the top 3 values per row for a series of columns and bind it to the bar chart

Example:

ID	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
1	5	1	1	0	2	5	8	2	0	1	12	100
2	0	0	22	0	0	0	0	0	10	0	1	200

Result:

ID	Column13	Column12	Column8	Column4	Column10
1	100	12	8
2	200			22	10

I need a way to determine the value of 13 columns

Thanks in advance

[Stachu]

can you unpivot the data so the columns would be in rows? you could still present it in the way the 2nd table is formatted but the code get's much more simple

[KarthikeyanS]

Thanks for your reply.

I unpivotted table and managed to put top 3 filter in "Visual filter", but the column names are not changing based on ID. It's same for all ID.

[v-chuncz-msft · Accepted solution]

@KarthikeyanS,

Unpivot Columns in Query Editor, then use Matrix visual and drag measure below to Values.

Measure =
IF (
    RANKX ( ALLSELECTED ( Table1[Attribute] ), CALCULATE ( SUM ( Table1[Value] ) ) )
        <= 3,
    SUM ( Table1[Value] )
)

[Anonymous]

I have 3 areas calculated as Area1, Area2 and Area3 in different columns. I'd like to get the 2nd highest area among the 3. 

How do I do this in the dax level instead of using a matrix visual and a measure? 

Your solution would be of great help.

Thanks in advance.