Forum Discussion
DAX - Retrieve a value in one column based on MAX in another column
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] ) ) )
Community ChampionHi 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] ) )
)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!
- AlB
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
- 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!
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)