Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have 2 tables, Task and Review. Task has TaskID as a primary key, and Review has TaskID as a foreign key. Each Task can have multiple Reviews.
I am wanting a column in the Task table to show the latest Review date, or null if there has been no review. Can anyone point me in the right direction in terms of the power query?
Thanks
Solved! Go to Solution.
Hi @rlymer
I know you have posted in the PQ forum but I think this is better off done in DAX as you don't have to add a new column to your dataset. And it'll be faster than doing a table joins if your data set is large.
Try this measure
Latest Review Date = CALCULATE(MAX('Review'[Review Date]), FILTER('Task', 'Task'[TaskID] = SELECTEDVALUE('Task'[TaskID])))
On this Review table
Giving this (the TaskID column is set to show items with no data)
Regards
Phil
Proud to be a Super User!
Hi @rlymer
I know you have posted in the PQ forum but I think this is better off done in DAX as you don't have to add a new column to your dataset. And it'll be faster than doing a table joins if your data set is large.
Try this measure
Latest Review Date = CALCULATE(MAX('Review'[Review Date]), FILTER('Task', 'Task'[TaskID] = SELECTEDVALUE('Task'[TaskID])))
On this Review table
Giving this (the TaskID column is set to show items with no data)
Regards
Phil
Proud to be a Super User!
@PhilipTreacy Thank you, this works great. I had to explicitly state the relationship with USERELATIONSHIP as in my model it was an inactive relationship.
NewStep=Table.TransformColumns(Table.NestedJoin(Task,"TaskID",Review,"TaskID","LastReviewDate",JoinKind.LeftOuter),{"LastReviewDate",each if _ is table then List.Max([ReviewDate]) else null})