Relation over 3 tables with a date in one and date span in another table

[lars_hei]

Hey there!

For analysing our sales activity, we currently have the following setup:

Phone Calls       Employees          Employee Assignments
--------------------------------------------------------
person_id - - - - person_id
                  employee_id - - -  employee_id
                                     employee_function
                  current_team       employee_team
call date - - - - - - - - - + - - -  date_from
                            + - - -  date_until

Please note that two tables share the person_id and two table share employee_id.

If deemed helpful, adding a column with person_id / employee_id is not be a problem.

A report has already been created, but in the current report the employee assignments are missing.

Instead, the _current_ team and function are used,

which is not accurate if we go back a few months or want to have year-to-year comparisons.

(Employees changed teams -> wrong assignment; or even left the company -> no assignment)

What would we like to achieve?

We would like to always assign the correct role and team
of en employee at the time of the phone call.

What was tried?

Relations:

I was not sure how to set up relations between

a date and a daterange with separate date_from and date_until fields

DAX:

I am pretty new to the and tried some random stuff with DAX.

But none of that seemed to bring me forward.

I have read that you can relate data in DAX without having an actual relation in the model,

but I could not quite figure it out.

Extra column:

Probably the simplest approach is to add two columns to a phone call,

and just write team and function into those columns.

In SQL that would be something like

select 
  team, function
from 
  phone_calls pc
  join employees e on e.person_id = pc.person_id
  join employee_assginments ea.employee_id = e.employee_id
where
  ea.date_from <= pc.date 
  and (ea.date_until is null 
    or ea.date_until >= pc.date)

SQL Query just added for clearer communication of what I would like to achieve 🙂

But I have no idea how to do this in PowerBI.

Can you give me a hint on what your recommended way of solving this would be?
Thanks 🙂

Example Data

name;person_id;employee_id
Anna;A;1
Bert;B;2
Carl;C;3
Dany;D;4

caller;call_date
A;2024-03-29
A;2024-03-25
A;2024-03-24
A;2024-03-23
A;2024-03-17
A;2024-03-14
A;2024-03-09
A;2024-03-03
A;2024-03-01
A;2024-02-24
A;2024-02-24
A;2024-02-21
A;2024-02-20
A;2024-02-18
A;2024-02-12
A;2024-02-08
A;2024-02-02
A;2024-01-28
A;2024-01-27
A;2024-01-21
A;2024-01-19
A;2024-01-12
A;2024-01-12
A;2024-01-07
A;2024-01-03
A;2024-01-02
A;2024-01-01
B;2024-04-12
B;2024-04-07
B;2024-04-03
B;2024-03-30
B;2024-03-27
B;2024-03-24
B;2024-03-20
B;2024-03-20
B;2024-03-18
B;2024-03-18
B;2024-03-18
B;2024-03-16
B;2024-03-11
B;2024-03-09
B;2024-03-07
B;2024-03-03
B;2024-02-29
B;2024-02-24
B;2024-02-24
B;2024-02-19
B;2024-02-16
B;2024-02-15
B;2024-02-12
B;2024-02-09
B;2024-02-04
B;2024-02-04
B;2024-02-04
B;2024-02-01
B;2024-01-31
B;2024-01-30
B;2024-01-25
B;2024-01-24
B;2024-01-24
B;2024-01-23
B;2024-01-23
B;2024-01-19
B;2024-01-14
B;2024-01-10
B;2024-01-05
B;2024-01-05
B;2024-01-01
C;2024-03-24
C;2024-03-18
C;2024-03-08
C;2024-03-04
C;2024-02-26
C;2024-02-26
C;2024-02-16
C;2024-02-07
C;2024-02-01
C;2024-01-30
C;2024-01-25
C;2024-01-18
C;2024-01-13
C;2024-01-12
C;2024-01-09
C;2024-01-01
D;2024-03-30
D;2024-03-29
D;2024-03-29
D;2024-03-26
D;2024-03-26
D;2024-03-24
D;2024-03-20
D;2024-03-17
D;2024-03-17
D;2024-03-12
D;2024-03-09
D;2024-03-04
D;2024-03-03
D;2024-02-28
D;2024-02-25
D;2024-02-23
D;2024-02-17
D;2024-02-14
D;2024-02-09
D;2024-02-04
D;2024-02-02
D;2024-01-30
D;2024-01-26
D;2024-01-23
D;2024-01-20
D;2024-01-17
D;2024-01-17
D;2024-01-17
D;2024-01-14
D;2024-01-09
D;2024-01-04
D;2024-01-02
D;2024-01-01

employee_id;employee_function;employee_team;date_from;date_until
1;lead;business;2020-01-01;
2;service;business;2022-01-01;2024-01-31
2;support;business;2024-02-01;
3;service;business;2023-01-01;2024-02-29
3;service;direct;2024-03-01;
4;support;business;2024-02-01;

[lars_hei]

Hello @Anonymous ,

thank you for your quick reply!

The result screenshot you posted shows exactly what I am looking for.

However, I have trouble understanding something:

1. What is the purpose of the MAX() function do in this case?
   I think I could answer this myself, it is because the filter returns a list (0..n rows), but we want exactly one row.

2. How does it know which employee to filter for? There seems to be no filter for the employee/person in your provided response? 🤔

Thanks,

Lars

[Anonymous]

Hi @lars_hei ,

FILTER(
'table3',
'table3'[date_from] <= 'table2'[call_date] &&
(ISBLANK('table3'[date_until]) || 'table3'[date_until] >= 'table2'[call_date])
)

In this expression, table3 has been filtered according to the required conditions, and the filtered table is obtained, and then the required columns are taken out using the calculate function.

The second parameter of CALCULATE defines a Boolean expression or table expression for the filter or filter modifier function. Filters can be: Boolean filter expressions, table filter expressions, filter modification functions. Here, a table filter expression is used. A table expression filter applies a table object as a filter. It can be a reference to a model table, but it is more likely to be a function that returns a table object. You can use the FILTER function to apply complex filter conditions, including conditions that cannot be defined by a Boolean filter expression.

For more detailed information, please refer to the following links:
CALCULATE function (DAX) - DAX | Microsoft Learn
The CALCULATE Function In Power BI - DAX Tutorial - Microsoft Fabric Community

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @lars_hei ,

Please try to create a calculated column.

EmployeeTeam = CALCULATE(
    MAX('table3'[employee_team]),
    FILTER(
        'table3',
        'table3'[date_from] <= 'table2'[call_date] &&
        (ISBLANK('table3'[date_until]) || 'table3'[date_until] >= 'table2'[call_date])
    )
)

EmployeeFunc = CALCULATE(
    MAX('table3'[employee_function]),
    FILTER(
        'table3',
        'table3'[date_from] <= 'table2'[call_date] &&
        (ISBLANK('table3'[date_until]) || 'table3'[date_until] >= 'table2'[call_date])
    )
)

If this is not the result you expect, please provide a screenshot or virtual table to help us better solve the problem.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.