cancel
Showing results for
Did you mean: Frequent Visitor

## Max of one column but use result from a different column

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?

1 ACCEPTED SOLUTION  Solution Sage

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 =
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

3 REPLIES 3  Solution Sage

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 =
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  Memorable Member

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

Proud to be a Super User! Frequent Visitor

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? Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  