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

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.

ssbagley's avatar
ssbagley
Helper III
3 years ago

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

    Ignore my previous message - I started a new file from scratch and it worked beautifully!  I cannot thank you enough for your help!

    • sevenhills's avatar
      sevenhills
      Super User
      3 years ago

      Glad to hear that it worked!