Lookup Date in one table, return value from another; Direct Import

[ssbagley]

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

[ssbagley]

@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.

[sevenhills]

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 

[ssbagley]

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?

[sevenhills]

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!

[ssbagley]

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?

[ssbagley]

Thank you for the guidance! I will try your suggestions on the morning!

[sevenhills · Accepted 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!

[ssbagley]

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? 

[sevenhills]

Share the data where and when it happens, we can take a look at it!

(Remove sensitive info)

[ssbagley]

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.  

[sevenhills]

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

•     Go to optimize tax and click Performance Analyzer
•      Create a page with only the table visual from all columns  and measures from 'Expense Data'
• Click refresh visual 
  
• Copy query and paste into notepad
• It contains both DAX and SQL. See where it is going wrong.
  Note: We cannot change this generated query. but gives you where it is going wrong!

Roughly it genarates as below:

// DAX Query

DEFINE

         VAR __DS0Core = ... 

EVALUATE ... 

ORDER BY ... 

// Direct Query

SELECT 

TOP (1000001) ... 

...

// Direct Query

SELECT SUM( ... 

I verified my own set of tables and works fine the DAX, using Direct Query and two tables scenario.

[ssbagley]

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...

// DAX Query

DEFINE

VAR __DS0Core = 

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL(

ROLLUPGROUP(

'Prod_Expense_Audit_Details'[Emp ID],

'Prod_Expense_Audit_Details'[TransactionDate],

'Prod_Expense_Audit_Details'[ExpenseTypeName]

), "IsGrandTotalRowTotal"

),

"SumAmount", CALCULATE(SUM('Prod_Expense_Audit_Details'[Amount])),

"SumTotal_Hours_by_Type_Absence", CALCULATE(SUM('Prod_Expense_Audit_Details'[Total Hours by Type Absence]))

)

VAR __DS0PrimaryWindowed = 

TOPN(

502,

__DS0Core,

[IsGrandTotalRowTotal],

0,

'Prod_Expense_Audit_Details'[Emp ID],

1,

'Prod_Expense_Audit_Details'[TransactionDate],

1,

'Prod_Expense_Audit_Details'[ExpenseTypeName],

1

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

[IsGrandTotalRowTotal] DESC,

'Prod_Expense_Audit_Details'[Emp ID],

'Prod_Expense_Audit_Details'[TransactionDate],

'Prod_Expense_Audit_Details'[ExpenseTypeName]

[ssbagley]

Ignore my previous message - I started a new file from scratch and it worked beautifully!  I cannot thank you enough for your help!

[sevenhills]

Glad to hear that it worked!