Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guru's
I have many variables that need to be created and they are mostly always relying an if statement.
My reports performance is slowing down drastically.
What is the best practice to optimize performance when using this type of formula?
Should I create a Measure or better to create a Calculated Column?
Should I use SUMX? or is there a performance optimizing alternative?
Solved! Go to Solution.
Hello @Anno2019
In your example it doesn't look like you need to use SUMX. Instead start with a base measure that sums your price
Price Amount = SUM ( 'Data'[Price] )
Then you use that in measures that apply the filtering you need
Value Actual =
CALCULATE (
[Price Amount],
KEEPFILTERS ( 'Data'[Value Category] = "Included in Pricing Model" )
)
SUMX is an iterator which can be expensive if you use it when you don't need to.
Hello @Anno2019
In your example it doesn't look like you need to use SUMX. Instead start with a base measure that sums your price
Price Amount = SUM ( 'Data'[Price] )
Then you use that in measures that apply the filtering you need
Value Actual =
CALCULATE (
[Price Amount],
KEEPFILTERS ( 'Data'[Value Category] = "Included in Pricing Model" )
)
SUMX is an iterator which can be expensive if you use it when you don't need to.
@jdbuchanan71 This sounds great, will most certainly try this. I have base measures for all my measures so will be easy to apply.
In my Opinion always creating a measure is best instead of creating additional column in the Data
First Create a measure with SUMX or SUM based on the data and then filter that with other parameters in another measure.
If you have some data sample I can help further
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |