Visual is slow

[Anonymous]

Hi there,

I have a visual that shows the receivables by Year_periodno like below:

The amount outstanding is calculated by filtering invoicedate before selected date and matchdate is after selected date. Selected date is Calender[date] in the slicer. There is no relationship between the calender table and  the invoices table. I guess this makes it slow. When I activate the relationship between Invoices[Invoice_date] and Calender[Date], I don't get the right values.

Does someone know a good solution?

[v-rongtiep-msft]

Hi @Anonymous ,

Please have a try.

Optimizing DAX functions.

• Do not use scalar variables in SUMMARIZE()

The SUMMARIZE() function is traditionally used to group columns and return resulting aggregations. However, the SUMMARIZECOLUMNS() function is newer and more optimized. Use that instead.

Only use SUMMARIZE() for grouped elements of a table that don’t have any associated measures or aggregations. For example: SUMMARIZE(Table, Column1, Column2)

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , is it a measure like

Before date =
var _max = maxx(allselected(Date),Date[Date])
return
calculate( Sum(Table[Value]), filter(all('Date'), 'Date'[Date] <=_max))

Another option is use before slicer with joined date table

[Anonymous]

It's like this

Amount Outstanding =

var t = SUMMARIZE(FACT_rows,FACT_rows[Invoice_amount], FACT_rows[Invoice_amount_outstanding], FACT_rows[Invoice_date], FACT_rows[Invoice_period], FACT_rows[Invoice_duedate], FACT_rows[Matchdate])

return

CALCULATE(SUMX(test, [Invoice Amount]), FILTER(FACT_rows, FACT_rows[Year-period]<= [selected yearperiod]))

[amitchandak]

@Anonymous , are there any duplicates in data because of which you have used summarized  ?

other wise this should do

CALCULATE(SUM(FACT_rows[Invoice Amount]), FILTER(FACT_rows, FACT_rows[Year-period]<= [selected yearperiod]))

[Anonymous]

Thank you, but that is not the problem. The problem is that when I put the column: Year-period in the x-axis, it takes 12 seconds to load instead of 1 second.

[amitchandak]

@Anonymous , Yes, That is why asking the need for a summarize table. It is creating an in-memory table, which might be taking time.

[Anonymous]

This is what happens when I don't use the summarized table :

[amitchandak]

@Anonymous , Got it.

Solution 1: Join with Date table and use before slicer. There is an option on the down arrow.

Solution 2: If you do a filter on the independent  Date table, You can not use that on axis, Axis should be joined date table or from the FACT_rows

refer if needed

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Anonymous]

Thank you for your help. However, this solutions won't work for this case. Example: Invoicedate = 1-1-2021, it will only show the invoice on 1-1-2021 while itsn't paid for just yet. The payment is in 1-6-2021, so the invoices needs to be in the visual until 1-6-2021.