cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bbirkenes Regular Visitor
Regular Visitor

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

 

IDSequenceValue
1000130
1000250
1000310
1001130
1001270

 

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. 

 

Answers highly appreciated. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi @bbirkenes

 

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] ) )
)
7 REPLIES 7
Super User
Super User

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

Hi @bbirkenes

 

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] ) )
)
bbirkenes Regular Visitor
Regular Visitor

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

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
Super User

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

@bbirkenes

 

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 

bbirkenes Regular Visitor
Regular Visitor

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

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
Super User

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

@bbirkenes

 

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.

 

 

Highlighted
Super User
Super User

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

@bbirkenes

 

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

     

bbirkenes Regular Visitor
Regular Visitor

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

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.