Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Structuring and grouping data

Hello all, I have a parts list with materials, which in turn can consist of many different components. I have now made various transformation steps to show the path. So I can see which material con...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

HI Anonymous ,

I'd like to suggest you use the path string with GENERATESERIES function to expand the text values to list. Then you can use the item list to filter on your table to summary correspond records based on current product code path index:

formula =
VAR currProduct =
    SELECTEDVALUE ( 'Table'[ProductCode] )
VAR _pathExplode =
    SELECTEDVALUE ( 'Table'[PathExplode] )
VAR _length =
    PATHLENGTH ( _pathExplode )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Product", PATHITEM ( _pathExplode, [Value] )
    )
VAR _pathIndex =
    MAXX ( FILTER ( _pathtable, [Product] = currProduct ), [Value] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [ProductCode]
                IN SELECTCOLUMNS (
                    FILTER ( _pathtable, [Value] >= _pathIndex ),
                    "Product", [Product]
                )
        )
    )

Regards,

Xiaoxin Sheng