Return top value ordered by another column

Another way to do this is through the use of the TOPN function.

The TOPN function returns entire row(s) instead of a single value. For example, the code

TOPN(1, TableA, TableA[Value])

returns the top 1 row of TableA ordered by TableA[Value]. The Group value associated with that top Value is in the row, but we need to be able to access it. There are a couple of possibilities.


Use MAXX:

Top Group = MAXX(TOPN(1, TableA, TableA[Value]), TableA[Group])

This finds the maximum Group from the TOPN table in the first argument. (There is only one Group value, but this allows us to covert a table into a single value.)


Use SELECTCOLUMNS:

Top Group = SELECTCOLUMNS(TOPN(1, TableA, TableA[Value]), "Group", TableA[Group])

This function usually returns a table (with the columns that are specified), but in this case, it is a table with a single row and a single column, which means the DAX interprets it as just a regular value.

Leave a Comment