Return top value ordered by another column

4
votes

Suppose I have a table as follows:

TableA =
DATATABLE (
    "Year", INTEGER,
    "Group", STRING,
    "Value", DOUBLE,
    {
        { 2015, "A", 2 },
        { 2015, "B", 8 },
        { 2016, "A", 9 },
        { 2016, "B", 3 },
        { 2016, "C", 7 },
        { 2017, "B", 5 },
        { 2018, "B", 6 },
        { 2018, "D", 7 }
    }
)

I want a measure that returns the top Group based on its Value that work inside or outside a Year filter context. That is, it can be used in a matrix visual like this (including the Total row):

Matrix Visual

It's not hard to find the maximal value using DAX:

MaxValue = MAX(TableA[Value])

or 

MaxValue = MAXX(TableA, TableA[Value])

But what is the best way to look up the Group that corresponds to that value?

I've tried this:

Top Group = LOOKUPVALUE(TableA[Group],
                TableA[Year], MAX(TableA[Year]),
                TableA[Value], MAX(TableA[Value]))

However, this doesn't work for the Total row and I'd rather not have to use the Year in the measure if possible (there are likely other columns to worry about in a real scenario).

Note: I am providing a couple solutions in the answers below, but I'd love to see any other approaches as well.

Ideally, it would be nice if there were an extra argument in the MAXX function that would specify which column to return after finding the maximum, much like the MAXIFS Excel function has.

2
powerbidax
Could you explain what you want your measure to return? Do you want it to return a whole table (not a scalar value) with only the A group (two records) because it is the record of A group that has the highest value of all the records? Or is it a sort of rank that you want to get for each group? – Przemyslaw Remin

2 Answers

5
votes

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.

0
votes

One way to do this is to store the maximum value and use that as a filter condition.

For example,

Top Group =
VAR MaxValue = MAX(TableA[Value])
RETURN MAXX(FILTER(TableA, TableA[Value] = MaxValue), TableA[Group])

or similarly,

Top Group =
VAR MaxValue = MAX(TableA[Value])
RETURN CALCULATE(MAX(TableA[Group]), TableA[Value] = MaxValue)

If there are multiple groups with the same maximum value the measures above will pick the first one alphabetically. If there are multiple and you want to show all of them, you could use a concatenate iterator function:

Top Group =
VAR MaxValue = MAX(TableA[Value])
RETURN CONCATENATEX(
           CALCULATETABLE(
               VALUES(TableA[Group]),
               TableA[Value] = MaxValue
           ),
           TableA[Group],
           ", "
       )

If you changed the 9 in TableA to an 8, this last measure would return A, B rather than A.