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,
I have 2 tables as below -
Table1 -
Employee ID | Date |
1001 | 9/12/2019 |
1006 | 9/20/2019 |
1001 | 9/15/2019 |
1010 | 10/12/2019 |
1004 | 10/2/2019 |
1009 | 9/30/2019 |
Table2 -
Employee ID | Check in Date | Check out Date |
1001 | 8/29/2019 | 8/31/2019 |
1004 | 8/20/2019 | 8/22/2019 |
1001 | 9/12/2019 | 9/15/2019 |
1005 | 9/20/2019 | 9/22/2019 |
1008 | 9/15/2019 | 8/16/2019 |
1004 | 10/12/2019 | 10/13/2019 |
I need to count the rows in Table 2 which satisfy the following conditions -
1. Table1[Employee ID] = Table2[Employee ID]
2. Table1[Date] >= Table2[Check in Date]
3. Table1[Date] <= Table2[Check out Date]
I added a column to Table1 for which the code looks like this-
= Table.AddColumn(#"Prev Step", "Flag", each (let tdate = [Date], id = [Employee ID] in Table.RowCount(Table.SelectRows(Table2,each [Check in Date] <= tdate and [Check out Date] >=tdate and [Employee ID] = id))))
The code seems to work well for small datasets, but my data has ~200,000 rows and the query seems to run forever. (Though the input file is 20MB, the query keeps loading data beyond 1GB and still does not finish).
Can you please advise?
Solved! Go to Solution.
You will get a huge performance increase by Merging on Employee ID first (use inner join). Then expand Table2's columns. Now apply your date logic in a calculated column. You can then total how you see fit.
Regards,
Mike
You will get a huge performance increase by Merging on Employee ID first (use inner join). Then expand Table2's columns. Now apply your date logic in a calculated column. You can then total how you see fit.
Regards,
Mike
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.