Powerpivot Equivalent for Excel SUMIFS Where Lookup Field Contains Partial Text from Another Field

Question

I am relatively new to DAX and have scoured the forums but not found anything that quite works in Powerpivot/Dax yet.

I am trying to sum each record where the Member No is contained within the Hierarchy string e.g. the first Member No occurs in rows 1, 10 and 12 and sums to 23, whereas the 3rd Member No only appears in Hierarchy row 3 so sums to 3.

The excel formula is: SUMIFS([Amount],[Hierarchy],""&[Member No]&"")

I tried adding "ab" to rows 5 & 9 as part of the attempt using the following formula to see if I could get close and then apply the "Like/contains" bit of logic but no luck: CALCULATE(SUM(Table1[Amount]),FILTER(Table1,FIND("ab",Table1[Hierarchy],,0)<>0))

I also tried CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Member No]=EARLIER(Table1[Member No]))) as a start, also with the idea of getting close and then adding the Hierarchy element, also with no luck.

Any help would be hugely appreciated! Thank you.

Answer 1

Thanks all for your input and assistance - I have received a response on another forum which does exactly what I need:
Column =
SUMX (
FILTER (
ALL ( 'Table' ),
PATHCONTAINS ( 'Table'[Hierarchy], EARLIER ( 'Table'[Member No] ) )
),
'Table'[Amount]
)

Answer 2

Excel 365 Pro Plus with Power Pivot and Power Query.
Expand Hierarchy with PQ Split() and PQ Filter(), then load to PP.
http://www.mediafire.com/file/iea9ffy4zew2qsd/11_20_20.xlsx/file
http://www.mediafire.com/file/2qz3ro3bwllqnqa/11_20_20.pdf/file

Answer 3

Another option if you want it in the Data Model as in your picture. Assuming data in Excel table tblSource

let
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(Source,
        {{"#", Int64.Type},{"Member No", Int64.Type},{"Hierarchy", type text},{"Amount", type number}}
    ),
    tblHrchy = Table.TransformColumnTypes(
        Table.ExpandListColumn(
            Table.TransformColumns(
                Table.SelectColumns(ChangedTypes,{"Hierarchy","Amount"}),
                {"Hierarchy", each Text.Split(Text.AfterDelimiter(_,"|",Occurrence.First),"|"),
                    type list
                }
            ),
            "Hierarchy"
        ),
        {{"Hierarchy", Int64.Type}}
    ),
    InnerJoin = Table.NestedJoin(ChangedTypes,"Member No", tblHrchy,"Hierarchy", "HrchyAmt"),
    AggregMemberAmt = Table.AggregateTableColumn(InnerJoin, "HrchyAmt",
        {{"Amount", List.Sum, "MemberAmt", type number}}
    )
in
    AggregMemberAmt

Then load as Connection + Add to the Data Model

Answer 4

Lz,
What we really need is a book on synergy of PQ and PP.
But there ain't none.
Herb