Forum Discussion

ssbagley's avatar
ssbagley
Helper 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
Helper 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.

  • ssbagley's avatar
    ssbagley
    Helper III
    3 years ago

    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's avatar
      sevenhills
      Super User
      3 years ago

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

        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?