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.
As soon as I add state for example though it gives me the states of the underlying children. Is there a way to just keep everything at the Parent ID and ignore the children except for the sum of the underlying cost?