Optimizing DAX query

[hoosha_11]

Hi,

I've included four DAX queries in one of the tables, each with a different QuestionID. But, when I try to refresh the dataset, it fails due to reaching the memory capacity limit of the premium capacity, despite the dataset being small.

Would changing "ALL" to "ALLEXCEPT" help in this situation?

Do you have any ideas on how I can optimize this query?

Thanks a lot!

Response =
CALCULATE(
MAX(Logs[Response]),
FILTER(
ALL(Logs),
Logs[InspectionId] = EARLIER ( Logs[InspectionId]) && Logs[QuestionID] = "112"
))

[v-zhouwen-msft]

Hi @tamerj1 ,thanks for the quick reply, I'll add further.

Hi @hoosha_11 ,

Regarding your question, I noticed that you are using the 'EARLIER' function, try to use a variable instead of the EARLIER function to improve the column definition of the calculation.

Response =
VAR CurrentInspectionId = Logs[InspectionId]
RETURN
    MAXX(
        FILTER(
            Logs,
           Logs[InspectionId] = CurrentInspectionId && Logs[QuestionID] = "112"
        ),
     Logs[Response]
    ) 

Use variables to improve your DAX formulas - DAX | Microsoft Learn

[tamerj1]

Hi @hoosha_11 

Looks like a calculated column if I'm not mistaken. 
If this is the case better to avoid CALCULATE and follow a No CALCULATE approach such as

Response =
MAXX (
FILTER (
Logs,
Logs[InspectionId] = EARLIER ( Logs[InspectionId] )
&& Logs[QuestionID] = "112"
),
Logs[Response]
)

[hoosha_11]

Thank you @tamerj1 

Yes they are calculated columns. I tried the MAXX as you mentioned and I still get the memory error during a refresh. We are on A2 SKU with 5GM memory limit and the PBIX file is small.

[tamerj1]

@hoosha_11 

When you say "calculate columns", like how many? What DAX are you using to calculate them? How many columns and rows the table is?