Forum Discussion
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
- AnonymousNot applicable
adam-coulter,
Would you mind sharing me the PBIX file so that I can check?
Regards,
Lydia- adam-coulterRegular Visitor
I can, but would prefer to not post it publicly. Do you have a Microsoft email address? Please contact me at [email protected] if you do :-)
- AnonymousNot applicable
adam-coulter,
You can upload PBIX file to OneDrive, and send me the shared link of the file via Private Message.
Regards,
Lydia