Hierarchy tree for bill of material with matrix and nodes

[abed_azazi]

Hi All 

I am new in DAX and understand that I can get the hierarchy using the PATH; which can accumulate the count of the child components in the parent record. the same table would have the bill of material for more than 10 different products using similar components in different levels.

ChildParentDescriptionProcess GroupHierarchy PATHLevelStatusProduct

AA60		Product1	Assembly	AA60	0	Pending	Product 1
AA61	AA60	1	Assembly	AA60|AA61	1	Pending	Product 1
AA62	AA60	2	Assembly	AA60|AA62	1	Released	Product 1
AA63	AA61	3	Assembly	AA60|AA61|AA63	2	Released	Product 1
AA64	AA61	4	Fabrication	AA60|AA61|AA64	2	Released	Product 1
AA65	AA63	5	Assembly	AA60|AA61|AA63|AA65	3	Released	Product 1
AA66	AA63	6	Fabrication	AA60|AA61|AA63|AA66	3	Pending	Product 1
AA67	AA64	7	Fabrication	AA60|AA61|AA64|AA67	3	Released	Product 1
AA68	AA62	8	Fabrication	AA60|AA62|AA68	2	Released	Product 1
AA69	AA65	9	Assembly	AA60|AA61|AA63|AA65|AA69	4	Released	Product 1
AA70	AA65	10	Assembly	AA60|AA61|AA63|AA65|AA70	4	Released	Product 1
AA71	AA69	11	Assembly	AA60|AA61|AA63|AA65|AA69|AA71	5	Released	Product 1
AA72	AA69	12	Assembly	AA60|AA61|AA63|AA65|AA69|AA72	5	Released	Product 1
AA73	AA72	13	Part	AA60|AA61|AA63|AA65|AA69|AA72|AA73	6	Pending	Product 1
AA74	AA72	14	Part	AA60|AA61|AA63|AA65|AA69|AA72|AA74	6	Released	Product 1
AA75	AA63	15	Assembly	AA60|AA61|AA63|AA75	3	Released	Product 1
AA76	AA75	16	Part	AA60|AA61|AA63|AA75|AA76	4	Pending	Product 1
AA77	AA65	17	Part	AA60|AA61|AA63|AA65|AA77	4	Released	Product 1
AA78		Product2	Assembly	AA78	0	Pending	Product 2
AA79	AA60	112	Assembly	AA78|AA79	1	Pending	Product 2
AA80	AA60	122	Assembly	AA78|AA79|AA80	2	Released	Product 2
AA81	AA61	1221	Assembly	AA78|AA79|AA80|AA81	3	Released	Product 2
AA82	AA61	12212	Fabrication	AA78|AA79|AA80|AA82	3	Released	Product 2
AA83	AA63	8976	Assembly	AA78|AA79|AA80|AA83	3	Pending	Product 2
AA84	AA61	3	Assembly	AA78|AA79|AA80|AA83	3	Released	Product 2
AA85	AA61	4	Fabrication	AA78|AA79|AA80|AA83	3	Released	Product 2
AA86	AA63	5	Assembly	AA78|AA79|AA80|AA83	3	Released	Product 2
AA87	AA63	6	Fabrication	AA78|AA79|AA80|AA83	3	Pending	Product 2

The need is to build a multi-level hierarchy visual for the bill of material by filtering the parent and accumulating the child components using a DAX measure without depending on the aggregation in the matrix visual, this is because I need to filter each matrix with a specific index of a particular parent to ensure the flexibility of the populated data based on the selected product.

AAACCC     PENDING    (Should automatically gets generated based on level 1 of the selected product1)                         

Process Group	Pending	Released
Assembly	0	7
Fabrication	0	3
Part	2	2

DDFFSSS    RELEASED     (Should automatically gets generated based on level 1 of the selected product1)                         

Process Group	Pending	Released
Assembly	0	0
Fabrication	0	1
Part	0	0

UUYYYRD      (Should automatically gets generated based on level 2 of the selected product1)                         

Process Group	Pending	Released
Assembly	0	6
Fabrication	1	0
Part	2	2

The same way should be generated for all the parent components of the levels from 1 to 4 accomulating the subsequent levels.

I tried using the custom hierarchy tree from xViz but it is very expensive and the trial one does not support matrix as tree nodes.

Anyone would help on this requirement ?

I have an idea of calculating the count of child components by using the Hierarchy PATH. Would this work ? if yes please write me the DAX.  

[Anonymous · Accepted solution]

Hi  @abed_azazi ,

Sorry for the late reply.

If your visual involves dynamic filtering of results, you could try replacing the all() function with allselected().

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

Refer to:

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

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

[Anonymous]

Thanks for the reply from lbendlin , please allow me to provide another insight: 
Hi  @abed_azazi ,

Here are the steps you can follow：

1. Create two slicer tables that are not connected to the main table.

Description_Table =
DISTINCT('Table'[Description])

Status_Table =
DISTINCT('Table'[Status])

2. Create measure.

Pending =
var _selectdescription=SELECTEDVALUE('Description_Table'[Description])
var _selectstatus=SELECTEDVALUE('Status_Table'[Status])
var _child=
MAXX(FILTER(ALL('Table'),'Table'[Description]=_selectdescription&&'Table'[Status]=_selectstatus),[Child])
var _level=
MAXX(FILTER(ALL('Table'),'Table'[Description]=_selectdescription&&'Table'[Status]=_selectstatus),[Level])
var _count=
COUNTX(
    FILTER(ALL('Table'),
    [Process Group]=MAX('Table'[Process Group])&&
    CONTAINSSTRING(
    [Hierarchy PATH],_child)=TRUE()&&[Status]="Pending"&&
     [Level]>_level),[Child])
RETURN
IF(
    _count=BLANK(),0,_count)

Released =
var _selectdescription=SELECTEDVALUE('Description_Table'[Description])
var _selectstatus=SELECTEDVALUE('Status_Table'[Status])
var _child=
MAXX(FILTER(ALL('Table'),'Table'[Description]=_selectdescription&&'Table'[Status]=_selectstatus),[Child])
var _level=
MAXX(FILTER(ALL('Table'),'Table'[Description]=_selectdescription&&'Table'[Status]=_selectstatus),[Level])
var _count=
COUNTX(
    FILTER(ALL('Table'),
    [Process Group]=MAX('Table'[Process Group])&&
    CONTAINSSTRING(
    [Hierarchy PATH],_child)=TRUE()&&[Status]="Released"&&
     [Level]>_level),[Child])
RETURN
IF(
    _count=BLANK(),0,_count)

3. Result:

There's a question:

When the selection is “AAACCC and PENDING”, I found that the data in which [Process Group]=Fabrication&&[Status]=Pending&&[level] is less than the selected data's corresponding [level] is one.

When the selection is “AAACCC and PENDING”, I found that the data in which [Process Group]=Fabrication&&[Status]= Released &&[level] is less than the selected data's corresponding [level] is two.

So the result is derived as:

Best Regards,

Liu Yang

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

[abed_azazi]

Thank you Liu,

I think you missed that we need to have an initial filter of the product as the table has the bill of material for more than 10 different products using the same components.

I believe we should not always use the ALL filter in the measures ! can you help here ?

[abed_azazi]

The data changes daily, and each engineer will release the design of a few components.

[lbendlin]

Why do you need a measure?  This data is immutable.