DAX measure to get top unique rows

[akshansh99]

Hey community!

I am trying to get the top rows from a table to use in my measure and the requirnments are:

1) user will select how many rows they want to retrieve based on a single select numerical slicer

2) the table has a date column which consists duplicate values, the result set should contain UNIQUE values which are less than or equal to the date selected in the slicer(single select dropdown)

I have tried almost every table manipulation function but i am not able to fetch only the unique values from the table i will share the codes i have tried and if you can suggest a better approach or guide me where i am going wrong that would be very helpful!

1) TOPN(

 24,

 CALCULATETABLE(

  DISTINCT(ExchangeRate),

  ExchangeRate[AsOf] <= date(2022,10,11)

 ), ExchangeRate[AsOf],DESC )

2)TOPN( 24, CALCULATETABLE( DISTINCT( SUMMARIZE( ExchangeRate, ExchangeRate[AsOf], ExchangeRate[ID], ExchangeRate[ToCurrencyID] ) ), ExchangeRate[AsOf] < <= date(2022,10,11) ), ExchangeRate[AsOf],DESC )

in these approaches i think the ID column does not allow me to filter the unique date values becuase it has a unique values in all the rows, i might be wrong here but that's what i understood

3) CALCULATETABLE(TOPN(24,CALCULATETABLE(VALUES(ExchangeRate[AsOf]),ExchangeRate[AsOf]<=date(2022,10,11)),ExchangeRate[AsOf]))

This approach doesn't give me the whole table it just gives me a single column table

4) ADDCOLUMNS(

CALCULATETABLE(TOPN(23,CALCULATETABLE(VALUES(ExchangeRate[AsOf]),ExchangeRate[AsOf]<=date(2022,10,11)),ExchangeRate[AsOf])), "ID",CALCULATE(MAXA(ExchangeRate[Id])))

 i have tried adding column to my single column table but this doesn't give a row level context to my measure and then because of that the matrix visual shows result like this 

 my end goal here is to show these stressed rates by ID or by DATE, something like this

[v-shex-msft]

Hi @akshansh99,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Notice: please remove the sensitive data before share.

Regards,

Xiaoxin Sheng