Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Add new columns and count values from another table

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

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , 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])

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , 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])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,
    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