March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
23 | |
15 | |
12 | |
9 | |
8 |
User | Count |
---|---|
41 | |
32 | |
29 | |
12 | |
12 |