Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
May 30, 2025

Better model to improve the performance calculated table/Dax measures

Hi Team,

Now, I have an issue with Data model I have developed. Attached is the model i use for my power bi reports that contains 3 dashboards. Invoice quality is one of the dashboards.


Below are the requirements to be created for visuals:
For the Data[DBName-Point_Id] & Data[Date] columns from Data table, we need to find the status(Dax) as below:
*Credit Cancels Invoice -- Number of Credits = Number of Invoices && net cost = 0
*Invoice-Credit Value Mismatch -- ISEVEN(Net invoices) && Net cost <>0
*Multiple Credits -- Number of Credits > Number of Invoices && Net cost <>0
*Multiple Invoices -- Number of invoices >1 && Number of invoices >Number of Credits

We need to use this Status dax in visual y-axis of stacked bar chart and use the Count of Dates from Data table as x-axis:


Number of Credits,Number of invoices, Net invoices and Net cost can dax or a calculated table columns.

Currently, we have created a calculated table using dax logic as below:

Invoice Issues =
CALCULATETABLE (
SUMMARIZE (
Data,
Data[DBName-Point_Id],
Data[Date],
"Invoice Issue", SUM ( Data[Invoice / Credit] ),
"Number of Invoices", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] > 0 ),
"Number of Credits", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] < 0 ),
"Net Cost", SUM ( Data[Cost] )
),
Data[Source] = "Invoice"
)


Based on the above table, Status calculated column in created in same table:

Status =
IF (
'Invoice Issues'[Number of Invoices] > 1
&& 'Invoice Issues'[Number of Invoices] > 'Invoice Issues'[Number of Credits],
"Multiple Invoices",
IF (
'Invoice Issues'[Number of Credits] > 'Invoice Issues'[Number of Invoices]
&& 'Invoice Issues'[Net Cost] <> 0,
"Multiple Credits",
IF (
ISEVEN ( 'Invoice Issues'[Net Invoices] )
&& 'Invoice Issues'[Net Cost] <> 0,
"Invoice-Credit Value Mismatch",
IF (
'Invoice Issues'[Number of Credits] = 'Invoice Issues'[Number of Invoices]
&& 'Invoice Issues'[Net Cost] = 0,
"Credit Cancels Invoice",
BLANK ()
)
)
)
)


This calculated table is then connected to Points and calendar table
DBName-Point_id(points table) --DBName-Point_id(Invoice Issues table)
Date (Calendar table) -- Date (Invoice Issues table)
This setup causes performance issues especially Status column. Is there any better ways to model this to improve the performance?

please let me know if you need further info!

PFA file here PR-419 - Data Coverage - Copy (4)_Daxoptimize_copy.pbix

Thanks in advance!
 SergeiBaklan​ 

admin
BI & Data Analysis
office 365
power bi
training

5 Replies

Sort By
  • Parisian's avatar
    Parisian
    Copper Contributor
    Jun 02, 2025

    Hi, thanks for sharing the details. Performance issues with calculated tables and complex DAX expressions are common with large datasets. To improve performance, consider using measures instead of calculated columns or tables since measures are calculated on the fly and often perform better. Using variables (VAR) in DAX can help avoid repeated calculations and make formulas easier to read. Avoid complex SUMMARIZE functions in calculated tables and try using GROUPBY or measures for aggregations instead. Apply filters as early as possible to reduce the data processed, such as filtering by Data[Source] = "Invoice". Also, check that relationships are correctly set to avoid ambiguity and many-to-many issues. For very large datasets, consider pre-aggregating data outside Power BI to reduce model complexity. If you want, I can help rewrite your DAX into optimized measures based on your needs. Hope this helps.

    • Excellove15's avatar
      Excellove15
      Iron Contributor
      Jun 02, 2025

      Hi SergeiBaklan​ sir,

       

      Apologies for the delay in response!

      I have attached the sample data herewith PR-419 - Data Coverage - Copy (4)_Daxoptimize_copy.pbix

      Can you please help me remodel this into a star schema?

      There are lot of thoughts/ideas running in my mind to optimize this model:

      • Creating star schema(Because current data model has no structure. It is what we call a spagetti model)
      • Power BI’s calculation groups instead of multiple calculated columns. This can offload some logic to runtime and help with performance
      • using dynamic DAX measures that respond to the current filter context. This approach enhances report interactivity, reduces memory overhead, and significantly improves model performance by calculating only what's needed, when it's needed.
      •  Also for transformations that don't need to respond to user interactions, consider performing them in Power Query. This can offload processing from DAX and improve overall performance

       

      Please guide us!🙏

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jun 01, 2025

    Would suggest look into:

     

    1. Prevent large datasets calculation
    2. Refactor status calculation using measures:
    Status Measure =
SWITCH(
    TRUE(),
    [Number of Invoices] > 1 && [Number of Invoices] > [Number of Credits], "Multiple Invoices",
    [Number of Credits] > [Number of Invoices] && [Net Cost] <> 0, "Multiple Credits",
    ISEVEN([Net Invoices]) && [Net Cost] <> 0, "Invoice-Credit Value Mismatch",
    [Number of Credits] = [Number of Invoices] && [Net Cost] = 0, "Credit Cancels Invoice",
    BLANK()
)

    3. Consider aggregating data in Power Query before hits your DAX model

    4. Ensure relationship between Invoice Issues, Point and Calendar to minimize slow filtering

    5. Leverage summarized tables instead of Row-Based calculations

Resources