Count Distinct Name where a sum condition is met

[Anonymous]

Hello I have yet to find a distinct count of "Project Name" where the sum of the "Actual Hrs." and "Planned Hrs" are > 0.

It has to be distinct count and sum because some projects have instances where there is 0 Actual Hrs. 

I am trying to make this work:

  CALCULATE(DISTINCTCOUNT('Project Hours'[Project Name]),Sum('Project Hours'[Actual Hrs.])>0,Sum('Project Hours'[Planned Hrs.])<.01)

,but Dax does not allow sum within calculate function (I also tried SUMX and it also is not allowed)

[AlB · Accepted solution]

Hi @Anonymous 

COUNTROWS (
    FILTER (
        DISTINCT ( 'Project Hours'[Project Name] ),
        CALCULATE ( SUM ( 'Project Hours'[Actual Hrs.] ) > 0 )
            && CALCULATE ( SUM ( 'Project Hours'[Planned Hrs.] ) < .01 )
    )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

You're amazing!!!! Thank you so much!!!! 

Ok, now I understand. You have to do calculate for sum and condition and then in outter layer do a filter for the calculate (filter on distinct count of "Name"). Also each sum condition has to be in its own calculate function 

[AlB]

@Anonymous 

Keeping the approach you were trying, you could do stg like:

CALCULATE (
    DISTINCTCOUNT ( 'Project Hours'[Project Name] ),
    FILTER (
        DISTINCT ( 'Project Hours'[Project Name] ),
        CALCULATE ( SUM ( 'Project Hours'[Actual Hrs.] ) > 0 )
            && CALCULATE ( SUM ( 'Project Hours'[Planned Hrs.] ) < .01 )
    )
)

but it'd be a bit redundant

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.