topic DAX Equivalent for Excel SUMIFS Where Lookup Field Contains Partial Text from Another Field in DAX Commands and Tips

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

Merc100 — Fri, 20 Nov 2020 13:32:13 GMT

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))

I have 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! Hopefully the description is understandable. Thank you in advance.

Re: DAX Equivalent for Excel SUMIFS Where Lookup Field Contains Partial Text from Another Field

amitchandak — Fri, 20 Nov 2020 14:06:42 GMT

Merc100 , this should have worked

CALCULATE(SUM(Table1[Amount]),FILTER(Table1,FIND("ab",Table1[Hierarchy],,0)<>0))

Try this too

CALCULATE(SUM(Table1[Amount]),FILTER(Table1,search("ab",Table1[Hierarchy],,0)>0))

if not.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: DAX Equivalent for Excel SUMIFS Where Lookup Field Contains Partial Text from Another Field

nandukrishnavs — Fri, 20 Nov 2020 14:08:53 GMT

Merc100

Try below DAX in the calculated column.

Column = SUMX ( FILTER ( ALL ( 'Table' ), PATHCONTAINS ( 'Table'[Hierarchy], EARLIER ( 'Table'[Member No] ) ) ), 'Table'[Amount] )

Regards,
Nandu Krishna

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 👍

Proud to be a Super User!

Re: DAX Equivalent for Excel SUMIFS Where Lookup Field Contains Partial Text from Another Field

Merc100 — Fri, 20 Nov 2020 15:04:59 GMT

Thank you Nandu, that worked perfectly!

Re: DAX Equivalent for Excel SUMIFS Where Lookup Field Contains Partial Text from Another Field

Merc100 — Fri, 20 Nov 2020 15:06:03 GMT

Thanks for taking the time to look amitchandak - much appreciated