Collating test results from related rows

[jpc]

I have source data tables of Tests and Results.   Some tests have explicit test results, but some don't; in the tests table they are defined as being dependent on other tests.

My objective is to add a column to the results table which is the consolidated pass/fail result, ie R3 is deemed a pass because R1 is a pass;  R4 is a fail (it is deemed a pass only if R1 and R2 pass).   How do i approach this?

Tests

Test	Dependency
R1
R2
R3	R1
R4	R1 & R2

Results

Test	Result
R1	Pass
R2	Fail
R3
R4

[v-deddai1-msft · Accepted solution]

Hi @jpc ,

Would you please try to split the dependency into rows by "& " in query editor:

Then create a new relationship between result table and test table:

 Create a new column in test table: 

Result = IF("Fail" IN CALCULATETABLE(VALUES(Results[Result]),FILTER(Tests,Tests[Calculated Result] = EARLIER(Tests[Calculated Result]))),"Fail","Pass")

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERrFulLwM8pHjyUo9O...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[ryan_mayu]

@jpc 

not clear about the logic, R3 is deemed a pass because R1 is a pass. Why in the result , R1 is pass and R3 is blank?

[jpc]

Thanks for taking time to read and try to understand my problem.

There is no test run for R3 and R4, so the dataset doesn't contain any pass/fail information.   

I see what you're saying, the results table would actually be like this (no result exists for R3 and R4)

Results

Test	Result
R1	Pass
R2	Fail

And i wonder if it helps to rename the Tests/test column as calculated result and explicitly state the implied logic behind R1 and R2 like this:

Tests

Calculated Result	Dependency
R1_calc	R1
R2_calc	R2
R3_calc	R1
R4_calc	R1 & R2

Now, I think I can see that having a relationship between the "Test" fields in both my originally defined tables was not the right approach.   But because i can have multiple dependencies, i'm not sure that adding a relationship between Dependency and Test is valid either.