topic Need to Create New Column using Combination of multiple row with same Code in DAX Commands and Tips

Need to Create New Column using Combination of multiple row with same Code

Dhrutivyasa-070 — Tue, 30 May 2023 09:40:52 GMT

Hello Users,

I need to create one custom column using Power Query / DAX

as per below images, i have one column conatins code against each item Name

in result i need one column that conatins only single line for each item (IF both item having same code)

please find below images for refrence. i need output as per shown in output snapshot.

Thanks in Advance.

tamerj1 — Tue, 30 May 2023 11:16:30 GMT

please try the following measures

Item (Combo) =
CONCATENATEX ( VALUES ( 'Table'[Item] ), 'Table'[Item], ", " )

Total Qty =
SUM ( 'Table'[Qty] )

Remark =
IF ( COUNTROWS ( VALUES ( 'Table'[Item] ) ) > 1, "Combo", "-" )

some_bih — Tue, 30 May 2023 11:22:04 GMT

Hi Dhrutivyasa-070 you can create NEW table in Power BI as following:

TableCombo = ADDCOLUMNS (

VALUES ( Sheet7[Code] ),

"Qty", [M_Qty],

"Item (Combo)",

CONCATENATEX (

CALCULATETABLE ( VALUES ( Sheet7[Item] ) ),

Sheet7[Item],

" + ",

Sheet7[Item],

ASC

)

TableCombo - is name of NEW table

Sheet7 you should adjust to your table

M_Qty = SUM(Sheet7[Qty]) - this is measure which you should create so table above is working

Original reference to this solution: https://dax.guide/concatenatex/

Output should be like on picture below

I hope this help

devanshi — Tue, 30 May 2023 13:10:41 GMT

cobovalues =

CALCULATE(DISTINCT(code),CONCATENATE([item],"+",[item]),SUM(Qty),IF(COUNTROWS(DISTINCT(code),"Combo","-")))

Dhrutivyasa-070 — Wed, 31 May 2023 09:34:04 GMT

Thanks for the solution, it worked for me!