cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Distinct count of applicants based on application stage

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!

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
3 REPLIES 3
Super User

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

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?

Responsive Resident

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors