Performance problem with simple measure chain

[music43]

Hi

I am in the early stages of learning DAX. I have what I think is a simple chain of measures BUT my Quantity Sold (over nWeeks) is taking 3 minutes to reurn the results so clearly I have an issue somewhere. The table contains an item number (dimension) which can have upto 99,999 rows (which I know is not ideal) and contains a couple of measures. When I remove the Quantity Sold (over nWeeks) measure it returns immediately, I believe this to be the throttle point.

Total Quantity Shipped =
SUM ( 'fctSales'[Quantity Sold] )

Max Invoice Date =
MAX ( 'fctSales'[Invoice Date] )

Number of Weeks for AWS (Default: 26) =
SELECTEDVALUE('WeekRange'[pWeekRangeForAWS], 26)

nDays (# of Days) =
7 * [Number of Weeks for AWS (Default: 26)]

nWeeks Start Date =
DATEVALUE ( [Max Invoice Date] - [nDays (# of Days)] )

Quantity Sold (over nWeeks) =
VAR StartDate = [nWeeks Start Date]
RETURN
CALCULATE (
[Total Quantity Shipped],
FILTER (
'dimCalendar',
'dimCalendar'[DATE: DATE] >= StartDate
)
)

Hopefully someone will be able to help and save me. Thank you for your time and reading this.

Oli

[music43]

From what I can tell, a low number of Storage Engine queries is good BUT a high percentage of time in the Formula Engine (99%) is bad. And I know that the total time is shocking.

Any further advice (other than give up 😏) would be great.

Thanks again

[AlB]

Hi @music43 

Can you share the pbix? Optimization frequently depends on your data structure and other particularities of the model.  Try this to start with

Quantity Sold (over nWeeks) =
VAR StartDate =
    MAX ( 'fctSales'[Invoice Date] )
        - 7 * SELECTEDVALUE ( 'WeekRange'[pWeekRangeForAWS], 26 )
RETURN
    CALCULATE ( [Total Quantity Shipped], 'dimCalendar'[DATE: DATE] >= StartDate )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@music43 , this seems fine. Do you bi-directional joins and a lot of visuals on the page.

Can make bi-directional join as single direction and check.

 if not can you share the data model?

[music43]

Hi @amitchandak 

The model hasn't got any unusual relationships.

Unfortunately I can't share the model as it contains company data ☹️

The only other useful information I could provide is this:

and the query performance code (which I don't fully understand yet)

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"6 weeks"}, 'WeekRange'[Week Range (for AWS)])

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('dimCalendar'[DATE: DATE])),
      AND(
        'dimCalendar'[DATE: DATE] >= DATE(2020, 1, 1),
        'dimCalendar'[DATE: DATE] < DATE(2020, 12, 1)
      )
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('dimProduct'[PRODUCT: NUMBER TEXT], "IsGrandTotalRowTotal"),
      __DS0FilterTable,
      __DS0FilterTable2,
      "Number_of_Weeks_for_AWS__Default__26_", 'M E A S U R E S'[Number of Weeks for AWS (Default: 26)],
      "Product_Age_in_Weeks__new_", 'M E A S U R E S'[Product Age in Weeks (new)],
      "Total_Quantity_Shipped", 'M E A S U R E S'[Total Quantity Shipped],
      "Quantity_Sold__over_nWeeks_", 'M E A S U R E S'[Quantity Sold (over nWeeks)]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'dimProduct'[PRODUCT: NUMBER TEXT], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'dimProduct'[PRODUCT: NUMBER TEXT]

Thanks