Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have applied this code many times for different variables. Everthing works, its just really slow.
Is there an easier way to compute this measure?
Thank you all in advance!
VolumeCY Comparable =
SUMX(SUMMARIZE(FILTER(FactData,FactData[Adjustments]=""&&FactData[One Time Article flag]="no"),
FactData[Customer Code],
FactData[Product code (SKU)],
FactData[One Time Article flag],
FactData[Sales channel],
FactData[Price Calculation Origin],
FactData[isServiceItem],
FactData[Sales Agent],
FactData[Manual overwrite?],
FactData[Base Unit of Measure],
FactData[Currency]),
if(and([VolumesCY]<>0,[VolumesPY]<>0),[VolumesCY],0))
Hi @Bundi009 ,
Please try:
VolumeCY Comparable =
SUMX (
SUMMARIZE (
FILTER (
FactData,
FactData[Adjustments] = ""
&& FactData[One Time Article flag] = "no"
),
FactData[Customer Code],
FactData[Product code (SKU)],
FactData[One Time Article flag],
FactData[Sales channel],
FactData[Price Calculation Origin],
FactData[isServiceItem],
FactData[Sales Agent],
FactData[Manual overwrite?],
FactData[Base Unit of Measure],
FactData[Currency]
),
SWITCH (
TRUE (),
[VolumesCY] = 0, 0,
[VolumesPY] = 0, 0,
[VolumesCY] <> 0
&& [VolumesPY] <> 0, [VolumesCY],
0
)
)
Or
VolumeCY Comparable =
SUMX (
SUMMARIZE (
FILTER (
FactData,
FactData[Adjustments] = ""
&& FactData[One Time Article flag] = "no"
),
FactData[Customer Code],
FactData[Product code (SKU)],
FactData[One Time Article flag],
FactData[Sales channel],
FactData[Price Calculation Origin],
FactData[isServiceItem],
FactData[Sales Agent],
FactData[Manual overwrite?],
FactData[Base Unit of Measure],
FactData[Currency]
),
IF ( [VolumesCY] ^ 2 + [VolumesPY] ^ 2 <> 0, [VolumesCY], 0 )
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Thank you very much Owen for your help. Unfortunately, it does not really make a change (<2% improvement).
VolumeCY is pretty straight forward too:
VolumesCY = calculate(sum(FactData[Qty sold]),(filter(FactData,FactData[Year]="2022")))
I think its just the shortcoming of having summarize as a measure in the model that makes it really slow when you have many rows (around 8M in my case) and with many columns to be taken into account. I tried this first in Power Query but it did not solve the issue either due to some model specifications.
Thanks again
Hi @Bundi009 ,
Any updates?
My idea is that if it is not possible to reduce the number of iterations, then try to reduce the number of times the measures in the second parameter needs to be calculated.
I'm not sure if this will work. I'm curious to see how it turns out.
Best Regards,
Gao
Community Support Team
Hi Gao,
Sorry for the late reply. Unfortunately it didnt work. Using Summarize in a measure over a large dataset simply slows down the overall performance. I changed the setup completely by creating different tables, creating relationships and then apply ''userelationship''. This way I could delete the need for summarize and have speedy model.
Thanks for your support!
You're welcome, and sorry to hear it didn't improve much.
Perhaps some improvements can be made to the overall data model, such as including dimension tables containing the various columns that SUMMARIZE is grouping by.
Also, a Date table would be useful if you have measures that filter based on year.
Hi @Bundi009
Here are a couple of suggested tweaks:
VolumeCY Comparable =
CALCULATE (
SUMX (
SUMMARIZE (
FactData,
FactData[Customer Code],
FactData[Product code (SKU)],
FactData[One Time Article flag],
FactData[Sales channel],
FactData[Price Calculation Origin],
FactData[isServiceItem],
FactData[Sales Agent],
FactData[Manual overwrite?],
FactData[Base Unit of Measure],
FactData[Currency]
),
VAR CY = [VolumesCY]
VAR PY = [VolumesPY]
RETURN
IF ( AND ( CY <> 0, PY <> 0 ), CY )
),
KEEPFILTERS ( FactData[Adjustments] = "" ),
KEEPFILTERS ( FactData[One Time Article flag] = "no" )
)
Does this improve performance at all?
Would there be anything to optimize in the [VolumesCY] and [VolumesPY] measures?
Regards,
Owen
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |