Forum Discussion
Performance issues with Date Table + Duplicate Date Table setup (Last 13 Months + complex measure)
- No, I found a workaround.
It seems not possible by looking into your post withouth having knowledge of your data model and examples. But it is quite possible to tell the main reason why you have slow metrics here. You’re slow because the current pattern forces the engine to do large row-by-row scans of the fact table and repeatedly “rebuild” customer lists with FILTER(ALL(Fact_Table)) + IN + SELECTCOLUMNS + DISTINCT. That defeats storage engine optimizations and becomes painful once you add a disconnected date table and rerun the logic per month.
Adding some details to cengizhanarslan very good answer
here a few bad parts:
VAR __DatesMax = SELECTCOLUMNS( FILTER( ALL(Dim_Date), [CurrYearOffset] = __OffsetMax), "Date", [Date] )
supposing [CurrYearOffset] is a measure
you could write, redusing overhead:
VAR __DatesMax = SELECTCOLUMNS( FILTER( ALL(Dim_Date[Date]), [CurrYearOffset] = __OffsetMax), "Date", [Date] )
Things of this nature reduce performance (context transition on a multiple columns table)
Best
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
[CurrYearOffset] is a column not a measure.
