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

DAX measure to calculate parent total from child evaluation context

I have a denormalized table with samples, tests and results.

 

Table.PNG

 

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])

Report.PNG

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: 627
Registered: ‎02-29-2016

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

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

View solution in original post


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

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

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

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: 627
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 Smiley Happy

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?