cancel
Showing results for
Did you mean:
Highlighted
djdonnel Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor

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

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

Proud to be a Datanaut!

4 REPLIES 4
OwenAuger Super Contributor

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

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

Proud to be a Datanaut!

djdonnel 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

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 Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

djdonnel 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?

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 219 members 2,488 guests
Recent signins:
• 2ccccc • MarkDengler • oscarcazalez1 • allen3just • doubles • otonielR • kleetus51 • JGreen • bmacquoid • zhgolden • jdkdc • hkhuong • Cindycy52 • BlastAM 