cancel
Showing results for
Did you mean:
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:

1       II
1      CC
3       JJ
3      HH
5      CC
7       II
7      KK
9       FF
9       BB
11     HH

0 REPLIES 0

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors