Forum Discussion

ssbagley's avatar
ssbagley
Icon for Helper III rankHelper III
3 years ago
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...
Need Help
  • sevenhills's avatar
    sevenhills
    3 years ago

    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's avatar
ssbagley
Icon for Helper III rankHelper III
3 years ago

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.