Forum Discussion
Solved
Confused - iterating over values
Hello, I am after some help if possible. I have a table, that contains a list of unique customer names. I have another table that contains details about that customer, this has a list of installa...
Hi,
Enter this calculated column formula in Table1
Column = LOOKUPVALUE(Table2[Software Name],Table2[Date Patch Applied],CALCULATE(MAX(Table2[Date Patch Applied]),FILTER(Table2,Table2[ID]=EARLIER(Table1[ID])&&Table2[Environment]="Production"&&Table2[Subsys]="CR")),Table2[ID],Table1[ID])Hope this helps.
| ID | Customer Name | Software Version (Trying to generate this) |
| 1 | ABC Co | Version 3 |
| 2 | Fred Bloggs | Version 2 |
| 3 | A Company | |
| 4 | B Company | |
| 5 | C Company |
Trying to create the above column, based on sample data below
Where the Environment = Production, the Subsys is CR and the latest date in the list per ID
| ID | Environment | Date Patch Applied | Subsys | Software Name |
| 1 | Production | 1/2/24 | CR | Version 1 |
| 1 | Production | 3/2/24 | CR | Version 2 |
| 1 | Production | 5/2/24 | CR | Version 3 |
| 1 | Staging | 3/2/24 | CR | Version 4 |
| 2 | Production | 3/2/24 | AB | Version 4 |
| 2 | Production | 5/2/24 | CR | Version 2 |
Hope that helps
Thanks
Ashish_Mathur
Super User
Super UserHi,
Enter this calculated column formula in Table1
Column = LOOKUPVALUE(Table2[Software Name],Table2[Date Patch Applied],CALCULATE(MAX(Table2[Date Patch Applied]),FILTER(Table2,Table2[ID]=EARLIER(Table1[ID])&&Table2[Environment]="Production"&&Table2[Subsys]="CR")),Table2[ID],Table1[ID])Hope this helps.