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.
How would I write this if I also need to select additional columns associated to the parent such as Area Path? This is ADO if you weren't aware.
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.
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
Result
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