Forum Discussion
Relation problem
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?
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
4 Replies
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
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])
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!!!
