Counting rows with condition based on grandchild

[jpc]

Hello,

The physical item bulidup that i'm trying to represent is as follows:

• Product
  • Item
    • SubComponent

A product can contain up to 2 items, and an item can contain up to 4 subcomponents.

So, i have 2 fields in the product table (Item1_ID and Item2_ID) and 4 fields in the Item table (SubComp1_ID ,SubComp2_ID , etc)

But I can't have multiple active relationships between the Item and Subcomponent tables - so I have created duplicate tables in DAX, so now i have tables Item2=Item and tables SubComponent4 = SubComponent3 = SubComponent2 = SubComponent.   Now, i can create the relationships, and can display the product buildup sensibly in a list of products. 

The problem i've just hit is trying to list all the components, and reporting how many products use them. 

I can get a list of all the subcomponents (from any of my 4 identical tables) but now i am trying to filter the products table by items that contain those subcomponents - which are two levels 'down' the hierarchy (hence i'm thinking of them as grandchildren)

I'm currently trying to end up with something like
MeasureUsage = COUTROWS(FILTER(Products, Assembly.SubComp(SubComp1_ID) = selectedvalue(SubCompID) ))   
[+ similar clauses for SubComp2,3,4]

But that dot notation isn't DAX, and it doesn't feel very elegant

   - so:  how should i filter a table by an attribute of its grandchildren?

   - and/or:  how should I re-structure the tables and relationships to make life easier?!

Thank you!

[Greg_Deckler]

@jpc Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jpc]

Hi @Greg_Deckler  thank you for reading my question - i'll try and add detail here to make it more clear.

(I'm starting to think there are issues with the way i've constructed duplicate tables and relationships, but i'll continue to frame this question as per the original post)

Example data:

Product table

Prod_ID	Prod_Name	Item1_ID	Item2_ID
1	ProdA	1
2	ProdB	1	2
3	ProdC	3	3
4	ProdD	3	4

Item table

Item_ID	Item_Name	SubComp1	SubComp2	SubComp3	SubComp4
1		1
2		2	3
3		1	4
4		6	1	5	6

SubComp table

SubComp_ID	SubComp_Name
1	M3 Screw 25mm
2	M3 Screw 35mm
3	M3 Screw 45mm
4	M4 screw 25mm
5	M4 screw 25mm
6	M4 screw 35mm

My PowerBI model view/relationships

Item2=Item
SubComp2 = SubComp
SubComp3 = SubComp
SubComp4 = SubComp

Desired output

I am trying to list all the components, and reporting how many products use them, eg:

SubComp_ID	Use count	Products where used
1	5	ProdA,ProdB,ProdC,ProdC,ProdD
2	1	ProdB
3	1	ProdB
4	3	ProdC,ProdC,ProdD
5	1	ProdD
6	2	ProdD,ProdD

I feel this should be fairly straightforward, but the fact that i'm struggling is suggesting to me that it could be more about the structure/relationships - all advice most welcome!

Thanks in advance

[jpc]

Addendum:  I think i may have made a bold step forward - I modified the query to unpivot the Item_ID  and SubComp_ID columns.   

The relationships & tables became much more straightforward, and i can almost build the desired output table I wanted.

but i do have a many-to-many relationship that i'm not sure how to address - but i'm aware there's a lot of reading material so i will now look into this myself