summarize a table

[Bruce_Fei]

Hello 

I have a  source table like below

objects	process	mat	serial_number	ob_dz	datetime	date	week_no	Month
XTA	Process1	mat1	S1	1	8/13/2020 5:53:36 PM	8/13/2020 12:00:00 AM	2020-W33	2020-8
XTA	Process1	mat1	S2	1	8/13/2020 5:53:10 PM	8/13/2020 12:00:00 AM	2020-W33	2020-8
XTA	Process1	mat1	S4	1	8/11/2020 1:22:17 PM	8/11/2020 12:00:00 AM	2020-W33	2020-7
XTA	Process1	mat1	S5	1	8/11/2020 1:21:33 PM	8/11/2020 12:00:00 AM	2020-W33	2020-7
XTA	Process1	mat2	S7	1	8/4/2020 1:28:38 PM	8/4/2020 12:00:00 AM	2020-W32	2020-8
XTA	Process1	mat2	S8	1	8/4/2020 1:28:06 PM	8/4/2020 12:00:00 AM	2020-W32	2020-8
XTA	Process1	mat2	S11	1	8/3/2020 9:25:30 PM	8/3/2020 12:00:00 AM	2020-W31	2020-7
XTA	Process1	mat2	S12	1	8/3/2020 9:24:34 PM	8/3/2020 12:00:00 AM	2020-W31	2020-7
XTA	Process1	mat3	S13	1	7/4/2020 2:14:03 PM	7/4/2020 12:00:00 AM	2020-W27	2020-7
XTA	Process1	mat3	S14	1	7/4/2020 2:13:30 PM	7/4/2020 12:00:00 AM	2020-W27	2020-7
XTA	Process1	mat3	S15	1	7/4/2020 2:12:50 PM	7/4/2020 12:00:00 AM	2020-W27	2020-8
XTA	Process2	mat3	S16	1	7/4/2020 2:12:12 PM	7/4/2020 12:00:00 AM	2020-W27	2020-8

I need to create a summrize table like below

mat	objects	count	Month
mat1	XT	2	2020-8
mat1	XT	2	2020-7
mat2	XT	2	2020-8
mat2	XT	2	2020-7
mat3	XT	2	2020-8
mat3	XT	2	2020-7

the column count is count the mat number. 

i use 

表 = ADDCOLUMNS(SUMMARIZE(Sheet1,Sheet1[objects],Sheet1[mat],Sheet1[Month]),"totalBuild",COUNT(Sheet1[mat]))

but i get the count is the total count, not base on the month and mat. how should i adjust?

thanks.

[amitchandak · Accepted solution]

@Bruce_Fei , Summarize will remove duplicate try like

SUMMARIZE(Sheet1,Sheet1[objects],Sheet1[Month],Sheet1[mat],"totalBuild",COUNT(Sheet1[mat]))

[Bruce_Fei]

@amitchandak thanks for your support.

i get the summarize table.

could you please help me another question?

now i get two tables base on your solution, i need append these two table to one table. the different column is "totalBuild" and "totalfail". i can't direct append via query editor. how to write the DAX?

thanks.