Forum Discussion

Auski's avatar
Auski
Icon for Advocate I rankAdvocate I
2 years ago

LOOKUP between two dates with related Email

Hi all,
I have looked at similar threads here for a solution, but haven't been very successful finding a suitable solution. This is somewhat of a non standard lookup....

I have two tables; 'Org Chart' and 'Cases'. The relationship of these tables is "Email".

The 'Org Chart' table contains a WEEKLY snapshot (including history) of my company's organisation chart over the past 5 years -which means there is a lot of duplicate data within the table. This table contains 'Employee Role' and a 'Start of Week' field and an 'End of Week' field.


  

 

 

 

 

 

The 'Cases' table contains a case created date, however it does not contain Employee Role






 

 

 

 

 

 

 

 

 

I would like to create a column in Cases that looks at created date and returns the Employee Role BETWEEN the related 'Start of Week' and 'End of Week' fields.

The expected outcome would be:



 

 

 

 

 

 

 

I hope that makes sense.... please let me know if it is not clear.

Thanks so much 🙂




Cases

EmailCreated Date
[email protected]25/04/2024
[email protected]27/04/2024
[email protected]24/04/2024
[email protected]26/02/2024
[email protected]27/04/2024
[email protected]26/04/2024
[email protected]22/02/2024
[email protected]26/04/2024
[email protected]15/03/2023
[email protected]27/12/2023
[email protected]27/04/2024
[email protected]24/04/2024


Org Chart

EmailStart of WeekEnd of WeekEmployee Role
[email protected]24/04/20241/05/2024Data Analyst
[email protected]24/04/20241/05/2024Data Analyst
[email protected]20/02/202427/02/2024Sales Officer
[email protected]20/02/202427/02/2024Sales Officer
[email protected]24/04/20241/05/2024Data Analyst
[email protected]9/03/202316/03/2023Marketing Coordinator
[email protected]24/12/202331/12/2023Sales Officer
[email protected]9/03/202316/03/2023Marketing Coordinator
[email protected]24/04/20241/05/2024Data Analyst
[email protected]24/04/20241/05/2024Team Leader
[email protected]24/12/202331/12/2023Sales Officer
[email protected]24/04/20241/05/2024General Manager

 

 

Need Help

3 Replies

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

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    Employee Role CC =
SUMMARIZE (
    FILTER (
        SUMMARIZE (
            'org chart',
            'org chart'[Email],
            'org chart'[Start of Week],
            'org chart'[End of Week],
            'org chart'[Employee Role]
        ),
        'org chart'[Email] = 'cases'[Email]
            && 'org chart'[Start of Week] <= 'cases'[Created Date]
            && 'org chart'[End of Week] >= 'cases'[Created Date]
    ),
    'org chart'[Employee Role]
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Write this calculated column formula in the Cases table

    Column = CALCULATE(MAX('Org chart'[Employee Role]),FILTER('Org chart','Org chart'[Start of Week]<=EARLIER('Cases'[Created Date])&&'Org chart'[End of Week]>=EARLIER('Cases'[Created Date])&&'Org chart'[Email]=EARLIER('Cases'[Email])))

     

  • Auski's avatar
    Auski
    Icon for Advocate I rankAdvocate I
    2 years ago

    Thank you both very much for your assistance here. Both options work here as a solution, however I am dealing with huge volumes of data (over 3million rows in each of the tables). Each of these calculations take over 20mins to complete.

    Perhaps a calculated column isn't the best way to go(?) - performance wise. Interested to hear your thoughts!