Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
8 years ago
Solved

Combining 2 tables and Summarize

Goodday,   Im hoping someone can help me out on this one. I have a table where ALL the incidents are registered and categorized based on 'Soort Schade' and a few extra colums.   What i want to d...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi RvdHeijden,

     

    In this scenario, you can adjust the formula like below. :smileyhappy:

    New Table = 
SUMMARIZE (
    Data,
    Data[Infrastructuur],
    "Civiel (BIS)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (BIS)" ) ),
    "Civiel (Tuin)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (Tuin)" ) )
)

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi RvdHeijden,

 

The formula(DAX) below should also work. :smileyhappy:

New Table =
SUMMARIZE (
    Data,
    Data[Infrastructuur],
    "Number Of Tickets", COUNTROWS ( Data )
)

 

Regards

  • RvdHeijden's avatar
    RvdHeijden
    Icon for Post Prodigy rankPost Prodigy
    8 years ago

    v-ljerr-msft

    it looks like i need to change my question a bit because i wasn't clear.

    in both you formulas you added the colum 'Number of Tickets' but that is not a column.

     

    my table where the 'Infrastructuur' column is in just lists the types, the new table should 'calculate' the number of times a type of Infrastructuur is in the table

    Besides the column 'infrastructure' i would like to add more colums such as 'teamtype' or whatever how do i add this to the formula ?

     

    so basically

     

    infrastructuur        TeamType

    gas                           Civiel (BIS)

    electric                     Civiel (BIS)

    water                        Civiel (Tuin)

    water                        Civiel (BIS)

    water                        Civiel (Tuin)

    electric                     Civiel (Tuin)

     

    the new table should calculate the times 'water' is in the other table to return the result

     

                   Civiel (BIS)          Civiel (Tuin)

    gas                 1

    electric           1                             1

    water              1                             2 

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi RvdHeijden,

       

      In this scenario, you can adjust the formula like below. :smileyhappy:

      New Table = 
SUMMARIZE (
    Data,
    Data[Infrastructuur],
    "Civiel (BIS)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (BIS)" ) ),
    "Civiel (Tuin)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (Tuin)" ) )
)

       

      Regards