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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
uif19085
Helper III
Helper III

Intersect count with filters

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

 

1 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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

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

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

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

uif19085_1-1673933699820.png

 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors