Forum Discussion
DAX Return Values From Another Table that has no relationship
Hi Guys,
I am working on the leave data of employees with leave type and days taken. I have created another table without leave type and consolidated continious date ranges together (Table1). What I want to do is, to understand what different leave types employees are taking in a cont. date range. To do this I want to bring earliest start date and latest end date from table1 to table 2 for the leaves that falls between those dates and for the same employeeID. You can see an example below. Could you pls help me with the DAX formula to achieve this?
thank you
Hi, Anonymous
The Return is a CONCATENATE column, you can separate them if needed.
DATA RANGE = VAR Current_Start = Table2[Start Date] VAR Current_End = Table2[End Date] VAR Range_Start = CALCULATE ( MAX ( Table1[Earlierst Start Data] ), FILTER ( Table1, Table1[Earlierst Start Data] <= Current_Start && Table1[Lastest End Date] >= Current_Start ) ) VAR Range_End = CALCULATE ( MAX ( Table1[Lastest End Date] ), FILTER ( Table1, Table1[Earlierst Start Data] <= Current_End && Table1[Lastest End Date] >= Current_End ) ) RETURN CONCATENATE ( Range_Start, CONCATENATE ( "~", Range_End ) )Best,
Paul
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, Anonymous
The Return is a CONCATENATE column, you can separate them if needed.
DATA RANGE = VAR Current_Start = Table2[Start Date] VAR Current_End = Table2[End Date] VAR Range_Start = CALCULATE ( MAX ( Table1[Earlierst Start Data] ), FILTER ( Table1, Table1[Earlierst Start Data] <= Current_Start && Table1[Lastest End Date] >= Current_Start ) ) VAR Range_End = CALCULATE ( MAX ( Table1[Lastest End Date] ), FILTER ( Table1, Table1[Earlierst Start Data] <= Current_End && Table1[Lastest End Date] >= Current_End ) ) RETURN CONCATENATE ( Range_Start, CONCATENATE ( "~", Range_End ) )Best,
Paul
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.