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

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

        Lorenzo 

        Thank you. I played a bit in DAX Studio with first two solutions. Yes, one with SUMMARIZE is slower, but not dramatically.

        When have more time will try to compare all three.

Resources