topic Re: Cumulative Count DAX in DAX Commands and Tips

Cumulative Count DAX

NessFlood — Mon, 21 Oct 2024 09:14:09 GMT

I am new Power BI and I need help with a DAX formula.

I would like a forumula that is a cumulative count of "Complete" in the Build Complete FC Status column against the Build Complete FC date columns - thank you 🙂

Re: Cumulative Count DAX

aduguid — Mon, 21 Oct 2024 09:47:04 GMT

Try this measure

CumulativeCompleteCount = CALCULATE( COUNTROWS(TableName), FILTER( ALLSELECTED(TableName[Build Complete FC Date]), TableName[Build Complete FC Date] <= MAX(TableName[Build Complete FC Date]) && TableName[Build Complete FC Status] = "Complete" ) )

Re: Cumulative Count DAX

NessFlood — Mon, 21 Oct 2024 21:38:33 GMT

Thank you, I tried but it didn't like the syntax, see below

Re: Cumulative Count DAX

Anonymous — Tue, 22 Oct 2024 03:06:07 GMT

Thanks for the reply from aduguid , please allow me to provide some information to add:
Hi NessFlood ,

According to the error message, you are using the table + column name 'CBR_REPORT' [BLD110 Build Complete FC] in allselect(), followed by 'CBR_REPORT' [Build Complete FC Status] which is not referenced in the dax equation

You can try the following DAX:

Measure：

CumulativeCompleteCount_Measure = IF( MAX([Build Complete FC Status])="Complete", COUNTX( FILTER(ALL('CBR_REPORT'), 'CBR_REPORT'[Build Complete FC Status]="Complete"&&'CBR_REPORT'[BLD110 Build Complete FC]<=MAX('CBR_REPORT'[BLD110 Build Complete FC])),[Ref ID]))

Caliculated Column：

CumulativeCompleteCount_Column = IF( [Build Complete FC Status]="Complete", COUNTX( FILTER('CBR_REPORT', 'CBR_REPORT'[Build Complete FC Status]="Complete"&&'CBR_REPORT'[BLD110 Build Complete FC]<=EARLIER('CBR_REPORT'[BLD110 Build Complete FC])),[Ref ID]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cumulative Count DAX

NessFlood — Wed, 23 Oct 2024 04:00:42 GMT

Hi there, the formulas works for the entire table but not when I filter by PSN Region.

Without filter

But with filter for Canterbury Pilot it should only total 13 but it totals 17?

thanks again for any help 🙂

Re: Cumulative Count DAX

Anonymous — Thu, 24 Oct 2024 06:40:50 GMT

Hi NessFlood ,

Are you referring to Meausure in the Total row not 13?You can use IF+ ISINSCOPE() to determine if it is the total row, customize the rule .

Here are the steps you can follow：

1. Create measure.

correct count = IF( ISINSCOPE('CBR_REPORT'[Ref ID]),[CumulativeCompleteCount_Measure],COUNTX(FILTER(ALLSELECTED('CBR_REPORT'),[CumulativeCompleteCount_Measure]<>BLANK()),[Ref ID]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Cumulative Count DAX

NessFlood — Thu, 24 Oct 2024 20:17:22 GMT

Hi Liu

Your latest formula in purple does calculate in the column but not in the graph. I'm wanting a cumulative graph like the one on the left, which does work with the old formula except when I filter it by region. It should only return 13 completed build sites for Canterbury - pilot but keeps saying 17 and no cumulative line using the new purple formula. Again thanks for any help 😀

Re: Cumulative Count DAX

Anonymous — Fri, 25 Oct 2024 08:37:01 GMT

Hi NessFlood ,

You can try the following DAX: Change ALL() to ALLSELECTED(), which can adjust with the changes in the filter.

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

ALLSELECTED function (DAX) - DAX | Microsoft Learn

CumulativeCompleteCount_Measure = COUNTX( FILTER(ALLSELECTED('CBR_REPORT'), 'CBR_REPORT'[Build Complete FC Status]="Complete"&&'CBR_REPORT'[BLD110 Build Complete FC]<=MAX('CBR_REPORT'[BLD110 Build Complete FC])),[Ref ID])

If dax gets results that don't match your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cumulative Count DAX

NessFlood — Tue, 05 Nov 2024 00:33:23 GMT

thank you - I changed to ALLSELECTED and realised that the visuals need to be drilled down also so I can see the full result 🙂