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 IDWork Item IDCost
 A 
AB10
AC3
AD5
BE4
BF4

 

I am trying to create a new table using SELECTCOLUMNS where I aggregate the children to a single a row, however it keeps saying I have 3 and 2 underlying data points which keeps messing up my joins. Anyone know how I can get it to the table below without doing the merge in powerquery and aggregation since it takes forever on a large dataset?

 

Parent IDCost
A18
B8
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. 

13 Replies

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

      • 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

      For example:

      Parent IDWork Item IDCostState
       A Open
      AB10Closed
      AC3New
      AD5Open
       B Closed
      BE4Open
      BF4New

       

      To this:

      Parent IDCostState
      A18Open
      B8Closed
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    You can create a simple table visual by dragging the Parent column and writing this measure

    Measure = sum(Data[Cost])

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      A lot more complicated than that unfortunately. I have 4 master data management tables that join on custom keys with values such as # of points.  When I do a measure based on the joined value, it multiplies it by the number of child tasks and the totals dont add up, and as they say, the best way to stop that from happening is to bring them in as columns, which is why I was trying to create a single table with all of the costs tied to the parents and then go from there.