Forum Discussion

music43's avatar
music43
Icon for Advocate II rankAdvocate II
5 years ago

Performance problem with simple measure chain

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      music43
      Icon for Advocate II rankAdvocate II
      5 years ago

      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

       

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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 

  • music43's avatar
    music43
    Icon for Advocate II rankAdvocate II
    5 years ago

    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