How to use lookup and max on dax correctly

[sagidam]

Hello friends,
I try to extract from the table the field of the greatest value, by field Id,
When I want to be filtered by field Bompname,
I would like to get the value in Ballance field
Example of a table "BALL"

id	compname	ballance
1	A	26
1	B	15
2	B	9
2	A	15
3	B	5
3	A	3

In this example I would like to receive

A=3

B=5

[LivioLanzo · Accepted solution]

You can do it like this:

=
SUMX (
    VALUES ( BALL[compname] ),
    VAR MxID =
        CALCULATE (
            MAX ( BALL[id] )
        )
    RETURN
        CALCULATE (
            MAX ( BALL[ballance] ),
            BALL[id] = MxID
        )
)

[sagidam]

Work like a charm

Thank you

[ofirk]

Hi @sagidam,

I expect there are several ways to do this... 

This one seemed to work for me:

Column = 
SUMX(
    FILTER(BALL, 
        [compname] = EARLIER([compname]) && 
        [id] = MAXX(BALL, [id])), 
    [ballance])

Results look like this (select "Don't summarize" for the visualization of the column):

[sagidam]

sorry something dont work with the EARLIER syntax