Forum Discussion
Solved
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...
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!
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]
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!