getting results from different fact tables of different topics - how to model?

[Anonymous]

Hi,

Say you have 2 facts tables, one listing which team an employee is in at different times:

Employee ID		Team		Start Date		End Date
1		A		01/01/2020		30/06/2020
1		B		01/07/2020		31/12/2020
2		B		01/01/2020		30/06/2020
2		A		01/07/2020		31/12/2020

And a second table listing instances of employee absence:

Employee ID		Illness Date
1		01/03/2020
1		01/09/2020
2		01/02/2020
2		01/10/2020

If you wanted to ask questions such as: Which employees were ill in Team B during September 2020? (in this case employee id 1), how might a data model be arranged such that you can ask questions like this? One obvious solution might be to have a Team column in the absence table recording the team at the time of absence, such as below. Is this best practice given you are increasing the volume of data? Is it a necessity? Do I need a many to many relationship on employee ID?

Employee ID		Illness Date		Team
1		01/03/2020		A
1		01/09/2020		B
2		01/02/2020		B
2		01/10/2020		A

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _date =
    SELECTEDVALUE ( illness[Illness Date] )
VAR _id =
    SELECTEDVALUE ( illness[Employee ID] )
VAR _result =
    CALCULATE (
        MIN ( 'Table'[Team] ),
        'Table'[Employee ID] = _id
            && 'Table'[Start Date] <= _date
            && 'Table'[End Date] >= _date
    )
RETURN
    _result

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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