topic Re: Union table without inheriting sum in DAX Commands and Tips

Union table without inheriting sum

Yaro — Fri, 31 Mar 2023 06:25:12 GMT

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

Re: Union table without inheriting sum

FreemanZ — Fri, 31 Mar 2023 07:00:15 GMT

hi Yaro

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

Re: Union table without inheriting sum

Yaro — Fri, 31 Mar 2023 07:27:59 GMT

hi FreemanZ

I have InitialTable for Team1 which looks like this:

Member	Sales	Revenue
Member1	300	200
Member2	200	100

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

Team	Sales	Revenue
Team1	500	300
Team2	500	300

Thank you.

Re: Union table without inheriting sum

Yaro — Fri, 31 Mar 2023 08:33:50 GMT

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

Re: Union table without inheriting sum

tamerj1 — Fri, 31 Mar 2023 09:36:01 GMT

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 )

Re: Union table without inheriting sum

Yaro — Fri, 31 Mar 2023 11:17:14 GMT

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

Re: Union table without inheriting sum

tamerj1 — Fri, 31 Mar 2023 11:25:19 GMT

Yaro

group them based on what?