Simplifying Dax Formula To Make it Perform Faster

[Danielnir]

Hi Everyone!

Need help with tweaking the performance of DAX formula.

I had to recreate the way Sage200c generates sales report. The code I ended up with looks like this:

Sales Value = 
var Invoices = calculate(
                            sum('SOPInvoiceCreditLine'[LineTotalValueGBP]),
                            'SOPInvoiceCreditType'[Name]="Invoice",
                            'SOPOrderReturnType'[SOPOrderReturnTypeName]<>"Quotation")
var CreditNote = calculate(
                            sum('SOPInvoiceCreditLine'[LineTotalValueGBP]),
                            'SOPInvoiceCreditType'[Name]="Credit Note",
                            'SOPOrderReturnType'[SOPOrderReturnTypeName]<>"Quotation")*-1
Var SalesValue = calculate(
                            invoices+CreditNote,
                            filter(
                                SOPOrderReturnType,
                                SOPOrderReturnType[SOPOrderReturnTypeName]<>"Quotation"))

Return

IF(SELECTEDVALUE('DIM: Currency Exchange'[Currency])="GBP",SalesValue,SalesValue*SELECTEDVALUE('DIM: Live Currency Exchange'[USD per unit]))

When I run a performance analyzer, this formula and a corresponding one with the volumes (same code, different column in sum) takes the longest time to calculate. Is there any way to simplify them?

Thanks in advance!

[ReneMoawad · Accepted solution]

Hi Daniel,

First you need to create a New Column with the below formula:

Total Value GBP =
Var CreditType = RELATED('SOPInvoiceCreditType'[Name])
Var ReturnTypeName = RELATED('SOPOrderReturnType'[SOPOrderReturnTypeName])
RETURN
SWITCH(TRUE()
    , CreditType = "Credit Note" && ReturnTypeName <> "Quotation", 'SOPInvoiceCreditLine'[LineTotalValueGBP] * -1
    , CreditType = "Invoice" && ReturnTypeName <> "Quotation", 'SOPInvoiceCreditLine'[LineTotalValueGBP]
    , BLANK()
)

And after that create the below measure:

Sales Value =
Var selectedCurrency = SELECTEDVALUE('DIM: Currency Exchange'[Currency])
Var currencyExchange = SELECTEDVALUE('DIM: Live Currency Exchange'[USD per unit])
RETURN
IF(selectedCurrency = "GBP", [Total Value GBP], [Total Value GBP] * currencyExchange)