Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I have two tables:
TABLE_1, that has a list of all employees on each month (below, just an example with one employee_id)
EMPLOYEE_ID | MONTHLY PERIOD |
2875 | 10/01/2016 |
2875 | 11/01/2016 |
2875 | 07/01/2017 |
2875 | 08/01/2017 |
TABLE_2 that has information for each employee of absenses (example: vacations, medical leave, pregnancy, etc).
EMPLOYEE_ID | START | END | TYPE |
2875 | 10/15/2016 | 11/05/2016 | 120 (VACATIONS) |
2875 | 07/20/2017 | 08/10/2017 | 290 (MEDICAL LEAVE) |
I´m trying to add a calculated column in TABLE_1 so I would have, for each period, what was the condition of the employee.
The conditions would be:
"IF (TABLE_2[START] < ENDOFMONTH(TABLE_1[MONTHLY PERIOD])
&& TABLE_2[END] > ENDOFMONTH(TABLE_1[MONTHLY PERIOD]))
THEN TABLE_2[TYPE]" <-- of that specific line
ELSE "ACTIVE"
The TABLE_1 would result in the below:
EMPLOYEE_ID | MONTHLY PERIOD | TYPE |
2875 | 10/01/2016 | 120 (VACATIONS) |
2875 | 11/01/2016 | ACTIVE |
2875 | 07/01/2017 | 290 (MEDICAL LEAVE) |
2875 | 08/01/2017 | ACTIVE |
Ideas on how to do it would be appreciated!
Solved! Go to Solution.
Hey,
here you will find pbix file and here the sample data
There is a new table "Sheet3" that represents the table with the uniqe "Employee_ID". From my understanding the relationships should look like this
There is a new measure in Sheet1
Measure 2 = IF(ISFILTERED(Sheet1[EMPLOYEE_ID]) ,CALCULATE( CONCATENATEX( SELECTCOLUMNS('Sheet1', "EMPLOYEE_ID", 'Sheet1'[EMPLOYEE_ID], "MONTHLY_PERIOD", 'Sheet1'[MONTHLY PERIOD]) ,var currentEmployee = [EMPLOYEE_ID] var currentMonthlyPeriod = CALCULATE(EOMONTH(MAX('Sheet1'[MONTHLY PERIOD]),0)) var empType = CONCATENATEX('Sheet2' ,IF('Sheet2'[EMPLOYEE_ID] = currentEmployee && 'Sheet2'[START] < currentMonthlyPeriod && 'Sheet2'[END] > currentMonthlyPeriod ,'Sheet2'[TYPE] ,BLANK() )) return IF(empType = "","ACTIVE",empType) ) ) )
and also a column in Sheet1, that is basically the same as the measure except the 1st check if Sheet1[Employee_ID] is filtered.
Dependig on the table size of your "Sheet1", I would recommend to use the column version if the table size is small (what ever that means) ;-), this is due to memory consumption of "physical / calculated columns".
Regards
Hey @Anonymous,
if you have some time, you may redownload the pbix file. There are two new calculated columns: column 3 and column 4.
If you are sure that there is just one daterange in table2 for any given date from table1 try column4, else go with column3.
Column3 is a little optimized in comparison to the initial column whereas Column 4 assumes just one valid daterange for any given employee / period.
I have to admit that I'm somewhat intereested if there a performance gain using the new columns
Happy to help
Regards
Tom,
I tested all of them. Column 4 is perfect for me, and it was extremely fast.
In my data set:
Column took 32 seconds
Column 2 took more than 3 minutes and I killed it
Column 3 took more than 3 minutes and I killed it
Column 4 took 3 seconds
I´m in a Dell M3800, i7 with 16gb RAM.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |