Distinct Count with Filters

[kartiklal7]

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

[johnt75 · Accepted solution]

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

[kartiklal7]

Thank you. This works perfectly. 

[jcalheir]

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! 🙂