Forum Discussion
Helper ICounting rows with condition based on grandchild
Hello,
The physical item bulidup that i'm trying to represent is as follows:
- Product
- Item
- SubComponent
- Item
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!
3 Replies
- Greg_Deckler
Community Champion
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 = SubCompDesired 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
