Relation problem

[Anonymous]

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?

[v-deddai1-msft · Accepted 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

[Anonymous]

Hi @v-deddai1-msft 

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!!!

[amitchandak]

@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])

[ThoSch-Ger]

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