Performance issues with Date Table + Duplicate Date Table setup (Last 13 Months + complex measure)

[bdpr_95]

Hi everyone,

I’m facing significant performance problems in a report where I need to calculate customer counts across fiscal years and also display the last 13 months using a disconnected date table. I would appreciate guidance on how to optimize this model and the DAX.

Data Model Overview

My model uses a classic star-schema structure:

Dim_Date – the primary date table

• Connected to the fact table
• Used by slicers and global filters
• Drives the semantic meaning of the date (Fiscal Year, Month Offset, etc.)

  Dim_DateVisuals – a duplicate, disconnected date table

  • Used only on visuals
  • Required because the business wants to see rolling last 13 months regardless of the slicer context
  • Charts are built using this table instead of the main date table

    There is no relationship between Dim_DateVisuals and the fact table.

    The idea is:

    • Users filter the report using Dim_Date (through slicers)
    • Visuals display values based on Dim_DateVisuals
    • My measures then have to “translate” the user’s slicer selections (from Dim_Date) into the appropriate filter context for Dim_DateVisuals → then apply the logic to the Fact table through Dim_Date.

      Functionally this workk, but performance is extremely poor, especially when the user changes a slicer or selects a different month.

      Main Measure (Customer Count Across Fiscal Years)

      # Customers PFY  =

      -- Parameter configuration

      VAR __OffsetMax = MAX ( Dim_Date[CurrYearOffset] )

      VAR __OffsetMin = MAX ( Dim_Date[CurrYearOffset] ) -1

      VAR __DatesMax = SELECTCOLUMNS( FILTER( ALL(Dim_Date), [CurrYearOffset] = __OffsetMax), "Date", [Date] )

      VAR __DatesMin = SELECTCOLUMNS( FILTER( ALL(Dim_Date), [CurrYearOffset] = __OffsetMin), "Date", [Date] )

      VAR __CompanyCode = "1"

      VAR __LockboxID =  "2" 

      -- Main calculation

      VAR __FactMax =

          SELECTCOLUMNS(

              FILTER(

                  ALL(Fact_Table)

                  ,[Date] IN __DatesMax &&

                  [Company ID] = __CompanyCode &&

                  [Lockbox ID] = __LockboxNumber

              ),

              "CustomerID",

              [CustomerID]

          )

      VAR __FactMin =

          SELECTCOLUMNS(

              FILTER(

                  ALL(Fact_Table)

                  ,[Date] IN __DatesMin &&

                  [Company ID] = __CompanyCode &&

                  [Lockbox ID] = __LockboxNumber

              ),

              "CustomerID",

              [CustomerID]

          )

      VAR __Result =

          IF (

              ISBLANK ( COUNTROWS ( DISTINCT ( __FactMin ) ) ),

              COUNTROWS ( DISTINCT ( __FactMax ) ),

              COUNTROWS ( DISTINCT ( __FactMin ) )

          )

      RETURN

      __Result

       

      "Last 13 Months" Measure Using Dim_DateVisuals

       

      # Customers PFY | Last 13 Months =

      VAR __MaxDate = MAX( Dim_Date[CurrMonthOffset] )

      VAR __MinDate = __MaxDate -13

      VAR __Result =

      IF(

          HASONEVALUE( Dim_DateVisuals[FY-Month] ) &&

          MAX( Dim_DateVisuals[CurrMonthOffset] ) <= __MaxDate &&

          MIN( Dim_DateVisuals[CurrMonthOffset] ) > __MinDate,

          CALCULATE(

              [# Customers PFY],

              FILTER(

                  ALL(Dim_Date),

                  Dim_Date[FY-Month] = VALUES(Dim_DateVisuals[FY-Month])

              )

          ),

          BLANK()

      )

      RETURN

      __Result

[cengizhanarslan]

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.

[FBergamaschi]

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

[FBergamaschi]

Hi @bdpr_95 

can you share the pbix or provide some tables sample to reproduce the problem?

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