Forum Discussion

Bruce_Fei's avatar
Bruce_Fei
Regular Visitor
5 years ago
Solved

summarize a table

Hello 

 

I have a  source table like below

objectsprocessmatserial_numberob_dzdatetimedateweek_noMonth
XTA  Process1mat1S118/13/2020 5:53:36 PM8/13/2020 12:00:00 AM2020-W332020-8
XTA  Process1mat1S218/13/2020 5:53:10 PM8/13/2020 12:00:00 AM2020-W332020-8
XTA  Process1mat1S418/11/2020 1:22:17 PM8/11/2020 12:00:00 AM2020-W332020-7
XTA  Process1mat1S518/11/2020 1:21:33 PM8/11/2020 12:00:00 AM2020-W332020-7
XTA  Process1mat2S718/4/2020 1:28:38 PM8/4/2020 12:00:00 AM2020-W322020-8
XTA  Process1mat2S818/4/2020 1:28:06 PM8/4/2020 12:00:00 AM2020-W322020-8
XTA  Process1mat2S1118/3/2020 9:25:30 PM8/3/2020 12:00:00 AM2020-W312020-7
XTA  Process1mat2S1218/3/2020 9:24:34 PM8/3/2020 12:00:00 AM2020-W312020-7
XTA  Process1mat3S1317/4/2020 2:14:03 PM7/4/2020 12:00:00 AM2020-W272020-7
XTA  Process1mat3S1417/4/2020 2:13:30 PM7/4/2020 12:00:00 AM2020-W272020-7
XTA  Process1mat3S1517/4/2020 2:12:50 PM7/4/2020 12:00:00 AM2020-W272020-8
XTA  Process2mat3S1617/4/2020 2:12:12 PM7/4/2020 12:00:00 AM2020-W272020-8

 

I need to create a summrize table like below

matobjectscountMonth
mat1XT22020-8
mat1XT22020-7
mat2XT22020-8
mat2XT22020-7
mat3XT22020-8
mat3XT22020-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.
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Bruce_Fei , Summarize will remove duplicate try like

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Bruce_Fei , Summarize will remove duplicate try like

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

    • Bruce_Fei's avatar
      Bruce_Fei
      Regular Visitor
      5 years ago

      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.