Forum Discussion
Resolver ILookup column of data from another table with time variable
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
Super UserHi 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.
Resolver IHello,
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