返回另一列排序的最高值

问题描述

假设我有一张表，如下所示：

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 }
    }
)

我想要一个返回顶部 Group 的度量根据其 Value 在 Year 过滤上下文内或外工作的值。也就是说，它可以在这样的矩阵视觉中使用（包括总计行）：

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):

使用DAX查找最大值并不难：

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

MaxValue = MAX(TableA[Value])

或

MaxValue = MAXX(TableA, TableA[Value])

但是是查找与该值相对应的 Group 的最佳方法？

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

我已经尝试过：

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

但是，这不会不适用于总计行，如果可能的话，我宁愿不必在度量中使用 Year （可能还有其他列

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.

理想情况下，如果 MAXX 函数，该函数将指定找到最大值后要返回的列，这与 Excel函数具有。

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.

推荐答案

另一种方法是使用 TOPN 函数。

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

TOPN 函数返回整行而不是单个值。例如，代码

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

TOPN(1, TableA, TableA[Value])

返回 TableA 的前1行，该行由 TableA排序[值] 。与该顶部 Value 关联的 Group 值位于行中，但我们需要能够访问它。有两种可能。

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.

使用 MAXX ：

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

这将从中找到最大的 Group 第一个参数中的TOPN 表。 （只有一个 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.)

使用 SELECTCOLUMNS ：

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

此函数通常返回一个表（具有指定的列），但是在这种情况下，它是具有单行和单列的表，这意味着DAX会将其解释为常规值

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.

这篇关于返回另一列排序的最高值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！