DAX: Retrieve Fields Based on Time and Employee

[ovetteabejuela]

Hi,

I have two tables, a movements data and a raw data.

The movements data keeps track of which department the employee had been in a particular duration or to which supervisor he was reporting to in any given duration as well (the table might explain better, and it only show movement of one employee for simplicity):

id_employee	id_sup	id_department	date_effective_start	date_effective_end
2447853	2447854	1	12/1/2016	12/4/2016
2447853	2447855	1	12/5/2016	12/10/2016
2447853	2447855	2	12/11/2016	12/16/2016
2447853	5000001	2	12/17/2016	12/31/2050

The raw data on the other hand is basically just a record of let's just say sales for example on any given day per employee.

But what I wanted to do is to retrieve the corresponding supervisor ID and department based on the raw data's transaction date and employee ID (the example below has only one employee for simplicity)

I hope the question/intent is clear and that is suffice to help me get out from this problem.

date_trans	id_employee	flag	id_sup	id_department
12/1/2016	2447853	P
12/2/2016	2447853	P
12/3/2016	2447853	P
12/4/2016	2447853	OFF
12/5/2016	2447853	OFF
12/6/2016	2447853	P
12/7/2016	2447853	P
12/8/2016	2447853	P
12/9/2016	2447853	P
12/10/2016	2447853	P
12/11/2016	2447853	OFF
12/12/2016	2447853	OFF
12/13/2016	2447853	P
12/14/2016	2447853	P
12/15/2016	2447853	P
12/16/2016	2447853	P
12/17/2016	2447853	P
12/18/2016	2447853	OFF
12/19/2016	2447853	OFF
12/20/2016	2447853	P
12/21/2016	2447853	P
12/22/2016	2447853	P
12/23/2016	2447853	P
12/24/2016	2447853	P
12/25/2016	2447853	OFF
12/26/2016	2447853	OFF
12/27/2016	2447853	P
12/28/2016	2447853	P
12/29/2016	2447853	P
12/30/2016	2447853	P
12/31/2016	2447853	P

[Phil_Seamark · Accepted solution]

You could try creating a DAX table that combines the two tables as you suggest'.

Just hit the "New Table" button on the modelling table and add this.  I've assumed your tables are named Raw and Movements

New Table = SELECTCOLUMNS(
                FILTER(
		CROSSJOIN('Raw','Movements'),
                    'Raw'[id_employee]='Movements'[id_employee]
                    && 'Raw'[date_trans] >= 'Movements'[date_effective_start]
                    && 'Raw'[date_trans] <= 'Movements'[date_effective_end].[Date]
                    )
                ,
				"date_trans",'Raw'[date_trans],
				"id_employee",'Raw'[id_employee],
				"flag",'Raw'[flag],
                "id_sup",'Movements'[id_sup],
                "id_department",'Movements'[id_department]
				)

[ovetteabejuela]

Perfect, thanks @Phil_Seamark! How will this joins(ie. crossjoin) fair in a large table say 500K rows?

[Phil_Seamark]

It should be ok. Everything is in memory but only one way to find out. It can be optimised further if needed.