Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
So I've found this solution that should work, but doesn't get me the results I needed.
My goal is to get the correct position for each employee during that week. So for every row, it should evaluate on which position that employee is working that week.
I have two tables:
- Table "Employee": Employee_code, week, first date of that week, position [to be filled by the merge]
Employee_code | Week | StartOfWeek | Position |
1 | 1 | 31-12-2018 | Warehouse |
1 | 2 | 7-1-2019 | Warehouse |
1 | 3 | 14-1-2019 | Warehouse |
1 | 4 | 21-1-2019 | Truck |
1 | 5 | 28-1-2019 | Truck |
1 | 6 | 4-2-2019 | Truck |
1 | 7 | 11-2-2019 | Truck |
2 | 1 | 31-12-2018 | Warehouse |
2 | 2 | 7-1-2019 | Warehouse |
2 | 3 | 14-1-2019 | Warehouse |
2 | 4 | 21-1-2019 | Truck |
2 | 5 | 28-1-2019 | Truck |
2 | 6 | 4-2-2019 | Truck |
2 | 7 | 11-2-2019 | Truck |
3 | 1 | 31-12-2018 | Warehouse |
3 | 2 | 7-1-2019 | Warehouse |
3 | 3 | 14-1-2019 | Warehouse |
3 | 4 | 21-1-2019 | Truck |
3 | 5 | 28-1-2019 | Truck |
3 | 6 | 4-2-2019 | Truck |
3 | 7 | 11-2-2019 | Truck |
- Table "Position": Employee, position, start date, end date
Employee_code | Start | End | Position |
1 | 31-12-2018 | 15-1-2019 | Warehouse |
1 | 16-1-2019 | 1-1-2099 | Truck |
2 | 31-12-2018 | 1-1-2099 | Ship |
3 | 31-12-2018 | 1-1-2099 | Truck |
As you can see employee #1 shows up fine in my first table and switches positions perfectly. However the pattern repeats itself, so #2 doesn't show "Ship" at all. #3 is also wrong.
I've used the following formula in my "Employee" table for column "Position" that looks if my StartOfWeek is between my Start and End date, and should result the matching position for that row:
= Table.AddColumn(#"Employee", "Position", each Table.SelectRows(Position, (Position) => [StartOfWeek]>=Position[Start] and [StartOfWeek]<=Position[End])[Position]{0})
What am I doing wrong here? Thanks!
hi,
According to your description, please add ‘Employee_code’ filter to your original formula.
= Table.AddColumn(#"Employee", "Position", each Table.SelectRows(Position, (Position) => [StartOfWeek]>=Position[Start] and [StartOfWeek]<=Position[End] and [
Employee_code] = Position[Employee_code])[Position]{0})
Here are my test tables.
And the test result.
Here is my pbix file.
If you still have questions about it, please for free to let me know.
Best Regards,
Giotto Zhi
Fantastic! Thanks so much, it works now.
Hello everyone,
It's been a while but I have found the time to implement this solution in my real-life PBI file. However, when I insert the formula I get the following error:
"Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:
[List]"
The formula I'm using is:
= Table.AddColumn(#"Changed Type", "Functie2", each Table.SelectRows(PowerBI_Medewerker_org_eenheid_en_functie, (PowerBI_Medewerker_org_eenheid_en_functie) => [StartDate]>=PowerBI_Medewerker_org_eenheid_en_functie[BeginDatum] and [StartDate]<=PowerBI_Medewerker_org_eenheid_en_functie[EindDatum] and [EmployeeNr] = PowerBI_Medewerker_org_eenheid_en_functie[EmployeeNr])[Functie]{0})
When I change EmployeeNr from the "org en eenheid" table to another column, the formula works (with some errors) but it's not the correct column. I really need to make a match on EmployeeNr.
Please help!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |