Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
That worked perfectly! Thank you!!!
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
70 | |
36 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
30 | |
28 | |
24 | |
22 |