Forum Discussion

rlymer's avatar
rlymer
Frequent Visitor
3 years ago
Solved

Power Query for Calculated Column - Last Review Date

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 th...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    Hi rlymer 

     

    Download example PBIX file

     

    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

PhilipTreacy's avatar
PhilipTreacy
Super User
3 years ago

Hi rlymer 

 

Download example PBIX file

 

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

rlymer's avatar
rlymer
Frequent Visitor
3 years ago

PhilipTreacy Thank you, this works great. I had to explicitly state the relationship with USERELATIONSHIP as in my model it was an inactive relationship.