Forum Discussion

NessFlood's avatar
NessFlood
Icon for Helper I rankHelper I
1 year ago
Solved

Cumulative Count DAX

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 colum...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

  • NessFlood's avatar
    NessFlood
    Icon for Helper I rankHelper I
    1 year ago

    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 🙂