Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KiranR
Frequent Visitor

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

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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])

View solution in original post

3 REPLIES 3
Aditya_Meshram
Solution Supplier
Solution Supplier

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)
)

 

 

Aditya_Meshram_0-1652092913330.png

 

 

Regards

CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1652091684206.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

amitchandak
Super User
Super User

@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])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.