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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Conditional Lookup (Match ID and dates and fetch correct salary)

Hi!

 

I have an issue with with report I'm building for my company. I have a two tables that I want to combine. One with Salary listed per employee and another table with worked hours. Salaries for employees is listed for each salary period in a new row (from one date to another). Worked hours is listed per day and shows worked hours for each day per employee ID. There is also a table with information about the employee where employees are only listed once per row.

 

I would like to somehow get the correct salary from the "Salary" table to the "Worked hours" table, looking at the employee ID and the date when they worked. This is to then make a report for the total cost of personnel-costs.

 

Here is an example of the data (a simplified example of how the real data model looks).

 

Would really appriciate any help as I've been stuck for the last couple of days.

 

Thanks,

Patrick

1 ACCEPTED SOLUTION
waltheed
Impactful Individual
Impactful Individual

Hi Patrick, 

 

I may have a solution for you.:

 

 

Add a calculated column to the HoursWorked table, and use this formula:

=
CALCULATE (
    MAXX ( Salary, Salary[Salary] ),
    FILTER (
        Salary,
        Salary[EmployeeID] = HoursWorked[EmployeeID]
            && Salary[FromDate] <= HoursWorked[Date]
            && (
                Salary[ToDate] > HoursWorked[Date]
                    || ISBLANK ( Salary[ToDate] )
            )
    )
)

 

Does this work for you?

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

View solution in original post

5 REPLIES 5
waltheed
Impactful Individual
Impactful Individual

Hi Patrick, 

 

I may have a solution for you.:

 

 

Add a calculated column to the HoursWorked table, and use this formula:

=
CALCULATE (
    MAXX ( Salary, Salary[Salary] ),
    FILTER (
        Salary,
        Salary[EmployeeID] = HoursWorked[EmployeeID]
            && Salary[FromDate] <= HoursWorked[Date]
            && (
                Salary[ToDate] > HoursWorked[Date]
                    || ISBLANK ( Salary[ToDate] )
            )
    )
)

 

Does this work for you?

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Anonymous
Not applicable

Amazing! It worked!!

 

If you have time to go through the formula and how it works I'd really appriciate that.

 

Many thanks!!

waltheed
Impactful Individual
Impactful Individual

Sure, I'll give it a try...

 

You want the Salary from the Salary table, but you want to filter the Salary table by some conditions.

 

First, you want the Employee ID's to match: Salary[EmployeeID] = HoursWorked[EmployeeID]

Also, the HoursWorked[Date] needs to be between the Salary[FromDate]and the Salary[ToDate]

Or the Salary[ToDate] can be empty. 

(by the way, && means AND, || means OR, so you must use brackets correctly)

 

Then you to need be sure that just one value is returned, because you use it as a value in a calculated column. Just take the MAXX, MINX could also work. 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

Hi, this is great. I was looking for this, but I'd like to have it as measure. I've tried myself but with no success. Could you please help me with it? Thanks a lot!!

Anonymous
Not applicable

Thanks again! Great explaination!! Great to understand what's happening in addition to having the correct formula 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.