Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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])
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
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! |
@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])
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |