Forum Discussion

adam-coulter's avatar
adam-coulter
Regular Visitor
9 years ago

Aggregating BLANKS unexpected behaviour

I have a problem with my aggregations when some fields are undefined or blank. In the below diagram I've shown four different matrix visualisations each with different measures.

 

  • Iteration Totals - This is just raw data with a basic count of the rows. It selects the data from the max date in the iteration date range (the last day of the iteration).
    • BasicCount = CALCULATE(COUNT(WorkItemDaily[WorkItemId]), FILTER(ALL(WorkItemDaily[DateSK], WorkItemDaily[Date]), MAX(WorkItemDaily[Date]) = WorkItemDaily[Date]))
  • Status Aggregate - This sums the row for all columns => current row field, example is in red showing the aggregation working correctly. The example in blue is showing the aggregation failing because it contains BLANK values. 
    • StatusAggregate = CALCULATE(COUNT(WorkItemDaily[WorkItemId]), FILTER(ALL(WorkItemDaily[ColumnState],WorkItemDaily[Date],WorkItemDaily[DateSK],WorkItemDaily[Week],WorkItemDaily[Iteration]), AND(MIN(WorkItemDaily[ColumnState]) <= WorkItemDaily[ColumnState], MAX(WorkItemDaily[Date]) = WorkItemDaily[Date])))
  • Status Aggregate Previous - This sums the row for all column => previous row field. This is the same as status aggregate but has a row offset.
    • StatusAggregatePrevious = CALCULATE(COUNT(WorkItemDaily[WorkItemId]), FILTER(ALL(WorkItemDaily[ColumnState], WorkItemDaily[Date], WorkItemDaily[DateSK], WorkItemDaily[Week], WorkItemDaily[Iteration]), AND(MIN(WorkItemDaily[ColumnState]) <= WorkItemDaily[ColumnState], MIN(WorkItemDaily[Date]) = (WorkItemDaily[Date]+1))))
  • Iteration Net Progress - This simply diffs the two status aggregates. Example is shown in purple.
    • Difference = [StatusAggregate] - [StatusAggregatePrevious]

 

I believe the issue is introduced with my StatusAggregate measures not dealing with BLANKS as zeros. I could handle this by making them consume my BasicCount measure which I can wrap in a BLANK check such as IF(ISBLANK([BasicCount],0,[BasicCount])) which will then treat the blanks as zero. Problem is I'm not sure how to make them use the BasicCount measure, because I need to expand the state (column) dimention again to calculate them.

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    adam-coulter,

    Would you mind sharing me the PBIX file so that I can check?

    Regards,
    Lydia

      • Anonymous's avatar
        Anonymous
        Not applicable

        adam-coulter,

        You can upload PBIX file to OneDrive, and send me the shared link of the file via Private Message.

        Regards,
        Lydia