Find all parents in Bom parent child hierarchy based on property

[EmielvdR]

I 'm trying to implement a Bill of materail view in a PowerBI table visual based on a slicer on MaterialDescription.

The goal is:

- Select a MaterialDescription in the slicer

- Table shows all materials found in de vFactBOM

- Table also shows all parents in the table visual (PATHCONTAINS) and removes parents which do based on the value of PropertyA of that specific parent

The data model look like this:

Facttable vFactBOM (which contains a Bill of Material including calculated column BOMLinePath=PATH(BOMLineID,BOMLineIDParent))

A Dimension table DimMaterial linked with MaterialID to vFactBOM (Dim 1:* Fact) (with MaterialID, MaterialDescription, BOMLinePath, PropertyA)

A unrelated calculated table BOMSlicerNames, which is a calculated table with SELECTCOLUMNS of DimMaterial (with MaterialID, MaterialDescription, BOMLinePath, PropertyA)

(this concept is implemented based on post: https://community.fabric.microsoft.com/t5/Desktop/Filter-Table-by-Column-Values-Associated-with-Sele...)

Current measures:

MEASURE vFactBOM[BOMSlicer_Measure] = 
CALCULATE (
    SELECTEDVALUE ( vFactBOM[BOMLinePath] ),
    TREATAS ( VALUES ( BOMSlicerNames[BOMLinePath] ), vFactBOM[BOMLinePath] )
)

MEASURE vFactBOM[QtyCum] =
VAR myp = vFactBOM[BOMSlicer_Measure] // will be empty when multiple or non-selected
VAR myval =
    IF (
        NOT (
            ISBLANK ( vFactBOM[BOMSlicer_Measure] )
        ),
        // Don't execute when not needed
        CALCULATE (
            PRODUCTX (
                vFactBOM,
                vFactBOM[Quantity]
            ),
            FILTER (
                ALL ( vFactBOM ),
                PATHCONTAINS (
                    myp // the path to search in
                    ,
                    vFactBOM[BOMLineID] // the item to search for
                ) = TRUE
            )
        )
    )
RETURN
    myval

The current measures help me to select the correct materials from the vFactBOM, since QtyCum will only show a value for the selected materials.

Now I need a additional measure to do the same but also showing the parents (based on PropertyA).

How can I make a measure which results in showing the correct lines?

I hope this explaintion is clear enough to give me some suggestions.

Setup:
Direct query mode with Tabular Azure Analysis services

[EmielvdR · Accepted solution]

Thank you I found a solution which works in dax (in direct query mode) myself.

ShowParents:=
var myp = 
  COUNTROWS(
  FILTER(
    CROSSJOIN(
      // Selected value in slicer
      VALUES(BOMSlicerNames[BOMLinePath]),
       // FACT value
      VALUES(vFactBOM[BOMLinePath])
    ),
    LEFT(BOMSlicerNames[BOMLinePath], LEN(vFactBOM[BOMLinePath])) = vFactBOM[BOMLinePath] 
    &&
    SELECTEDVALUE(PLM_vDimMaterial[PropertyA])="002"
    )
  )
        
RETURN myp

Addin this metric to the table show the number of parents and is blank if the item doesn't match the criteria (in this case match on Path and PropertyA = 002.

[lbendlin]

Bill of Materials (BOM) solution in Excel and PowerBI – (thebiccountant.com)

Keep in mind that Power BI is a reporting tool, not a material planning tool.

[EmielvdR]

Thank you for the reply. I also found this solution, but m-query is not possible in direct query mode.

[lbendlin]

Instead of Direct Query you can access your SSAS Tabular source tables in import mode. That butchers the entire concept of SSAS but will allow you to run the M code.