Forum Discussion

Yaro's avatar
Yaro
Frequent Visitor
3 years ago

Union table without inheriting sum

Hi.
I need help please.
Need to join 2 tables in DAX, so code goes like this:

FirstTable = ADDCOLUMNS(
"Team","Team1",
"Sales", sum(InitialTable[Sales],
"Revenue", sum(InitialTable[Revenue])

SecondTable = ADDCOLUMNS(
"Team","Team1",
"Sales", 100,
"Revenue", 20)

return union(FirstTable,SecondTable)


So for example First table is:
Sales Revenue
Team1 500 300

But then it is overwrites SecondTable inputs and I get at the end:
           Sales Revenue
Team1 500 300
Team2 500 300

It supposed to be:

            Sales Revenue
Team1 500 300
Team2 100 20

Somehow measures from FirstTable overwrite values from SecondTable.

I need not to mix them.

What I am doing wrong?

Thank you

6 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Yaro 

    UNION is not supposed to overwrite values. Could you paste some sample data?

    • Yaro's avatar
      Yaro
      Frequent Visitor
      3 years ago

      hi FreemanZ 

      I have InitialTable for Team1 which looks like this:

      MemberSalesRevenue
      Member1300200
      Member2200100


      So I write code summarizing that table.
      var FirstTable=ADDCOLUMN ("Team","Team1","Sales",sum(InitialTable[Sales]),sum(InitialTable[Revenue]))
      then I want to add Team2 just with dummy numbers
      var SecondTable={("Team2",0,0)}

      return union(FirstTable,SecondTable) gives me that result

      TeamSalesRevenue
      Team1500300
      Team2500300


      Thank you.

  • Yaro's avatar
    Yaro
    Frequent Visitor
    3 years ago

    I think the answer is to Union tables on row level and then summarize the final output

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    HI Yaro 
    Please try

    =
VAR T1 =
    ROW (
        "Team", "Team1",
        "Sales", SUM ( InitialTable[Sales] ),
        "Revenue", SUM ( InitialTable[Revenue] )
    )
VAR T2 =
    ROW ( "Team", "Team1", "Sales", 100, "Revenue", 20 )
RETURN
    UNION ( T1, T2 )
    • Yaro's avatar
      Yaro
      Frequent Visitor
      3 years ago

      hi @Tameer
      thank you for your reply
      Row will return only one row of data?
      The example I have given is just to showcase the situation
      in fact I need to group those tables by many teams