[Introduction] Power BI & DAX

DAX is a formula expression language used in Power BI, Power Pivot and Excel. In simpler terms:

It’s the language that Power BI speaks, you need to know it to get the most out of your data.

To become an expert on Power BI and all its capabilities, attend one of our Power BI Courses.

What is DAX?

DAX stands for Data Analysis Expressions and is the language behind Power BI  – see What Is Power BI for an introduction to Power BI.

Microsoft created DAX to manipulate data within some of its applications, and it is a very powerful language.

This is in contrast to other business intelligence solutions like Google Data Studio where the learning curve can be steeper.

At its simplest level, DAX in Power BI is used for 2 things:

1. 1. Calculated columns
   2. Calculated measures.

These will be a part of any data model that you produce in Power BI.

If you are familiar with pivot tables in Excel, it works in a very similar way to calculated fields.

Calculated columns are columns that can be added to your data to enrich it.

For example, you may want to know what your profit is per line item of a sale. Your calculated column could be created as Profit = [Sale Price] – [Cost]. This formula would generate a Profit value for each row in your data and allow you to summarize it to any level you choose when visualizing it.

Now, what about if you wanted to calculate a profit percentage?

This is better calculated as a measure as you may want to aggregate it based on product category, or time period, or some other category.

The process is very similar for a measure, the only real difference is how it is calculated and the context.

A calculated column is evaluated for each row of data.

A measure is evaluated based on the context of the reporting level you are viewing.

As you summarize the data more, a measure will automatically scale with your context, you will have to choose how best to summarize your column. All of this might seem a little complicated, but I assure you, once you get your hands on it, it will make a lot of sense.

You may be saying to yourself, that’s great and all, but Excel can do all of that, why bother with Power BI?

Why Is DAX So Useful?

DAX provides you with the ability to manipulate data without having to create a new dataset altogether. This can be a huge difference with Excel.

In Excel, you will encounter situations where you need to slice data multiple ways and create different pivot tables to be able to see things as you want to.

To do this you will need to create multiple copies of the same dataset.

The beauty of DAX is that it allows you to have a single dataset that you enhance with calculations.

Now if all DAX could do was these simple calculations listed above, that would still be beneficial, but it can do so much more.

Explaining this is often a question in Power BI interviews.

What Is The DAX Learning Curve?

The learning curve for DAX is not steep for experienced Excel users.

The simple formulas will seem no different to Excel’s, and you will be able to pick them up in no time.

The guided formula builder in Power BI makes this even easier if you’re not quite sure of something.

Obviously, as you get more advanced and move beyond Excel-like formulas the learning curve will be steeper but by then you will be comfortable working with Power BI which makes keeps it manageable.

Advanced DAX

DAX allows you to build complicated formulas to manipulate and aggregate your data.

If you are familiar with writing code, or even complicated formulas in Excel, then this will be very similar, just more complicated.

Since DAX has its own syntax, it will take some time to get comfortable with the more advanced features, which is why we run an Advanced DAX course to get people up to speed.

DAX enables you to do many of the transformations that previously would have had to be done in a database with advanced queries and logic.

This frees up data engineer and DBA time and removes them as a bottleneck from the process.

Final Thoughts

Power BI was built as a tool to enable report writers and analysts the ability to have more freedom and flexibility, while reducing the burden on traditional IT.

What used to require multiple tools and processes to achieve can now be done with only Power BI.

As long as you have access to your raw SQL data, you can manipulate it to provide maximum impact without having to pester IT.

While you begin to master DAX, you will be amazed at how simple some things are that you probably spent hours or days on in the past with other tools trying to figure out how you could achieve your vision.