Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Visual is slow

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?

Need Help

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago
      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's avatar
        amitchandak
        Super User
        4 years ago

        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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.