CODEWITHSUNDEEP
powerbidax
Ceci Semble Absurde.
Ceci Semble Absurde.

Reputation: 530

How to concatenate multiple rows from an other table in DAX?

I'm new to Power BI and I need to concatenate in a new column Suppliers_List all the suppliers for a given item.

I have a table Orders where I need to create the column :

Order_Number Customer_ID Item_Number
0001         1           1
0002         2           1
0003         1           3
0004         1           4
0005         3           2
0006         3           2
0007         4           2

A table Items where a same product can have multiple supplier:

Unique_ID Item_Number Supplier_ID Item_Name
1         1           1           "wheel"
2         1           2           "wheel"
3         2           1           "door"
4         2           2           "door"
5         3           1           "motor"
6         3           3           "motor"
7         4           4           "dashboard"

And a table Suppliers :

Supplier_ID Supplier_Name
1           "Metal&co"
2           "SmithSE"
3           "BetterMotors"
4           "TinyCircuits"

I'm trying to get the following result in the table Orders where I store the suppliers name, separated by a comma :

Order_Number Customer_ID Item_Number Suppliers_List
0001         1           1           "Metal&co, SmithSE"
0002         2           1           "Metal&co, SmithSE"
0003         1           3           "Metal&co, BetterMotors"
0004         1           4           "TinyCircuits"
0005         3           2           "Metal&co, SmithSE"
0006         3           2           "Metal&co, SmithSE"
0007         4           2           "Metal&co, SmithSE"

I know that the dax expression for my column Suppliers_List probably deals with Concatenate and GroupBy functions but I don't have the knowledge to get it.

The solution I'm thinking of is to group the field Item_Number from the table Items and concatenate the field Supplier_Name from table Suppliers.

my not working try :

Suppliers_list = CONCATENATE(GROUPBY('Items';Suppliers[Supplier_Name]) ; ", ")

Can someone help ?

UPDATE

I finaly found something way far from my first guess.

See my anwser.

Upvotes: 1

Views: 9051

Answers (1)

Ceci Semble Absurde.
Ceci Semble Absurde.

Reputation: 530

I finaly found something way far from my first guess.

First, you need to create a new column Supplier_Name in the table Items where you will store value in the field Supplier_Name from Suppliers. I've achieve it like so :

Supplier_Name = CONCATENATEX (
    FILTER ( Suppliers; Items[Supplier_ID] = Suppliers[Supplier_ID] );
    Suppliers[Supplier_Name];
    ""
)

Now that we have our Supplier_Name in our table Items, we can compute the suppliers list by grouping every product and concatenate the supplier name in a new column Suppliers_List.

Suppliers_List = CONCATENATEX (
    FILTER ( Items; Items[Item_Number] = EARLIER ( Items[Item_Number] ) );
    Items[Supplier_Name];
    ", "
)

You can now import it in a visualization element such as a table.

Hopping that will help the community

Upvotes: 3

Related Questions