Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

DAX optimization problem

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- 

IDMonth_YearKPI1KPI2
1Jan-20251224
1Feb-20252010
1Apr-20254060

 

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

IDKPI1KPI2
1Sum of values between selected monthsSome 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's avatar
    v-priyankata
    1 year ago

    Hi Anonymous 
    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.

  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    Hi Anonymous 

    You're working with a Power BI report that includes a single table visual displaying over 170 columns, many of which are complex DAX measures that use filters, date ranges, and conditional logic. These measures are calculated dynamically based on slicer selections (like "From Month" and "To Month") and involve row-level computations, including filters using TOPN, CALCULATE, and VAR logic. Because all these measures are evaluated simultaneously for each row in the visual, and the table has a wide structure, the report is consuming excessive memory and computational resources, resulting in the "Resources exceeded" error—especially since you're not using Premium capacity. Additionally, you have a filter condition (Date_Filter) that adds further complexity by evaluating each row's status and date range to conditionally include or exclude data.

     

    To improve performance, it's recommended to break the large visual into smaller sections using bookmarks or multiple pages, thereby reducing the memory footprint per visual. Also, pre-aggregating data at the query level (using Power Query or SQL) or creating intermediate summary tables in DAX can help avoid expensive row-level filtering and repeated logic across measures. You can also optimize the measures by avoiding redundant use of FILTER and TOPN, and instead use simpler filter expressions within CALCULATE. If possible, move logic like Date_Filter into a calculated column during data load, which will help offload the logic from the visual evaluation at runtime. Finally, use the Performance Analyzer in Power BI Desktop to identify which specific measures are causing the most delay, and optimize those first. These steps should collectively help you avoid memory overload and improve responsiveness, even without Premium capacity.

     

7 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

     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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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

  • v-priyankata's avatar
    v-priyankata
    Community Support
    1 year ago

    Hi Anonymous 
    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.

    • v-priyankata's avatar
      v-priyankata
      Community Support
      1 year ago

      Hi Anonymous 

      I hope the information shared was helpful to you. If your question has been answered, kindly mark the most relevant reply as the Accepted Solution. This small action can make a big difference for others who are looking for the same solution.

       

      • v-priyankata's avatar
        v-priyankata
        Community Support
        1 year ago

        Hi Anonymous 

        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    Hi Anonymous 

    You're working with a Power BI report that includes a single table visual displaying over 170 columns, many of which are complex DAX measures that use filters, date ranges, and conditional logic. These measures are calculated dynamically based on slicer selections (like "From Month" and "To Month") and involve row-level computations, including filters using TOPN, CALCULATE, and VAR logic. Because all these measures are evaluated simultaneously for each row in the visual, and the table has a wide structure, the report is consuming excessive memory and computational resources, resulting in the "Resources exceeded" error—especially since you're not using Premium capacity. Additionally, you have a filter condition (Date_Filter) that adds further complexity by evaluating each row's status and date range to conditionally include or exclude data.

     

    To improve performance, it's recommended to break the large visual into smaller sections using bookmarks or multiple pages, thereby reducing the memory footprint per visual. Also, pre-aggregating data at the query level (using Power Query or SQL) or creating intermediate summary tables in DAX can help avoid expensive row-level filtering and repeated logic across measures. You can also optimize the measures by avoiding redundant use of FILTER and TOPN, and instead use simpler filter expressions within CALCULATE. If possible, move logic like Date_Filter into a calculated column during data load, which will help offload the logic from the visual evaluation at runtime. Finally, use the Performance Analyzer in Power BI Desktop to identify which specific measures are causing the most delay, and optimize those first. These steps should collectively help you avoid memory overload and improve responsiveness, even without Premium capacity.