Data Analysis Expressions — DAX Power BI

6 min readFeb 12, 2023

I’ve started visualizing data with Power BI. “This looks like an Excel pivot table,” I thought initially. Easy, breezy. During the lessons, however, I realized that some columns had to be created or specific figures that were not readily available needed to be generated from the tables to improve analysis. Let’s assume we need the average total of column X, for analyses. How do we achieve this? We could write an expression or formula to draw the figures from the columns present. That is a data analysis expression! Power BI allows us to accomplish precisely that with a language known as DAX — Data Analysis Expression.

Throughout this post, I will guide you through creating basic DAX, we will explore the differences between a measure and a calculated column, and their applications. I assume you have some familiarity with the Power BI interface already.

Data analysis expressions are formulae written to manipulate data in Power BI. Manipulation includes creating new columns, tables, and measures; basically creating new information from a table that already exists.

DAX is to Power BI what functions are to Excel, it is the formula language.

Why DAX

You can use Power BI without writing DAX and still create beautiful visualizations. But what if you need to analyze quarter-to-quarter sales increase across the years? DAX presents the flexibility to observe trends and hidden patterns in the data with mathematical concepts.

Terminologies to note:

We are still working with structured data (stored in tables with rows and columns). Take note of these:

Calculated columns — these are columns that are generated using DAX. They are tagged as calculated to differentiate them from the original columns that came with the tables in a dataset. The calculated columns expand the dataset without editing the data source directly.
Measures — the measure feature is used to make complex calculations. This results in a field that can be used for calculations. The “measure” aggregates the rows and returns a value whenever called upon, yet does not add up to the bulk of the data.
DAX functions — predefined formulae that perform operations on values called arguments. Function categories include logical (OR(), AND(), IF()), aggregation (sum(), average()), date and time ( DAY(), YEAR())

Basic DAX Syntax

Depending on the object being created (column, table, measure, etc.): they all require specific features and arguments. However, the very similar components of a DAX are:

The name of the object (column, measure, table)
equal to sign (=) — indicates the beginning of the formula
table names[column names] — shows the table and column that would be used to generate the new object
functions ( divide, sum, etc.)
operators (&, +, -, *, etc).

For example, to create a measure that sums a column in a table, this is the DAX you could go with:

Measure name = SUM(tablename[column name])

Creating a Measure

I assume that you already have your Power BI installed, opened, and have your data imported. In this tutorial, I will use an in-built dataset in Power BI. Access it on the Menu bar,

select Help > Examples > Sample dataset > Load Sample Data. A ‘financials’ dataset appears; tick the sheet1 box and click the Load button. Sheet1 contains a single table and several columns (country, segment, product, units sold, sales price, gross sales). Let’s create a measure that aggregates the profit column. The figure below shows how the data looks when imported. Notice that the last tab on the Menu bar is the Column tools.

To create our first measure:

Click on Sheet1 under the Fields pane. The Table Tools tab appears
Select New Measure from the tables tool tab options

3. A formula bar appears with “Measure =” highlighted. Type the intended name of the measure in place of the highlight followed by an equal to (=) sign. In our case, we want the sum of profit hence;

Sum of profit = SUM(Sheet1[Profit])

NB: The measure cannot be found in the data view. However, you can visualize the measure through any visual of your choice. For our presentation, we will do a tabular visualization with the “Product” column and the “Sum of profit” measure we just created.

Notice the “sum of profit” measure created in the Fields pane, the measure tools, and the formula we used in the formula bar. The canvas also contains the table generated from the product column and the “sum of profit” measure.

There is also a relatively easy way to create a measure. With the table Sheet1 selected, when you hover the mouse pointer over it to the farthest right, you see three (3) dots, ellipses, and a pop-up that reads More options. Click on the ellipses and select New Measure. Continue from step 3 in the steps described above.

creating measures using the ellipses route

Congratulations, we have created our first Measure in Power BI.

Creating a Calculated Column

To create a calculated column:

Select the Sheets1 table in the fields pane
Select New Column in the tables tool
A formula bar appears with the formula highlighted.

4. Type the name of the formula and the calculations you expect to be done. Notice the check button beside the highlighted column name in the picture above. You either click it or press the Enter key on your keyboard to create the column.