Performance question based on Dax change

[lomorris]

Below is a measure I that I have put together that works with not issues other than sometimes it returns “Blank” when there is no data to return.

Customer Misc Amount =
VAR miscCalc =
CALCULATE (
SUM ( ‘Service Ledger Entries’[Amount] ) * -1,
FILTER (
‘Service Ledger Entries’,
‘Service Ledger Entries’[Resource Type] = “Service Cost”
&& ‘Service Ledger Entries’[Customer Number]
= SELECTEDVALUE ( Customers[Customer Number] )
&& ( ‘Service Ledger Entries’[Service Order Type] = “PM”
|| ‘Service Ledger Entries’[Service Order Type] = “FLD”
|| ‘Service Ledger Entries’[Service Order Type] = “ABU”
|| ‘Service Ledger Entries’[Service Order Type] = “CDMG” )
)
)
RETURN
miscCalc

To handle the Blank value being returned, I added the following If statement.

RETURN
IF(ISBLANK(miscCalc),0,miscCalc)

The issue I am having is, after added this If statement, my performance goes way way down. I take out the If statement and it is very fast again.

Any thoughts?

[BrianJ]

@lomorris,

Try

RETURN
COALESCE (miscCalc, 0) and see if that performs significantly better.

• Brian

[lomorris]

I was hoping to say yes that worked, but no. I am going use Dax Studio to see if I can figure out what the issue is.

[BrianJ]

@lomorris,

My other suggestion would be to submit it to The Optimization Lab, and have it analyzed in depth by the master @AntrikshSharma.

Introducing A New Enterprise DNA Initiative – Optimization Lab

All, As an adjunct to the excellent new Optimizing DAX course that we premiered in January, we are starting a new initiative this month called Optimization Lab, featuring Enterprise DNA expert @AntrikshSharma. If you are a regular on this forum, you undoubtedly have come across Antriksh’s amazing DAX deep-dive explanations of key DAX concepts, like this one and this one. In addition to his virtuoso DAX coding skills, he is also incredibly knowledgeable about the intricacies of DAX optimization. …

• Brian

[lomorris]

That sounds like a great ideal!

Thanks Brian, I will do that.

[BrianJ]

@lomorris,

If you haven’t yet seen the first Optimization Lab video he did last month, I can’t recommend it highly enough - it is an absolute master class:

How To Optimize Slow Measures In Power BI

• Brian

[lomorris]

I’m on it. Thanks Brian.

[EnterpriseDNA]

Hi @lomorris, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @lomorris, we’ve noticed that no response has been received from you since the 22nd of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @lomorris, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!