CODEWITHSUNDEEP
ssasmdxdaxcubetabular
Nhon Tran
Nhon Tran

Reputation: 113

Performance issue when converting MDX to DAX queries

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.

Upvotes: 3

Views: 174

Answers (1)

Dan
Dan

Reputation: 10690

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]

Upvotes: 2

Related Questions