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

Need help to filter data from table based on latest entry

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.

 

 2017-07-24_1745.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Need help to filter data from table based on latest entry

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])))


1.PNG

Best Regards,
Angelia

4 REPLIES 4
v-huizhn-msft Super Contributor
Super Contributor

Re: Need help to filter data from table based on latest entry

Hi @prashantvns,


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

1.PNG

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.

2.PNG

Best Regards,
Angelia 

prashantvns Frequent Visitor
Frequent Visitor

Re: Need help to filter data from table based on latest entry

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

 

2017-07-25_1005.png

 

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 Super Contributor
Super Contributor

Re: Need help to filter data from table based on latest entry

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])))


1.PNG

Best Regards,
Angelia

prashantvns Frequent Visitor
Frequent Visitor

Re: Need help to filter data from table based on latest entry

Hi, 

 

Your solution worked but one more thing Smiley Happy 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