How to mimic SQL command "SELECT TOP 1 col FROM table WHERE condition ORDER BY othercol DESC" in DAX

[Anonymous]

Question is pretty much in the subject.

I want a measure that mimic this SQL command : 

SELECT TOP 1 col

FROM table

WHERE condition

ORDER BY othercol DESC

Example input and output :

MyTable

id tag value

1 colour blue

2 shape triangle

3 height 7

4 shape round

5 colour orange

if I apply this SQL query :

SELECT TOP 1 value

FROM MyTable

WHERE tag = 'colour'

ORDER BY id DESC

it will return "orange"

How to do exactly the same in DAX ?

[CNENFRNL · Accepted solution]

Measure = 
MAXX(
    TOPN( 1, FILTER( INFO, INFO[tag] = "colour" ), INFO[id] ),
    INFO[value]
)

[Anonymous]

Fantastic. Thanks.