Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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:
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |