The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ?
Solved! Go to Solution.
Measure =
MAXX(
TOPN( 1, FILTER( INFO, INFO[tag] = "colour" ), INFO[id] ),
INFO[value]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Measure =
MAXX(
TOPN( 1, FILTER( INFO, INFO[tag] = "colour" ), INFO[id] ),
INFO[value]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Fantastic. Thanks.