cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kuehcj
Frequent Visitor

Max of one column but use result from a different column

I have a table like this:

SELCMC
7 Jul 19 D22270.86%12
7 Jul 19 DV278.18%28
7 Jul 19 DVS284.32%43
7 Jul 19 N22279.96%3
7 Jul 19 NV290.29%130
8 Jul 19 DV2 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:

 

SELC
7 Jul 19 DVS284.32%
7 Jul 19 NV290.29%
8 Jul 19 DV2 

 

May I know how can I achieve that with DAX?

1 ACCEPTED SOLUTION
TeigeGao
Solution Sage
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 =
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:

PBIDesktop_UbBctUEkJr.png

Then we can create the following visual:

PBIDesktop_Mw3MOL5boL.png

Best Regards,

Teige

View solution in original post

3 REPLIES 3
TeigeGao
Solution Sage
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 =
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:

PBIDesktop_UbBctUEkJr.png

Then we can create the following visual:

PBIDesktop_Mw3MOL5boL.png

Best Regards,

Teige

View solution in original post

AiolosZhao
Memorable Member
Memorable Member

Hi @kuehcj ,

 

I think below is what you want, please try:

Max of one column but use result from a different column.PNG

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors