Lookup related record in same table

[EmJa]

Product table

Sku	ID	Price
Prod1_a	Prod1	5
Prod1_b	Prod1	6
Prod1_c	Prod1	7
Prod2_a	Prod2	7
Prod2_b	Prod2	6

What dax measure can be used to return the price of the MAX(Sku) for each ID?

i.e All Prod1 would show 7 and all Prod2 would show 6.

Thank you

[smpa01 · Accepted solution]

@EmJa  you can use a measure like this

Measure =
VAR _id =
    MAX ( 'Table 1'[ID] )
VAR _sku =
    CALCULATE (
        MAX ( 'Table 1'[Sku] ),
        FILTER (
            ALL ( 'Table 1' ),
            'Table 1'[ID] = _id
                && RIGHT ( 'Table 1'[Sku], 1 )
                    = CALCULATE (
                        RIGHT ( MAX ( 'Table 1'[Sku] ), 1 ),
                        ALLEXCEPT ( 'Table 1', 'Table 1'[ID] )
                    )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table 1'[Price] ),
        FILTER ( ALL ( 'Table 1' ), 'Table 1'[ID] = _id && 'Table 1'[Sku] = _sku )
    )

[EmJa]

Thank you @smpa01 , not just for the solution but for th concept. 

(That was a really quick response!)