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

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

3 Replies

  • 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 ) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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