Forum Discussion

ChoiJunghoon's avatar
ChoiJunghoon
Icon for Helper III rankHelper III
6 years ago
Solved

[DAX] How to append Table A/B

I want to create "NewTable".  I am able to use only DAX code.    Could you help me....   
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi ChoiJunghoon ,

     

    You may create calculated table like DAX below.

     

    Table3 =

var _Table= EXCEPT(VALUES(Table2[Date]),VALUES(Table1[Date]))

return

UNION(SUMMARIZE(Table1,Table1[Date],"Value", SUM(Table1[Value])),

SUMMARIZE(_Table, [Date],"Value", SUM(Table2[Value])) )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Please try this expression for your table

 

New Table =
VAR __Table2 =
    SELECTCOLUMNS ( Table2, "Date", Table2[Date], "Value", Table2[Value] )
VAR __Table1 =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( Table1, Table1[Date] ),
            "@Value", CALCULATE ( SUM ( Table1[Amount] ) )
        ),
        "Date", [Date],
        "Value", [@Value]
    )
RETURN
    UNION ( __Table2, __Table1 )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

  • ChoiJunghoon's avatar
    ChoiJunghoon
    Icon for Helper III rankHelper III
    6 years ago

    Thank you for your answer. 

    But your answer is gap with what i want. 

    A0
    B0
    C0
    D0
    A15
    B15
    D13

     

    I want to remove duplication data..

    I want to create "New Table" 

    A15
    B15
    C0
    D13
    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      My bad.  Forgot that part.  Please try this one.  I didn't put your data into a model so can't confirm myself.

       

       

      New Table =
VAR __Table2 =
    SELECTCOLUMNS ( Table2, "Date", Table2[Date], "Value", Table2[Value] )
VAR __Table1 =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( Table1, Table1[Date] ),
            "@Value", CALCULATE ( SUM ( Table1[Amount] ) )
        ),
        "Date", [Date],
        "Value", [@Value]
    )
VAR __unioned =
    UNION ( __Table2, __Table1 )
RETURN
    SUMMARIZE ( __unioned, [Date], "Value", SUM ( [Value] ) )

       

        

      If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

      Regards,

      Pat

      • ChoiJunghoon's avatar
        ChoiJunghoon
        Icon for Helper III rankHelper III
        6 years ago
        NewTable =
        Var _Table2 =
        SELECTCOLUMNS(Table2,"Date",Table2[Date],"Value",Table2[Value])
        Var _Table1 =
        SELECTCOLUMNS(
        ADDCOLUMNS(SUMMARIZE(Table1,Table1[Date]),
        "@Value",CALCULATE(SUM(Table1[Value]))
        ),"Date",[Date]
        ,"Value",[@Value])

        Var _unioned=UNION(_Table2,_Table1)
        RETURN
        SUMMARIZE(_unioned,[Date],"Value",SUM([Value]))


        sum[Value] is error " Cannot Identify the table that contains [Value] Column."