Union between two SUMMARIZE -> RETURN SELECTCOLUMNS (SUM) ???

[lboldrino]

Hi. Hier is my dax for my SUMMARIZE -table: 

Customer_TotalTickets =
VAR mytable =
Union
( filter(filter(filter(filter(SUMMARIZE (
Tickets,Tickets[Close Time].[Year],Tickets[Closed Month],Tickets[Closure],Tickets[Time Spend AG],Tickets[IM Time Spend RecordAG],Tickets[Accounting Information],Tickets[BW Comment]
,"TicketsAnzahl", DISTINCTCOUNT(Tickets[Incident ID])),Tickets[Closure]="Normal"),Tickets[IM Time Spend RecordAG] in {"ICS420AS - Customer SAP BW"}),Tickets[Time Spend AG] in {"ICS420AS - Customer SAP BW"
}),Tickets[Accounting Information]="Standard")
,
filter(filter(filter(filter(SUMMARIZE (
Tickets,Tickets[Close Time].[Year],Tickets[Closed Month],Tickets[Closure],Tickets[Time Spend AG],Tickets[IM Time Spend RecordAG],Tickets[Accounting Information],Tickets[FI Comment]
, "TicketsAnzahl",DISTINCTCOUNT(Tickets[Incident ID])),Tickets[Closure]="Normal"),Tickets[IM Time Spend RecordAG] in {"ICS420AS - Customer SAP FI/CO"
}),Tickets[Time Spend AG] in {
"ICS420AS - Customer SAP FI/CO"
}),Tickets[Accounting Information]="Standard")

)
RETURN
SELECTCOLUMNS (
mytable,
"ClosedYear", [Close Time].[Year],
"Closed Month", [Closed Month],
"IM Time Spend RecordAG", [IM Time Spend RecordAG]
,"Time Spend AG",[Time Spend AG]
,"TotalTickets",[TicketsAnzahl]
,"Comment",[BW Comment]
) )

The Result :

Closed Month	TotalPreis	ClosedYear	TotalTickets	IM Time Spend RecordAG	Time Spend AG	Comment
2019.01		2019	16	customer SAP FI/CO	FI/CO	FI + Finance
2019.01	50811,3	2019	306	customer SAP FI/CO	FI/CO	FI
2019.01		2019	14	customer BW	BW	BW +FI
2019.01		2019	92	customer BW	BW	BW

But i will to calcute totaltickets over IM Time Spend RecordAG

the result must to be: 

Closed Month	TotalPreis	ClosedYear	TotalTickets	IM Time Spend RecordAG	Time Spend AG	Comment
2019.01		2019	306 + 16	customer SAP FI/CO	FI/CO	FI + Finance
2019.01		2019	92 + 14	customer BW	BW	BW +FI

any Idea???

ThnX fpr Your Answers 🙂

[dedelman_clng]

Hi @lboldrino -

UNION will not sum anything. Also, you have 2 different values for [BW Comment], so you're going to get a row for each in your SUMMARIZE result.

By putting a MAX on [BW Comment] and [FI Comment] you should get the summary you want.

Also note - doing that many nested filters is going to be painfully slow as your dataset gets larger.  Look into CALCULATETABLE or us the AND() or && construct with a single FILTER command. You also may find that doing the FILTER result as the first argument in SUMMARIZE works even better.

var myTab2 =           
UNION (
SUMMARIZE (
 FILTER (Tickets,
   Tickets[Closure] = "Normal" &&
   Tickets[IM Time Spend RecordAG] IN { "ICS420AS - Customer SAP FI/CO" } &&
   Tickets[Time Spend AG] IN { "ICS420AS - Customer SAP FI/CO" } &&
   Tickets[Accounting Information] = "Standard"), 
 Tickets[Close Time].[Year],
 Tickets[Closed Month],
 Tickets[Closure],
 Tickets[Time Spend AG],
 Tickets[IM Time Spend RecordAG],
 Tickets[Accounting Information],
 "Comment", MAX(Tickets[FI Comment]),
 "TicketsAnzahl", DISTINCTCOUNT ( Tickets[Incident ID] )
), 
etc

Hope this helps

David