Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ssbagley
Helper III
Helper III

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 I look up the date on the Expense Table and return the total time for that day from the Time Table, with the ability to filter by that value and or the "Type" (see example)? There can be multiple rows for each date on both Expense and Time tables for each employee ID.

 

There has to be an easier way than what I'm currently doing. Keeping Direct Import is preferred, since I've currently downloaded over 10 million rows just for the last quarter. 

 

Example Data (very simplified):

 

Expense Data   
EmpIDDateExpense Type Amount Total HoursType (Absence)
1Tuesday, August 1, 2023Dinner $    25.0085
1Tuesday, August 1, 2023Lunch $    18.0085
2Thursday, August 3, 2023Breakfast $    10.0088
3Thursday, August 3, 2023Taxi $    11.0080
3Thursday, August 3, 2023Lunch $    20.0080
3Thursday, August 3, 2023Dinner $    50.0080
4Saturday, August 5, 2023Taxi $    27.0000

 

Time Data  
EmpIDDate Hours  Type 
1Tuesday, August 1, 20235 Absence 
1Tuesday, August 1, 20232 External 
1Tuesday, August 1, 20231 Internal 
1Wednesday, August 2, 20238 External 
1Thursday, August 3, 20238 Internal 
1Friday, August 4, 20238 Internal 
1Saturday, August 5, 20230 
2Tuesday, August 1, 20238 Internal 
2Wednesday, August 2, 20238 External 
2Thursday, August 3, 20238 Absence 
2Thursday, August 3, 20232 Internal 
2Friday, August 4, 20238 Internal 
2Saturday, August 5, 20230 
3Tuesday, August 1, 20238 Internal 
3Wednesday, August 2, 20238 External 
3Thursday, August 3, 20234 External 
3Thursday, August 3, 20234 Internal 
3Friday, August 4, 20238 Internal 
3Saturday, August 5, 20230 
4Tuesday, August 1, 20238 Internal 
4Wednesday, August 2, 20238 External 
4Thursday, August 3, 20238 External 
4Friday, August 4, 20238 Internal 
4Saturday, August 5, 20230 
1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

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.

 

sevenhills_0-1691619856016.png

 

Hope it helps!

 

View solution in original post

14 REPLIES 14
ssbagley
Helper III
Helper III

@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.

We already have in DAX to include the date for filtering ...

&& 'Time Data'[Date] = SELECTEDVALUE('Expense Data'[Date])

  

If you want to show the dates that do not have any hours, you may have to do "Show Items with no data". 

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data 

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?

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!

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?

ssbagley
Helper III
Helper III

Thank you for the guidance! I will try your suggestions on the morning!

sevenhills
Super User
Super User

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.

 

sevenhills_0-1691619856016.png

 

Hope it helps!

 

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? 

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

 

(Remove sensitive info)

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.  

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 
    sevenhills_0-1691627821203.png

     

  • 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.

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...

ssbagley_0-1691676767965.png

 



// 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]

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

Glad to hear that it worked! 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.