Need Urgent help with picking latest test result for a across builds & runs.

[srikiran]

Hi,

We are trying to solve below problem. Can some one help me how to achieve this in measures in DAX.

Requirement:

We have a test tracking dashboard where we want to track the Pass% for all test cases. In our scenario, some tests can run multiple times on same build and also some other tests run on different build.

Pass %  measure that we want to calculate (No. of distinct test cases where its latest result = passed  across builds in selected build numbers filter by user )  / No. of distinct test cases with passed or failed in their latest results in selected build numbers filter by user).

If its C# / SQL, I would do something like for each test case, get the latest build id / max completed date and against that record what is the result and do count on those. I am unable to achieve that in DAX measures. Can someone please help me on how to achieve above pass%

Below is one example:

In below example

Scenario 1:

when user selects build number filter as “2.1.1285.20244” then Pass % should be  (5 / 9)  * 100 = 55.5%  - as three test cases failed in their latest run (latest run is either by buildid or max completed date) and there are 9 distinct test cases in total which are either passed or failed in their runs. So it should be (5/9) * 100 = 55.5%

Scenario 2:

When user selects build number filter as “2.1.1292.1708” then pass% should be (3/4) * 100 = 75% as three tests passed in latest run.

Scenario 3:

When user selects build number filter as both i.e (2.1.1285.20244, 2.1.1292.1708) then it should be (5/12) * 100 as only 3 test cases passed in their latest runs ( latest run is determined either by max completed date or buildid) and there are 13 distinct test cases in total which  are either passed or failed. So it should be (5/12) * 100 = 41.6%.

We are trying to achieve above using measure and unable to get latest test result for each test case and do a count on how many have passed in that. I tried using Calculate, summarize and Filter and unable to achieve cover all scenarios. When I tried below measure I was able to achieve selecting latest result with in a build and that too it doesn’t work if all buildid doesn’t have all test cases ( below is what I tried).

Can you or someone from your team please help me on how to achieve this in measures so that it can calculate right values and changes accordingly based on user selection? I am happy to jump on a quick call to discuss this if need more clarification on this email.

Measure I tr

