Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

CONCATENATEX does not work with GROUPBY

I have an invoice table which contains the columns "Client-ID", "Product" and "Price", amongst other columns. I'd like to group this table by client, with a second column "Product combination" that contains a concatenation of all the products per client. Initially, this is the code that I tried:

Clients = 
GROUPBY('Invoices'; 'Invoices'[Client-ID];
"Product combination"; 
CONCATENATEX(CURRENTGROUP(); 'Invoices'[Product code]))

When I use the code above, Power BI throws 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()." Which doesn't seem to make much sense, because I'm using CURRENTGROUP(), which 'Invoices'[Product code] is a part of.

I'm aware that I could use SUMMARIZE (which does work) instead of GROUPBY. Yet I intend to use the code above in a measure, with another GROUPBY to get the number of clients per product table. I have found that GROUPBY tends to be more accurate/intelligent when it comes to context filters, so I'd rather use GROUPBY (if possible). Could anybody tell me why this doesn't work and/or how to fix it? :)

Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    So it looks like CURRENTGROUP simply does not work with CONCATENATEX.  Per the DAX documentation:

    The CURRENTGROUP function takes no arguments and is only supported as the first argument to one of the following aggregation functions: AverageX, CountAX, CountX, GeoMeanX, MaxX, MinX, ProductX, StDevX.S, StDevX.P, SumX, VarX.S, VarX.P.

    It is possible to do what you're attempting via Power Query, pretty easily:

    https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values

     

    Once you create the new table this way, you should be able to run measures against it as normal.

  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

    You can use the below DAX expression 

     

     

    Table = 
ADDCOLUMNS(
    VALUES('Invoices'[Client-ID]);
    "Product combination";  CALCULATE( 
        CONCATENATEX( 
            VALUES( 'Invoices'[Product Code] );
            'Invoices'[Product Code]; "/ "
        ) 
    )
)


    Regards,
    Mariusz

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

     

2 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    So it looks like CURRENTGROUP simply does not work with CONCATENATEX.  Per the DAX documentation:

    The CURRENTGROUP function takes no arguments and is only supported as the first argument to one of the following aggregation functions: AverageX, CountAX, CountX, GeoMeanX, MaxX, MinX, ProductX, StDevX.S, StDevX.P, SumX, VarX.S, VarX.P.

    It is possible to do what you're attempting via Power Query, pretty easily:

    https://stackoverflow.com/questions/44058355/powerquery-how-can-i-concatenate-grouped-values

     

    Once you create the new table this way, you should be able to run measures against it as normal.

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

    You can use the below DAX expression 

     

     

    Table = 
ADDCOLUMNS(
    VALUES('Invoices'[Client-ID]);
    "Product combination";  CALCULATE( 
        CONCATENATEX( 
            VALUES( 'Invoices'[Product Code] );
            'Invoices'[Product Code]; "/ "
        ) 
    )
)


    Regards,
    Mariusz

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