I have a denormalized table with samples, tests and results.
Tests have a parent/child relationship e.g. Test 1 is a parent test to 1a, 1b and 1c.
I created a measure to calculate the number of times each test has been run:
Test count = COUNTROWS([Table])
I want a second measure that calculates for each test, the number of times the parent test has been run. For example, Test 1a, Test 1b and Test 1c would all evaluate to 4 becasue their parent test "Test 1" was run four times.
Thank you Owen. This alternative to TREATAS works.
As an aside, I have some cases where no child tests exsits so the parent test count doesn't get calculated. For example, 100 samples are screened for a drug (parent test) but none are positive so no confirmations (child test) were done. But I still want to see the number of parent tests.
My understanding is that when no rows exist for 'Table'[Test name] then VALUES ( 'Table'[Parent test] ) will return null (and it does this for either the TREATAS way or the FILTER way). I have got around this problem by replacing VALUES ( 'Table'[Parent test] ) with CALCULATETABLE ( VALUES ( 'Table'[Parent test]), ALLEXCEPT ( Table, 'Table'[Parent test] ).
Is there a more efficient way to still calculate parent test count when no child tests exist?