CODEWITHSUNDEEP
excelexcel-formuladaxpowerpivot
gr33ns
gr33ns

Reputation: 83

Max(If( equivalent in DAX PowerPivot

This question has been asked and answered before, however the solutions I've found don't seem to work for my current situation.

My table looks like:

enter image description here

I want to return the highest value in column B for all instances of it's match in column A in power pivot.

With a standard excel function, I would use =max(if(a2=a:a,b:b)) in column C.

I've tried =CALCULATE(max(Table1[B]),filter(Table1,Table1[A]=Table1[A])) but this is the result

enter image description here

Any help would be appreciated!

Upvotes: 1

Views: 3301

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Table1[A] always equals Table1[A] so your condition is always true and thus doesn't do any meaningful filtering.

What you're looking for is to filter by the earlier row context (from the original table, not the FILTER iterator function):

CALCULATE (
    MAX ( Table1[B] ),
    FILTER ( Table1, Table1[A] = EARLIER ( Table1[A] ) )
)

Another way to do this is to use a variable to grab the row context before you're inside of the FILTER.

VAR CurrRowA = Table1[A]
RETURN
    CALCULATE ( MAX ( Table1[B] ), Table1[A] = CurrRowA )

Upvotes: 2

Related Questions