Forum Discussion

Danielnir's avatar
Danielnir
Helper II
3 years ago
Solved

Simplifying Dax Formula To Make it Perform Faster

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!

 

Need Help
  • ReneMoawad's avatar
    ReneMoawad
    3 years ago

    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)

1 Reply

  • ReneMoawad's avatar
    ReneMoawad
    Resolver III
    3 years ago

    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)