Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |