cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## DAX - Retrieve a value in one column based on MAX in another column

Hi,

First post and I actually got problems even formulating the subject.

Anyway - this is what I want to achieve, and apparently my skills are not sufficient to figure out the solution.

In my head I want to to something like this:

For each ID -> Find Max sequence -> Return Value in same row

 ID Sequence Value 1000 1 30 1000 2 50 1000 3 10 1001 1 30 1001 2 70

So in this case I want to extract

ID 1000 - Value 10

ID 1001 - Value 70

I got a feeling this could be easy, but I cannot find the correct method. I always end up with sum of all Values per ID.

1 ACCEPTED SOLUTION
Super User

Hi @Anonymous

Try this, where Table1 is the name of your table:

1. Set Table1[ID] in the rows of a matrix visual

2. Place this measure in values of the matrix:

```ValMaxSequence =
CALCULATE (
VALUES ( Table1[Value] ),
FILTER ( ALL ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) )
)```
9 REPLIES 9
Anonymous
Not applicable

Thank you all,

I had the same issue except that the maximum value to be retrieved is from another table. yet it worked well..

now i have an issue as I want to create a visual where the value of the new measure can be aggregated to find out the total... but it is not working ( I only get to see one value) and there is no optoin to aggregate can you please help thanks

Super User

Hi @Anonymous

Try this, where Table1 is the name of your table:

1. Set Table1[ID] in the rows of a matrix visual

2. Place this measure in values of the matrix:

```ValMaxSequence =
CALCULATE (
VALUES ( Table1[Value] ),
FILTER ( ALL ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) )
)```
Regular Visitor

Hi, I expected receive both values with this formula , for ID 1000 and 1001, however I get only the max for 1000 (the formula get the absolute max of Sequence). How can I get both max? thanks in advance! (this measure was created in powerpivot in Excel and I know could be a little different in PowerBI)

Anonymous
Not applicable

Thank you, that solved it!

I do however struggle with figuring out why this works..

I have to work on this one. Thank you again!

Super User

@Anonymous

Let's run an example of how the measure works.

Take the row in your matrix visual with ID =1000. In that row you have an active filter on Table1[ID]=1000. We want to select the row in Table1 that has the max sequence and that is  why we use:

FILTER ( ALL ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) )

Note that MAX ( Table1[Sequence] ) is affected by the filter context and thus will return the MAX sequence for ID=1000, i.e. 3.

The result  of filter FILTER( ), which is now the row in Table1 with ID=1000 and Sequence=3, is then applied to the first parameter in CALCULATE : VALUES( Table1[Value]). This returns the  value in column Table1[Value] in the filtered row of Table1, i.e. 10

Anonymous
Not applicable
I tend to forget the filter applied from the matrix, and this was an eye opener in that regard.
I’m getting closer to understanding, but still got problems with why ALL is necessary? Isn’t the table already filtered by ID=1000? Why do we need to go via ALL to filter on the Sequence?
Actually my initial thought was that by applying ALL we would remove the applied filter on ID and find MAX of all the values in Sequence. I do see that this is not happening, but I don’t understand why.

I’m very grateful for your replies so far!
Super User

@Anonymous

Legitimate question. ALL () in this case is actually not necessary. It would work also with

FILTER ( VALUES ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) )

and even with

FILTER ( Table1, Table1[Sequence] = MAX ( Table1[Sequence] ) )

because in all cases we're modifying the filter on Table1[Sequence] but leaving the one on Table1[ID] unaltered

The behavior of ALL( ) is a bit tricky. It can work in two ways that might sound like the same but certainly ain't:

a) as a filter remover or

b) as a function that just returns a table without filters applied (but without removing filters elsewhere).

The latter is what happens in this case.  Have a look at this excellent article by the Italian gurus.

Super User

@Anonymous

good that you ask  cause I just realized that the code has a problem. It works in this case because in your sample table the max Sequence is different for the two IDs. If it were the same for both, the code would generate an error at the grand total row (no filter on Table1[ID] there). We'd need to update it like this:

```ValMaxSequence =
CALCULATE (
SELECTEDVALUE ( Table1[Value] ),
FILTER ( ALL ( Table1[Sequence] ), Table1[Sequence] = MAX ( Table1[Sequence] ) )
)```

Anonymous
Not applicable
Thanks again!

This clarifies even further.

When ALLSELECTED is introduced, I realize I need to study the ALL-functions in more depth. I’ve read a bit, but it’s often hard to understand without data which are meaningful to myself. I will play around with these alternative expressions and try to make sense of it all.