group by String on Power BI works similar to STRING_AGG on SQL Server

user_14142514 0
Viewed 860

I am working on a Power BI report and using a SQL Server query to return result onto Power BI Desktop like this:

ProductID CustomerID ProductName
P1 C11 Bike
P1 C12 Bike
P2 B21 Mountain Bike
P2 B22 Mountain Bike
P2 B23 Mountain Bike

and I want to group by ProductID and ProductName and then concatenate CustomerID to have result on Power BI report as below:

ProductID CustomerID ProductName
P1 C11, C12 Bike
P2 B21, B22,B23 Mountain Bike

I have tried with Dax fuctions such as GROUPBY, ADDCOLUMNS... ON Power BI but has not helped

dax powerbi
1 Answers

I think, that CONCATENATEX is what you need. I put only ProductID to my output table and Measure:

enter image description here

Measure 3 = CONCATENATE(
CONCATENATEX(Prod,Prod[CustomerID],",") , CONCATENATEX(VALUES(Prod[ProductName]),Prod[ProductName],",")
)
user_143665380
Related