Hello, i need to find a measure with the following logic:
Firstly, a link is dictated by Table 2 (LinkedID column) back to Table 1 (UniqueID column) = LinkedID -> UniqueID
For example: ID 1/UniqueID AA is a S_Req and has links with ID 9/UniqueID II because ID 1 is found in Table 2 with LinkedID = II which back in Table 1 will be ID9/Unique II and this is an E_Req, next link is to ID 3/UniqueID CC(M_Req) and from these links(S_Req linked with E_Req or M_Req) also with the help of Table2 will determine the links with an Tst, so from ID 9/UniqueID II will have link to ID 6/UniqueID FF(Tst - Passed) and ID 2/UniqueID BB (Tst - Failed) and ID 3/UniqueID II will have a links to ID 10/UniqueID JJ (Tst - Failed) and ID 8/Unique HH(Tst - Not Done)
I need a measure that calculates the total number of S_Req having at-least one TST with the TestResult "passed" and none "failed" via M_req or E_Req links = 1 =
= 0 (explanation: as explian above, there's an TST with "Failed" result, so the other doesn't matter) +
+ 0 (explication: ID 5/UniqueID EE -> ID 3/UniqueID CC(M_Req) -> ID 10/UniqueID JJ (Tst - Failed) the other link doesn't matter) +
+ 1 (ID 7/UniqueID GG(S_Req) -> ID 9/Unique II(E_Req) -> ID6/UniqueID FF (Tst - Passed)
-> ID 11/Unique KK(M_Req) -> ID8/UniqueID HH (Tst - Not Done) )
That means will be a thrid layer intersection as i explian earlier:
Someone actually helped me with a measure to get a total number of S_Req that have at least a link with M_Req or E_Req and that's only one layer of intersection:
Measure =
VAR _tab1 =
CALCULATETABLE (
VALUES ( 'Table 1'[ID] ),
FILTER ( 'Table 1', 'Table 1'[S_Req] = 1 )
)
VAR _tabuid =
CALCULATETABLE (
VALUES ( 'Table 1'[UniqueID] ),
FILTER ( 'Table 1', 'Table 1'[S_Req] = 1 || 'Table 1'[E_Req] = 1 )
)
VAR _tab2 =
CALCULATETABLE (
VALUES ( 'Table 2'[ID] ),
FILTER ( 'Table 2', 'Table 2'[LinkedID] IN _tabuid )
)
VAR _tab3 =
INTERSECT ( _tab2, _tab1 )
RETURN
COUNTX ( _tab3, [ID] )
Table1:
ID UniqueID TestResult S_Req M_Req E_Req Tst
1 AA 1 0 0 0
2 BB Failed 0 0 0 1
3 CC 0 1 0 0
4 DD Passed 0 0 0 1
5 EE 1 0 0 0
6 FF Passed 0 0 0 1
7 GG 1 0 0 0
8 HH Not Done 0 0 0 1
9 II 0 0 1 0
10 JJ Failed 0 0 0 1
11 KK 0 1 0 0
Table2:
ID LinkedID
1 II
1 CC
3 JJ
3 HH
5 CC
7 II
7 KK
9 FF
9 BB
11 HH
