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

[Dhrutivyasa-070]

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.

[devanshi]

cobovalues =

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

[some_bih · Accepted solution]

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

[Dhrutivyasa-070]

Thanks for the solution, it worked for me!

[tamerj1]

Hi @Dhrutivyasa-070 

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", "-" )