topic Re: Distinct Count with Filters in DAX Commands and Tips

Distinct Count with Filters

kartiklal7 — Wed, 10 Aug 2022 14:52:42 GMT

Hi All,

From the data in the screenshot below I want to calculate the distinctcount of contactid when both Gen 113 and Gen 116 certifications are active for a contact. The answer for this set of data should be 2 but am I getting 0. Obviously I am doing something wrong. Please see my measure below.

Measure =
CALCULATE (
DISTINCTCOUNT ( 'Training Certification'[ContactId] ),
'Training Certification'[CertificationName] = "GEN 116 Software Assurance",
'Training Certification'[CertificationName] = "GEN 113 Mitel Performance Analytics",
'Training Certification'[IsActive] = TRUE
) + 0

Re: Distinct Count with Filters

Anonymous — Wed, 10 Aug 2022 15:17:50 GMT

I could suggest you use the Filter function

CALCULATE ( DISTINCTCOUNT ( 'Training Certification'[ContactId] ), FILTER(ALL(Training Certification[CertificationName]), 'Training Certification'[CertificationName] = "GEN 116 Software Assurance" && 'Training Certification'[CertificationName] = "GEN 113 Mitel Performance Analytics" && 'Training Certification'[IsActive] = TRUE ) )

If it still doesn't work, try to drag the measure to your example table and debug the results

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Re: Distinct Count with Filters

johnt75 — Wed, 10 Aug 2022 15:14:25 GMT

Try

Count gen 113 & 116 = VAR gen113 = CALCULATETABLE ( DISTINCT ( 'Training Certification'[Customer ID] ), 'Training Certification'[CertificationName] = "GEN 113 Mitel Performance Analytics", 'Training Certification'[IsActive] = TRUE ) VAR gen116 = CALCULATETABLE ( DISTINCT ( 'Training Certification'[Customer ID] ), 'Training Certification'[CertificationName] = "GEN 116 Software Assurance", 'Training Certification'[IsActive] = TRUE ) RETURN COUNTROWS ( INTERSECT ( gen113, gen116 ) )

Re: Distinct Count with Filters

kartiklal7 — Wed, 10 Aug 2022 15:27:13 GMT

Thank you. This works perfectly.