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!

     

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?