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
Quenril
Resolver I
Resolver I

Lookup column of data from another table with time variable

Hello,

 

I hope someone can help. I have a 'Client meeting' table in which I am trying to populate the 'Consultant Practice' and 'Consultant Team' columns from my 'Consultant lookup' table via calculated columns. The difficulty is that as individuals get promoted or move teams, their Team and Practice details change and I want it to return the correct Team or Practice result based upon the latest 'Effective date column' in the lookup table compared to the meeting date. 

 

Below is the formula I am inputting to return the Consultant Team. It is working in many rows, however not in all. I can't see any patterns in temrs of which are/aren't working. Moreover, it sometimes works and sometimes doesn't work for the same Consultant name:

 

Consultant Team =
VAR SelectedConsultant = 'Client Meetings'[Consultant Name]
VAR MaxEffectiveFromDate =
    CALCULATE(
        MAX('Consultant lookup'[Effective from date]),
        'Consultant lookup'[Consultant Name] = SelectedConsultant,
        'Consultant lookup'[Effective from date] <= MAX('Client Meetings'[Date of Meeting])
    )
RETURN
    CALCULATE(
        VALUES('Consultant lookup'[Team]),
        'Consultant lookup'[Consultant Name] = SelectedConsultant,
        'Consultant lookup'[Effective from date] = MaxEffectiveFromDate
    )

 

Here are the 'Consultant lookup' and 'Client meeting' tables with some of the rows that are working and some that aren't (redacted data):

  Consultant lookup dummy.xlsx

CLIENT_MEETING_20240723070228_dummy.csv

 

Please can someone help?

Thank you so much

Quenril

1 ACCEPTED SOLUTION

Hello,

Thankyou both so much. I have tried both versions and they return exactly the same result as I had before... Do you have any other ideas? The Client table I provided has the Consultants listed as Attendee 1/2 etc, however I've unpivoted that part in Query Editor so when I look at the table in Power BI itself I have a column for 'Lead of Invitee' and then a column called 'Consultant Name' which is the column I want the lookup to use...

 

Do you have any other ideas please?

Thank you

Becky  

View solution in original post

4 REPLIES 4
HotChilli
Super User
Super User

There's no 'Client Meetings'[Consultant Name] column in the data provided. It's in the calculated column - is it supposed to refer to

Consultant Attendee 1?
 

What rows show the problem you are experiencing and what are you expecting to see?

gmsamborn
Super User
Super User

Hi @Quenril 

 

In addition to the change by @HotChilli  , I set up a variable for Meeting Date and then used that in the comparison.

 

 

Consultant Team =
VAR SelectedConsultant = [Consultant Attendee 1]
VAR MeetingDate = [Date of Meeting]
VAR MaxEffectiveFromDate =
    CALCULATE(
        MAX('Consultant lookup'[Effective from date]),
        'Consultant lookup'[Consultant Name] = SelectedConsultant,
        'Consultant lookup'[Effective from date] <= MeetingDate
    )
RETURN
    CALCULATE(
        MAX('Consultant lookup'[Team]),
        'Consultant lookup'[Consultant Name] = SelectedConsultant,
        'Consultant lookup'[Effective from date] = MaxEffectiveFromDate
    )

 

 

Let me know if you have any questions.

 



Proud to be a Super User!

daxformatter.com makes life EASIER!

Hello,

Thankyou both so much. I have tried both versions and they return exactly the same result as I had before... Do you have any other ideas? The Client table I provided has the Consultants listed as Attendee 1/2 etc, however I've unpivoted that part in Query Editor so when I look at the table in Power BI itself I have a column for 'Lead of Invitee' and then a column called 'Consultant Name' which is the column I want the lookup to use...

 

Do you have any other ideas please?

Thank you

Becky  

HotChilli
Super User
Super User

I don't like this part:

CALCULATE(
        VALUES

That's going to return a table.

It's possible (though I have never tried it) that you get data being returned if there is only one row being returned from this but not multiple rows or blanks or nulls. Just a theory but you can investigate at your side.

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.