[DAX] How to append Table A/B

[ChoiJunghoon]

I want to create "NewTable". 

I am able to use only DAX code. 

Could you help me.... 

[v-xicai · Accepted solution]

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.

[Ashish_Mathur]

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]

Sorry, I have to use only DAX function. 

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

[mahoneypat]

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]

Thank you for your answer. 

But your answer is gap with what i want. 

A	0
B	0
C	0
D	0
A	15
B	15
D	13

I want to remove duplication data..

I want to create "New Table" 

A	15
B	15
C	0
D	13

[mahoneypat]

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]

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