Get the count of records for Max date

[SushmaReddy]

Hi Team,

The requirement is to get the count of materials for Max date.

The below logic is getting the distinct count where i want to display the count.

VAR _M = SUMMARIZE(T1,T1[Material],

"MAX",MAX(T1[Date]),

"Status",CALCULATE(MAX(T1[Status]),

FILTER(T1,T1[Material]=EARLIER(T1[Material]))))

return

COUNTROWS(FILTER(_Mat,[Status]="Failed"))+0

Mat   Date               Status     Error

123   03-06-2022     Failed    GMDN required

123   03-06-2022     Failed    FDA required

The count should be 2

Thanks in advance

[SushmaReddy]

The code is working and getting the correct count ....Thank you for the help

[v-yalanwu-msft · Accepted solution]

Hi, @SushmaReddy ,

You could create a measure or column as follow:

1.measure:

count = var _max=CALCULATE(MAX('T1'[Date]),ALLSELECTED(T1))
return CALCULATE(COUNT(T1[Material]),FILTER(ALLSELECTED(T1),[Material]=MAX('T1'[Material])&&[Date]=_max&&[Status]="Failed"))

2. column:

Column = 
 var _max=MAX('T1'[Date])
return CALCULATE(COUNT(T1[Material]),FILTER(ALLSELECTED(T1),[Material]=EARLIER('T1'[Material])&&[Date]=_max&&[Status]="Failed"))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.