Max of one column but use result from a different column

[kuehcj]

I have a table like this:

S	E	L	C	MC
7 Jul 19 D	22	2	70.86%	12
7 Jul 19 D	V	2	78.18%	28
7 Jul 19 D	VS	2	84.32%	43
7 Jul 19 N	22	2	79.96%	3
7 Jul 19 N	V	2	90.29%	130
8 Jul 19 D	V	2		1

Of which I would like to get the MAX of MC, grouped by S and L. Here is the output that I would like to get:

S	E	L	C
7 Jul 19 D	VS	2	84.32%
7 Jul 19 N	V	2	90.29%
8 Jul 19 D	V	2

May I know how can I achieve that with DAX?

[TeigeGao · Accepted solution]

Hi @kuehcj ,

If we want to use E as legend in a chart, we will need to use a calculated table rather than a measure, a measure cannot be used as a legend, Please refer to the following sample:

Table =
ADDCOLUMNS (
    SUMMARIZE ( Table1, Table1[S], Table1[L], "MC", MAX ( Table1[MC] ) ),
    "E", CALCULATE (
        MIN ( Table1[E] ),
        FILTER (
            ALL ( Table1 ),
            Table1[S] = EARLIER ( Table1[S] )
                && Table1[MC] = EARLIER ( [MC] )
        )
    ),
    "C", CALCULATE (
        MIN ( Table1[C] ),
        FILTER (
            ALL ( Table1 ),
            Table1[S] = EARLIER ( Table1[S] )
                && Table1[MC] = EARLIER ( [MC] )
        )
    )
)

We will get a table like below:

Then we can create the following visual:

Best Regards,

Teige

[AiolosZhao]

Hi @kuehcj ,

I think below is what you want, please try:

max E = CALCULATE(MAX(Table5[E]),FILTER(ALL(Table5[E],Table5[C],Table5[MC]),Table5[MC] = MAX(Table5[MC])))

max C = CALCULATE(MAX(Table5[C]),FILTER(ALL(Table5[E],Table5[C],Table5[MC]),Table5[MC] = MAX(Table5[MC])))

Measure = CALCULATE(MAX(Table5[MC]),ALL(Table5[E],Table5[C]))

Aiolos Zhao

[kuehcj]

Thanks a lot @AiolosZhao! That seems to work. Do you know if there is any way to use Max E as legend in a chart?