{"id":38370,"date":"2020-08-04T16:00:18","date_gmt":"2020-08-04T15:00:18","guid":{"rendered":"https:\/\/www.microsoft.com\/en-gb\/industry\/blog\/?p=38370"},"modified":"2020-12-18T23:49:40","modified_gmt":"2020-12-18T22:49:40","slug":"power-bi-performance-tuning-workflow-part-2","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-gb\/industry\/blog\/technetuk\/2020\/08\/04\/power-bi-performance-tuning-workflow-part-2\/","title":{"rendered":"Power BI Performance Tuning Workflow – Part 2"},"content":{"rendered":"

\"An<\/p>\n

In Part 1 of the series<\/a>, you learned how to ingest, transform, and load data, and how to develop and optimise the Power BI data model. This article provides guidance that enables developers to write optimised DAX expressions and design reports, as well as enabling administrators to deploy and manage Power BI solutions.<\/p>\n

 <\/p>\n

DAX\u00a0Calculations<\/h2>\n

Variables vs Repeatable Measures<\/h3>\n

Let’s take a look at DAX Measures versus DAX with Variables:<\/p>\n

DAX Measures<\/strong><\/p>\n

\"A<\/p>\n

DAX with Variables<\/strong><\/p>\n

\"A<\/p>\n

In the second scenario, the measure Total Rows in the first scenario is executed twice, whereas it is executed only once in the variables scenario. Under such scenarios, variables can improve performance significantly.<\/p>\n

 <\/p>\n

Handling Blanks<\/h3>\n
Sales (No Blank<\/span>) =\r\nIF<\/span> (\r\n    ISBLANK<\/span>([Sales]),\r\n    0,\r\n    [Sales]\r\n)<\/pre>\n
It’s recommended that measures return BLANK when a meaningful value cannot be returned, as this design allows Power BI to render reports faster. Also, returning BLANK is efficient because report visuals\u2014by default\u2014eliminate groupings when summarisations are set to BLANK.<\/p>\n
 <\/p>\n
SELECTEDVALUE () vs\u00a0HASONEVALUE ()<\/h3>\n
A common scenario is to use HASONEVALUE() to check if there is only one value present in a column after applying slicers and filters, and then use the VALUES(column name) DAX function to get the single value.<\/p>\n
SELECTEDVALUE () performs both the above steps internally and gets the value if there is only one distinct value present in that column, or returns blank in case there are multiple values available.<\/p>\n
 <\/p>\n
SELECTEDVALUE () vs VALUES ()<\/h3>\n
VALUES () will return an error if it encounters multiple values. Normally, users handle it using error functions which are bad for performance. Instead of using that, SELECTEDVALUE () must be used. It is a better approach and returns blank in case of multiple values being encountered.<\/p>\n
 <\/p>\n
DISTINCT () vs VALUES ()<\/h3>\n
Power BI adds a blank value to the column in case it finds a referential integrity violation. For direct queries, Power BI by default adds a blank value to the columns as it does not have a way to check for violations.<\/p>\n
DISTINCT ()<\/strong>: does not return blank when encountering an integrity violation. It returns blank only if it is in part of the original data.<\/p>\n
VALU<\/span><\/strong>ES ()<\/strong>: includes blank, as it’s added by Power BI due to referential integrity violations.<\/span><\/p>\n
The usage of either of the functions should be the same throughout the whole report. Use VALUES () in the whole report if possible so that blank values are not an issue.<\/p>\n
 <\/p>\n
Avoid FORMAT in measures<\/h3>\n
Format functions are done in a single-threaded formula engine and slows down the calculation for large numbers of string values.<\/p>\n
 <\/p>\n
Optimize Virtual Relationships using TREATAS ()<\/h3>\n
A virtual relationship is simulated with DAX and column equivalency. FILTER|CONTAINS executes slower than\u00a0TREATAS.<\/p>\n
\"An<\/p>\n
 <\/p>\n
ISBLANK () vs Blank ()<\/h3>\n
The built-in function ISBLANK () to check for blank values is faster than using the comparison operator \u201c= Blank ()\u201d<\/p>\n
 <\/p>\n
Ratio Calculation efficiently<\/h3>\n
Use (a-b)\/b with variables instead of (a\/b)-1. The performance is the same in both cases usually, but under edge cases when both a and b are blank values, the former will return blanks and filter out the date whereas the latter will return -1 and increase the query space.<\/p>\n
 <\/p>\n
DIVIDE () vs \/<\/h3>\n
DIVIDE () function has an extra parameter which is returned in cases where the denominator value is zero. For scenarios that might have a zero in the denominator, it is suggested to use DIVIDE () as it will internally check if the denominator is zero. It also checks for ISBLANK (). However, if there can be a guarantee that the denominator will be non-zero, it is better to use \/ because DIVIDE() will perform an additional if ( denominator <> 0 ) check.<\/p>\n
 <\/p>\n
Avoid IFERROR () and ISERROR ()<\/h3>\n
IFERROR () and ISERROR () are sometimes used in measure. These functions force the engine to perform a step by step execution of the row to check for errors. So wherever possible, replace with the in-built function for error checking.<\/p>\n
Example: DIVIDE () and SELECTEDVALUE () perform an error check internally and return expected results.<\/p>\n
 <\/p>\n
COUNTROWS () vs COUNT ()<\/h3>\n
When the intention is to count table rows, it is recommended that you always use the COUNTROWS function. It’s more efficient and doesn’t consider BLANKs contained in any column of the table.<\/p>\n
 <\/p>\n
SUMMARIZE () Vs\u00a0SUMMARIZECOLUMNS ()<\/h3>\n
SUMMARIZE is used to provide aggregated results by performing groups of actions on the columns. It is recommended to use the SUMMARIZECOLUMNS () function, which is the optimised version. SUMMARIZE should only be used to get just the grouped elements of a table without any measures\/aggregations associated with it.<\/p>\n
 <\/p>\n
FILTER (all (ColumnName)) vs\u00a0FILTER (VALUES ()) vs FILTER(Table)<\/h3>\n
Instead of applying a filter to the whole table, it is better to apply filters to a desired column. Also, use ALL with the FILTER function if there is no specific need to keep the current context. To calculate measures while ignoring all the filters applied on a column, use the All (ColumnName) function along with FILTER instead of Table or VALUES ().<\/p>\n
 <\/p>\n
Avoid\u00a0AddColumns () in measure expression<\/h3>\n
By default, measure expressions are calculated in an iterative manner. Adding Addcolumns will convert it to a nested loop and further slow down the performance.<\/p>\n
 <\/p>\n
Complex Row Level Security<\/h2>\n