Forum Discussion
New Table Aggregating Column
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.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?
I'm not quite sure what you mean.
I see you've modified the output example in the question. How does it know whether A is open or closed?
A variation of this I guess if you know a simpler way:
Table =
VAR A =
ADDCOLUMNS (
CALCULATETABLE (
DISTINCT( Table[Parent Work Item Id] ),
NOT Table[Parent Work Item Id] = ""
),
"Cost", CALCULATE ( SUM(Table[Cost] ) )
)VAR B =
SELECTCOLUMNS(Table,
"Area Path",Table[Area Path],
"Category",Table[Type],
"State",Table[State])VAR Result =
NATURALLEFTOUTERJOIN(A,B)Return
ResultBut this doesnt work because there isnt technically a column in Var A to match it on.
It's a column in ADO. Technically its a flat list of items where the Parents also Appear in the Work Item ID column so the state is tied to that. So with the example below, I essentially need to row for items with Parent ID = "" but need to sum all the Work Items that have that "Parent" listed in the Parent column. and remove them from the list. I tried to duplicate the sheet and build a connection but was much more complicated than anticipated so I was hoping to just pull the flat list of rows with the sum of children in a new table.
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