Trying to Filter the Top 1

[HishamAmin]

Hi 

I'm trying to get a similar reults to this SQL statment but in DAX , is it possible

I'm trying to get the RGBA Color stored for each range and each one has a Value_To , and the Null one is the excedding the last range which in below table is 70

SELECT TOP 1 RGBA
FROM TS.Module_Qualification_Range mqr
where mqr.Module_ID = 91 AND COALESCE(mqr.Value_To, 99999999999999999999999) > 90
ORDER BY COALESCE(mqr.Value_To, 99999999999999999999999)

Table date : 

id                    Value_To                rgba
510                  50.00000                rgb(255, 0, 0)
511                  70.00000                rgb(253, 245, 0)
512                     NULL                   rgb(0, 255, 0)

the result should be => rgb(0, 255, 0)

[v-diye-msft]

Hi @HishamAmin 

You can use the TOPN function or RankX function

https://docs.microsoft.com/en-us/dax/topn-function-dax 

https://docs.microsoft.com/en-us/dax/rankx-function-dax 

Here's more reference could provide you more pointers:

https://www.sqlbi.com/articles/from-sql-to-dax-projection/ 

[amitchandak · Accepted solution]

@HishamAmin , You can use TOPN

example

Top 10 City Rank = CALCULATE([Sales],TOPN(1,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

or use rank and filter

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...