Suboptimal queries on Azure SSAS Tabular in live connection mode

[sebastiaan-pbi]

We use Power BI in direct query mode with Azure SSAS Tabular. The queries generated by Power BI seem to be slower than necessary. 

For example, I have a table in Power BI that displays 1 dimension and 3 measures. The query generated runs for 10.2 seconds in DAX Studio:

DEFINE
    MEASURE 'TRANSACTION'[Rank on Revenue Is Lower then Other Customers] = ( /* USER DAX BEGIN */
    [Product Name Clock Rank on Revenue Other Customers]
        - [Product Name Clock Rank on Revenue] )
		
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS ( VALUES ( 'CUSTOMER'[Customer] ) ),
            SEARCH ( "<customer name>", 'CUSTOMER'[Customer], 1, 0 ) >= 1
        )
    VAR __DS0FilterTable2 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'TRANSACTION TYPE'[Transaction Level 1] ) ),
            OR (
                'TRANSACTION TYPE'[Transaction Level 1] = "<level 1 desc 1>",
                'TRANSACTION TYPE'[Transaction Level 1] = "<level 1 desc 2>"
            )
        )
    VAR __DS0FilterTable3 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'PRODUCT'[Product Level 4] ) ),
            'PRODUCT'[Product Level 4] = "<Product Level 4 Name>"
        )
    VAR __DS0FilterTable4 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'CALENDAR'[In Last 6 Years] ) ),
            'CALENDAR'[In Last 6 Years] = TRUE
        )
    VAR __DS0FilterTable5 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'PRODUCT'[FPC] ) ),
            'PRODUCT'[FPC] = "<FPC Name>"
        )
    VAR __DS0FilterTable6 =
        FILTER ( KEEPFILTERS ( VALUES ( 'CALENDAR'[Year] ) ), 'CALENDAR'[Year] = <Year> )
		
    VAR __ApplyFilterSQDS0 =
        TOPN (
            50,
            SUMMARIZECOLUMNS (
                'PRODUCT'[Product Name Clock],
                __DS0FilterTable,
                __DS0FilterTable2,
                __DS0FilterTable3,
                __DS0FilterTable4,
                __DS0FilterTable5,
                __DS0FilterTable6,
                "Product_Name_Clock_Rank_on_Revenue", 'TRANSACTION'[Product Name Clock Rank on Revenue]
            ),
            [Product_Name_Clock_Rank_on_Revenue], 1
        )
		
    VAR __ValueFilterDM0 =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'PRODUCT'[Product Name Clock],
                    __DS0FilterTable,
                    __DS0FilterTable2,
                    __DS0FilterTable3,
                    __DS0FilterTable4,
                    __DS0FilterTable5,
                    __DS0FilterTable6,
                    __ApplyFilterSQDS0,
                    "Product_Name_Clock_Rank_on_Revenue", 'TRANSACTION'[Product Name Clock Rank on Revenue],
                    "Product_Name_Clock_Rank_on_Revenue_Other_Customers", 'TRANSACTION'[Product Name Clock Rank on Revenue Other Customers]
                )
            ),
            NOT ( ISBLANK ( [Product_Name_Clock_Rank_on_Revenue] ) )
        )
        
EVALUATE
TOPN (
    501,
    SUMMARIZECOLUMNS (
        'PRODUCT'[Product Name Clock],
        __DS0FilterTable,
        __DS0FilterTable2,
        __DS0FilterTable3,
        __DS0FilterTable4,
        __DS0FilterTable5,
        __DS0FilterTable6,
        __ApplyFilterSQDS0,
        __ValueFilterDM0,
        "Product_Name_Clock_Rank_on_Revenue", 'TRANSACTION'[Product Name Clock Rank on Revenue],
        "Product_Name_Clock_Rank_on_Revenue_Other_Customers", 'TRANSACTION'[Product Name Clock Rank on Revenue Other Customers],
        "Rank on Revenue Is Lower then Other Customers", 'TRANSACTION'[Rank on Revenue Is Lower then Other Customers]
    ),
    [Product_Name_Clock_Rank_on_Revenue], 1,
    'PRODUCT'[Product Name Clock], 1
)
ORDER BY
    [Product_Name_Clock_Rank_on_Revenue],
    'PRODUCT'[Product Name Clock]

I am not a DAX expert, but interpreting the query it seems that it calculates 2 help-tables: __ApplyFilterSQDS0 and __ValueFilterDM0. In this particular example, they do not seem very useful; By removing them and calculating all 3 measures in the final result, I was able to halve the run time (5.1 seconds!), with identical results:

DEFINE 
	MEASURE 'TRANSACTION'[Rank on Revenue Is Lower then Other Customers] = ( 
    	[Product Name Clock Rank on Revenue Other Customers] - [Product Name Clock Rank on Revenue] 
	)
    
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS ( VALUES ( 'CUSTOMER'[Customer] ) ),
            SEARCH ( "<customer name>", 'CUSTOMER'[Customer], 1, 0 ) >= 1
        )
    VAR __DS0FilterTable2 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'TRANSACTION TYPE'[Transaction Level 1] ) ),
            OR (
                'TRANSACTION TYPE'[Transaction Level 1] = "<name 1>",
                'TRANSACTION TYPE'[Transaction Level 1] = "<name 2>"
            )
        )
    VAR __DS0FilterTable3 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'PRODUCT'[Product Level 4] ) ),
            'PRODUCT'[Product Level 4] = "<product>"
        )
    VAR __DS0FilterTable4 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'CALENDAR'[In Last 6 Years] ) ),
            'CALENDAR'[In Last 6 Years] = TRUE
        )
    VAR __DS0FilterTable5 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'PRODUCT'[FPC] ) ),
            'PRODUCT'[FPC] = "FPC Planten Groen"
        )
    VAR __DS0FilterTable6 =
        FILTER ( KEEPFILTERS ( VALUES ( 'CALENDAR'[Year] ) ), 'CALENDAR'[Year] = 2015 )

EVALUATE
TOPN (
    501,
    SUMMARIZECOLUMNS (
        'PRODUCT'[Product Name Clock],
        __DS0FilterTable,
        __DS0FilterTable2,
        __DS0FilterTable3,
        __DS0FilterTable4,
        __DS0FilterTable5,
        __DS0FilterTable6
        // 00:00.0
        
        ,"Product_Name_Clock_Rank_on_Revenue", 'TRANSACTION'[Product Name Clock Rank on Revenue]
        // 00:01.0

	,"Product_Name_Clock_Rank_on_Revenue_Other_Customers", 'TRANSACTION'[Product Name Clock Rank on Revenue Other Customers]
	// 00:02:4

	,"Difference", 'TRANSACTION'[Rank on Revenue Is Lower then Other Customers]
	// 00:03.5
		
	// The 3 measures combined:
	// 00:05.1
    )
)
ORDER BY 'PRODUCT'[Product Name Clock]

Did I get the same results by coincidence? Or are the helper tables generally necessary?

I am happy to provide more details if needed, cause the lack of performance is a kind of big issue for us!

Kind regards,

Sebastiaan

[v-haibl-msft]

@sebastiaan-pbi

It seems that __ApplyFilterSQDS0 and __ValueFilterDM0 are two filtered tables. Are you able to get the same results with more data and involve some slicers in your data source?

Best Regards,
Herbert