Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

 

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

View solution in original post

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 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

lopa123_1-1634903787405.png

 

 

 

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!  

@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!

@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.

 

 

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.