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
... View more