I am facing the following challenge in DAX, I created some mock tables to help illustrate.
| ID | UniqueID | TestResult | S_Req | M_Req | E_Req | Test | R_Req |
| 1 | AA | | 1 | 0 | 0 | 0 | 0 |
| 2 | BB | Failed | 0 | 0 | 0 | 1 | 0 |
| 3 | CC | | 0 | 1 | 0 | 0 | 0 |
| 4 | DD | Passed | 0 | 0 | 0 | 1 | 0 |
| 5 | EE | | 1 | 0 | 0 | 0 | 0 |
| 6 | FF | Passed | 0 | 0 | 0 | 1 | 0 |
| 7 | GG | | 1 | 0 | 0 | 0 | 0 |
| 8 | HH | Not Done | 0 | 0 | 0 | 1 | 0 |
| 9 | II | | 0 | 0 | 1 | 0 | 0 |
| 10 | JJ | Failed | 0 | 0 | 0 | 1 | 0 |
| 11 | KK | | 0 | 1 | 0 | 0 | 0 |
| 12 | LL | Passed | 0 | 0 | 0 | 1 | 0 |
| 13 | MM | | 1 | 0 | 0 | 0 | 0 |
| 14 | NN | | 0 | 0 | 0 | 0 | 1 |
| ID | LinkedID |
| 1 | II |
| 1 | CC |
| 3 | HH |
| 3 | BB |
| 5 | CC |
| 7 | KK |
| 9 | FF |
| 9 | BB |
| 11 | HH |
| 11 | LL |
| 13 | NN |
The second table acts as a "dictionary" that connects rows from Table 1 to other rows from Table 1.
For example:
- ID 1 can be found twice in table 2. Once linked with "II" and a second time linked with "CC".
This means that ID 1 from the first table is asociated with IDs 9 and 3, respectively.
- Next, values "CC" and "II" are themselves connected (based on Table 2 data) to "JJ", "HH" and "FF","BB".
These four can be found back in Table 1 as "test rows" as they have a "TestResult" value associated with each (and a 1/0 value for the Test column).
I am looking for a formula/measure that will count how many S_req rows have at least 1 "Passed" test case connected to it (based on the connections described in Table 2) as well as no "Failed" test cases connected to it, also only E_Req and M_Req will have links to Test. I do have a measure for the first layer of links that checks how many S_Req have at least one link with E_Req or M_Req:
Number of Linked S_Req =
VAR a =
CALCULATETABLE (
VALUES ( Table1[ID] ),
FILTER ( Table1, Table1[S_Req] = 1 )
)
VAR b =
CALCULATETABLE (
VALUES ( Table1[UniqueId] ),
FILTER ( Table1, [E_Req] = 1 || [M_Req] = 1)
)
VAR c =
CALCULATETABLE (
VALUES ( Table2[ID] ),
FILTER ( Table2,[LinkedID] IN b)
)
VAR d =
INTERSECT (c, a)
RETURN
COUNTX ( d, [ID] )
EXAMPLE CALCULATION: - For row ID=1 (AA), we can use Table 2 to conclude that "AA" is connected to "II" and "CC". - Then, by using Table 2 again, we conclude that "II" and "CC" are connected (as a second layer) to "BB", "FF", "HH", and "LL" - For rows "BB", "FF", "HH" and "LL" the values for "TestResult" contain no "Failed" values and at least one "Passed".
- The result for this is 0, because "BB" is unfortunately a failed test.
CALCULATION 2: For row ID=5(EE) by the same logic the result will be 0 beacause will have a failed test.
EXAMPLE CALCULATION 3: - For row ID=7 (GG), we can use Table 2 to conclude that "GG" is connected to "KK". - Then, by using Table 2 again, we conclude that "KK" is connected (as a second layer) to "HH" and "LL" - For rows "HH" and "LL" the values for "test Result" contain no "Failed" values and at least one "Passed" - The result for this is 1, because there is a "Passed" test and none failed.
Row 13/MM is having a link with an R_Req which it doesn't have an Test link, only E_Req and M_Req have Test links
So the final result will be the sum of all these 3 calculation, so the result is 1.
