Forum Discussion
Get column value in one table based on max value from another column in a related table
I have a Projects (fact) table where each row describes a project's start and end date for each step. In effect, I have multiple rows for each project for all projects. I also have a Project Activities (dimension) table which specifies each activity and its sequence.
I want to be able to create a calculated column in the Projects table that shows a specific project's ongoing activity (i.e., the last step in the sequence which has a start date).
I am able to create the following code, however it's incomplete. What it gets is the activity with the latest start date, but if 2 activities start on the same date, it only sorts Activity alphabetically since I am only getting the Max [Activity]. I don't know how to revise the code to make it get the last step based on the sequence of activities in the other table.
Current Status OPEX =
var _maxstart =
CALCULATE(
MAX('OPEX Projects Execution'[Start Date]),
'OPEX Projects Execution'[Status]="Actual",
ALLEXCEPT('OPEX Projects Execution','OPEX Projects Execution'[SIO])
)
return
CALCULATE(
MAX('OPEX Projects Execution'[Activity]),
FILTER(
ALLEXCEPT('OPEX Projects Execution','OPEX Projects Execution'[SIO]),
'OPEX Projects Execution'[Start Date] = _maxstart
)
)
To explain the status expression in the variable, each line item may also be planned or actual. I only want to get the actual start.
I also tried another formula but this shows circular dependency error
Current Status OPEX =
var _maxstart =
CALCULATE(
MAX('OPEX Projects Execution'[Start Date]),
'OPEX Projects Execution'[Status]="Actual",
ALLEXCEPT('OPEX Projects Execution','OPEX Projects Execution'[SIO])
)
var _table =
FILTER(
ALLEXCEPT('OPEX Projects Execution','OPEX Projects Execution'[SIO]),
'OPEX Projects Execution'[Start Date] = _maxstart
)
return
CALCULATE(
MAX('OPEX Projects Execution'[Activity]),
TOPN(
1,
_table,
RELATED('Projects Activities'[Sort]),
DESC
)
)
Please see sample data:
| Project | Activity | Start Date | End Date | Expected Column |
| A | Commercial Negotiation | 4/1/23 | 4/22/23 | Ongoing |
| A | Ongoing | 4/23/23 | Ongoing | |
| C | Shopping Cart Creation | 2/5/23 | 3/18/23 | Commercial Negotiation |
| C | Commercial Negotiation | 4/30/23 | Commercial Negotiation | |
| C | Technical Evaluation | 4/30/23 | 5/6/23 | Commercial Negotiation |
| C | Pre-bid Meeting | 4/9/23 | 4/15/23 | Commercial Negotiation |
| C | Submission of Bids | 4/16/23 | 4/29/23 | Commercial Negotiation |
Project Activities:
| Activities | Sequence |
| Shopping Cart Creation | 1 |
| Pre-bid Meeting | 2 |
| Submission of Bids | 3 |
| Technical Evaluation | 4 |
| Commercial Negotiation | 5 |
| Awaiting Award of PO | 6 |
| Ongoing | 7 |
| 100% Posting of Payment | 8 |
9 Replies
- Ashish_Mathur
Super User
Hi,
Share some data (in a format that can be pasted in an MS Excel file) and show the expected result.
Hi! I updated my question. but also sharing here the data. I want to create a calculated column resulting to the last column, Expected Column.
Project Activity Start Date End Date Expected Column A Commercial Negotiation 4/1/23 4/22/23 Ongoing A Ongoing 4/23/23 Ongoing C Shopping Cart Creation 2/5/23 3/18/23 Commercial Negotiation C Commercial Negotiation 4/30/23 Commercial Negotiation C Technical Evaluation 4/30/23 5/6/23 Commercial Negotiation C Pre-bid Meeting 4/9/23 4/15/23 Commercial Negotiation C Submission of Bids 4/16/23 4/29/23 Commercial Negotiation Activities Sequence Shopping Cart Creation 1 Pre-bid Meeting 2 Submission of Bids 3 Technical Evaluation 4 Commercial Negotiation 5 Awaiting Award of PO 6 Ongoing 7 100% Posting of Payment 8 - Ashish_Mathur
Hi,
I do not understand the significance of the second table. This calculated column formula in the first table works
Column = LOOKUPVALUE(Data[Activity],Data[Start Date],CALCULATE(MAX(Data[Start Date]),FILTER(Data,Data[Project]=EARLIER(Data[Project])&&Data[End Date]=BLANK()&&Data[Start Date]>=EARLIER(Data[Start Date]))),Data[Project],Data[Project],Data[End Date],BLANK())Hope this helps.
