Forum Discussion

Dhrutivyasa-070's avatar
Dhrutivyasa-070
Icon for Helper I rankHelper I
3 years ago
Solved

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

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...
"DAX"
"Need Help"
  • some_bih's avatar
    some_bih
    3 years ago

    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
     

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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