Filter column by row value PowerBI

Question

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

Alexis Olson Alexis Olson · Accepted Answer · 2018-03-12T17:45:50

There are a couple ways to do this for a calculated column.

One way is to remove all row context and explicitly define your condition:

  Max = CALCULATE(MAX(RankOfArea[Count]),
                ALL(RankOfArea),
                RankOfArea[Line] = EARLIER(RankOfArea[Line]))

(The EARLIER function refers to the earlier row context.)

Another way is to remove just the [Count] row context:

  Max = CALCULATE(MAX(RankOfArea[Count]), ALL(RankOfArea[Count])

In this case, since there are only two columns, this is equivalent to removing all row context except for the [Line] value:

  Max = CALCULATE(MAX(RankOfArea[Count]), ALLEXCEPT(RankOfArea, RankOfArea[Line]))

I recommend this latter approach in case your table acquires more columns.

Filter column by row value PowerBI

1 Answers