Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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?
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 🙂
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |