Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I've got 2 tables
ActivityID | Date | EmpID | EmpName |
1 | 19-8-2020 | 3 | Jansen |
2 | 20-8-2020 | 4 | de Boer |
3 | 21-8-2020 | 5 | Fransen |
EmpID | EmpName | Function | From | To |
3 | Jansen | Psycholoog | 1-1-2020 | 31-7-2020 |
3 | Jansen | GZ-Psycholoog | 1-8-2020 | |
4 | de Boer | Schoonmaker | 1-1-2020 | |
5 | Fransen | Controller | 1-1-2020 |
The relation is between EmpID fields. I want to add a column where I can see function of the employee on the date of the activity. What Dax formula can I use?
Solved! Go to Solution.
Hi @Anonymous ,
Would you please try the calculated column below:
Function =
CALCULATE (
CONCATENATEX ( VALUES ( Table2[Function] ), Table2[Function], "," ),
FILTER (
Table2,
Table2[From] <= EARLIER ( 'Table'[Date] )
&& COALESCE ( Table2[To], TODAY () ) >= EARLIER ( 'Table'[Date] )
&& Table2[EmpID] = EARLIER ( 'Table'[EmpID] )
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Anonymous ,
Would you please try the calculated column below:
Function =
CALCULATE (
CONCATENATEX ( VALUES ( Table2[Function] ), Table2[Function], "," ),
FILTER (
Table2,
Table2[From] <= EARLIER ( 'Table'[Date] )
&& COALESCE ( Table2[To], TODAY () ) >= EARLIER ( 'Table'[Date] )
&& Table2[EmpID] = EARLIER ( 'Table'[EmpID] )
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
I tried your formula and I got some good answers but not everything is correct.
ActivityID | Date | EmpID | EmpName | Function |
1 | 19-8-2020 | 3 | Jansen | GZ-psycholoog |
2 | 20-8-2020 | 4 | de Boer | Schoonmaker |
3 | 21-8-2020 | 5 | Fransen |
I expected Controller in activity 3. Do you know what went wrong?
Edit: The issue was because the current date (20-8-2020) was before the date of the activity. I've changed the dates in the tables and everything worked, thanks!!!
@Anonymous , I did get you to need completely.
Try a new column in Table 1
maxx(filter(table2, table2[From]<=Table1[Date] && coalesce(table2[From],today())>=Table1[Date] ), table2[Function])
Do you really need it inside the first table? If you do there is no more need for separate tables with a relationship since you have it all in one table.
I would recommend to match the data only in your report. that keeps your data model tidy and fast.
matrix visual:
Date rows I Employer Name column from table one
Function from table two
that should do it
by the way you have a cardinality problem with employee jansen. he has two funktions with the same EmpID. I would recommend to give a new EmpID for the new qualification
cheers