cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djdonnel Frequent Visitor
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
OwenAuger Super Contributor
Super Contributor

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




djdonnel Frequent Visitor
Frequent Visitor

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?

OwenAuger Super Contributor
Super Contributor

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




djdonnel Frequent Visitor
Frequent Visitor

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?

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 253 members 2,654 guests
Please welcome our newest community members: