Forum Discussion

dimitrishuk's avatar
dimitrishuk
Icon for Helper I rankHelper I
8 years ago
Solved

How to improve a slow DAX query using ALL()

Hi,   I'm in the process of building a tabular data model for our accounts receivables that is up to date up to the last day. The table is around 1m rows and around 50MB.    A single DAX query wi...
Need Help
Tips and Tricks
  • dimitrishuk's avatar
    dimitrishuk
    8 years ago

    Hey guys, I managed to solve my slow query time.

     

    What I did was define a MaxDate variable, with MaxDate being the date from my Date Table.

     

    Then I ran the date filter once on a calculated table using SUMMARIZECOLUMNS. I removed the relationship between the date table and accounts receivable ledger. See below:

     

    = VAR

         MaxDate= MAX('Date Dimension'[Date])

    RETURN

     

    SUMMARIZECOLUMNS('Accounts Receivable Ledger'[Company],'Accounts Receivable Ledger'[Business Unit],'Accounts Receivable Ledger'[Address Number],'Accounts Receivable Ledger'[Document Unique],'Accounts Receivable Ledger'[Invoice Due Date],'Accounts Receivable Ledger'[Draft- Due Date],FILTER('Accounts Receivable Ledger','Accounts Receivable Ledger'[Transaction Date]<=MaxDate),"Open Amount",SUM('Accounts Receivable Ledger'[Amount - Gross]))

     

    By filtering on SUMMARIZECOLUMNS and moving my measures to the new table I saved repeated IN comparisons in each measure and also evened out the spread between Formula and Storage Engines. 

     

    By grouping I also reduced the cardinality which helped alot.

     

    The big problem with SUMMARIZECOLUMNS is it doesnt work well with calculated columns so I also had to change my table views at the SQL level. Still, performance is much better now.

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
8 years ago

dimitrishuk

This is just an idea: in some cases, I have found DATESBETWEEN(...) performs better than a boolean inequality on a Date column, since the boolean version translates to FILTER ( ALL (...),... )

 

For example, you could try this:

Overdue 1000+ :=
VAR MaxDate =
    MAX ( 'Date Dimension'[Date] )
RETURN
    CALCULATE (
        CALCULATE (
            CALCULATE (
                SUM ( 'Accounts Receivable Ledger'[Amount - Gross] ),
                DATESBETWEEN (
                    'Accounts Receivable Ledger'[Draft- Due Date],
                    BLANK (),
                    MaxDate - 1
                )
            ),
            DATESBETWEEN (
                'Accounts Receivable Ledger'[Invoice Due Date],
                BLANK (),
                MaxDate - 1000
            )
        ),
        DATESBETWEEN ( 'Date Dimension'[Date], BLANK (), MaxDate )
    )

Also, if you found performance better without the relationship with 'Date Dimension', could you use an outer CALCULATE with CROSSFILTER(..., None ) to disable the relationship for the purpose of this measure? It's not something I've ever tried so would be interested in whether it makes any difference.

 

On side question, is it necessary for the CALCULATE functions to be nested, effectively applying their filters in sequence? It may make no difference, but you could try a single CALCULATE with the three date filters applied simultaneously?

 

Regards,

Owen