DAX Max value from another column based on row value

[Anonymous]

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

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

Try this

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

[JQuon]

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

[Zubair_Muhammad]

@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"
)

[ara0530]

what if X was a numeric value and I want to filter those that are not zero?   This is not working on that scenario

[JQuon]

I think this worked! Thank you so much!

[Zubair_Muhammad]

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

[Zubair_Muhammad]

@JQuon

See attached file

[Anonymous]

Hi Zubair_Muhammad ,

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.

[Zubair_Muhammad]

Hi @Anonymous

I didn't change the post title

[Anonymous]

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 🙂

[Zubair_Muhammad]

HI @Anonymous

Here is a wonderful article on ALL functions.

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

[Anonymous]

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]

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