Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ben_w
Frequent Visitor

Cumul sum performance improvements


Hi


I'm using PBI with DirectQuery to SSAS Tabular 2016 standard.

Scenario: Costs per project, sliceable by time, for current value and total project value, up to the chosen date.


Current cost:

SUM(FactTable[Costs])


Cumul cost:

CALCULATE (
    [Current cost];
    FILTER (
        ALL ( 'Dim_Date'[PK_Date] );
        'Dim_Date'[PK_Date] <= MAX ( 'Dim_'[PK_Date] )
    )
)


Running the 'Current cost' measure for all projects (18k): <0.5sec

Running the 'Cumul cost' measure for all projects: 5sec

 

Fact table has about 10m rows.

This seems odd to me, since all i'm doing is changing the date filter to be smaller then the maximum in the date dim.


I'm running into the issue that if i use this logic for more complex base measures, i'm facing loading times of 30sec to one minute, where the base formula takes about 2 to 3 seconds.

Also, ram memory seems to build up, i can't seem to find the necessity in this, as no intermediate calculations are made.


Any more performant solutions possible?


3 REPLIES 3
Stachu
Community Champion
Community Champion

as long as you have the some sort of time filter (Year, Month), [Current Cost] will actually run on smaller dataset, cause it will be evaluated only in that filter context (e.g. 2018 only)
the other measure:
1) determines the max date in the given filter context (coming from slicers, visuals etc.)
2) overwrites that filter context with ALL and applies the filter from 1) so everything before or equal to 2018. As FILTER is iterator it does the comparison for each row of the Dim_Date[PK_Date]
have a read here
https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/

you could try this, my hope would be that it would save time by not evalulating MAX ( 'Dim_'[PK_Date] ) in each iteration, but I'm not sure how effective this would be

Cumul cost =
VAR _MaxDate =
    MAX ( 'Dim_'[PK_Date] )
RETURN
    CALCULATE (
        [Current cost];
        FILTER ( ALL ( 'Dim_Date'[PK_Date] ); 'Dim_Date'[PK_Date] <= _MaxDate )
    )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

ben_w
Frequent Visitor

 

Thanks for the explanation.

I have extensively tried the parameters suggestion, it unfortunately does not result in faster times. Not worse either though, exactly the same.

Iit seems that even though it evaluates each row, it does not compute each time the max(), hance no improvement with parameter.

Stachu
Community Champion
Community Champion

this video is quite helpful in regards to optimization

https://www.sqlbi.com/tv/dax-optimization-examples/

a bit over 1h, but very informative with hands on examples



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.