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.

7 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    In the Query Editor, append the two tables.  To your visual, drag the Date column from the appended dataset and write this measure

    =SUM(Data[Value])

    Hope this helps.

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

      Sorry, I have to use only DAX function. 

      Because, I use the Direct Query. 
      and I've already done this report.