Microsoft – Page 19 – STL

DAX (Data Analysis eXpressions) is the function language in Power BI desktop. When DAX is used to create measures, it can hard to understand the logic at times. Especially one function, the Calculate function, can be challenging.

We visit the mysterious Calculate Function for the third time, in this four part series of blogs.

The Calculate function reacts differently to row, column, filter, and table context than the other DAX functions and the Calculate function is important for nesting other functions.

In this blog post you will see some examples of how the calculate function works with time-intelligence.

Time Intelligence

The time-intelligence functions that enable you to control data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.

Most time-intelligence functions need to be nested inside the Calculate function.

In this example, sales need to be compared up against previous year.

The structure of the Calculate function:

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

In all the examples the expression will be total sales and in the filter arguments, the time-intelligence functions will be nested.

First, the SamePeriodLastYear function.

All time-intelligence functions need to know the primary key in the Dates table (Calendar Table).

Last year = CALCULATE([sales],SAMEPERIODLASTYEAR(Dates[Dates]))

Below in the example a Gauge visual is used. The Sales are added to Value and above measure to Target. The page is filtered to show 2016 by a slicer.

The blue part of the Gauge chart (£20.42m) is the sales for 2016, and the line in the Gauge chart (£13.99m) is last year. In this example 2015.

Below the page is filtered by two slicers to February 2016. The blue part of the Gauge chart (£20.42) is the sales for February 2016, and the line in the Gauge chart (£13.99) is February 2015. The combination of the Calculate and SamePeriodLastYear function will always go back to the same period last year. In this case, to February 2015.

In the next example of the combination of the Calculate and SamePeriodLastYear function. The sales growth needs to be visualised.

The DAX measure used for this: % Growth = DIVIDE([sales]-[Last year],[Last year],0)

The sales minus last year’s sales divided with last year’s sales.

The Last year sales measure from the previous example above, has just been reused here. In the Matrix below right, the Matrix display the percentage difference from previous year and same month previous year.

Matrix

Conclusion

I call the Calculate function the mother of DAX functions. It is the most important DAX function (my opinion), but to get the most out of it you will need to understand, how the function reacts to row, column, filter, and table context, and how the Calculate function takes care of other functions

This is part 3 of a series of blog posts as mentioned at the top about the mysterious Calculate function.

In the next blog post in this series, you will see how the mysterious Calculate function is different from other DAX functions when it comes to filter context.

DAX in Power BI Desktop is an amazing tool that allows you to fully analyse your data giving you real insights to achieve profitable outcomes.

You can get a lot of Business Intelligence from your reports without any DAX whatsoever. However, you may want specific information that is only possible to obtain with DAX. For example, % Sales Growth Year on Year by Region or a Rolling Last 12 month total. In this article you will find out what DAX means, what it can be used for and how it can help you to gain a competitive advantage in your data analysis.

What is DAX and why is it so useful?

DAX stands for Data Analysis Expressions. It is a formula language used to optimise the performance of data analysis in Power BI Desktop. One of its main benefits is being able to re-use it numerous times. This means faster processing time since a calculation used in another calculation will not need to be reprocessed because the first calculation is already stored in memory. Consequently, end users can make instant business decisions more efficiently as they receive their updated reports. Moreover, the greater flexibility in using DAX means that end users will have a competitive edge over those who only use standard analysis tools.

How to get started

The world of DAX is vast and can get complicated especially if the data is spread across many tables. To keep things simple, we will look at a practical example drawing data from a single dataset. Let’s say you had some global sales data and you needed to show the Average number of Orders per Country. To achieve this, we will look at creating 3 DAX measures:

- - Total number of Orders (measure 1)
  - Number of Countries (measure 2)
  - Average Orders per Country (measure 3)

The first two DAX measures will be created separately and then combined to make the third measure thus speeding up data processing time.

1. 1. Import a single Excel file e.g. ‘Global Food Sales’ into Power BI Desktop
  2. On the ribbon go to HOME > NEW MEASURE
  3. Type the following:

Number of Orders = COUNT(FoodSales[Order ID])

This calculates a total count of all orders where ‘FoodSales’ is the name of the table and OrderID is the field name (note the requirement for square brackets as it is a field)

1. 1. Create a ‘Card’ visual to show the total number of orders:

1. 1. Create another new measure and type:

Number of Countries = DISTINCTCOUNT(FoodSales[Country])

This calculates a unique number of countries out of a list of multiple entries for each country

1. 1. Create another Card visual to show the number of countries:

7. Create a third measure and type:

Average Orders per Country =

DIVIDE([Number of Orders],[Number of Countries])

where the [Number of Orders] is the ‘Numerator’

and [Number of Countries] is the ‘Denominator’

1. 1. Format this measure to no decimal places
  2. Again add this measure to a Card:

If you add any new Countries to future sales data, this average will automatically update.

Conclusion

Gaining a competitive edge in the marketplace is key to being more profitable. The ability to fully exploit DAX in Power BI Desktop will definitely help you to achieve this goal.

For more articles on Data Analysis and practical uses, please click below:

https://www.stl-training.co.uk/b/easily-track-performance-with-analytical-lines-in-power-bi/

https://www.forbes.com/search/?q=data%20analytics&sh=288804da279f