Forum Discussion

Caz_16's avatar
Caz_16
Helper II
6 years ago
Solved

GROUPBY and CONCATINATEX Error: Function 'GROUPBY' scalar expressions have to be Aggregation

Hello,


I am working with project (time and task) data and am stuck with the following problem. I am getting a DAX error when attempting to concatenate text with CONCATENATEX over my raw data table, the text I want to concatenate is the Task Description, grouped by who's task it is, and by what project.


Link to Google Sheets to show an example of my raw data, and the Matrix (Pivot Table) I am attempting to create Here:
https://drive.google.com/file/d/19DxuY8okp5r6gZ1zbe5Q1NSHeR41usrg/view?usp=sharing

 

My current DAX formula is as follows.

 

 

Table 2 = 
GROUPBy(
    Table1,
    Table1[Project ID],
    Table1[User Name],
    "ConcatX3",
    CONCATENATEX(
        CURRENTGROUP(),
        Table1[Description],
        "|"
    ))

 

When I do this, I get the error: "Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().", but clearly I HAVE the CURRENTGROUP() being called in my CONCATENATEX formula. 

 

Please help!

Thanks.

Need Help
  • Caz_16's avatar
    Caz_16
    6 years ago

    So I actually found my own solution to my problem. In my SUMMARIZE table, I had added in an additional layer of grouping that was throwing off my matrix where I needed to make it a SUMX measure included in the "Expression" part of the SUMMARIZE Function. 

    I created the matrix I desired with the following below. 

     

    Table = 

    SUMMARIZE(
        Table1,
        Table1[User Name],
        Table1[Project ID],
        "ConcatX3",
        CONCATENATEX(
            Table1,
            Table1[Description],
            " | "
            ),
        "Sum of Duration",
        SUMX(
            Table1,
            [Sum of Duration 2]
        )
    )

    I originally had the Measure "Sum of Duration" up in the grouping section, which was causing the ConcatX3 to be concatenating over the sum of the group as well.  

4 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Caz_16 ,

     

    Please create a calculated table like this.

    Table = 
SUMMARIZE(
    Raw,
    Raw[Project ID], Raw[User Name],
    "ConcatX3",
    CONCATENATEX(
        Raw,
        Raw[Description],
        "|"
    )
)

    And you'll get this table.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Caz_16's avatar
      Caz_16
      Helper II
      6 years ago

      v-lionel-msft 

       

      Thanks for your response, but this does not work for me, again for reasons I cannot understand when I put this into a Matrix table it ends up putting all of this into a column that concatenates at only the project level and not the Project then User level.


      Here is an example of what I see this formula result in. 

       

         

       

      ProjectUserDuration

      Description

      Alpha 3.5Notes, Comments, Meetings, Meetings
       

      Joe

      1

      		Notes, Comments, Meetings, Meetings
       Bob.5Notes, Comments, Meetings, Meetings
       Chris2Notes, Comments, Meetings, Meetings

       

      What I am looking for is:

       

      ProjectUserDurationDescription
      Alpha 3.5Notes, Comments, Meetings, Meetings
       

      Joe

      1

      		Notes
       Bob.5Comments, Meetings
       Chris2Meetings


      It is something about applying the Matrix table that throws it off. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Caz_16 ,

         

         

        You cna create a measure

         

        DESC =
IF (
    ISINSCOPE ( 'Table'[User Name] ),
    CONCATENATEX (
        'Table',
        CALCULATE (
            MAX ( 'Table'[Description] )
        ),
        ","
    ),
    CONCATENATEX (
        FILTER (
            ALL (
                'Table'[Project],
                'Table'[User Name],
                'Table'[Description]
            ),
            'Table'[Project]
                = MAX ( 'Table'[Project] )
        ),
        CALCULATE (
            MAX ( 'Table'[Description] )
        ),
        ","
    )
)

         

         

         


        Regards,

        Harsh Nathani


        Appreciate with a Kudos!! (Click the Thumbs Up Button)

        Did I answer your question? Mark my post as a solution!