cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors
Users online (815)