Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Icon for Impactful Individual rankImpactful Individual
9 years ago
Solved

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 reporti...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

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

     

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    ovetteabejuela
    Icon for Impactful Individual rankImpactful Individual
    9 years ago

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

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago
      It should be ok. Everything is in memory but only one way to find out. It can be optimised further if needed.