Distinct count of applicants based on application stage

[Meenasubu]

Hi All,

I have a table with applicant id, name, the application stage and date. I want to create a DAX measure that counts the number of applicants whose application stage is "Hakenut". For example, applicant 15840610 belongs to two stages - Hakenut and Poteniaalinen - then he should not be included in the calculation. Those applicants who are part of only Hakenut stage should be included in the count (like 16020859).

In the above example, the result I want is as follows:

applicant_id	stage	date	count
16020859	Hakenut	01/03/2024	1
15954547	Hakenut	28/02/2024	1

So the final result should be 2 applicants.

Any help would be appreciated. Thanks!

[Fowmy · Accepted solution]

@Meenasubu 

Create this meaure and change the table name as per your table. Assign this to visual filter and set it equal to 1 

CountRec = 
VAR __t1 = 
CALCULATETABLE(
    VALUES( table[stage] ),
    ALLEXCEPT( table , table[applicant_id] )
)
RETURN
    INT("E-mail" IN __t1 && COUNTROWS( __t1 ) = 1)

[Meenasubu]

Hi @vs_7 , thanks for the reply. I tried the DAX measure but I am not able to use the function EXISTS in the calculation. Is tehre any other alternative?

[vs_7]

Hi @Meenasubu ,

try below dax measure

HakenutApplicantsCount = CALCULATE(
    DISTINCTCOUNT('YourTableName'[applicant_id]),
    FILTER(
        ALL('YourTableName'),
        'YourTableName'[stage] = "Hakenut" &&
        NOT EXISTS(
            FILTER(
                'YourTableName',
                'YourTableName'[applicant_id] = EARLIER('YourTableName'[applicant_id]) &&
                'YourTableName'[stage] <> "Hakenut"
            )
        )
    )
)