Performance issue

DAX DAX Calculations
1

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 statement.

RETURN
COALESCE(miscCalc,0)

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

Customer Fleet Report.pbix (11.9 MB)

Any thoughts?

2

Hi @lomorris

Please try and see if you get desired result and improved performance.

RETURN
miscCalc + 0

Thanks
Ankit J

2 Likes
3

Ankit,
Thank you, it worked like a charm, but I have to ask. Is “blank” really being returned? I am not sure why such a simple fix would work, but the a function like COALESCE didn’t.

4

Hi @lomorris

Ideally it shall not have that much performance impact. Only situation, I can think of is in Visual like Table/Matrix returning large number of rows where IF condition have to be checked for each row and thus impacting performance.

Thanks
Ankit J

5

@ankit,

:+1: - good one! The +0 option didn’t even occur to me, and even if it had I would’ve expected it to perform exactly as COALESCE did. Got to admit that like @lomorris, I’m still scratching my head a bit over the fact that there was a significant difference in performance between the two.

It’s also got me wondering whether the new IF.EAGER() function could be used here to improve performance? Looping in @AntrikshSharma to get his take as well.

– Brian

1 Like
6

@lomorris You don’t need to inject filters manually when you have already done that throught the filters pane, and a filter over Customers[Customer Number] already exists in the filter context so no need to add it inside CALCULATE.

You should write your code in this way:

Total Ledger Amount = 
SUM ( 'Service Ledger Entries'[Amount] )

.

Customer Labor Amount 2 = 
CALCULATE (
    [Total Ledger Amount] * -1,
    KEEPFILTERS ( 'Service Ledger Entries'[Resource Type] = "Resource" )
)

.

Customer Misc Amount 2 = 
CALCULATE (
    [Total Ledger Amount] * -1,
    KEEPFILTERS ( 'Service Ledger Entries'[Resource Type] = "Service Cost" )
)

.

Customer Parts Amount 2 = 
CALCULATE (
    [Total Ledger Amount] * -1,
    KEEPFILTERS ( 'Service Ledger Entries'[Resource Type] = "Item" )
)
3 Likes
7

@lomorris What’s the total computation time with Ankit’s solution? It is still slow for me, and I know the reason why it is slow, so I am unsure if adding 0 has improved your code, can you please check and confirm?

1 Like