## Desktop

Frequent Visitor
Posts: 6
Registered: ‎07-10-2017

# DAX measure to calculate parent total from child evaluation context

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.

Accepted Solutions
Super User
Posts: 634
Registered: ‎02-29-2016

## Re: DAX measure to calculate parent total from child evaluation context

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

All Replies
Super User
Posts: 634
Registered: ‎02-29-2016

## Re: DAX measure to calculate parent total from child evaluation context

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

Frequent Visitor
Posts: 6
Registered: ‎07-10-2017

## Re: DAX measure to calculate parent total from child evaluation context

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?

Super User
Posts: 634
Registered: ‎02-29-2016

## Re: DAX measure to calculate parent total from child evaluation context

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

Highlighted
Frequent Visitor
Posts: 6
Registered: ‎07-10-2017

## Re: DAX measure to calculate parent total from child evaluation context

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?