Add new columns and count values from another table

[KiranR]

Hi Guys,

This might be simple but struggling a bit hence need your support.

I have 2 different tables with dates; I want to create new columns in Table 1 and count the number of times the dates are repeated from Table 2 columns comparing to Table 1 rows. The third table in this query is the result I want.

Thanks in advance.

Table 1
Date
01-05-22
02-05-22
03-05-22
04-05-22
05-05-22
06-05-22
07-05-22
08-05-22
09-05-22
10-05-22

Table 2
Scheduled       Actual
01-05-22         01-05-22
01-05-22         02-05-22
02-05-22         02-05-22
02-05-22         02-05-22
02-05-22         02-05-22
02-05-22         02-05-22
02-05-22         02-05-22
03-05-22         03-05-22
04-05-22         04-05-22
04-05-22         04-05-22
04-05-22         04-05-22
04-05-22         05-05-22
04-05-22         05-05-22
04-05-22         05-05-22

Table 1
Date         Scheduled Count Actual Count
01-05-22                 2                   1
02-05-22                 5                   6
03-05-22                 1                   1
04-05-22                 6                   3
05-05-22                 0                   3
06-05-22                 0                   0
07-05-22                 0                   0
08-05-22                 0                   0
09-05-22                 0                   0
10-05-22                 0                   0

[Anonymous]

Hi @KiranR ,
in case you want a separate table and want 0's instead of NULL values in your table, you can use this DAX

Table 3 = 
ADDCOLUMNS(
    'Table 1',
    "Actual Count",COALESCE(COUNTX(FILTER('Table 2','Table 1'[Date] = 'Table 2'[Actual]),'Table 2'[Actual]),0),
    "Scheduled Count",COALESCE(COUNTX(FILTER('Table 2', 'Table 1'[Date] = 'Table 2'[Scheduled]) ,'Table 2'[Scheduled]),0)
)

Regards

[CNENFRNL]

[amitchandak · Accepted solution]

@KiranR , a new column in table one

Scheduled = countx(filter(Table2, Table1[Date] = Table2[Scheduled]) ,Table2[Scheduled])

Actual= countx(filter(Table2, Table1[Date] = Table2[Actual]) ,Table2[Actual])