Count IFS By Latest Date

[imranamikhan]

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

[danextian · Accepted solution]

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

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

LatestRecord—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 · Accepted solution]

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

[imranamikhan]

Apologies this did work - the error was my part. Thank you very much.

[imranamikhan]

Hi @danextian Thanks for your help. I guess I am not being clear on the requirement because the solution is producing the same incorrect value as the measure kindly provided by @v-frfei-msft 

I have a set of data. Column A contains a list of unique entries (Task ID), Column B contains a Status value, and Column C contains a Time Stamp. When the data is refreshed, new entries are appended to the existing data with the same IDs but with an updated Status. I want to count the number of occurrences of a specific Status (such as "Completed") by the latest record. It is simple to do this in Excel for example, you count the number of entries based on the latest Time Stamp AND filter that count by a Status of “Completed”.

E.g.

=COUNTIFS(column B,MAX(column b),(column c),”Completed”)

In the below example, 23/03/2020  09:12 is the latest refresh date. So I want to count the number of entries with that date AND fitler the result by a status of “Completed”. The OneDrive sample file includes the spreadsheet below. Does this clarify?

best regards,

ImranAmi

[imranamikhan]

Hi - if anyone has any possible solutions that would be appreciated.

best regards,

ImranAmi

[vivran22 · Accepted solution]

@imranamikhan

Try this:

Solution =
VAR MaxDate =
    MAX ( TaskTable[RecordTimestamp] )
VAR _STATUS =
    FILTER (
        TaskTable,
        TaskTable[Status] = "Completed"
            && TaskTable[RecordTimestamp] = MaxDate
    )
VAR _Count =
    COUNTROWS ( _STATUS )
RETURN
    _Count

Bless 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

[imranamikhan]

Thank you very much

[v-frfei-msft]

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]

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

[vivran22]

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

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