Intersect count with filters

[uif19085]

Hello, i really need help with some counting, i've posted here before but didn't find a way to count what i exactly need,
i've tried to replicate what i need and i'll try to explain it. I have two tables with one to many relationship and in the Table 2 the "Link" id tells me if has a link in the Table 1 found in the UnID column(can look over the table example and observe it), first i need a measure that counts the number of "str" (im flaging this with 1 after some indentification criteria that uses more complex calculation) that has a link with one or many other "sys" or "eer" and as i said this is determinated by the Table 2 that has links back in Table1. For example:

Number of 'str' linked with one or many "sys"/"eer" =  1 (ArtID 1 is a "str" and has links with O-12 which is "eer" back in Table 1 and with O-15 which is an "eer") + 0 (ArtID 3 has links only with another "str" O-11 or an UnId O-17 that indetifies as nothing) + 1 ( ArtID 6 has link with O-12 which back into Table 1 is an "eer" and a link with an "sys" O-14 but doesn't matter beacause the condition is already fulfilled by one link to "eer") + 1 (ArtID 8 because it has a link with O-15 which is an "eer" and the other link doesn't count because we need to have just one link thats' an "sys' or "eer") = 3. This would be a Measure.

And secondly another measure with filter like this: I need to count the number of these "str" with implementation in {"implemented"} or all in-linked "eer" and "sys" with implementation in {"implemented"} like =  1 (ArtID 1 has implementation in {"implemented"} already meets the requirement so doesn't matter the linked ones)  + 0 (ArtID 3 has implementation in {"not_implemented"} in Table 1 and the linked in Table 2 O-11 which is not an "eer" or "sys" and O-17 which is again indentifies as nothing) + 0 (Art6 is an "str" but has implementation in {"not_implemented"} and the linked ones are O-12 which is an "eer" and has implementation in {"implemented"} but  O-14 which is the other link has implementation in {"not_implemented"} so this doesn't meet our criteria to have all links in {"implemented"} + 1 (Art8 is an "str" has implementation in "not_implemented" but the other 2 links are an "eer" O-12 and the other still an "eer" O-15 and both with implementation in {"implemented"} = 2

Table1:
ArtID         UnID         Implementation               STR         SYS         EER

   1             O-11           implemented                   1             0             0

   2             O-12           implemented                   0             0             1

   3             O-13           not_implemented            1             0             0

   4             O-14           not_implemented            0             1             0

   5             O-15           implemented                   0             0             1

   6             O-16           not_implemened             1             0             0

   7             O-17           implemented                   0             0             0
   8             O-18           not_implemented            1             0             0

Table 2
ArtId       Link
   1          O-12
   1          O-15
   2          O-16
   3          O-11
   3          O-17
   4          O-18
   5          O-17
   6          O-12
   6          O-14

   7          O-12

   8         O-15
   8         O-12

[lbendlin]

Your description is unclear to me. Can you please simplify it?

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[uif19085]

This would be a simplfied version of the Measures i need:
Measure 1: Number of "STR" that have links with one or many EER or SYS = 1 (ArtID 1 has link with an EER or SYS) + 0 (ArtID3 has no link with an EER or SYS) + 1 (ArtID 6 has  one link  with EER or SYS) + 1 (ArtID 8 has link with an EER or SYS)
Measure 2: 
Number of "STR" with implementation in {"implemented"} OR all in-linked "EER" and "SYS" with implementation in {"implemented"}: 1 (ArtID 1 has "implemnted") + 0 ( ArtID 3 "str" with not_implemented and all links are in not_implemented) + 0 (Art 6 "str" with not_implemented and not all links with in implemented, only O-12 is implemented) + 1 (Art 8 "str" not_implemented but all links are in implemented, O-15 and O-12) = 2. Hope it helps. Thanks

[lbendlin]

No, still unclear. Sorry.  Maybe someone else chan chime in.

[uif19085]

[v-yiruan-msft · Accepted solution]

Hi @uif19085 ,

It seems that you raised another thread with the similar requirement, please find my reply in that thread. And if you have any comment or problem, please update it in that new thread. We will follow it... Thank you.

Count if has a link with filter

Best Regards

[uif19085]

Hello @lbendlin I tried to explain this as well as i could: Table 2 has the linked(Link) information for every ArtID from Table 1, for example for ArtID 1 has one to many relationship with Table 2 where will find information for the links that ArtID 1 is having, so the Link refers to the UnID back to Table 1. That means ArtID 1 has links with O-12 and O-15 which back into Table 1 is ArtID 2 and ArtID 5. The other columns like "STR" only flags what kind of "ArtID" we're dealing with. I have no idea what information should i provide. I know this might be unclear but i explain it in details or maybe you can tell me what exactly is unclear.