topic Re: Count Distinct Name where a sum condition is met in DAX Commands and Tips

Count Distinct Name where a sum condition is met

Anonymous — Fri, 19 Feb 2021 20:30:27 GMT

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)

Re: Count Distinct Name where a sum condition is met

AlB — Fri, 19 Feb 2021 21:10:00 GMT

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.

Re: Count Distinct Name where a sum condition is met

Anonymous — Fri, 19 Feb 2021 21:25:46 GMT

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

Re: Count Distinct Name where a sum condition is met

AlB — Fri, 19 Feb 2021 22:02:06 GMT

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.