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

  • Question

  • Morning all,

    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)) as well as 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! Hopefully the description is understandable.

    # Member No Hierarchy Amount Excel SUMIFS Sum of ab
    1 26442776 10781676|26442776 1 23 14
    2 26197185 10781676|26197185 2 10 14
    3 21924723 10781676|21924723 3 3 14
    4 26073332 10781676|26073332 4 4 14
    5 11076148 10781676|11076148 ab 5 5 14
    6 12111163 10781676|12111163 6 6 14
    7 2130914 10781676|2130914 7 7 14
    8 10862777 10781676|10862777|26197185 8 19 14
    9 16870234 10781676|16870234 ab 9 9 14
    10 24910224 10781676|26442776|24910224 10 10 14
    11 23249092 10781676|10862777|23249092 11 11 14
    12 18312922 10781676|26442776|18312922 12 12 14


    Thank you

    Paul

    Friday, November 20, 2020 12:45 PM

All replies