Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.