topic SUMIFs in DAX to add a factor of another row to the current row in DAX Commands and Tips

SUMIFs in DAX to add a factor of another row to the current row

mecefink — Tue, 21 Apr 2020 22:12:56 GMT

Hi,

I am trying to build a DAX formula to build a measure in Power Pivot to replicate what I am doing in a table formula right now in Excel. Goal is increase speed, the data set is quite massive and the table formula is very slow. I have a data set where there is a parent producer and 0-2 children producers that I would like to combine production from. I want to add half of each adjacent child producer to the parent producer volume. So, no adjacent children, parent production remains the same; 1 child, parent production + 0.5*child production; 2 children, parent production + 0.5*child_1 production + 0.5*child_2 production. I made a table to define how many children a parent has, 0 means none, 1 means a child with the same number as the parent, -1 means a child number less than the parent and 2 means there is a child on either side. For example, assume the following producer arrangement: P1, P1C, P2, P2C, P3, P4

Table name: ParCh

Parent	Child
P1	1
P2	2
P3	-1
P4	0

Dataset looks like this, each producer gets one line per day. In actuality there are multiple columns that would all need to be combined in this fashion, but they are all the same idea, so this is representative

Daytime	Producer	Production
2020/04/01	P1	100
2020/04/01	P1C	50
2020/04/01	P2	125
2020/04/01	P2C	80
2020/04/01	P3	200
2020/04/01	P4	175

Using the following cell formula I am able to get my desired result. Note the IF statements for D1 are teh only case where the parent prefix does not match the child prefix.

=IFERROR(IF(INDEX(ParCh,MATCH([@Producer],ParCh[Parent],0),2)=2,[PRODUCTION]+0.5*(SUMIFS([PRODUCTION],[DAYTIME],"=" &[@DAYTIME],[Producer],"=" &[@Producer] & "C")+SUMIFS([PRODUCTION],[DAYTIME],"=" &[@DAYTIME],[Producer],IF([@Producer]="D1","=C0C","=" & LEFT([@Producer],1) & MID([@Producer],2,1)-1 & "C"))),IF(INDEX(ParCh,MATCH([@Producer],ParCh[Parent],0),2)=1,[PRODUCTION]+0.5*SUMIFS([PRODUCTION],[DAYTIME],"=" &[@DAYTIME],[Producer],"=" &[@Producer] & "C"),[PRODUCTION]+0.5*SUMIFS([PRODUCTION],[DAYTIME],"=" &[@DAYTIME],[Producer],IF([@Producer]="D1","=C0C","=" & LEFT([@Producer],1) & MID([@Producer],2,1)-1 & "C")))),IF(RIGHT([@Producer],2)="C","",[PRODUCTION]))

Daytime	Producer	Production	Parent + 0.5Child
2020/04/01	P1	100	125
2020/04/01	P1C	50
2020/04/01	P2	125	190
2020/04/01	P2C	80
2020/04/01	P3	200	240
2020/04/01	P4	175	175

This is quite a cumbersome formula to run on a large dataset, and is taking way too long to execute for it to be of use. I have found that measures execute much fast I am just unsure how to get this result using a DAX formula. I have tried using the Calculate function to filter data, but I am unable to make a reference to the producer designation to do so.

I'm not sure if this is possible in DAX, but if anyone has any tips or solutions, they would be greatly appreciated.

Re: SUMIFs in DAX to add a factor of another row to the current row

Stachu — Fri, 24 Apr 2020 19:23:33 GMT

I would start by changing the ParCh table to something like this

Parent	Child
P1	P1C
P2	P1C
P2	P2C
P3	P2C
P4	NA

it's basically the same information, but is much easier to work with with DAX, as you can pass filters using whole columns
Tables shouldn't have any joins for this to work:

Column = VAR __Date = Data[Daytime] VAR __Producer = Data[Producer] VAR __IsParent = __Producer IN VALUES ( ParCh[Parent] ) VAR __Children = FILTER ( ParCh, 'ParCh'[Parent] = __Producer ) VAR __ChildrenProduction = CALCULATE ( SUM ( Data[Production] ), ALL ( Data ), TREATAS ( SELECTCOLUMNS ( __Children, "Children", [Child] ), Data[Producer] ), Data[Daytime] = __Date ) RETURN IF ( __IsParent, 'Data'[Production] + __ChildrenProduction * 0.5 )

the result:

Re: SUMIFs in DAX to add a factor of another row to the current row

mecefink — Fri, 01 May 2020 20:52:54 GMT

That worked thanks!

I was also able to get it working another way. I used the table for ParCh that you recommended and then made a new "Parent" column on my table that used lookup(Match) to assign the parent well. Then I made another "Parent + 0.5Child" column that multiplied all child producers by 0.5 and parents by 1. I then made a key for the parent producer.

Pulling "Parent + 0.5Child" into a pivot table with the Parent Key, I got the desired end result. Much fast cell formula than my original, but definitely not as elegant as your solution. My less elegant solution is a bit more intuitive for the other/future users to decipher though... so now to choose between elegance and job security or ease of access.