Need help to filter data from table based on latest entry

[prashantvns]

Hi,

Refering "Existing Data table" basically this an auto test data where  on specific dates different machines "Partition Key"  shows the total number of test case "Count" being executed.

Current Problem : In any specific day when auto test is runned multiple times on same machine then we log the "TestRunID" and associated number of test case executed along with that RunID.  I need to show only one entry in reports based on the latest TestRunID. Example :  Please check the new data table where highlighted Red test need to be ignored or to be avoid in the reports. Currently when I plot in the reports it shows 2 times for both TestRunID.

 

Please suggest me how to  avoid the lowest  TestRunID and keep highest for spefic dates and machine 

[v-huizhn-msft]

Hi @prashantvns,

I create similar sample table as follows and get expected result.



Then click "New Table" under Modeling on Home page, type the formula.

New = SUMMARIZE('Existing','Existing'[PartitionKey],'Existing'[TestRunTime],"TestRunID",MAX('Existing'[TestRunID]),"Count",FIRSTNONBLANK('Existing'[Count],1))

Please see the expected result shown in the following screenshot.



Best Regards,
Angelia 

[prashantvns]

Thanks for your help but the solution you have given is not working fully 😞 and I still need  your help

If you see that the count for TestRunID(2) is 1308 but in your solution it come as 1302

to conclude the final row should be A-----7/17/2017------2-----1308.  

Thanks again and wating for your resposne 

[v-huizhn-msft · Accepted solution]

Hi @prashantvns,

Please use the following formula and get expected result.

New1 = FILTER('Existing','Existing'[TestRunID]=CALCULATE(MAX('Existing'[TestRunID]),ALLEXCEPT('Existing','Existing'[PartitionKey],'Existing'[TestRunTime])))

Best Regards,
Angelia

[prashantvns]

Hi, 

Your solution worked but one more thing 🙂 Is it possible to execute this in Query view instead of Data model view.  I need to do this calculation in query view... 

Thanks

Prashant