Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 Ja...
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    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

ThoSch-Ger's avatar
ThoSch-Ger
Helper I
6 years ago

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