Most effecient way to include a IF statement in measure, Best Practise

[Anno2019]

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?

Value Actual =

SUMX(

    'Data',

    IF(

        'Data'[Value Category]="Included in Pricing Model",

        'Data'[Price],

        0

    )

)

[jdbuchanan71 · Accepted 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. 

[Anno2019]

@jdbuchanan71  This sounds great, will most certainly try this.  I have base measures for all my measures so will be easy to apply.

[VijayP]

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