CONCATENATEX does not work with GROUPBY

[Anonymous]

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? 🙂

[Cmcmahan · Accepted solution]

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 · Accepted solution]

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.

[Cmcmahan · Accepted solution]

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.