Power BI: Count rows of a table based on a criteria of another table

Question

I have a table resuming the list of apps by month and year like this:

Table 1  
App | Month | Year  
A   |  1    |  2019  
B   |  1    |  2019  
C   |  1    |  2019  
D   |  1    |  2019  
E   |  1    |  2019  
...  
etc

And I have a table that contains the info of all the tickets of the company, including the App and the Month and Year, like this:

Table 2  
IDTicket | App | Month | Year  
44424    | B   | 1     | 2019  
44425    | D   | 1     | 2019  
44426    | B   | 1     | 2019  
44427    | A   | 1     | 2019  
44428    | E   | 1     | 2019  
...  
etc

I need to add to the Table 1, a column that count the amount of tickets of Table 2 according to each app, month and year, like this:

Table1  
App | Month | Year  | CountOfTickets  
A   |  1    |  2019 | 1  
B   |  1    |  2019 | 2  
C   |  1    |  2019 | 0  
D   |  1    |  2019 | 1  
E   |  1    |  2019 | 1

Are you trying to write a DAX calculated column or a custom column in the query editor? — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2020-02-27T19:40:40

Try something like this:

CountOfTickets =
CALCULATE (
    COUNT ( Table2[IDTicket] ),
    Table2[App]   = EARLIER ( Table1[App] ),
    Table2[Month] = EARLIER ( Table1[Month] ),
    Table2[Year]  = EARLIER ( Table1[Year] )
)

Power BI: Count rows of a table based on a criteria of another table

1 Answers