Performance issue when converting MDX to DAX queries

[Anonymous]

Hi all, 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.

[srk_powerbi]

@Anonymous  how did you convert from MDX to DAX query? or did you just write a DAX query from scratch?

[OwenAuger · Accepted solution]

Hi @Anonymous 

The DAX function SUMMARIZECOLUMNS should be the most efficient way of doing this.

Try this (hopefully I got the table/column references correct):

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] ASC

By default, SUMMARIZECOLUMNS will exclude any rows where all expressions evaluate to BLANK.

Also, SUMMARIZECOLUMNS is the function generally used by DAX queries generated by Power BI visuals.

Does this query perform better?

Regards,

Owen

[Anonymous]

Hi @OwenAuger , it helped me to reduce the query time from 34 secs to 2 secs by using SUMMARIZECOLUMNS, thanks a lot.

Regards,

Nhon