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.
Hello,
I have created a measure that averages weekly item placements over the previous six weeks. (Because the same item may be placed in the same store each week, I need to capture this at the weekly level and then create an average of weekly figures so that these are not counted twice.) The measure works, it's just very slow. Could you offer any advice on how to optimize this? One note: I am using a measure for Placements right now, but could switch to a column if that enables other methods.
Solved! Go to Solution.
Context transition might be jamming you up on the [Placements] measure. In my testing, this returns the same results and is almost 3x faster.
Placements =
COUNTROWS(
CALCULATETABLE(
VALUES(Sales[Item Name + Store Name]),
Sales[Units Sold] > 0
)
)
This is your measure.
This is mine
That chart looks like you are comparing a trend line to the actual. What if you put your placements and mine side by side in a table by date?
I had also established the MaxDate as another variable and it was throwing things off, when I rewrote with my original six MAX(Calendar_Lookup[Date]) syntax, our numbers matched. Thanks so much for your help!
@jdbuchanan71 Can you see any reason why the CalcTable version you suggested would affect my ability to move the dates? I'm trying to compare the last six weeks' weekly average with the weekly average from 46-51 weeks ago (LY forward rate). When I use my old [Placements] measure, it will calculate the old range correctly, but using the new CalcTable version of the measure these two produce the same outcome:
LY Forward 6W Avg Weekly Placements:=
Make this into a measure rather than a variable.
TabPlacements = COUNTROWS(CALCULATETABLE(VALUES(Sales[Item Name + Store Name]),Sales[Units Sold]>0))
Then use the measure in your other calcs.
LY Forward 6W Avg Weekly Placements:=
DIVIDE(
CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-358),-7,DAY))+
CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-351),-7,DAY))+
CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-344),-7,DAY))+
CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-337),-7,DAY))+
CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-330),-7,DAY))+
CALCULATE([TabPlacements],DATESINPERIOD(Calendar_Lookup[Date],(MAX(Calendar_Lookup[Date])-323),-7,DAY))
,6,0
)
Context transition might be jamming you up on the [Placements] measure. In my testing, this returns the same results and is almost 3x faster.
Placements =
COUNTROWS(
CALCULATETABLE(
VALUES(Sales[Item Name + Store Name]),
Sales[Units Sold] > 0
)
)
This is your measure.
This is mine
Thanks, @jdbuchanan71 . I wasn't able to completely reproduce the [Placements] value with the CALCULATETABLE version. Here are the results of the two measures on the same chart:
I have triple-checked the dates involved, so I'm thinking there's just a difference on what values the two measures reach. Think it's worth me switching to a Column?
Thanks!
Hi all,
Thanks for the notes so far. [Placements] =
Can you give us the calculation for the [Placements] measure? It's possible that this would give you the same result depending on what that measure is calculating.
Six WK Avg Weekly Placements =
DIVIDE (
CALCULATE (
[Placements],
DATESINPERIOD ( Calendar_Lookup[Date], MAX ( Sales[Date] ), -42, DAY )
),
6
)
Hey @fullcount ,
that's a good question. It can have a few reasons why it's slow. I guess it would help to show or analyze the Measure [Placements] as you use it many times. Can you post the source code of that measure or better the file if possible?
What will definitely make it a little bit faster is to put the MAX(Sales[Date]) in a variable. Like that is has to be evaluated only once and not 6 times:
Six WK Avg Weekly Placements =
VAR vMaxDate = MAX(Sales[Date])
RETURN
DIVIDE(
CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate ,-7,DAY))+
CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -7,-7,DAY))+
CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -14,-7,DAY))+
CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -21,-7,DAY))+
CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -28,-7,DAY))+
CALCULATE([Placements],DATESINPERIOD(Calendar_Lookup[Date],vMaxDate -35,-7,DAY)),
6,
0
)
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |