cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Find MAX based on two criteria

Hi All,

 

I have set up a duplicate filter in Power Query to find the latest revision of a document from the system. I did this by creating a copy of my original table and using the group function to group these document names by the latest/max revision number that is created by the system.

 

This worked well. However, I came across an issue where a previous revision of a document would be updated on the system and this transaction will then get the latest system revision number instead of the actual latest revision of the document.

 

CorneGeyser_0-1596705078800.png

From the image above, I use the 'INT_KEY' column to find the latest revision. The 'Rev' column is what indicates the actual document revision as per company standards.

 

What I Need:

 

if there is a 0.1 revision in the 'Rev' column for a document, it has to keep that line for a document. If there is no 0.1 for a document, it must continue using my grouping method or something similar/better.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Find MAX based on two criteria

Hi @CorneGeyser ,

 

Apologies for the delay.

Please try this code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZzdkppKEMdfhdrrcHZARM3dCLPKCV81jK5JKu9w3v/qDKgoMNPMuESbKkKyaoRfTff0vz/Y37/fvLdvb55HNoHv7mPulrxw/12RjVsmsUuI55L6A/Sf+lxweXLlnw/GeRI51SEt91QwJyryI+NVUuTyTRK+E//dJz5xCPneHPWr3rs8vA0xeFUe8jbqcxLXVyu4k+TynFFxvsTI8efb7zcfxPKNsCpRcLpjDs1jJy6qJN+hwVuAeAsl3v5nzIsdy52S8eKUxAwbVABCBWeouMapGItfQFVftrmwAczSzK9qO/2MXuJXe/qrKOU1sozlojKjCs3cCqR6jls9Qrcy86oO3TPt7xGmtYlT9ZiapTrUi0ajBNsibcwcq94hXxSwklwwntP0CmbG5REz3wLBnuNbDwLCUmOhBHymez2IBUuNQIn1NA97kAnWFx0nM4tenjcxGGcRS44sdj54kTkPYsKKwzfCVLocTlxYkyyUuIAD4oSEJUqghNS5I07Ce5nCcpel0ZkwTSphnLOVKc2FkxXSep36P8qP+FPQyruR58s39gGdK78h6XqU1DckfQqeIdVmlOq2ftptZw7r55NRUt+QtMYLvorXSEx7wCqqg0H7I6uIFw1ZvS5rxi6sceXSnd4vs+Qk9xpH0PyHQ3eJoKJ5y59CCMh7kOe4ethUfV8P9UFzLVQV8cN2y7gjP4QHZqGH2QM75764uBcOikBPUUaplkJwmlfStZzykJV4aJZjNOpSXWtguGhCgCbT11MBjYUEbDUGplkmja5CQrUeo1IXUvsBChvWZgwrmCPWoicmIprcAmwiXJFTnX/9t3cyJq85FYu89BdZPD1LCrOk7MhSp9nNs0SggvLHoH7MEGoBQFXpGUq9+Z2hqs9ERHs8PIEBj3rbw8mz1POUsCeVnFXVgTOcdhcCXPxYb+S6dWq5OEsT9uEcaXpkeMBWBmDqAIUcbK0HE7AlCpZJ5UcFWmPc6NFOR4iMRuIgtUSMbK0Cay3RVifutcQaBYu1lmhZXhF2LUtIgbWomBWdtbro0T0tGtuCWcuMuYBZ640W7EV6wxbQWngMAY3is796JwtD7g/xAGNdtm0ameejrIhPh7jWcmTeuNYipcV9nUixtWBbtdIiPl2tWJItrWRLHTmVJRAUsmVpJVs6LMrAjgPKSq3MBcpKpCigbrEcBw/QQVF06trdQdepmwIKqI7a7hGAQBkqrxmsFtBTUbQgbyMdvRYkslUCGiqKXuRtGufai0SGA8iKoSruGJ1OFeMwPqiVMmy23rRSv9mKa7lCMoalzjkHXVdkWICkGIrbjhUC4haFIYbAIIais2wyvYdt8YDpDEWLeXR0DxseUBAZJCUd0xwmJTgsEprcGDbPtdNdaBdsdJZDXRWYDZ+u8AFOkSZ5JfihuVw6oRI5W+OXpipDQIKoc2NVSV+iGFdpnpDwh0ApQ50kg9VvZHQroJyhzpZnRQcVOJRps64Ejg0MKnIoM8y5gAGFDnUWM1bbxwYIiBC1QDao/mJjBOofSqGlr/5iI9N1ZsDnBoYRe0KsaR4TWJk+0NLRxQYPREjMr5NO8MTHqqdNyqT86jDPJGjyPnS2aQi2MQCzG+bBAbYmBmB2XVCy/EtgnB0T9mnqbGvPgMyu4YmFTKdLLtuI2sGSot0tpsBQ74tnEEOMngoBd0Obx8Mm4VPvhpYLBciQfqq2HUxy18/ZFryUf9P056+zBAmnDdQP/faSNdR4gamU2QwKJmAYpJ+hzYYJmPjo52VKplv6ggIHqHv0s7E54AAVj34OttUoCoRGtwFKHb2spEc1zEpQ8OiKG90gtR2rHk4Ic/nGx3CAkoYAjQ7Ii1GsE1DSUGf8pwtYRPOIpWkT09UZoxe8k5Xhq6cuy/2Xt8dJ8a+WQycZVNrulvu22s4jf1nbmWqfzZ1KaACyioTU3dYR57oQzfXoFcIj5Nprdnb1RJ83Rf7OTrXcPja/Te3I8lgaV0wFVdI0z7+v2h+bOx6ChQCYD4P5qMFGGiXq2HpnelMktB3Ti2T21RRZzJOKDfCoa2fmqYehmXmahOmul/UIETCa0SEiRkT1bh00RG/3s7r9d5rJ3LsbrzflqGjCWmS+K3uEGN78tlvzevnN//kf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INT_KEY = _t, DocumentNo = _t, Rev = _t, STS = _t, StsRcvd = _t, Title = _t, Received = _t, Schedule = _t, Forecast = _t, Authorised = _t, Discipline = _t, Type = _t, Reason = _t, Remarks = _t, CLOSED = _t, EPO = _t, EPO1 = _t, EPO2 = _t, PO3 = _t, PACKAGE1 = _t, PACKAGE2 = _t, PACKAGE3 = _t, PACKAGE4 = _t, PM = _t]),
    addProjectName = Table.AddColumn(Source, "Project Name", each "110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT"),
    groupDocNoProject = Table.Group(addProjectName, {"DocumentNo", "Project Name"}, {{"data", each _, type table [INT_KEY=text, DocumentNo=text, Rev=text, STS=text, StsRcvd=text, Title=text, Received=text, Schedule=text, Forecast=text, Authorised=text, Discipline=text, Type=text, Reason=text, Remarks=text, CLOSED=text, EPO=text, EPO1=text, EPO2=text, PO3=text, PACKAGE1=text, PACKAGE2=text, PACKAGE3=text, PACKAGE4=text, PM=text, Project Name=text]}}),
    addMaxCalc = Table.AddColumn(groupDocNoProject, "max", each if [data][Rev] <> "0.1" then Table.Max([data], "INT_KEY") else null),
    expandMaxCalc = Table.ExpandRecordColumn(addMaxCalc, "max", {"INT_KEY", "Rev"}, {"INT_KEY", "Rev"}),
    remDataCol = Table.RemoveColumns(expandMaxCalc,{"data"})
in
    remDataCol

 

 

The output looks correct to me, but I think there was only one example of a "0.1" report in the sample dataset, so difficult for me to confirm that it is working correctly.

Adjust the expandMaxCalc step to bring in more of your fields for testing.

 

Pete

View solution in original post

6 REPLIES 6
Highlighted
Super User II
Super User II

Re: Find MAX based on two criteria

Hi @CorneGeyser ,

 

Would it be possible to share just that visible page of data in table format please? It appears as though your [DocumentNo] naming convention is very well implemented, so I think there may be simple way to do this using that field alone, but I'd like to test it first.

 

Pete

Highlighted
Regular Visitor

Re: Find MAX based on two criteria

Sure Pete.

 

INT_KEYProject NameDocumentNoRev
366110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HDR-MEC-J709-DDT-008-04A.1
93110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-**bleep**-TNA001-01A.2
132110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-PRO-J709-HAZ-001-01B.1
137110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HMN-GEN-J709-SCH-001-01B.1
160110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-MS02A-06B.1
161110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-MS03A-23B.1
162110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-MS04A-09B.1
163110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-PS03A-01B.1
164110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-MS04A-08B.1
165110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HPR-QUA-J709-QCP-SS01A-01B.1
167110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HMN-DCT-J709-STD-001-01B.1
169110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-001-01-
170110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-002-01-
171110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-003-01-
172110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-004-01-
173110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-005-01-
174110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-006-01-
175110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-007-01-
176110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-008-01-
177110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-B02-009-01-
178110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-001-01-
179110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-002-01-
180110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-003-01-
181110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-004-01-
182110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-005-02-
183110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-006-01-
184110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-007-01-
185110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-008-01-
186110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D01-009-01-
187110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-001-01-
188110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-002-01-
189110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-003-01-
190110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-004-01-
191110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-005-01-
192110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-006-01-
193110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-007-01-
194110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-J709-IS01A-D34-008-01-
268110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-AIT-TNA001-010.1
271110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-LIT-TNA001-010.1
272110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-LIT-TNK001-010.1
273110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-LSL-TNK002-010.1
274110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT110942-HEN-CAI-J709-DS-LSL-TNK003-010.1
Highlighted
Super User II
Super User II

Re: Find MAX based on two criteria

Hi @CorneGeyser ,

 

I'm hoping I've understood your requirements correctly, but you will need to check.

 

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZc9b4MwEIb/SsSI4sofBOzRAVpo+QqQpVGWStk69P9PpXbU5HA63XVh4p6Xk++RudMpUmkabSMhuEkke824YVPf1k2/afrj8nwux7HON9OxGSo7l5uhsd18K6iKkbVl7guLYmaca8aT5QX7JKLz9hQZhcGXHcttfcVPLI4/Pi+Xrzhmc2c5F4wLFyVdlFASmTWMvS+s7Dv75e+vrQiVYfhtx16WDF+YVw/4Kcfwh5EdjtYXHvKBtROXlvEUJAjiBGWZVCABdQYPEpKlBwMSUBO1ThhcD6tzSP6hBw0SdpQJ08RF0AN2Vot8vhbOxaNZNQi+K6jdV++5vNGZZ2cYDwK2hGyMAQFbQTZm9gN2AtmYqQ/YO8jGzHvATiEbM+kBO4NszNUVsDVkY/wJ2AayNQ27+PFm7Q6Rl54N3dFEXno2dEcTeenZ0B1N5KVnL+7IOzaRl54N3dFEXno2dEcTeenZ0B1N5KVnQ3c0kZeFSgJ3NJWXjr1yh8pLx4buGCovHRu6Y6i8dGx47xgqLx0buoPaOwI2dMdQeenYwB2ZYmZwvS/ZegabEr/+vUnUX9A6pfkzBbuPhSlvD1IoN8xmanyKXKegdoK/UtRdyvkb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INT_KEY = _t, #"Project Name" = _t, DocumentNo = _t, Rev = _t]),
    dupeDocNo = Table.DuplicateColumn(Source, "DocumentNo", "docNo"),
    splitEndDigits = Table.SplitColumn(dupeDocNo, "docNo", Splitter.SplitTextByPositions({0, 6}, true), {"Document", "Revision"}),
    groupDocument = Table.Group(splitEndDigits, {"Document"}, {{"data", each _, type table [INT_KEY=number, Project Name=text, DocumentNo=text, Rev=text, Document=text, Revision=text]}}),
    addMaxRevision = Table.AddColumn(groupDocument, "max", each Table.Max([data], "Revision")),
    expandMaxRevision = Table.ExpandRecordColumn(addMaxRevision, "max", {"INT_KEY", "Project Name", "DocumentNo", "Rev", "Revision"}, {"INT_KEY", "Project Name", "DocumentNo", "Rev", "Revision"}),
    remOthCols = Table.RemoveColumns(expandMaxRevision,{"Document", "data", "Revision"})
in
    remOthCols

 

 Summary:

1) Split last 6 digits from [DocumentNo] as these appeared to be revision numbers independent of the [Rev] field, and called these two new fields [Document] and [Revision].

2) Grouped table by [Document].

3) Retained rows where [Revision] was the max in the [Document] group.

4) Expanded rest of the table back out and removed created fields.

 

Pete

Highlighted
Regular Visitor

Re: Find MAX based on two criteria

Thanks for the assistance Pete. However, the DocumentNo column does not contain a document revision. The numbers that you are extracting are sheet numbers.

 

Below is a link to a sample document for more information:

 

Sample document 

 

The source table has 100 line items. Items 98 - 100 is the same document but they are all different revisions. If you follow my query steps as on this sample document, the document with 'Rev' as 'B.2' will be the latest revision. However in this case the actual latest revision is actually '0.1'. '0.1' should always be the latest revision.

Highlighted
Super User II
Super User II

Re: Find MAX based on two criteria

Hi @CorneGeyser ,

 

Apologies for the delay.

Please try this code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZzdkppKEMdfhdrrcHZARM3dCLPKCV81jK5JKu9w3v/qDKgoMNPMuESbKkKyaoRfTff0vz/Y37/fvLdvb55HNoHv7mPulrxw/12RjVsmsUuI55L6A/Sf+lxweXLlnw/GeRI51SEt91QwJyryI+NVUuTyTRK+E//dJz5xCPneHPWr3rs8vA0xeFUe8jbqcxLXVyu4k+TynFFxvsTI8efb7zcfxPKNsCpRcLpjDs1jJy6qJN+hwVuAeAsl3v5nzIsdy52S8eKUxAwbVABCBWeouMapGItfQFVftrmwAczSzK9qO/2MXuJXe/qrKOU1sozlojKjCs3cCqR6jls9Qrcy86oO3TPt7xGmtYlT9ZiapTrUi0ajBNsibcwcq94hXxSwklwwntP0CmbG5REz3wLBnuNbDwLCUmOhBHymez2IBUuNQIn1NA97kAnWFx0nM4tenjcxGGcRS44sdj54kTkPYsKKwzfCVLocTlxYkyyUuIAD4oSEJUqghNS5I07Ce5nCcpel0ZkwTSphnLOVKc2FkxXSep36P8qP+FPQyruR58s39gGdK78h6XqU1DckfQqeIdVmlOq2ftptZw7r55NRUt+QtMYLvorXSEx7wCqqg0H7I6uIFw1ZvS5rxi6sceXSnd4vs+Qk9xpH0PyHQ3eJoKJ5y59CCMh7kOe4ethUfV8P9UFzLVQV8cN2y7gjP4QHZqGH2QM75764uBcOikBPUUaplkJwmlfStZzykJV4aJZjNOpSXWtguGhCgCbT11MBjYUEbDUGplkmja5CQrUeo1IXUvsBChvWZgwrmCPWoicmIprcAmwiXJFTnX/9t3cyJq85FYu89BdZPD1LCrOk7MhSp9nNs0SggvLHoH7MEGoBQFXpGUq9+Z2hqs9ERHs8PIEBj3rbw8mz1POUsCeVnFXVgTOcdhcCXPxYb+S6dWq5OEsT9uEcaXpkeMBWBmDqAIUcbK0HE7AlCpZJ5UcFWmPc6NFOR4iMRuIgtUSMbK0Cay3RVifutcQaBYu1lmhZXhF2LUtIgbWomBWdtbro0T0tGtuCWcuMuYBZ640W7EV6wxbQWngMAY3is796JwtD7g/xAGNdtm0ameejrIhPh7jWcmTeuNYipcV9nUixtWBbtdIiPl2tWJItrWRLHTmVJRAUsmVpJVs6LMrAjgPKSq3MBcpKpCigbrEcBw/QQVF06trdQdepmwIKqI7a7hGAQBkqrxmsFtBTUbQgbyMdvRYkslUCGiqKXuRtGufai0SGA8iKoSruGJ1OFeMwPqiVMmy23rRSv9mKa7lCMoalzjkHXVdkWICkGIrbjhUC4haFIYbAIIais2wyvYdt8YDpDEWLeXR0DxseUBAZJCUd0xwmJTgsEprcGDbPtdNdaBdsdJZDXRWYDZ+u8AFOkSZ5JfihuVw6oRI5W+OXpipDQIKoc2NVSV+iGFdpnpDwh0ApQ50kg9VvZHQroJyhzpZnRQcVOJRps64Ejg0MKnIoM8y5gAGFDnUWM1bbxwYIiBC1QDao/mJjBOofSqGlr/5iI9N1ZsDnBoYRe0KsaR4TWJk+0NLRxQYPREjMr5NO8MTHqqdNyqT86jDPJGjyPnS2aQi2MQCzG+bBAbYmBmB2XVCy/EtgnB0T9mnqbGvPgMyu4YmFTKdLLtuI2sGSot0tpsBQ74tnEEOMngoBd0Obx8Mm4VPvhpYLBciQfqq2HUxy18/ZFryUf9P056+zBAmnDdQP/faSNdR4gamU2QwKJmAYpJ+hzYYJmPjo52VKplv6ggIHqHv0s7E54AAVj34OttUoCoRGtwFKHb2spEc1zEpQ8OiKG90gtR2rHk4Ic/nGx3CAkoYAjQ7Ii1GsE1DSUGf8pwtYRPOIpWkT09UZoxe8k5Xhq6cuy/2Xt8dJ8a+WQycZVNrulvu22s4jf1nbmWqfzZ1KaACyioTU3dYR57oQzfXoFcIj5Nprdnb1RJ83Rf7OTrXcPja/Te3I8lgaV0wFVdI0z7+v2h+bOx6ChQCYD4P5qMFGGiXq2HpnelMktB3Ti2T21RRZzJOKDfCoa2fmqYehmXmahOmul/UIETCa0SEiRkT1bh00RG/3s7r9d5rJ3LsbrzflqGjCWmS+K3uEGN78tlvzevnN//kf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INT_KEY = _t, DocumentNo = _t, Rev = _t, STS = _t, StsRcvd = _t, Title = _t, Received = _t, Schedule = _t, Forecast = _t, Authorised = _t, Discipline = _t, Type = _t, Reason = _t, Remarks = _t, CLOSED = _t, EPO = _t, EPO1 = _t, EPO2 = _t, PO3 = _t, PACKAGE1 = _t, PACKAGE2 = _t, PACKAGE3 = _t, PACKAGE4 = _t, PM = _t]),
    addProjectName = Table.AddColumn(Source, "Project Name", each "110942-J709-SOMILO LOULO FERRIC SULPHATE PLANT"),
    groupDocNoProject = Table.Group(addProjectName, {"DocumentNo", "Project Name"}, {{"data", each _, type table [INT_KEY=text, DocumentNo=text, Rev=text, STS=text, StsRcvd=text, Title=text, Received=text, Schedule=text, Forecast=text, Authorised=text, Discipline=text, Type=text, Reason=text, Remarks=text, CLOSED=text, EPO=text, EPO1=text, EPO2=text, PO3=text, PACKAGE1=text, PACKAGE2=text, PACKAGE3=text, PACKAGE4=text, PM=text, Project Name=text]}}),
    addMaxCalc = Table.AddColumn(groupDocNoProject, "max", each if [data][Rev] <> "0.1" then Table.Max([data], "INT_KEY") else null),
    expandMaxCalc = Table.ExpandRecordColumn(addMaxCalc, "max", {"INT_KEY", "Rev"}, {"INT_KEY", "Rev"}),
    remDataCol = Table.RemoveColumns(expandMaxCalc,{"data"})
in
    remDataCol

 

 

The output looks correct to me, but I think there was only one example of a "0.1" report in the sample dataset, so difficult for me to confirm that it is working correctly.

Adjust the expandMaxCalc step to bring in more of your fields for testing.

 

Pete

View solution in original post

Highlighted
Regular Visitor

Re: Find MAX based on two criteria

Thanks Pete. This is way more efficient than my previous steps. Everything seems to be in order. I will have to do some checks but this looks great! Thanks for the help.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors