Forum Discussion

PBIUser9090's avatar
PBIUser9090
Regular Visitor
3 years ago

OFFSET Optimization for Large Dataset

I have a measure that is working as expected to determine the sum of the Total YTD Sales in the previous row in the Sales table when used on a partition (1 company key for 1 year('Product Sales'[Date])) of a larger dataset.  However, when the full dataset is loaded, the visual exceeds the available resources when the measure is used.  My dataset in full is approx 150 million records in the Product Sales table with the largest partition being about 30 million records.  Can this measure be optimized?

Sales =

VAR RelationshipV = ALL('Product Sales'[PointBreak],'Product Sales'[Date],'Product Sales'[Total YTD Sales], 'Product Sales'[Sales_key])

VAR PrevPayV =

CALCULATE(Sum('Product Sales'[Total YTD Sales]),
OFFSET(
        -1,
        RelationshipV,
        ORDERBY('Product Sales'[Date],ASC, 'Product Sales'[Total YTD Sales], ASC),
        KEEP,
        PARTITIONBY('Product Sales'[PointBreak])
),'Product Sales'[record_identifier] = "P4", Company[timeframe] = "YTD", REMOVEFILTERS('Product Sales'))

RETURN

PrevPayV

 

Thanks for your help!
Need Help

3 Replies

  • PBIUser9090's avatar
    PBIUser9090
    Regular Visitor
    3 years ago

    amitchandak - Thank you for your reply! In this case I am reversing YTD data into the change since the previous record by pointbreak sorted by date and Total YTD Sales amount.  I think that I need to somehow filter the measure to only iterate on the selected company_key while ignoring all other filters in the visual as the formula works with just one company_key of data, but I have not been able to do this.

  • PBIUser9090's avatar
    PBIUser9090
    Regular Visitor
    3 years ago

    I think I have narrowed down the issue to deployment in Analysis Services.  The measure is working as expected with a subset of the data in PowerBI with an import connection to a SQL database.  I've loaded the same subset of data to Analysis Services with the same OFFSET measure used in PowerBI, but when Analysis Services is connected to PowerBI it returns an error that there isn't enough memory to view the output of the measure.  I have attempted scaling up AS with no difference in outcome.  Is the OFFSET measure not available for use in Analysis Services yet?  Is there some other difference that could be causing the issue?  Thank you for any help!