Forum Discussion

Lorenzo's avatar
Lorenzo
Silver Contributor
Jul 15, 2023
Solved

DAX: Count items if a condition is met

Hi

 

Relates to Counting with multiple criteria. Looked at a PowerPivot option and whilst it appears to work it seems overcomplicated to me and probably not efficient

 

 

In Table1 we have Names with potential duplicate Certifications. In the PivotTable we want to count Names by Certification only if those Names have more than 1 certification (measure Avail. Resources above). Not sure the Grand Total makes sense but let's assume it does...


Will appreciate any better/more efficient DAX measure(s) that does it
Thanks

(Excel 365 v2306 / Windows)

BI & Data Analysis
excel
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 15, 2023

    Lorenzo 

    Not sure about efficiency, as variant

    Available:=COUNTROWS (
    FILTER (
        VALUES ( Table1[Name] ),
        CALCULATE ( COUNTROWS ( DISTINCT ( Table1 ) ), ALL ( Table1[Certification] ) ) > 1
    )
)

Total Available:=IF (
    HASONEVALUE ( Table1[Certification] ),
    [Available],
    SUMX ( VALUES ( Table1[Certification] ), [Available] )
)

    which gives

     

6 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 15, 2023

    Lorenzo 

    Not sure about efficiency, as variant

    Available:=COUNTROWS (
    FILTER (
        VALUES ( Table1[Name] ),
        CALCULATE ( COUNTROWS ( DISTINCT ( Table1 ) ), ALL ( Table1[Certification] ) ) > 1
    )
)

Total Available:=IF (
    HASONEVALUE ( Table1[Certification] ),
    [Available],
    SUMX ( VALUES ( Table1[Certification] ), [Available] )
)

    which gives

     

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jul 16, 2023

      SergeiBaklan 

      Even if I shorten mine as:

      =VAR FilteredNames =
    FILTER (
        SUMMARIZE (
            ALL ( Table1 ),
            Table1[Name],
            "DISTINCT_CERTIF", DISTINCTCOUNT ( Table1[Certification] )
        ),
        [DISTINCT_CERTIF] > 1
    )
RETURN
    IF (
        ISFILTERED ( Table1[Certification] ),
        CALCULATE ( DISTINCTCOUNT ( Table1[Name] ), FilteredNames ),
        SUMX ( FilteredNames, [DISTINCT_CERTIF] )
    )

      your approach appears more straightforward

       

      If no other suggestion in the next couple of days I'll mark your option

      Thanks much

Resources