Poor report performance

Question

Ok, I've hit a wall on a report/dataset and am reaching out for suggestions from this group to improve the performance of my visual.&nbsp;It's a report to calculate AR Aging and gives a historical "as of" view so we can see trends of our Aged AR balances over time. The report and visuals all calculate but one is painfully slow (4+ minutes in desktop and won't even load in Service).&nbsp;I know the slow down is coming from using the FILTER function, which I have to use because I am filtering by a Measure. Hoping that someone else has a better idea than me to achieve the same result.&nbsp;This is the visual that is the problem, and it's only a problem when I want to show the different buckets of our Aged balances. If I remove Aging from the Legend it loads fast. It's only the context of determining the buckets that slows it way down.&nbsp;&nbsp;&nbsp;Here is my DAX.&nbsp;Historical Trend (Aging Summary) = IF (&nbsp; &nbsp; &nbsp;SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] ) &lt; TODAY (),&nbsp; &nbsp; &nbsp;SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE ( 'Aging Key'[Bucket] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1, [Historical Trend (Current)],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2, [Historical Trend (1 - 30 Days)],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3, [Historical Trend (31 - 60 Days)],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4, [Historical Trend (61 - 90 Days)],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5, [Historical Trend (91 Days)],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Historical Trend (Aged Trial Balance)]&nbsp; &nbsp; &nbsp;),&nbsp; &nbsp; &nbsp;BLANK ())&nbsp;Historical Trend (31 - 60 Days) = CALCULATE ( &nbsp; &nbsp; &nbsp;[Historical Trend (Aged Trial Balance)],&nbsp; &nbsp; &nbsp;FILTER ( 'AR Transactions', [Historical Trend (Bucket)] = 3 ))Historical Trend (Bucket) = IF (&nbsp; &nbsp; &nbsp;DATEDIFF ( SELECTEDVALUE ( 'AR Transactions'[Modified Due Date] ), [Historical Trend (HATB Date)], DAY ) &lt;= 0,&nbsp; &nbsp; &nbsp;1,&nbsp; &nbsp; &nbsp;IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEDIFF ( SELECTEDVALUE ( 'AR Transactions'[Modified Due Date] ), [Historical Trend (HATB Date)], DAY ) &lt;= 30,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DATEDIFF ( SELECTEDVALUE ( 'AR Transactions'[Modified Due Date] ), [Historical Trend (HATB Date)], DAY ) &lt;= 60,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEDIFF ( SELECTEDVALUE ( 'AR Transactions'[Modified Due Date] ), [Historical Trend (HATB Date)], DAY ) &lt;= 90,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEDIFF ( SELECTEDVALUE ( 'AR Transactions'[Modified Due Date] ), [Historical Trend (HATB Date)], DAY ) &gt; 90,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp;))&nbsp;Historical Trend (HATB Date) = CALCULATE ( &nbsp; &nbsp; &nbsp;SELECTEDVALUE ('Historical AR Dates (for Trend)'[Date]))&nbsp;Historical Trend (Aged Trial Balance) = VAR Step1 =&nbsp; &nbsp; &nbsp;CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM ( 'AR Apply'[Apply_From_Apply_Amount] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'AR Transactions'[Transaction Type | Number], 'AR Apply'[Applied From Doc Type | Doc Number] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'AR Transactions'[Post_Date] &lt;= SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; 'AR Transactions'[Void_Date] &gt; SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'AR Apply'[Apply_Document_Date] &lt;= SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'AR Apply'[Apply_to_Document_Date] &lt;= SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] )&nbsp; &nbsp; &nbsp;)VAR Step2 =&nbsp; &nbsp; &nbsp;CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM ( 'AR Apply'[Functional Applied Net Amount] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'AR Transactions'[Transaction Type | Number], 'AR Apply'[Applied To Doc Type | Doc Number] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'AR Transactions'[Post_Date] &lt;= SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; 'AR Transactions'[Void_Date] &gt; SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'AR Apply'[Apply_Document_Date] &lt;= SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] )&nbsp; &nbsp; &nbsp;)VAR Step3 =&nbsp; &nbsp; &nbsp;CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM ( 'AR Transactions'[Functional Original Amount] ) + Step1 + Step2,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'AR Transactions'[Post_Date] &lt;= SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; 'AR Transactions'[Void_Date] &gt; SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'AR Apply'[Apply_Document_Date] &lt;= SELECTEDVALUE ( 'Historical AR Dates (for Trend)'[Date] )&nbsp; &nbsp; &nbsp;)RETURNStep3My the Historical AR Dates is a calcualted table to get the month end for all months with this calculated table DAX.&nbsp;Historical AR Dates (for Trend) = &nbsp; &nbsp; &nbsp;CALCULATETABLE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DISTINCT (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECTCOLUMNS ( 'Date Table', "Date", EOMONTH ( 'Date Table'[Date], 0 ) ) ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Date Table'[Year] = 2022&nbsp; &nbsp; &nbsp;)&nbsp;Here are the realtionships.&nbsp;&nbsp;I truely appreciate any guidance / suggestions on how to speed this up from 4+ minutes in desktop to load.&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;，
First, you can use the Performance Analyzer to check which measures are taking longer to execute. After confirming this, you can refer to the following links to optimize your DAX.
1. Identify visuals that are impacting the performance of the report, and identify the reason for the impact by using the&nbsp;Performance Analyzer
Use Performance Analyzer to examine report element performance

2. Optimize DAX
DAX Best Practice Guide
Optimizing DAX Video Course
HOW TO IMPROVE POWER BI PERFORMANCE - PART II
Best Regards

anonymous · Answer

If your amount is a positive amount when is booking is debit (invoice) and the payment is negative (when the invoice is paid you can get the benefit by summarize this field by using this formula:

MEASURE:

AMOUNT DUE PER MONTHEND =

VAR S = SELECTEDVALUE(DATES[LAST DAY IN MONTH])

RETURN

CALCULATE([RUNNING TOTAL AMOUNT],'AR TRANSACTIONS'[DOCUMENTDATE]<= S && 'AR TRANSACTIONS'[PAYDATE > S)

>> FOR INVOICES THAT ARE NOT PAID YET I PUT THE PAY DATE ON 31/12/9999

IN THIS DAX FORMULA A RUNNING TOTAL IS USED:

RUNNING TOTAL AMOUNT =

CALCULATE(

SUM('AR TRANSACTIONS'[AMOUNT]),

FILTER(ALL(DATES[DATE]), DATES[DATE] <= MAX(DATES[DATE])))

In my model is have a join between the date tabel created in DAX and the document date

This only works if you amount column has negative numbers for payments and positive amounts for invoices!!

I have millions of rows and do not have the problem you have. On the other hand for showing the right historical bucket classes I have a quite similar problem. Too much memory problem or the column for the aging classes shows nothing. Still working on that:)

anonymous · Answer

You might try this solution which might mitigate a lot of dax frustating hours...export every month automatically with Power Automate the data of the current outstanding amounts including snapshot_date, aging classes and pile them up on a onedrive folder and then extract them again with PowerBI but only take e.g. the last 6 months

https://www.youtube.com/playlist?list=PLDz00l_jz6zze26MVT-0YV7qcjismMFFo

Forum Discussion

Poor report performance

3 Replies

Related content

Poor ODBC insert performance in Warehouse

Report Performance

Poor Performance of Report

Sales performance Report templates

Report performance on fabric workspace