Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

New Table Aggregating Column

Hi All,   Should be an easy question but I keep getting an error with the summarize feature. I have the following table that has a parent child relationship.  Parent ID Work Item ID Cost ...
Need Help
  • bcdobbs's avatar
    bcdobbs
    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.

  • bcdobbs's avatar
    bcdobbs
    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. 

bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

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?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    But this doesnt work because there isnt technically a column in Var A to match it on.

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Will give it some thought!

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    This does what you're asking for I think. Effectively creates a single row for each exisiting combination but adds up Cost based just on Parent Id.

    SummaryTable = 
ADDCOLUMNS (
    CALCULATETABLE (
        SUMMARIZE( 
            OriginalTable,
            OriginalTable[Parent ID],
            OriginalTable[State],
            OriginalTable[Area Path],
            OriginalTable[Category] ),
        NOT OriginalTable[Parent ID] = ""
    ),
    "Cost", CALCULATE ( 
                SUM( OriginalTable[Cost] ),
                ALLEXCEPT ( OriginalTable, OriginalTable[Parent Id] )
            )
)

     

    Not sure what your next steps are with this but you could avoid the calculated table all together and just write a measure to return that cost based on current visual:

    Parent Id Cost = 
    CALCULATE ( 
                SUM( OriginalTable[Cost] ),
                REMOVEFILTERS( OriginalTable ),
                VALUES (OriginalTable[Parent Id] )
            )

    Used in a table visual:

    (Note I'd put dummy numeric values in Area Path and Category so had to make sure they were set to not summarise.)