Best Practices in Power BI

June 1, 2022

By Allurean
0 comments

· Write Explicit measures in DAX instead of Implicit

In PBI Desktop, any column with numerical values can be used as a measure in a visualization, or it can be referenced in a measure. When you use such a column, it’s an implicit measure. When you write a DAX formula to create a measure, it’s an explicit measure. The best practice is to use explicit measures everywhere, even for simple measures like sum, min, max and average. The reason behind this is that if you use implicit measures in the formula of other measures and at some point, the business logic changes – meaning you need to write a formula for this basic measure – you will need to update every single measure where this base measure was used. Explicit measures promote reusability and make it easier to refactor your code.

In your model, you can recognize implicit measures by the summation symbol ∑ (sigma), and explicit measures by the calculator icon.

Ideally, every numerical column which might lead to implicit measures is hidden (with the exception of a column containing years or months for example) and explicit measures are created on top of those columns.

Using DIVIDE function instead of divide operator (/)

As a data modeler, when you write a DAX expression to divide a numerator by a denominator, you can choose to use the DIVIDE function or the divide operator (/ – forward slash).

It’s recommended that you use the DIVIDE function whenever the denominator is an expression that could return zero or BLANK()

Profit Margin = [Profit] /[Sales] – Not recommended

Profit Margin = DIVIDE([Profit], [Sales]) – Recommended

Consider the following measure definition that explicitly converts BLANK results to zero:

Sales (Blank to 0) =
IF(
    ISBLANK([Sales]),
    0,
[Sales]
)

Carefully consider whether the DIVIDE function should return an alternate value.
For measures, it’s usually a better design that they return BLANK.

Returning BLANK is better because report visuals - by default - eliminate groupings when summarizations are BLANK. It allows the visual to focus attention on groups where data exists. In that way you can hide rows / columns that are not relevant.

It's recommended that your measures return BLANK when a meaningful value cannot be returned.

Let’s see what happens when the Profit Margin measure is added to a table visual, grouping by customer.

The table visual displays an overwhelming number of rows. (There are in fact 18,484 customers in the model, and so the table attempts to display all of them.) Notice that the customers in view haven’t achieved any sales. Yet, because the Profit Margin measure always returns a value, they are displayed.

When there are too many data points to display in a visual, Power BI may use data reduction strategies to remove or summarize large query results.

Let’s see what happens when the Profit Margin measure definition is improved. It now returns a value only when the Sales measure isn’t BLANK (or zero).

Profit Margin = DIVIDE([Profit], [Sales]) – Recommended

It's recommended that your measures return BLANK when a meaningful value cannot be returned.

The following measure expression produces a safe division, but it involves using four DAX functions.

Profit Margin (No Blank and zero) =
IF(
    OR(
        ISBLANK([Sales]),
        [Sales] == 0
    ),
    BLANK(),
    [Profit] / [Sales]
)

This measure expression achieves the same outcome, yet more efficiently and elegantly.

Profit Margin = DIVIDE([Profit], [Sales]) – Recommended

· Import only the columns that are necessary.

Filtering the columns after the import won’t help you to avoid stressing the performance.

· It’s better / faster to have long tables with more rows than wide tables with more columns.

Therefore, consider using the unpivoted tables and getting narrow and long tables.

· Creating dimension tables (lookup tables).

By duplicating the fact table and removing duplicates helps to ensure consistency of both.

· Using Star Schema is preferred instead of the Snowflake Schema in terms of performance.

This means best case there is one fact table with several dimension tables around it.

· Whenever possible a Measure is preferred over a calculated column.

A measure calculates only when it is used in a visualization for the parts of the column while a column is calculated for the whole report and column.

· Create Measure’s Table

To find measures easier, there should be one central place for these measures or so-called measure’s table. In case of more than one table you can create a dedicated table for the measures and shift the measures to this place.

By changing the “display folder” name you can structure the measures along appropriate folders.

· Turn off Auto/Date time – global settings

· Turn off Auto relationship

· Use cross-filtering instead of cross-highlighting

· Avoid visual level filters

It is static and can change. Also, using measure you need to change only one time, and this is changed everywhere the measure is used in the report, not need to change every visual filter in all pages.

Use SWITCH function instead of nested IFs.

This makes code easier to read

Typical excel based nested IFs:

The same condition using SWITCH

Optimization of SWITCH

Note: to remove all instances of a text string, select the text, press Ctrl + Shift + L and delete:

By transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.

Julien B.

Associate Director, Pharmaceutical Company

Julien B.

Associate Director, Pharmaceutical Company

Julien B.

Associate Director, Pharmaceutical Company