Count of Records When Relationship Information is in a Second Table

[MTOnet]

Hi There,

I am trying to process some information, but I'm having trouble coming up with a formual to get the result that I am looking for.

My data is in two tables, with all objects in one table and the relationship between each object stored in a second table.  The data looks like below.  How do I create a column or measure, that gives me the number of objeccts of a specific type, that are related to the first item.  For example, in the data below, I want to get a count of two for ID 1 and 0 for ID 2 (the other ID's can be 0 or blank).

DataTable

Relationship

I can get the result of ID 1 to 3, but I can't figure out how to filter out the type that I don't need..

Any help is appreciated.

[parry2k]

@MTOnet so how these two tables are connected? And what you are need to do, although you explained but still not clear.

[MTOnet]

Hi @parry2k , thanks for responding.  Hopefully I can explain what I am looking for well enough.

What I am trying to do, is count the number of records in the Relationship Table that are of LinkType2 for each ID of type Type1, in the Data Table.  See my slightly expanded and updated example below.

As for how the two tables are connected, all records are storied in the Data Table.  The relationship table holds the details for how the different types of records are related.  The Relatioship table will only have values that are in the data table, but all records may not be in the Relationship table.  In the Relationship Table, ID1 and ID2 can be considered as the Parent-Child relationship, although that is just one of the actual relationship types.

Data Table

Relationship Table

Result

[v-xicai]

Hi @MTOnet ,

>>In the Relationship Table, ID1 and ID2 can be considered as the Parent-Child relationship, although that is just one of the actual relationship types.

Could you please clarify more about the logic between the parent-Child relationship (for ID1 and ID2) and your Result output? And what does the Relationship Table look like, just like showing in the second Relationship Table picture?

Best Regards,

Amy

Community Support Team _ Amy

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

[MTOnet]

Hi @v-xicai,

In the Relationship table, ID1 and ID2 can only be ID values found in the Data table.  It's many-to-many, with ID1 being the 'parent' and ID2 being the 'Child'.  It is possible for a 'parent' to have multiple 'children', each of which are a different type in the Data table.  A 'child' in ID2, could also have multiple 'parents' in ID 1.  There is additional detail in the Relationship table, like created date and user, which is not useful for the question at hand.  Otherwise, it is as shown.

As for the result, I want a count for each ID in the Data table that has a Type of Type1, of how many times the ID from the Data table is in ID1, where the linktype in the Relationship table is LinkType1 and for the value in ID2 to have a type of Type2 in the Data table. So, in the tables in my second example, for ID=1 in the Data table, I want a result of 2, as ID 1 appears in the relationship table 4 times, but only 3 of those records are of LinkType1 and of those 3 records coming from the Relationship table only 2 have a value of Type2 in the Data table.  

I'm ultimately trying to find records like ID 9 & 10, which have no links to a record in the Data table with the specific type.  As every record should have atleast one of that type of relationship and this will let us easily call out the exceptions that need to be investigated.

[MTOnet]

@parry2k  @v-xicai 

I'm trying to revisit this, as I havent been able to find a solution.  I'm hoping someone can help me, since the solution eludes me.  I feel like I have an idea of how to accomplish this, but I can't quite see it through.

I've tried creating measure in a few ways, but never really getting passed the total of records of type 1 in the relationship.  I'm not sure if I set the relationships properly (Data_ID to Relationship_ID1, 1:Many),

I feel like ultimately, I need something like

CALCULATE(
   COUNT('Data'[ID]),
   Filter( Returned Table, 'Data'[Type]="Type2"
)

Where Returned Table is built off of multiple Filters, with this first one filtering the Relationship table, to give all records for ID1, where the linktype is Linktype1 (which for DAta_ID 1, would return link Id's 1 & 2.

IF(
  MAX('Data'[Type1),
     FILTER(
        'Relationship',
        'Relationship'[ID1]=Max('Data'[ID])
     )
   )

It would the be the Realtionship_ID2 values that would then filter the Data Table by ID, where ID is the ID2 values from the relationship table.

FILTER(
   'Data'.
   'Relationship'[ID2]=Data[ID]
)

This last part is where I not able to determine. Do I need to set a set a relationship between Data_ID and Realtionship_ID2 in this case? If I did this, would just give me a total number of related items, rather than a table of them?
Being able to find these exceptions, where certain items would be such a big help, as we currently have to manually go through hundreds of records a month to ensure there is the proper links for each record to meet compliance with certain metrics.

Thanks for any and all assistance

[parry2k]

@MTOnet I really want to help you to get over the line but everything is so confusing and not able to wrap my head around. May be too much to digest, you know your data and business problem, so it is easy for you, but sitting here, it is not easy to understand what you are trying to achieve.

I would highly recommend to put data in excel sheet, and explain use case there with expected result so that community member can see what you are trying to achieve. Showing these screen shots , helps a lot but sometime takes time to understand the business logic on how you are getting to those numbers. So please put stuff in excel, add formulas there what you are trying to achieve, it will help to provide the solution. Hope you understand.

[MTOnet]

Hi @parry2k , I do appreciate your response.  You are right, my explanation of this complicated scenario does make sense to me. But that does not mean i have done a good job explaining it to someone else.  See my latest attempt, which includes more distinct naming of data elements and a representative diagram.

Here we have objects of differet types (type1=Square, type2=Circle, ...) with different types of connectors (LinkType1 - Dotted Line, LinkType2 = Sold Line, ...).

What I am trying to determine, is the number of Circles that are connected to each Square, by a Dotted Line.  So the Squares would have following values

1. Square 1 = 2 (Circles 3 & 4 are connected by a Dotted Line, Circle 8 is excluded since it is connected by a Solid Line and 11 is excluded since it is a Diamond Connected by a Double Line)
2. Square 2 = 1 (Circle 5 is connected by a Dotted Line and 6 is a Square connected by a Solid Line)
3. Square 6 = 1 (Circle 3, excuding Circle 11 and Triangle 9
4. Square 12 = 0 (no Circles connected by a Dotted Line)
5. Square 14 = 0 (no connections at all)

In the Data Table below, an ID can only appear once.

The Relationship table below is not completely representative.  There are relationships that are shown that, while possible are not probable (like the Solid Line Connecting a Square to Circle), but I wanted it to show what I was looking to accomplish more explicitly, by exclding similar situations.  It is also possible for some of the relationships to be switched (such as Sqaure 1 being in the Target to Circle 3 in the source column.  I do not want these to be counted as it is an invalid scenario.

I really do appreciate you looking at this.  Hopefully this description is clearer.  I am unfortantely not able to upload a sample file, due to restrictions at my organization.