DAX optimization problem

[PowerBIDAXProb]

Hello everyone,

I am working on a report with a single table visual which has some 170 columns (including measures). 
The data that I am getting is like following- 

ID	Month_Year	KPI1	KPI2
1	Jan-2025	12	24
1	Feb-2025	20	10
1	Apr-2025	40	60

Now, based on this data and two filters (From Month and To Month), I am creating some measures to show the final data as 

ID	KPI1	KPI2
1	Sum of values between selected months	Some logic defined below

Some of the measures are normal - 

var FromMonth = MAX('From-Month/YearMaster'[FromMonthYearKey])

var ToMonth = MAX('To-Month/YearMaster'[ToMonthYearKey])

RETURN

CALCULATE (

sum ( 'Table'[KPI1] ), 'Table'[MonthYearKey] >= FromMonth, 'Table'[PostingMonthYearKey] <= ToMonth
)

Some other measures are as per below logic:

Opening KPI2 = 

var filteredTable = FILTER('Table', 'Table'[MonthYearKey] >= SELECTEDVALUE('From-Month/YearMaster'[FromMonthYearKey]))

RETURN

CALCULATE(

    MAX('Table'[Opening WIP Expense]),

    TOPN(1,filteredTable,'Table'[MonthYearKey],ASC)

)

Here, we just need to pick up the first value from the table after filtering from From Month. 

When all these KPIs are viewed together in one visual, it is taking a lot of memory and giving the Resources exceeded error.

Do you have any suggestions to optimize these measures?

One more thing, there is one more filter applied on the visual. Let me explain that logic as well:
There can be 3 statuses for 1 ID. Status A is fine. But, if the status is B or C, then you need to show only those IDs where the Status_Date is between selected From and To months.

For that I have applied following filter:
Date_Filter = 

var FromMonth = MAX('From-Month/YearMaster'[FromMonthYearKey])

var ToMonth = MAX('To-Month/YearMaster'[ToMonthYearKey])

var stat_date= MAX('Table'[statMonthYearKey])

var Stat = MAX('Table'[STATUS])

RETURN

    IF(Stat = "A",1,

    IF(stat_date>= FromMonth && stat_date<= ToMonth, 1,0))

Any idea on optimizing this also?

Premium capacity is not being considered by the management and asking to optimize this as much as possible

[v-priyankata]

Hi @PowerBIDAXProb 
Thank you for reaching out to the Microsoft Fabric Community.

I have reproduced your scenario in Power BI using your logic for KPI calculations, From/To month filters, and the conditional status filtering. After implementing your measures and applying the visual-level filter based on Date_Filter, I was able to get the expected output as per your requirement.

For your reference, I’m attaching the .pbix file I used for this validation so you can review and adjust it to fit your full dataset.

If this information is helpful, please “Accept as solution”  to assist other community members in resolving similar issues more efficiently.
Thank you.

[bhanu_gautam]

 Optimized Sum Measurevar FromMonth = MAX('From-Month/YearMaster'[FromMonthYearKey])
var ToMonth = MAX('To-Month/YearMaster'[ToMonthYearKey])
RETURN
CALCULATE (
SUM('Table'[KPI1]),
'Table'[MonthYearKey] >= FromMonth,
'Table'[MonthYearKey] <= ToMonth
)

Optimized Opening KPI2 Measure

DAX
var FromMonth = MAX('From-Month/YearMaster'[FromMonthYearKey])
var filteredTable = FILTER('Table', 'Table'[MonthYearKey] >= FromMonth)
RETURN
CALCULATE(
MAX('Table'[Opening WIP Expense]),
TOPN(1, filteredTable, 'Table'[MonthYearKey], ASC)
)

 

Optimized Date Filter Measure

DAX
var FromMonth = MAX('From-Month/YearMaster'[FromMonthYearKey])
var ToMonth = MAX('To-Month/YearMaster'[ToMonthYearKey])
var stat_date = MAX('gold Report_Revenue_By_Engagement'[statMonthYearKey])
var Stat = MAX('gold Report_Revenue_By_Engagement'[STATUS])
RETURN
IF(
Stat = "A",
1,
IF(
stat_date >= FromMonth && stat_date <= ToMonth,
1,
0
)
)

 

Reduce the number of columns in the visual: Since you have 170 columns, try to reduce the number of columns displayed in the visual. Only include the necessary columns to minimize memory usage.

Use variables to store intermediate results: This can help in reusing the calculated values and avoid recalculating them multiple times.

Filter data early: Apply filters as early as possible in your calculations to reduce the amount of data being processed.

Simplify calculations: Simplify your DAX measures where possible. For example, avoid using complex nested calculations if they can be broken down into simpler steps.

Optimize the use of CALCULATE and FILTER: Ensure that you are using these functions efficiently. For example, avoid using FILTER inside CALCULATE if you can achieve the same result with simpler conditions.

[PowerBIDAXProb]

Hey,

Thanks for replying. But, I think you have just formatted the same measures. Only change I see is in the second measure where you have added a new variable. Could you please tell if I am missing something?

Also, could you please edit your message to replace the table name with 'Table' for security reasons. My bad.. I mentioned it earlier in the main question