Forum Discussion

Quenril's avatar
Quenril
Icon for Resolver I rankResolver I
2 years ago
Solved

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

Need Help
  • Quenril's avatar
    Quenril
    2 years ago

    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  

4 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    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.

     

    • Quenril's avatar
      Quenril
      Icon for Resolver I rankResolver I
      2 years ago

      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's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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?