Summarize based on ID and Name with Min Date of Entire Dataset

[KyawMyoTun]

Dear Experts,

  I am trying to summarize the columns based on ID and Name with Min Date.
Here is my data,

ID	Name	Date
1111	KMT	6/1/2023
1111	KMT	6/2/2023
1112	MMT	6/2/2023
1112	KKT	6/2/2023

I tried with the summarize function as below dax

Ticket =

COUNTROWS(SUMMARIZE(Sheet1,Sheet1[ID],Sheet1[Name],"Min Dt", MIN(Sheet1[Date])))
As ID 1111 and Name KMT is duplicate, I would like to count as only 1 at the 6/1/2023(do not want to count at 6/2/2023).
So, my expected result will be,
Day Total Count
6/1/2023 1

6/2/2023              2
even after selecting from slicer.
Please help me to correct my dax funtion.

Regards,
KMT

[rajendraongole1 · Accepted solution]

Hi @KyawMyoTun You can use the following DAX expression

Ticket =
COUNTROWS(
SUMMARIZE(
FILTER(
Sheet1,
[Date] = CALCULATE(
MIN(Sheet1[Date]),
ALLEXCEPT(Sheet1, Sheet1[ID], Sheet1[Name])
)
),
Sheet1[ID],
Sheet1[Name],
"Min Dt", MIN(Sheet1[Date])
)
)

The above expression should give you the expected result where duplicates are counted only once for each unique combination of ID and Name, based on the minimum date.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[KyawMyoTun]

Dear @rajendraongole1 ,
 This works perfectly. Thanks a lot for your help.