Forum Discussion
Helper VCount IFS By Latest Date
Hello,
I have a number of records with duplicates. Each record has a timestamp (date & time) and a status column.
I am using the following DAX expression to count records by the most recent timestamp. How do I modify this expression to also include only “Completed” records in the count?
LatestRecord =
VAR MostRencentTime =
CALCULATE (
MAX ( TaskTable[RecordTimestamp]),
ALLEXCEPT ( TaskTable, TaskTable[Task ID] )
)
RETURN
IF ( MostRencentTime = MAX ( TaskTable[RecordTimestamp] ), 1, 0 )
Best regards,
ImranAmi
Hi @imranamikhan ,
I'm not sure how you want the count to be calculated exactly, but try this:
UniqueCount = VAR _max = CALCULATE ( MAX ( TaskTable[RecordTimestamp] ), ALLEXCEPT ( TaskTable, TaskTable[Task ID] ) ) RETURN CALCULATE ( COUNTROWS ( TaskTable ), FILTER ( ALLEXCEPT ( TaskTable, TaskTable[Task ID] ), TaskTable[Status] = "Completed" && TaskTable[RecordTimestamp] = _max ) )Try this:
Solution = VAR MaxDate = MAX ( TaskTable[RecordTimestamp] ) VAR _STATUS = FILTER ( TaskTable, TaskTable[Status] = "Completed" && TaskTable[RecordTimestamp] = MaxDate ) VAR _Count = COUNTROWS ( _STATUS ) RETURN _CountBless you!
Vivek
If it helps, please mark it as a solution
Congratulations would be a cherry on top 🙂
https://www.vivran.in/
Connect connects on LinkedIn
12 Replies
- vivran22
Community Champion
Hello imranamikhan
You may try:
LatestRecord = VAR MostRencentTime = CALCULATE ( MAX ( TaskTable[RecordTimestamp]), ALLEXCEPT ( TaskTable, TaskTable[Task ID] ), KEEPFILTERS(TaskTable[Status] = "Completed" ) RETURN IF ( MostRencentTime = MAX ( TaskTable[RecordTimestamp] ), 1, 0Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedIn 
v-frfei-msftCommunity Support
Hi imranamikhan ,
Have a try please.
LatestRecord = VAR MostRencentTime = CALCULATE ( MAX ( TaskTable[RecordTimestamp] ), FILTER ( ALLEXCEPT ( TaskTable, TaskTable[Task ID] ), TaskTable[Status] = "Completed" ) ) RETURN IF ( MostRencentTime = MAX ( TaskTable[RecordTimestamp] ), 1, 0 )- imranamikhan
Thanks v-frfei-msft and vivran22.
I have tried both options but both return a value of 1, whereas what I am looking for is a count of records with a "Completed" status by the most recent time stamp. If I could translate the DAX into an Excel formula, it would look like this:
=COUNTIFS(
Table_TaskList[RecordTimestamp],
MAX(Table_TaskList[RecordTimestamp]),
Table_TaskList[ProcessStatus],"Completed"
)- danextian
Super User
Hi imranamikhan ,
Have you tried using the formula of v-frfei-msft and vivran22 in a calculated column then use the result for your count? If you are going to use a measure, the formula should be something like below:
Count = CALCULATE ( MAX ( TaskTable[RecordTimestamp] ), FILTER ( ALLEXCEPT ( TaskTable, TaskTable[Task ID] ), TaskTable[Status] = "Completed" ) )
- v-frfei-msft
Hi imranamikhan ,
Does that meet your requirement?
LatestRecord = VAR MostRencentTime = CALCULATE ( MAX ( TaskTable[RecordTimestamp] ), ALLEXCEPT ( TaskTable, TaskTable[Task ID] ) ) VAR a = IF ( MostRencentTime = MAX ( TaskTable[RecordTimestamp] ) && MAX ( TaskTable[Statue] ) = "Completed", 1, 0 ) RETURN aLatestRecord—1 = SUMX(TaskTable,[LatestRecord])- imranamikhan
Thanks danextian. I am working with a push dataset so I am limited to using measures only. The suggested DAX is only returning a date, not a count.
Thanks again v-frfei-msft. Unfortunately the suggested measure is producing incorrect results. I have attached my working file via OneDrive here:
https://1drv.ms/u/s!AkH_PeScw-iuhpd6-3DAVBy805z36A?e=gyY196
Could you please take a look when you have a moment?
- danextian
Hi @imranamikhan ,
I'm not sure how you want the count to be calculated exactly, but try this:
UniqueCount = VAR _max = CALCULATE ( MAX ( TaskTable[RecordTimestamp] ), ALLEXCEPT ( TaskTable, TaskTable[Task ID] ) ) RETURN CALCULATE ( COUNTROWS ( TaskTable ), FILTER ( ALLEXCEPT ( TaskTable, TaskTable[Task ID] ), TaskTable[Status] = "Completed" && TaskTable[RecordTimestamp] = _max ) )
