Forum Discussion
Slow DAX Disconnected Table
I have a 2 to 3 second hit on performance in this situation and I cannot figure out why. My measure is: Switch(SELECTEDVALUE(Time_Frame_Selection[Time Frame],"year"), "Year", [Net Sales YTD], "Quarter", [Net Sales QTD] "Period", [Net Sales PTD])). NOTE: the Time_Frame_Selection table is a tiny 1 column and 3 row table disconnected from the star schema data model. When I have the disconnected table column "Time Frame" unfiltered then naturally the default value "year" is selected in "Selectedvalue" and the measure above is FAST (half a second). But if I apply the filter on the column of the disconnected table in a slicer or filter pane, the exact same measure takes 2.5 seconds. The query plan completely changes and is much more complex when I filter the disconnected table column vs leaving it unfiltered. The only difference in all of this is the result based on "Selectedvalue." Naturally if I don't filter the disconnected table column then the default value is selected and it works fast. I would greatly appreciate any help. Here is a link to the .pbix file in drop box. Field parameters and calculation groups are not viable options in this situation so I'm really hoping someone can help me understand why this is happening?
Query plans are pictured below the first is when the column is NOT filtered (first picture with shorter query plan) the second picture is when the filter on the disconnected table is applied. (second picture with longer query plan). See how much more complex the seond one is where the filter is applied.
FAST query plan when disconnected table is NOT filtered:
SLOW query plan when disconnected table is filtered:
SLOW:
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"Year"}, 'Time_Frame_Selection'[Time Frame])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'FACT_Invoice'[Invoice NBR],
'DIM_II_Item'[Item Key],
'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
"Net_Sales_CY", 'FACT_Invoice'[Net Sales CY]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'FACT_Invoice'[Invoice NBR],
1,
'DIM_II_Item'[Item Key],
1,
'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)],
1
)
EVALUATE
__DS0PrimaryWindowed
FAST:
// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'FACT_Invoice'[Invoice NBR],
'DIM_II_Item'[Item Key],
'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)]
), "IsGrandTotalRowTotal"
),
"Net_Sales_CY", 'FACT_Invoice'[Net Sales CY]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'FACT_Invoice'[Invoice NBR],
1,
'DIM_II_Item'[Item Key],
1,
'DIM_II_CUST_Site_Bill_To'[Site Key (Bill)],
1
)
EVALUATE
__DS0PrimaryWindowed
5 Replies
lukiz84 thanks for the response. Unfortunately I have read that article but that does not solve the problem. I am already ensuring that I apply the best practices from that article (the selected value column is the column I am filtering). We're you able to run my queries? Any other thoughts? Thanks for your help.
Hi noahh. I think you were on the wrong path. It doesn't matter if I use the disconnected table or not. When I removed it and just used:
Net Sales CY = CALCULATE( SUM(FACT_Invoice[TRX Net Sales AMT]), DIM_Date[Rolling Fiscal Years To Dt] = 0, DIM_Date[Rolling Fiscal Periods To Dt] <= 0 )or even just
Net Sales CY = SUM(FACT_Invoice[TRX Net Sales AMT])it always took a few seconds. I think thats because there are 10M rows and that takes some time on a local power bi model. Tabular would be faster.
As I said, i removed the filter for the disconnected table and it was the same behaviour. You thinking that it was faster after removing the filter is just because the results are loaded when opening the file (filter set to year by default) and when we remove the filter it's already in the cache and just seems fast.
lukiz84 Did you run the two queries in Dax studio that I provided. I clear cache when I run those. They should be the same speed but they are not. Thoughts? Also what do you mean it would be faster in tabular?
yes i tried all different combinations and as i said, i even removed all the disconnected stuff. it always takes between 2 and 5 seconds. and i think it's because it's 9m rows. tabular in general is faster, but thats not an option here. (ssas tabular)
