Forum Discussion
New Table Aggregating Column
- 4 years ago
Issue is being caused by an empty string in Parent ID.
Try
SummaryTable = ADDCOLUMNS ( CALCULATETABLE ( DISTINCT( OriginalTable[Parent ID] ), NOT OriginalTable[Parent ID] = "" ), "Cost", CALCULATE ( SUM(OriginalTable[Cost] ) ) )BLANK function (DAX) - DAX | Microsoft Docs states
Some DAX functions treat blank cells somewhat differently from Microsoft Excel. Blanks and empty strings ("") are not always equivalent, but some operations may treat them as such. - 4 years ago
To use more than one column use SUMMARIZE instead.
SummaryTable = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZE( OriginalTable, OriginalTable[Parent ID], OriginalTable[Column 2] ), NOT OriginalTable[Parent ID] == "" ), "Cost", CALCULATE ( SUM(OriginalTable[Cost] ) ) )
I'm actually confused as to why NOT ISBLANK( OriginalTable[Parent ID] ) doesn't work! Can't find any reference as to when BLANK() == "" and when it doesn't.
Issue is being caused by an empty string in Parent ID.
Try
SummaryTable =
ADDCOLUMNS (
CALCULATETABLE (
DISTINCT( OriginalTable[Parent ID] ),
NOT OriginalTable[Parent ID] = ""
),
"Cost", CALCULATE ( SUM(OriginalTable[Cost] ) )
)
BLANK function (DAX) - DAX | Microsoft Docs states
Some DAX functions treat blank cells somewhat differently from Microsoft Excel. Blanks and empty strings ("") are not always equivalent, but some operations may treat them as such.
For example:
| Parent ID | Work Item ID | Cost | State |
| A | Open | ||
| A | B | 10 | Closed |
| A | C | 3 | New |
| A | D | 5 | Open |
| B | Closed | ||
| B | E | 4 | Open |
| B | F | 4 | New |
To this:
| Parent ID | Cost | State |
| A | 18 | Open |
| B | 8 | Closed |