Distinct count of application ids based on application status

[Anonymous]

Good day. I need help on creating DAX formula for the application example.

Background information: 

The application starts off being 'new' and depending on the situation, will progress on to become either 'Completed' , 'On-hold' or 'Suspended'. We will count the application id only once.  For example as shown in table below, application ID 1 progresses from 'New' to 'Completed' ,and therefore is counted as 'Completed' once. Application id 5 progresses from 'New' to 'Suspended' and therfore is counted as 'Suspended' once. If the application is new and have no updated progress i.e. application ID 2 and 4, it will be counted 'New' once.

My distinct count calculation would be 5, broken by according to the application status. That is,  2 new applications, 1 completed, 1 suspended and 1 On-hold.

Any help would be appreciated. Thanks!

Application Status	Application ID	Distinct count of Application ID
New	1	0
New	2	1
New	3	0
New	4	1
New	5	0
Completed	1	1
On hold	3	1
Suspended	5	1
		5

[mahoneypat · Accepted solution]

This expression gives your expected results from the example data

DC App ID =
SUMX (
    'ID',
    CALCULATE (
        VAR __notNew =
            SELECTEDVALUE ( 'ID'[Application Status] )
                IN { "Completed", "On Hold", "Suspended" }
        VAR __onlyNew =
            CALCULATE (
                DISTINCTCOUNT ( 'ID'[Application Status] ),
                ALLEXCEPT ( 'ID', 'ID'[Application ID] )
            ) = 1
        RETURN
            IF ( OR ( __notNew, __onlyNew ), 1, 0 )
    )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi Pat.

I used the DAX measure and I can’t apply Application ID field as shown below. Can you run me through your thought process as well please?

Thanks.

[lbendlin]

How is the data sorted when it comes in? Do you have timestamps for status changes?

[Anonymous]

The only time stamp provided is the day. For example on 15 May 2020, the application status changes from 'New' to 'Completed'. Or it could be the it takes three days to change its status from 'New' to 'Suspended'. the application will always start off as 'New' and will take on the new status accordingly.