Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anno2019
Helper IV
Helper IV

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

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
    )
)
1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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. 

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

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.

VijayP
Super User
Super User

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.