Compare dates with a many to one relationship between the tables and indirect connection

[wind0soft]

Hello-

I have three tables. One with hours logged, one with project information, and another with project parameter fields.  They are linked together with a project ID in all three tables.  What I need is to compare a parameter value date to a record in the hours table. It is a many to one relationship between the hours and parameter values.

Hours Table example

Log Date	project ID	hours	Phase (this is the calculated column I need)
1/15/23	123	2	Phase 1
1/16/23	123	5	Phase 1
1/19/23	123	4	Phase 1
2/1/23	456	3	Phase 1
2/16/23	123	7	Phase 2
2/21/23	456	3	Phase 1
2/22/23	123	2	Phase 2
3/2/23	123	6	Phase 3

Parameter Values Table

Project ID	Kick Off Date	Go-Live Date
123	2/1/23	3/1/23
456	3/1/23	4/1/23

In words what I need is a calculated colum for

Calculated column called "Phase"= IF Log date is less than kick off date, "Phase 1", If Log date is greater than or equal to kick off date but less than go-live date then "Phase 2", if log date is greater than go-live date then "Phase 3".  

Do I do a LOOKUPVALUE expression with a IF statement?  Not sure wher to begin.

Any help would be greatly appreciated.

[wind0soft]

That worked perfectly! Thank you!!!

[vicky_ · Accepted solution]

You're on the right track. I would recommend using the related function, since you've already got a relationship between the project IDs. 

Here's the DAX I used:

Phase = 
var kickOff = RELATED(Params[Kick Off Date])
var goLive = RELATED(Params[Go-Live Date])

return SWITCH(TRUE(), 
    Hrs[Log Date] < kickOff, "Phase 1",
    Hrs[Log Date] >= kickOff && Hrs[Log Date] < goLive, "Phase 2",
    Hrs[Log Date] >= goLive, "Phase 3",
    "??? Phase"
)

 And here's the results I got