Combining 2 tables and Summarize

[RvdHeijden]

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 do is make a new table and a colum that lists all the unique values of the column 'Infrastructuur' and then counts all the times that value is in that column

So basically i now have:

Damage                 Infrastructuur

1.                             Electric

2.                             Gas

3.                             Water

4.                             Water

and i want a new table that combines that data into:

Infrastructuur              Number of tickets

Electric                            1

Gas                                  1

Water                              2

then i want to calculate other stuff but that is for later 🙂 

[v-ljerr-msft]

Hi @RvdHeijden,

The formula(DAX) below should also work. 

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

Regards

[RvdHeijden]

@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 · Accepted solution]

Hi @RvdHeijden,

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

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

[Datatouille]

So basically you want to summarize this table :

Damage                 Infrastructuur

1.                             Electric

2.                             Gas

3.                             Water

4.                             Water

Let's assume the table above is called 'Data'.

If the 'Data' table comes from a DAX query, then it won't be available in Power Query and you need to use a DAX method:

Use this DAX query to create your desired table ('Modeling' tab and then 'New Table' in Power BI Desktop):

New Table = ADDCOLUMNS( SUMMARIZE(Data;Data[Infrastructuur]);"Number Of Tickets"; CALCULATE(COUNTROWS(Data) ))

if your 'Data' table is available in Power Query, you can use a the 'Group by' feature and chose count rows in the 'operation' section.