Pass % = DIVIDE(CALCULATE((DISTINCTCOUNT('Work Items'[ID])) , FILTER('Work Items','Work Items'[ATR.CompletedDate] = MAX('Work Items'[ATR.CompletedDate])), 'Work Items'[ATR.Outcome] = "Passed"),CALCULATE(DISTINCTCOUNT('Work Items'[ID]), FILTER('Work Items','Work Items'[ATR.CompletedDate] = MAX('Work Items'[ATR.CompletedDate])),'Work Items'[ATR.Outcome] = "Passed" || 'Work Items'[ATR.Outcome] = "Failed")

ID	Priority	Outcome	BuildId	Build Number	Completed Date
5657906	1	Passed	33712136	2.1.1285.20244	8/11/2020 17:27
7674420	1	Passed	33712136	2.1.1285.20244	8/11/2020 17:27
7674454	1	Failed	33712136	2.1.1285.20244	8/11/2020 17:27
7674455	1	Failed	33712136	2.1.1285.20244	8/11/2020 17:27
7674529	1	Failed	33712136	2.1.1285.20244	8/11/2020 17:27
7674530	1	Passed	33712136	2.1.1285.20244	8/11/2020 17:27
5657906	1	Failed	33641364	2.1.1285.20244	8/9/2020 3:02
7674420	1	Passed	33641364	2.1.1285.20244	8/9/2020 3:02
7674454	1	Passed	33641364	2.1.1285.20244	8/9/2020 3:02
7674455	1	passed	33641364	2.1.1285.20244	8/9/2020 3:02
7674529	1	passed	33641364	2.1.1285.20244	8/9/2020 3:02
7674530	1	Passed	33641364	2.1.1285.20244	8/9/2020 3:02
7674350	1	Passed	33641364	2.1.1285.20244	8/9/2020 3:02
7674402	1	Passed	33641364	2.1.1285.20244	8/9/2020 3:02
7947401	0	Failed	33641364	2.1.1285.20244	8/9/2020 3:02
7673909	1	Passed	32960773	2.1.1292.1708	7/17/2020 16:55
7947334	0	Failed	32960773	2.1.1292.1708	7/17/2020 16:47
7947445	0	Passed	32960773	2.1.1292.1708	7/17/2020 14:41
7947401	0	Passed	32960773	2.1.1292.1708	7/17/2020 13:34
7673909	1	Failed	32922025	2.1.1292.1708	7/16/2020 17:20
7947334	0	Passed	32922025	2.1.1292.1708	7/16/2020 15:38
7947445	0	Failed	32922025	2.1.1292.1708	7/16/2020 14:35
7947401	0	Failed	32922025	2.1.1292.1708	7/16/2020 13:30

[Fowmy]

@srikiran 

Should Scenario-1 be (3 / 6)  * 100 = 50.00% ?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[srikiran]

Hi,

There are 9 distict test cases (based on test caseid) for build 2.1.1285.20244 and some ran on 1st build (build runid = 33641364) and 2nd build (build runid = 33712136) 

and out of those 5 are passed.

5657906
7674420
7674454
7674455
7674529
7674530
7674350
7674402
7947401

[srikiran]

Hi,

Below are the test cases passed in the list.

5657906

7674420

7674530

7674350

7474402

To simply I am looking something like below logic in Dax measures.

In all selected build numbers (build number needs to be a page level filter on dashboard so that user can select)

          for each distinct test case

               get the latest result (based on max completed Date or Max BuildId column) 

               Perform a count on result to know how many passed and how many failed to get pass%.

Hope this helps you to understand problem. Please let me know if this can be achieved in PowerBI measures so that users can filters on specific builds and we can do pass%.

[Fowmy]

@srikiran 

Please check the solution I came up with. Add the following measure to calculate the %. Let me know if you need the count of Passed and Failed as separate measures that can be extracted from this measure.

You can download the file: HERE

Passed = 
VAR _MAXBN = MAX('Work Items'[BuildId])
VAR _LATEST = 
CALCULATETABLE(
    VALUES('Work Items'[ID]),
    'Work Items'[BuildId] = _MAXBN
)

VAR _COUNTLATESTPASS = 
COUNTROWS(
     CALCULATETABLE(
         VALUES('Work Items'[ID]),
         'Work Items'[ATR.Outcome]="Passed",
         'Work Items'[BuildId] = _MAXBN
    )
) 

VAR _COUNTPREVPASS = 
COUNTROWS(
    CALCULATETABLE(
        VALUES('Work Items'[ID]),
        'Work Items'[ATR.Outcome]="Passed",
        'Work Items'[BuildId] < _MAXBN, 
        NOT 'Work Items'[ID] IN _LATEST
 )
) 

RETURN
DIVIDE(
   _COUNTLATESTPASS + _COUNTPREVPASS,
    DISTINCTCOUNT('Work Items'[ID])
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[srikiran]

Thanks Fowmy for this code. I tried but when user selects multiple builds then this code is not working as i believe this below condition doesn't count properly as it will count all passed test cases which are not in latest build and after latest build, it won't look for latest build again for each test cases which are missing in MaxBuildid. 

I really appreciate for trying this and giving it to me. I learnt other things here on how to do multiple things in measures. 

Thanks a lot. 🙂

VAR _COUNTPREVPASS = 
COUNTROWS(
    CALCULATETABLE(
        VALUES('Work Items'[ID]),
        'Work Items'[ATR.Outcome]="Passed",
        'Work Items'[BuildId] < _MAXBN, 
        NOT 'Work Items'[ID] IN _LATEST
 )
) 

[Fowmy]

Thanks @srikiran ,

It was great learning for me as well, I couldn't grasp the question correctly, especially the 3rd scenario. Your expectation was 
(5/12) * 100 but not sure how it is supposed to be (7/12) * 100

Thanks

[harshnathani · Accepted solution]

Hi @srikiran ,

You can create these measures

Last Run Status = 
var a = CALCULATE(MAX(Table4[Completed Date]), ALLEXCEPT(Table4,Table4[ID],Table4[Build Number]))
RETURN
CALCULATE(MAX(Table4[Outcome]), FILTER(Table4,Table4[Completed Date] = a))

Test Count = CALCULATE(DISTINCTCOUNT(Table4[ID]),FILTER(VALUES(Table4[ID]),[Last Run Status] = "Passed"))

Total Id = DISTINCTCOUNT(Table4[ID])

Pass Percent = DIVIDE([Test Count],[Total Id])

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[srikiran]

Hi @harshnathani ,

I tried your solution and it is working only partially and unable to get what is really happening with data. Can you please help me with this.

I am sharing file here with real data, where you can see that i am trying to find Priority 0 tests % (i.e. Pass P0 % measure) based on their last known result across builds and its showing 0 when all Builds are selected though you can see in file that all Priority 0 tests are passed in their last run ( when sorted by completed date in descending order). Where as when a particular build in which P0 tests ran is selected then its showing correct. Also, for other measures like Priority 1 (P2) tests %, its showing correct value even when i don't select any build and its picking last known result which is not happening for P0 or P1 though their is no change in measure formulae for P0 pass % , P1 pass % and P2 pass % except priority of test cases condition that is added.

Also its showing incorrect values when we change with branches and other date filters which i am confused why its behaving like this. For example, if you select Master in Branch filter then it shows correct values for P0 pass %where as it doesn't show correct values for p2 pass % and if you select Aug2020 in Branch filter then it shows correct values for P2 but not for P1 , P0 and confused what is happening.

I am unable to get what is happening and why its showing correct values. Can you please help me here.

If you need any clarification is needed in understanding forumulae then feel free to reply here and i will help.

Also, in pass % forumulae, first part IsBlank(......) is kept to handle scnearios where data is 0 in denominator. so if condition and else condition logic is same in pass % measures.

Thanks,

Sri

[srikiran]

@harshnathani - Please let me know if you got a chance to look at it. If you need any information then do let me know and i will provide.

[srikiran]

Thanks Harsh. This worked like a charm for all scenarios. Really appreciate it. 👏👏

[Fowmy]

@srikiran 

Can you mark the 5 IDs that are passed here?
I not getting it.

5657906

7674420

7674454

7674455

7674529

7674530

7674350

7674402

7947401

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn