Forum Discussion
Top 10 table slow - Performance Analyzer DAX query slow
I have a table visual where I want to show the top 10 only.
For some reason it is taking really long to load (20 seconds or so).
The fields in table are unique ID (from fact table) and amount (from fact table) and four other fields from four dimension tables.
Fact table is around 400k rows, dim tables around 40k rows.
I have a CY Top 10 measure where i'm taking revenue for CY and adding a RAND() to it (to break ties) and then am filtering the table for Top 10 by unique ID based on CY Top10 measure. Not sure if it makes a difference but i have a slicer for MTD, QTD, and YTD so it will show top 10 for month/qtr/year depending on user selection.
The below is the query from the performance analyzer. Is there a way to tell what is making the table load so slow?
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"SelectedValue"}, ‘fact_tbl'[Field])
VAR __DS0FilterTable2 =
TREATAS({"2022/09"}, 'Date'[YearMonthnumber])
VAR __DS0FilterTable3 =
TREATAS({"YTD"}, 'TimeIntelligence'[Time Frame])
VAR __DS0FilterTable4 =
TREATAS({2022}, 'Date'[Year])
VAR __DS0FilterTable5 =
TREATAS({"SL1",
"SL2"}, ‘Dim_tbl1'[SL])
VAR __DS0FilterTable6 =
TREATAS({"SL1",
"SL2"}, ‘fact_tbl'[SL])
VAR __SQDS0Core =
SUMMARIZECOLUMNS(
‘fact_tbl'[uniqueID],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
"CY_Top10", 'TimeIntelligence'[CY Top10]
)
VAR __SQDS0BodyLimited =
TOPN(10, __SQDS0Core, [CY_Top10], 0)
VAR __ValueFilterDM2 =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
‘fact_tbl'[uniqueID],
‘Dim_tbl2'[PT],
‘Dim_tbl1'[SSL],
‘Dim_tbl3'[Date],
‘Dim_tbl4'[Location],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__SQDS0BodyLimited,
"CY_Top10", 'TimeIntelligence'[CY Top10]
)
),
OR([CY_Top10] < -1, [CY_Top10] > 1)
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
‘fact_tbl'[uniqueID],
‘Dim_tbl2'[PT],
‘Dim_tbl1'[SSL],
‘Dim_tbl3'[Date],
‘Dim_tbl4'[Location]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__SQDS0BodyLimited,
__ValueFilterDM2,
"CY_Top10", 'TimeIntelligence'[CY Top10]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
[CY_Top10],
0,
‘fact_tbl'[uniqueID],
1,
‘Dim_tbl2'[PT],
1,
‘Dim_tbl1'[SSL],
1,
‘Dim_tbl3'[Date],
1,
‘Dim_tbl4'[Location],
1
)
EVALUATE
SUMMARIZECOLUMNS(
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__SQDS0BodyLimited,
__ValueFilterDM2,
"Title_CurrentYearT10", IGNORE('TimeIntelligence'[Title_CurrentYearT10])
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC,
[CY_Top10] DESC,
‘fact_tbl'[uniqueID],
‘Dim_tbl2'[PT],
‘Dim_tbl1'[SSL],
‘Dim_tbl3'[Date],
‘Dim_tbl4'[Location]1 Reply
- lbendlinSuper User
Load that query into DAX Studio. Enable Query Plan. Run the query. Examine the query plan. Check for large numbers of records. Refactor your code.