Forum Discussion

bdpr_95's avatar
bdpr_95
Helper IV
5 months ago
Solved

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

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
Need Help

8 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    5 months ago

    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's avatar
      FBergamaschi
      Super User
      5 months ago

      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

      • bdpr_95's avatar
        bdpr_95
        Helper IV
        5 months ago

        [CurrYearOffset] is a column not a measure.

  • v-tejrama's avatar
    v-tejrama
    Community Support
    5 months ago

    Hi bdpr_95 ,

     

    Thank you cengizhanarslan  for the response provided!

    Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

    Thank you

      • v-tejrama's avatar
        v-tejrama
        Community Support
        5 months ago

        Hi bdpr_95,

         

        Thanks for the update. Glad you found a workaround. Let me know if you need anything else.

        Thank you.