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

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

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

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

     

    (Remove sensitive info)

    • ssbagley's avatar
      ssbagley
      Helper III
      3 years ago

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

        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.