Forum Discussion
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 installations (not unique) and what I would really like to do, is create a column in customer name, that has the latest version of the software, based on date and subsys where the environment is set to production
So, as an example, I would like the customer table to end up something like this, with an entry for each customer, based on the latest date from another table, based on subsys = CR and environment = production
Customer Table - Key = ID
Patch Table - Key is IdentifiedInstallationID
So I am thinking I would need to iterate over the dates, per IdentifiedInstallationID where SubSys = CR and Environment = production and bring back the latest entry
Thanks in advance
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.
3 Replies
Hi Parkyerbike
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523ID 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
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.
