Forum Discussion
Lookup Date in one table, return value from another; Direct Import
Table 1 contains all expense data for my company - direct connection to table on SQL server
Table 2 contains all time data for my company - direct connection to table on SQL server.
How can I look up the date on the Expense Table and return the total time for that day from the Time Table, with the ability to filter by that value and or the "Type" (see example)? There can be multiple rows for each date on both Expense and Time tables for each employee ID.
There has to be an easier way than what I'm currently doing. Keeping Direct Import is preferred, since I've currently downloaded over 10 million rows just for the last quarter.
Example Data (very simplified):
| Expense Data | |||||
| EmpID | Date | Expense Type | Amount | Total Hours | Type (Absence) |
| 1 | Tuesday, August 1, 2023 | Dinner | $ 25.00 | 8 | 5 |
| 1 | Tuesday, August 1, 2023 | Lunch | $ 18.00 | 8 | 5 |
| 2 | Thursday, August 3, 2023 | Breakfast | $ 10.00 | 8 | 8 |
| 3 | Thursday, August 3, 2023 | Taxi | $ 11.00 | 8 | 0 |
| 3 | Thursday, August 3, 2023 | Lunch | $ 20.00 | 8 | 0 |
| 3 | Thursday, August 3, 2023 | Dinner | $ 50.00 | 8 | 0 |
| 4 | Saturday, August 5, 2023 | Taxi | $ 27.00 | 0 | 0 |
| Time Data | |||
| EmpID | Date | Hours | Type |
| 1 | Tuesday, August 1, 2023 | 5 | Absence |
| 1 | Tuesday, August 1, 2023 | 2 | External |
| 1 | Tuesday, August 1, 2023 | 1 | Internal |
| 1 | Wednesday, August 2, 2023 | 8 | External |
| 1 | Thursday, August 3, 2023 | 8 | Internal |
| 1 | Friday, August 4, 2023 | 8 | Internal |
| 1 | Saturday, August 5, 2023 | 0 | |
| 2 | Tuesday, August 1, 2023 | 8 | Internal |
| 2 | Wednesday, August 2, 2023 | 8 | External |
| 2 | Thursday, August 3, 2023 | 8 | Absence |
| 2 | Thursday, August 3, 2023 | 2 | Internal |
| 2 | Friday, August 4, 2023 | 8 | Internal |
| 2 | Saturday, August 5, 2023 | 0 | |
| 3 | Tuesday, August 1, 2023 | 8 | Internal |
| 3 | Wednesday, August 2, 2023 | 8 | External |
| 3 | Thursday, August 3, 2023 | 4 | External |
| 3 | Thursday, August 3, 2023 | 4 | Internal |
| 3 | Friday, August 4, 2023 | 8 | Internal |
| 3 | Saturday, August 5, 2023 | 0 | |
| 4 | Tuesday, August 1, 2023 | 8 | Internal |
| 4 | Wednesday, August 2, 2023 | 8 | External |
| 4 | Thursday, August 3, 2023 | 8 | External |
| 4 | Friday, August 4, 2023 | 8 | Internal |
| 4 | Saturday, August 5, 2023 | 0 |
If you are looking for a measure DAX syntax, add this measure to your Expense Data table.
Total Hours by Type Absence = var _curValue = CALCULATE( SUM('Time Data'[ Hours ]), FILTER( 'Time Data', 'Time Data'[EmpID] = SELECTEDVALUE('Expense Data'[EmpID]) && 'Time Data'[Date] = SELECTEDVALUE('Expense Data'[Date]) && 'Time Data'[ Type ] = " Absence" )) RETURN IF ( HASONEVALUE('Expense Data'[EmpID]) && HASONEVALUE('Expense Data'[Date]) , IF (ISBlank(_curValue), 0, _curValue) , BLANK())Adjust the formula, I see / got spaces when I copy pasted your sample data.
Hope it helps!
14 Replies
If you are looking for a measure DAX syntax, add this measure to your Expense Data table.
Total Hours by Type Absence = var _curValue = CALCULATE( SUM('Time Data'[ Hours ]), FILTER( 'Time Data', 'Time Data'[EmpID] = SELECTEDVALUE('Expense Data'[EmpID]) && 'Time Data'[Date] = SELECTEDVALUE('Expense Data'[Date]) && 'Time Data'[ Type ] = " Absence" )) RETURN IF ( HASONEVALUE('Expense Data'[EmpID]) && HASONEVALUE('Expense Data'[Date]) , IF (ISBlank(_curValue), 0, _curValue) , BLANK())Adjust the formula, I see / got spaces when I copy pasted your sample data.
Hope it helps!
Thank you so much for the help and replay. Unfortunately, results are blank (even for days when I know there should be Absence time). Any thoughts?
Share the data where and when it happens, we can take a look at it!
(Remove sensitive info)
Thank you for the guidance! I will try your suggestions on the morning!
sevenhills One more question - could this code be modified to include a total for absence hours AND include dates with no hours? By looking, I think it can but I'm not sure what to tweak.
We already have in DAX to include the date for filtering ...
&& 'Time Data'[Date] = SELECTEDVALUE('Expense Data'[Date])If you want to show the dates that do not have any hours, you may have to do "Show Items with no data".
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data
I'm struggling with the "Show items with no data". I've enabled that for all of the necessary fields, but am still not getting the expenses on dates with no hours. There's no option to "show items with no data" for the measure. Any suggestions?
