Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mecefink
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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:

Capture.PNG

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

2 REPLIES 2
Stachu
Community Champion
Community Champion

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:

Capture.PNG

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors