topic Re: Help with measure that adds values based if Parent value is showing within a hierarchy in DAX Commands and Tips

Help with measure that adds values based if Parent value is showing within a hierarchy

sc27 — Mon, 10 May 2021 13:25:13 GMT

Hello, I am trying to figure out how to write a measure that will make a calculation based on what value is present. For example

Child	Child Goal	Parent	Parent Goal
Chiefs	5	West	10
Chargers		West	10
Raiders		West	10
Broncos		West	10
Ravens		North	15
Steelers	2	North	15
Browns		North	15
Bengals		North	15
Jets	1	East
Patriots		East
Bills		East
Dolphins		East

I would like the Child Goal value for every row to be added ONLY if its respective Parent Goal value is blank (or zero). This data works as a hierarchy, so If a Parent Goal is showing once for West, it will show up (and have the same exact number) for every other instance of West.

The Parent Goal values should also not be double counted. So using the above data, the expected Total is 10 + 15 + 1 (as this is the only Child Goal that does not have a Parent Goal value) = 26

Re: Help with measure that adds values based if Parent value is showing within a hierarchy

amitchandak — Mon, 10 May 2021 13:46:20 GMT

sc27 , Try a new column like

New column =
var _sum = sumx(filter(Table, [Parent] =earlier([Parent])),[Child Goal])
return
if(isblank([Parent Goal]) || [Parent Goal] =0 , _sum, [Parent Goal])

Re: Help with measure that adds values based if Parent value is showing within a hierarchy

sc27 — Mon, 10 May 2021 13:49:03 GMT

Sorry that I didn't mention this, but the data source I am using is a data model which doesn't allow me to insert columns (as far as I can tell). I also won't be able to add the column within the model itself

Re: Help with measure that adds values based if Parent value is showing within a hierarchy

amitchandak — Mon, 10 May 2021 16:03:45 GMT

sc27 , Try a measure like

New column =
var _sum = sumx(filter(allselected(Table), [Parent] =max([Parent])),[Child Goal])
return
if(isblank(sum(Table[Parent Goal])) || sum(Table[Parent Goal]) =0 , _sum, sum(Table[Parent Goal]))

Re: Help with measure that adds values based if Parent value is showing within a hierarchy

sc27 — Mon, 10 May 2021 16:14:50 GMT

amitchandak

I'm getting an error saying MAX can only evaluate numbers or dates. Does not work with Strings