Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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?
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"
)
)
)
)
User | Count |
---|---|
98 | |
91 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |