Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to Solution.
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!
@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?
You dont have that option for measure. I am still not understanding why you are not getting.
... text from the above link ...
The Show items with no data feature lets you include data rows and columns that don't contain measure data (blank measure values).
-----
I follow these guidelines, which is simple as like this in your visuals
* Use Facts table for data columns or rows.
This can be done using related dimension tables.
Difference is dimension tables allows you all combinations vs Facts allows only combinations from the transactions
* Use Facts table for measure
There are caveats and complicated scenarios, but these two points are good start.
Some call dims as lookups/master data and facts as transactions.
Hope this analogy helps!
I feel like it's because there isn't a date value to match in the time table. The expense table has Saturday, 08/05/2023 but because there was no time on that day (because a weekend), the date value isn't there to match. Does that make sense?
Thank you for the guidance! I will try your suggestions on the morning!
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)
Easier said than done -- both source tables are Direct Query connections to SQL tables with millions and millions of rows. I added the column with your "formula", updating for my data sources (and double checked that I got it correct), but the whole column is blank.
Since you said that it is SQL tables direct query, you can do this
a) Check the Dax if you have missed any logic
b) Review the code generated
Roughly it genarates as below:
I verified my own set of tables and works fine the DAX, using Direct Query and two tables scenario.
Here is the code generated -- I marked the "new" column that pulls from the hours table in blue. The issue I see is that I don't have reference to SQL...
Ignore my previous message - I started a new file from scratch and it worked beautifully! I cannot thank you enough for your help!
Glad to hear that it worked!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
95 | |
86 | |
76 | |
64 |
User | Count |
---|---|
138 | |
113 | |
109 | |
98 | |
93 |