topic Re: Make rows in new table based on column values in existing table in DAX Commands and Tips

Make rows in new table based on column values in existing table

Anonymous — Wed, 10 Apr 2024 13:06:05 GMT

I have this table definition in Power BI:

CustomerProductCodes = DISTINCT(

SELECTCOLUMNS(
CALCULATETABLE('CustomerProduct')
,"CustomerID"
,[CustomerID]
,"ProductCodes"
,if([Toys]="T","T,","")&if([Shoes]="S","S,","")&if([Food]="F","F,","")&if([Dresses]="D","D,","")
)
)

It will be like this:

CustomerID	ProductCodes
23	T,F,D,
44	F,D,
87	S,F,D

I don't want the codes in the same column but each code on it's own row like this:

CustomerID	ProductCodes
23	T
23	F
23	D
44	F
44	D
87	S
87	F
87	D

How do I do this in defining the table?

Re: Make rows in new table based on column values in existing table

tamerj1 — Wed, 10 Apr 2024 16:25:13 GMT

Hi Anonymous

please try

CustomerProductCodes =
DISTINCT (
GENERATE (
SUMMARIZE (
CALCULATETABLE ( 'CustomerProduct' ),
'CustomerProduct'[CustomerID],
'CustomerProduct'[Toys],
'CustomerProduct'[Shoes],
'CustomerProduct'[Food],
'CustomerProduct'[Dresses]
),
UNION (
SELECTCOLUMNS (
{ 'CustomerProduct'[Toys] },
"ProductCodes", IF ( [Toys] = "T", "T" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Shoes] },
"ProductCodes", IF ( [Shoes] = "S", "S" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Food] },
"ProductCodes", IF ( [Food] = "F", "F" )
),
SELECTCOLUMNS (
{ 'CustomerProduct'[Dresses] },
"ProductCodes", IF ( [Dresses] = "D", "D" )
)
)
)
)

Re: Make rows in new table based on column values in existing table

Anonymous — Thu, 11 Apr 2024 10:31:32 GMT

I forgot that in CustomerProduct I have a column with YearMonth. The Customers buy several things in different months.

It will be like this when I add YearMonth in spec:

YearMonth	CustomerID	ProductCodes
202401	23	T,F,D,
202401	44	F,D,
202401	87	S,F,D,
202402	44	D,T,
202402	87	F,D,

I want YearMonth in the CustomerProductCodes table, too. How do I do that?

Re: Make rows in new table based on column values in existing table

Anonymous — Mon, 15 Apr 2024 07:15:46 GMT

Hi tamerj1 ,thanks for the quick reply, I'll add further.

Hi Anonymous ,

Regarding your question, the table data are as follows.

Try to modify your DAX expression.

Table = DISTINCT( SELECTCOLUMNS( CALCULATETABLE('CustomerProduct'), "YearMonth",[YearMonth], "CustomerID",[CusTomerID], "ProductCodes",if([Toys]="T","T,","")&if([Shoes]="S","S,","")&if([Food]="F","F,","")&if([Dresses]="D","D,","") ) )

You can also use Power Query if you are trying to split.Copy the table data to Excel, use 'Get data' and click 'Transform data'.

Details can be found in the following articles.

Split columns by delimiter - Power Query | Microsoft Learn

Use the following DAX expression to create a Table

Table = FILTER('Sheet3','Sheet3'[ProductCodes] <> BLANK())

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