3

I have a MDX query which I want to convert to DAX to improve performance, however the result is not as expected, MDX took 11 secs to complete while DAX was 34 secs. any suggestion to improve the DAX query

MDX Query:

SELECT
{
    [Measures].[Internet Total Sales]
} ON COLUMNS,
ORDER(
    NONEMPTY
    (
        {
            [Product].[Model Name].[Model Name].AllMembers *
            [Product].[Product Line].[Product Line].AllMembers *
            [Product].[Product Name].[Product Name].AllMembers *
            [Customer].[First Name].[First Name].AllMembers *
            [Customer].[Last Name].[Last Name].AllMembers
        },
        {
            [Measures].[Internet Total Sales]
        }
    ),
    [Product].[Model Name].CurrentMember.MemberValue, ASC
) ON ROWS
FROM [Model]

DAX Query:

EVALUATE
CALCULATETABLE 
(
    FILTER
    (
        SUMMARIZE
        (
            CROSSJOIN('Product', 'Customer'),
            [Model Name],
            [Product Line],
            [Product Name],
            [First Name],
            [Last Name],
            "Internet Total Sales",
            [Internet Total Sales]
        ),
        NOT ISBLANK([Internet Total Sales])
    )
)
ORDER BY [Model Name] ASC

Thank you.

Nhon Tran
  • 113
  • 1
  • 1
  • 7
  • 2
    take a look here: https://www.sqlbi.com/articles/introducing-summarizecolumns/ – RADO Jan 24 '20 at 01:45
  • 1
    Hi @RADO, thanks a lot, the summarizecolumns helped reduce the DAX query from 34 secs to 2 secs – Nhon Tran Jan 24 '20 at 04:44
  • You welcome. It might be a good idea to post the new query as an answer, in case someone else has a similar problem. – RADO Jan 24 '20 at 05:34

1 Answers1

2
EVALUATE
SUMMARIZECOLUMNS(
    'Product'[Model Name],
    'Product'[Product Line],
    'Product'[Product Name],
    'Customer'[First Name],
    'Customer'[Last Name],
    "Internet Total Sales", [Internet Total Sales]
)
ORDER BY 'Product'[Model Name]
Dan
  • 10,480
  • 23
  • 49