Aggregating BLANKS unexpected behaviour

[adam-coulter]

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.

[v-yuezhe-msft]

@adam-coulter,

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

Regards,
Lydia

[adam-coulter]

I can, but would prefer to not post it publicly. Do you have a Microsoft email address? Please contact me at adam.coulter@accenture.com if you do 🙂

[v-yuezhe-msft]

@adam-coulter,

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

Regards,
Lydia