cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anno2019 Member
Member

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

Accepted Solutions
Super User
Super User

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

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
Highlighted
VijayP Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

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

Anno2019 Member
Member

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,501)