Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

DAX measure to get top unique rows

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(

  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
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



Community Support
Community Support

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.


Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors