Calculated Table causes performance issue and dataset refresh fails

[Excellove15]

Hi @Sergei Baklan ,

I have a calculated table called 'Invoice Issues' as below which is derived from the existing Data table:

The main purpose of this table is to create a calculated column called 'Status' which is used in visual:

calculated column:

Visual:

This works perfectly and our report is liked by the customers.But this causes lot of performance issues and dataset report refresh fails in workspace.

Is there a way to creat this as a dax measure or any other alternate solutions is much appreciated?

PFA file herewith PR-419 - Data Coverage RLS ADB (1).pbix

Thank you!

[#2]

@Excellove15 

I've carefully analyzed the information you've provided, including the image, the feedback from the ratings, and the insights from experts. Here's a comprehensive response that addresses the performance and refresh issues caused by the calculated table in your Power BI report:

Understanding the Issue:

• The calculated table Invoice Issues with its nested IF statements likely leads to complex calculations, impacting performance during data refresh and visual interactions.
• Converting the entire logic to a single measure might not be feasible or efficient, especially for filtering or conditional formatting based on multiple criteria.

Potential Solutions:

1. Simplify Calculated Table Logic:

   • Break down the Status column calculation into smaller, simpler steps using helper columns or measures. This can significantly reduce complexity and improve performance.
   • Consider using SWITCH or VAR functions for more concise and efficient logic, if applicable.

2. Create Separate Measures for Filtering and Conditional Formatting:

   • If the Status column is primarily used for these purposes, create separate measures:
     • Filtering: Use CALCULATE or HASONEVALUE to filter based on specific conditions.
     • Conditional Formatting: Use measures with IF statements to dynamically format visuals based on calculated criteria.

Example (Assuming Filtering by Invoice Issue):

Code snippet

; Measure for filtering by 'Invoice Issue' StatusFilterMeasure = VAR CurrentInvoiceIssue = SELECTEDVALUE('Data'[Invoice Issue]) RETURN CALCULATE( SUMX( 'Data', [Amount] ), 'Data'[Invoice Issue] = CurrentInvoiceIssue )

1. Optimize Data Model:

   • Ensure proper relationships between tables to avoid unnecessary calculations and improve query efficiency.
   • Consider materialized views (available in Power BI Premium) to pre-calculate complex expressions and enhance query performance.

2. Investigate Hardware and Resource Constraints:

   • If your workspace or dataset experiences resource limitations, consider:
     • Upgrading to a higher service tier (Power BI Premium or Pro) for increased processing power.
     • Optimizing report visuals and filters to reduce complexity.

Additional Tips:

• Thoroughly test any changes in a development environment before deploying to production.
• Monitor performance after implementing solutions to identify improvements and potential areas for further optimization.
• Seek assistance from experienced Power BI developers or the Microsoft community for more complex scenarios.

Key Considerations:

• The provided code snippet suggests using SELECTEDVALUE within the calculated table's Status column, which can negatively impact performance during visual interactions. Consider refactoring the logic using measures or helper columns to avoid this issue.
• The specific approach will depend on the exact requirements of your report and the complexity of the Status column logic. Carefully evaluate your needs and choose the solution that best balances performance, functionality, and maintainability.

By following these guidelines and carefully considering the feedback from the ratings, you should be able to effectively address the performance and refresh issues while maintaining the desired functionality in your Power BI report

[#3 · Accepted solution]

@Excellove15 

First few general notes. You didn't disable Time Intelligence for the model

as result you have a lot of generated hidden date tables

which could significantly decrease of overall performance. Better to mark Calendar table as date one and use only it disabling Time Intelligence. However, on this step that could crash the model, if to do that when step by step.

Next, if you don't really need datetime use date Date data type instead. For your initial calculated table that decreases the refresh time on about 30%. Also, depends on how critical the precision is, better to use Fixed decimal number instead of Decimal number data type.

Back to calculated table I don't think we may avoid it. But the may decrease it a bit. First, creating explicit measures

which are obvious, like

0 number of Credits = CALCULATE(
    COUNTROWS(Data),
    Data[Source] = "Invoice",
    Data[Invoice / Credit] < 0
)

With that calculated table could be

CALCULATETABLE(
    DISTINCT(
        SELECTCOLUMNS( Data, Data[DBName-Point_Id], Data[Date] )
    ),
    Data[Source] = "Invoice"
)

with adding to it calculating column as in your variant

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

Alternatively we may create the table with Status column from scratch

VAR selectData =
    DISTINCT(
        SELECTCOLUMNS( Data, Data[DBName-Point_Id], Data[Date] )
    )
RETURN
    CALCULATETABLE(
        ADDCOLUMNS(
            selectData,
            "Status",
                SWITCH(
                    TRUE( ),
                    [0 Number of Invoices] > 1
                        && [0 Number of Invoices] > [0 Number of Credits], "Multiple Invoices",
                    [0 Number of Credits] > [0 Number of Invoices]
                        && [0 Net Cost] <> 0, "Multiple Credits",
                    ISEVEN( [0 Invoice Issue] ) && [0 Net Cost] <> 0, "Invoice-Credit Value Mismatch",
                    [0 Number of Credits] = [0 Number of Invoices]
                        && [0 Net Cost] = 0, "Credit Cancels Invoice",
                    BLANK( )
                )
        ),
        Data[Source] = "Invoice"
    )

Both are in the model, I added only this visuals for them

DAX Studio shows bit better performance, but not critically. On the other hand on my PC all 3 variants work.

File is here TechComm - OneDrive (live.com)

[#4]

Hi @Sergei Baklan 

Many thanks for the wonderful explanation with amazing solutions!

This has worked for this report and increased the performance drastically!

As always you came to our rescue and thanks a lot again! especially screenshots you provided for auto generated date tables cautioned us to turn off the time intelligence!

We have also, put all your points into our guide so that we can refer in future!

I will mark this as the best response

As always, please let me know if i can do anything from my end as a gesture of thanks giiving

Thanks 

[#5]

@Excellove15 

Thank you for the feedback, great to know that helped.