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.

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