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 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.

 

"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
     

     

4 Replies

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

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    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
     

     

  • devanshi's avatar
    devanshi
    Icon for Helper V rankHelper V
    3 years ago

    cobovalues =

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