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

     

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

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

      Lorenzo , they are not too far

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

        Reading https://www.sqlbi.com/articles/introducing-summarizecolumns/ and https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/ it would appear SUMMARIZE - as initially posted - could cause performance issues with big table(s). An alternative with https://www.sqlbi.com/blog/marco/2016/02/15/groupby-vs-summarize-in-dax-powerbi-powerpivot/ could be:

         

        =VAR FilteredNames =
    FILTER (
        GROUPBY (
            DISTINCT ( ALL ( Table1 ) ),
            Table1[Name],
            "CERTIFS", COUNTAX ( CURRENTGROUP (), Table1[Certification] )
        ),
        [CERTIFS] > 1
    )
RETURN
    IF (
        ISFILTERED ( Table1[Certification] ),
        CALCULATE ( DISTINCTCOUNT ( Table1[Name] ), FilteredNames ),
        SUMX ( FilteredNames, [CERTIFS] )
    )


        though, it remains more complex than SergeiBaklan's option => Marked as solution

Resources