DAX measure to calculate parent total from child evaluation context

[djdonnel]

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. 

I have tried this: 

Parent test count = 
CALCULATE ( [Test count],
     FILTER(Table, 
          Table[Test name]=Table[Parent test]
     )
)

I am aware that this formula doesn't work because the filter is for rows where the test name and parent test are equivalent.

But I don't know how to re-write it to get the intended result.

[OwenAuger · Accepted solution]

Hi @djdonnel

You can use the TREATAS function to apply the values of Parent test as a filter on Test name.

The measure would look like this:

Parent test count = 
CALCULATE (
	[Test count],
	TREATAS ( VALUES ( 'Table'[Parent test] ), 'Table'[Test name] ),
	ALL ( 'Table'[Parent test] )
)

We also need to clear the Parent test filter (otherwise we would be trying to count rows where Parent test = Test name), so ALL ( 'Table'[Parent test] ) is included as a filter argument.

Regards,

Owen

[djdonnel]

Thank you Owen,

This formula works in Power BI, but what if I want to replicate it in Power Pivot which doesn't have the TREATAS function?

[OwenAuger]

This should work in Power Pivot regardless of version:

Parent test count =
CALCULATE (
    [Test count],
    FILTER (
        ALL ( 'Table'[Test name] ),
        CONTAINS (
            VALUES ( 'Table'[Parent test] ),
            'Table'[Parent test], 'Table'[Test name]
        )
    ),
    ALL ( 'Table'[Parent test] )
)

Also have a look at this page for alternative patterns that work in Excel 2016.

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Owen 🙂

[djdonnel]

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?