Optimization of PBI

DAX Optimization:

In this section, we present the recommended practices for optimizing the performance of your report solutions when utilizing DAX.

Tips

Calculated columns

•  Only add calculated columns (instead of using PowerQuery custom columns) for columns that span relationships or multiple rows.

• Use Measures with DAX for calculations that need to be performed at runtime.

Use Aggregation tables

• In DirectQuery or composite semantic models, consider creating aggregation tables to improve navigation and calculations at higher levels of granularity than the fact table. Multiple aggregation tables can exist within a single model, each at a different level of granularity.

Specify column nullability

• Specify the nullability of join columns used in DirectQuery data models. By indicating that a column is required, an inner join will be generated to the underlying database instead of an outer join or an additional test for NULL. This optimization enhances performance.

Avoid using floating point numbers

• Avoid using imprecise data types like FLOAT and DOUBLE, as they can lead to inaccurate aggregation results or missing join data. When working with decimals, it is advisable to use fixed decimal number datatypes, also known as Currency.

Create a Date Dimension in DAX

• Instead of relying on the Auto Date/dateTime functionality, create a custom Date dimension in DAX. This approach provides more control and flexibility. Refer to resources here and here for guidance.

Utilize Calculation groups

• Instead of duplicating calculations for multiple measures, consider using calculation groups.

• Calculation groups are beneficial when a model has multiple measures requiring additional calculations based on time, such as current month, YTD, or average of the last three months.

• Creating a calculation group simplifies the management of time-based measures. Learn more here.

Consistently use DISTINCT() and VALUES() functions

• Power BI adds a blank value to a column if it detects a referential integrity violation. When performing direct queries, Power BI adds blanks to columns due to the inability to check for violations.

• Note the differences between DISTINCT() and VALUES() functions:

  • DISTINCT(): Does not include blanks added due to integrity violations. It only includes blanks that are part of the original data.

  • VALUES(): Returns both original blanks and blanks added by Power BI due to referential integrity violations.

  • To maintain consistent values for blank columns, ensure the consistent use of DISTINCT() and VALUES() functions throughout the entire report. If blank values are not an issue, using the VALUES() function is recommended.

Use ISBLANK() instead of =Blank() check

• Instead of using the comparison operator = Blank() to check for blank values, use the built-in ISBLANK() function. While = Blank() returns for blank values or empty strings, ISBLANK() exclusively checks for blanks.

Prefer SELECTEDVALUE() over VALUES() for single value comparison

• The VALUES function can return a table with multiple rows, which results in an error when comparing it to a scalar value. To avoid this error, it is recommended to use the SELECTEDVALUE() function when comparing single values.

Utilize variables instead of repeating measures inside the IF branch

• To avoid calculating the same expression multiple times, store the resulting measure value in a variable. By using variables, you can reference the stored value whenever needed, reducing repetitive calculations. Note that variables act as constants.

Use IFERROR instead of IF(ISERROR)

• Instead of using ISERROR within an IF statement to check for a result, utilize the IFERROR function. This saves one function call. Ideally, use DAX functions that have error handling built-in, such as DIVIDE.

Prefer the DIVIDE function over division operator

• Instead of using the division operator /, use the DIVIDE function. The DIVIDE function handles division by zero and returns a blank instead of throwing an error.

Use COUNTROWS instead of COUNT to get the number of rows in a table

• For efficiency and to exclude blanks in column values, use COUNTROWS instead of COUNT when counting the number of rows in a table.

By implementing these best practices, you can optimize the performance of your report solutions when working with DAX.

Performance best practices:

1. Minimize the number of visuals in dashboards and reports:-

   To maintain optimal report performance, avoid overloading a single report with too many visuals. Restrict widget visuals to a maximum of eight per report page and limit grids to one per page. For dashboards, keep the number of tiles limited to 10. As a general guideline, aim to keep the total points for all visuals on a page below 30, considering that each visual type has a different point value.

   
   

2. Control the number of Pages in a Report:-

   To ensure efficient performance, it is advisable to limit the number of pages in a report to a maximum of 10. By keeping the report concise and focused, you can enhance the overall user experience.

   
   

3. Eliminate unnecessary interactions between visuals:-

   By default, all visuals on a report page can interact with each other. However, disabling unnecessary interactions can significantly reduce the number of queries executed at the backend, leading to improved report performance. Evaluate the interactions between visuals and disable any that are not essential for the analysis.

   
   

4. Assess custom visual performance before implementation:-

   Custom visuals may exhibit poor performance when dealing with large datasets or complex aggregations. It is important to test the performance of custom visuals before utilizing them in your reports. Note that uncertified custom visuals are generally not tested by the Power BI team. If a custom visual performs poorly, consider replacing it with a different visual. Power BI-certified custom visuals, available on AppSource, have undergone rigorous quality testing by Microsoft and are known to have robust and efficient code. These certified visuals can also be viewed in Export to PowerPoint and email subscriptions.

   
   

5. Limit the complexity of measures and aggregations in data models:-

   Instead of using calculated columns, opt for calculated measures whenever possible. Additionally, it is beneficial to push calculated columns and measures closer to the data source. The closer they are to the source, the better the performance is likely to be. Minimizing the complexity of measures and aggregations helps improve overall data model performance.

   
   

6. Avoid hierarchical filters:-

   To enhance performance in Power BI, it is recommended to remove hierarchical filters. Instead, utilize multiple filters for the hierarchy. This approach can lead to improved query execution and faster performance.

   
   

7. Utilize tooltips to provide additional information:-

   Report tooltips offer a space-efficient way to provide supplementary information within a report. However, it is important to use tooltips judiciously and limit the number of visuals included in tooltips to avoid overwhelming the user with excessive information.

   
   

8. Utilize report backgrounds for static images:-

   When including static images in your report, consider using report backgrounds instead of multiple visuals. By utilizing report backgrounds, you can convey the same information to the end user while minimizing the performance impact.

   
   

9. Reduce the amount of data loaded on each page:-

   To optimize page load time, consider utilizing bookmarks, drillthrough pages, and tooltips to reduce the amount of data loaded on a page. By selectively loading data based on user interactions, you can improve the overall performance of the report, especially for landing pages.

   
   

10. Use slicers sparingly

    Slicers provide an interactive way for users to navigate data. However, each slicer generates two queries: one to retrieve the data and another to fetch selection details. Adding too many slicers can significantly slow down performance. Evaluate the necessity of each slicer and remove any unnecessary ones using the Filter pane.

    
    

11. Minimize the use of "Show Items with no data":-

    When creating visuals, Power BI displays only relevant data to manage how data is presented. It is recommended to avoid enabling the "Show items with no data" option for categories (dimensions) unless absolutely necessary. Setting up proper relationships in the data model can eliminate the need for this option and improve performance.

By following these best practices, you can optimize the performance of your report solutions and ensure a smooth experience for users interacting with your Power BI reports.

DAX variables usage or application:

Using variables in DAX expressions improves code readability and eliminates redundancy by storing and referencing expressions. Let examine the following code as an example:

 IF (

    ISERROR ( Orders[Values] / Orders[Quantity] ),

    BLANK (),

    Orders[Values] / Orders[Quantity]

)

As you can see, the expression Orders[Values] / Orders[Quantity] is repeated twice.

To address this, we can utilize variables in DAX. Variables are declared using the VAR keyword, followed by the variable name and its definition. Each variable is then followed by a RETURN section, which specifies the value that the variable represents. Multiple variables can be defined within an expression, and they are scoped locally to that expression.

It important to note that variables defined in an expression cannot be accessed outside of that expression, as there are no global variables in DAX. Additionally, variables in DAX are computed using lazy evaluation. This means that if a variable is defined but not used in the code, it will not be evaluated. Once computed, the value of a variable is stored and reused for subsequent references within the same expression, making them efficient for optimizing complex expressions.

Variables in DAX can store either a single value (scalar) or a table. They can have a different type than the final expression returned after the RETURN keyword. Within the same VAR/RETURN block, multiple variables can have different types, whether scalar values or tables.

One common use of variables is to break down complex formulas into logical steps by assigning intermediate results to variables. This approach enhances code readability and simplifies the understanding of complex calculations. Additionally, the VertiPaq engine is optimized to run DAX code with variables more efficiently, as it better understands the developer intentions.

Although named variables, in DAX, they function more like constants. Once assigned a value, a DAX variable cannot be altered. Evaluation of variables occurs only within the scope of their definition (VAR) and not when their value is utilized.

DAX evaluates the variable within the evaluation context where it is defined, rather than where it is utilized. However, the evaluation of the variable itself is delayed until its initial use. This approach, known as lazy evaluation, is crucial for performance as it ensures that a variable not used in an expression will not be evaluated. Furthermore, once a variable is computed for the first time, it will not be recomputed within the same scope.

In some scenarios, such as when applying CALCULATE, it is important to note that the Order Value variable retains its original value and remains unchanged. Its value is computed only once, and subsequent references to the variable return the same value, regardless of the filter context applied by CALCULATE.

However, it essential to be cautious when using variables inside iterators. For example, the following DAX code will return an incorrect (larger) value:

 Order Value =
    SUMX(
        Orders,
        Orders[Quantity] * RELATED(Products[UnitPrice])
    )

Wrong Var in Iteration

VAR OrderValue_outside_iteration__ = [Order Value]
RETURN
    SUMX (
        DimCalendar,
        OrderValue_outside_iteration__
    )

This occurs because the total value of the measure [Order Value] is computed once and then repeatedly summed for each row in the DimCalendar table. As a result, [Order Value] is not evaluated individually for each date, but rather the same value is calculated for each date.

To correct this, the measure should be written as follows:

Order Value =
    SUMX(
        Orders,
        Orders[Quantity] * RELATED(Products[UnitPrice])
    ) 

Correct Var inside Iteration =
RETURN
    SUMX (
        DimCalendar,
        VAR OrderValue_inside_iteration__ = [Order Value]
        RETURN
        OrderValue_inside_iteration__
    )

Lazy evaluation and the constant behavior of variables can be beneficial in certain scenarios, such as navigating between nested row contexts, thereby avoiding the use of the EARLIER function.

By leveraging variables in DAX expressions, you can enhance code readability, eliminate repetition, and optimize performance in your Power BI reports and calculations.