cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
uif19085
Helper III
Helper III

A measure of two layers of Intersection and a validation

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

0 REPLIES 0

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors