Forum Discussion

kartiklal7's avatar
kartiklal7
Frequent Visitor
4 years ago
Solved

Distinct Count with Filters

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...
Need Help
Need Help DAX
  • johnt75's avatar
    johnt75
    4 years ago

    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 ) )
johnt75's avatar
johnt75
Super User
4 years ago

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's avatar
    kartiklal7
    Frequent Visitor
    4 years ago

    Thank you. This works perfectly.