Introduction to DAX in Power BI

Photo by Stephen Phillips - Hostreviews.co.uk on Unsplash

DAX — Data Analysis Expression, is the main language used in Power BI, an interactive data visualizations and business analytics tool for business intelligence (BI) by Microsoft. Power BI is used to connect to data from multiple different sources, pre-process the data and produce interactive visuals and reports that deliver insights, which can be shared with the required end users. DAX, in some way, is similar to functions in Excel, but is optimized to work with Power BI’s structured data model.

In this article, I will be giving a brief introduction to the following main calculation concepts of DAX:

• Calculated Columns
• Measures
• Calculated Tables

1. Calculated Columns:

Calculated columns are computed for each row of a table and stored in the data model. To demonstrate this, I will be using data from a pharmaceutical company (table name = Pharma Data) and DAX to add a calculated column that adds unit price information to my dataset. Adding the columns is done by going to the Modelling tab, then selecting ‘Calculated Column’.

Figure 1.1: Sample of the dataset used — with the calculated column ‘Unit Price’

We can get unit price by dividing the sales by the quantity for each product, writing the in DAX would give us:

Unit Price = ‘Pharma Data’[Sales]/’Pharma Data’[Quantity]

When referring to a column, the table name must be referred to in single quotes (‘’) then the column name in square brackets [].

2. Measures:

While a calculated column creates multiple numbers, one for each row (as seen in figure 1.1), we are not interested in calculating the unit price for each row for every product. A measure creates only a single number for each product. Same as before, a measure is added from the modelling tab. The syntax for the Unit Price measure is:

Unit Price Measure = SUM(‘Pharma Data’[Sales])/SUM(‘Pharma Data’[Quantity])

Measures perform calculations on aggregated data, and not data for individual rows. After creating this measure it wont appear in the table, because measures are only calculated when they are being used (for examples when they are added to a visual). This give measures an advantage of being more performance friendly when being compared to calculated columns.

3. Calculated Tables:

The third calculation option in DAX is a calculated table, which is a new table added to your model using DAX formulas.

Our current dataset contains sales transaction details of various pharmaceutical products. We would like to add a new table that contains details of each customer. In this example I will be creating a calculated table containing the ID of each customer in the dataset.

Go to the Modelling tab, then ‘New Table’ to start. Start by writing the table name (Customer Details). Next, the DISTINCT function is used to retrieve all distinct Customer IDs from the dataset. The syntax for the calculated table is:

Customer Details = DISTINCT(‘Pharma Data’[Customer ID])

Figure 1.2: Calculated table of distinct customer IDs

Conclusion

Tools like Power BI are growing to be more and more vital in the daily lives of analysts, developers and data professionals. While this is just a brief introduction to the main concepts of DAX calculations available in Power BI, there are a lot more functionalities in this tool that you can learn.