1

For this DAX, I can select individual Items one-by-one and it will populate an actual rate. The issue is a select all situation. If I wish to see all locations Actual Rates, The query exceeds resources, and the visual breaks.

Is there any way I can simplify or optimize this formula? Am I using too many variables?

VAR maxinventorydate = 
    CALCULATE(
        MAX(fct_fsr_inputs[ReportStartDate]),
        REMOVEFILTERS('Date')
    )

VAR beginventorydate = 
    CALCULATE(
        MAX(fct_fsr_inputs[ReportStartDate]),
        FILTER(ALL('Date'), 'Date'[Date] < maxinventorydate)
    )

VAR gallonsdelivered = 
    VAR gallons = 
        CALCULATE(
            SUM(fct_invoice_lines[Gallons]),
            FILTER(ALL('Date'), 'Date'[Date] < maxinventorydate && 'Date'[Date] > beginventorydate)
        )

    VAR stgallons = 
        CALCULATE(
            SUM(fct_st_invoices[Quantity]),
            FILTER(ALL('Date'), 'Date'[Date] < maxinventorydate && 'Date'[Date] > beginventorydate)
        )
        RETURN IF(ISBLANK(gallons),stgallons,gallons)

VAR endinginventory = 

CALCULATE(MAX(fct_fsr_inputs[Tank Inventory]),
FILTER(ALL('Date'), 'Date'[Date] = maxinventorydate)
)
VAR beginninginv = 

CALCULATE(MAX(fct_fsr_inputs[Tank Inventory]),
FILTER(ALL('Date'), 'Date'[Date] = beginventorydate)
)


VAR days = DATEDIFF(beginventorydate,maxinventorydate,DAY)

VAR useage = (beginninginv + gallonsdelivered) - endinginventory

VAR actualrate = useage/days
RETURN 
actualrate/4 ``` 
3
  • As a test, update it to return days and see if you get the same issue. Also is this something you can move to a Calculated Column? Commented Aug 11 at 16:08
  • What is the actual error? Commented Aug 11 at 23:29
  • you are repeatedly using FILTER(ALL('Date'), …) and calculating things row-by-row inside VARs, which means Power BI might be scanning huge date ranges multiple times. Avoid this and use DIVIDE :) Commented Aug 12 at 11:56

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.