cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DAX Max value from another column based on row value

I'm trying to write a DAX function to find the maximum value in one column based on a condition in another, but have this condition change dynamically based on the row value.
 

With this code:

 

CALCULATE(MAX(RankOfArea[count]),filter(RankOfArea,RankOfArea[Line]="Pic"))

I get this table:

 

count |  Line  |  Max
7220  | Pic    |  7220
283   | Dis    |  7220
3557  | Pic    |  7220
317   | Met    |  7220
500   | Met    |  7220

And I'd like this result:

 

count |  Line  |  Max
7220  | Pic    |  7220
283   | Dis    |  283
3557  | Pic    |  7220
317   | Met    |  500
500   | Met    |  500

Of course I have to remove the ="Pic", but not sure what to replace it with? Many thanks

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

HI @Anonymous

 

Try this

 

=
CALCULATE (
    MAX ( RankOfArea[count] ),
    ALLEXCEPT ( Rankofarea, Rankofarea[Line] )
)
Regards
Zubair

Please try my custom visuals


View solution in original post

12 REPLIES 12
Zubair_Muhammad
Community Champion
Community Champion

HI @Anonymous

 

Try this

 

=
CALCULATE (
    MAX ( RankOfArea[count] ),
    ALLEXCEPT ( Rankofarea, Rankofarea[Line] )
)
Regards
Zubair

Please try my custom visuals


View solution in original post

Is it possible to use this formula and only show where the Maximum is within the given row and all other rows show blank?  I would like to return an X in the row where the max is.  I need to do this so I can filter the data through a slicer for X and ignore blanks?

 

 

Type      Version   Max

Apple         1

Apple         3

Apple         8

Apple         9          x

Peach         1

Peach         10        x

Peach         7

Peach         4

@JQuon

 

You can use this calculated column

 

Assuming your TableName  is Table1

 

MAx_Column =
IF (
    Table1[Version]
        = CALCULATE ( MAX ( Table1[Version] ), ALLEXCEPT ( Table1, Table1[Type] ) ),
    "X"
)
Regards
Zubair

Please try my custom visuals


I think this worked! Thank you so much!

@JQuon

 

Also you can use a ranking technique...i.e. rank the versions for each type and then filter for RANK 1

i.e use this calculated column

 

RANK =
RANKX (
    FILTER ( Table1, Table1[Type] = EARLIER ( Table1[Type] ) ),
    Table1[Version],
    ,
    DESC,
    DENSE
)
Regards
Zubair

Please try my custom visuals


@JQuon

 

See attached file

 

Jquon.png

Regards
Zubair

Please try my custom visuals


Anonymous
Not applicable

Hi  ,

 

Thanks for your reply.

 

Why did you change the post title please? I don't want to return a running total, rather the maximum value.

 

Edit - sorry that worked, my typing. No idea why. Thanks.

Hi @Anonymous

 

Smiley SurprisedI didn't change the post titleSmiley SurprisedSmiley Surprised

 

 

Regards
Zubair

Please try my custom visuals


Anonymous
Not applicable

Hi Zubair,

 

Oh sorry, that was me assuming - I saw you were a power user !

 

Must've been admin. Anyway cheers again for your response - didn't understand it but it worked 🙂

HI @Anonymous

 

Here is a wonderful article on ALL functions.

 

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

Regards
Zubair

Please try my custom visuals


Anonymous
Not applicable

So the line:

 

 ALLEXCEPT( RankOfArea, RankOfArea[Line] )

 

creates a reference to a different table on each row? And that table is RankOfArea, filtered by whatever is in current row of the [Line] column?

Anonymous
Not applicable

Cheers I'll take a look. I was watching AF's introductory video last night.

 

It's perhaps the CALCULATE bit I don't get but possibly trying to run before I can walk..

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors