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.

Helper II
Helper II

Count ID filtered by last date for each ID

Hopefully someone can help me with this.
I want to distinct count each ID, filtered by the maximum date for each ID. So for example: 


The rows which are marked yellow, need to be counted.
I want to use a measure, since it depends on the selected date in the slicer.

Hopefully someone can help me! 

Super User
Super User


You can use the following DAX measure to count the distinct IDs filtered by the maximum date for each ID:


Distinct Count by Max Date = CALCULATE( DISTINCTCOUNT('Table'[ID]), FILTER( ALL('Table'), 'Table'[Date] = MAX('Table'[Date]) && 'Table'[ID] IN VALUES('Table'[ID]) ) )

In this measure, we first use the MAX function to get the maximum date for each ID, and then use the FILTER function to filter the table to only include rows with that maximum date for each ID. Finally, we use the DISTINCTCOUNT function to count the distinct IDs in the filtered table.

Make sure to replace 'Table' with the name of your actual table. You can then use this measure in a visual along with a date slicer to get the count of distinct IDs filtered by the selected date.

Thanks for your response. However, the measure is counting the customers for the maximum date of the slicer since this are way to little ID's: 



> I am selecting months in my slicer, not days.


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.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

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