Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

DAX: Retrieve Fields Based on Time and Employee

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_employeeid_supid_departmentdate_effective_startdate_effective_end
24478532447854112/1/201612/4/2016
24478532447855112/5/201612/10/2016
24478532447855212/11/201612/16/2016
24478535000001212/17/201612/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_transid_employeeflagid_supid_department
12/1/20162447853P  
12/2/20162447853P  
12/3/20162447853P  
12/4/20162447853OFF  
12/5/20162447853OFF  
12/6/20162447853P  
12/7/20162447853P  
12/8/20162447853P  
12/9/20162447853P  
12/10/20162447853P  
12/11/20162447853OFF  
12/12/20162447853OFF  
12/13/20162447853P  
12/14/20162447853P  
12/15/20162447853P  
12/16/20162447853P  
12/17/20162447853P  
12/18/20162447853OFF  
12/19/20162447853OFF  
12/20/20162447853P  
12/21/20162447853P  
12/22/20162447853P  
12/23/20162447853P  
12/24/20162447853P  
12/25/20162447853OFF  
12/26/20162447853OFF  
12/27/20162447853P  
12/28/20162447853P  
12/29/20162447853P  
12/30/20162447853P  
12/31/20162447853P  
1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft Employee
Microsoft Employee

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]
				)

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Phil_Seamark
Microsoft Employee
Microsoft Employee

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]
				)

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors