News & tips
By the end of this introduction to DAX article, you will have a good all-round understanding of Power BI’s Data Analysis Expressions (DAX) language.
DAX stands for Data Analysis Expressions and is the language behind Power BI. It is also used in PowerPivot and SQL Server Analysis Services.
Microsoft created DAX to manipulate data within some of its applications and it is a very powerful language.
The good news is that if you have a good working knowledge of Excel learning DAX as you develop your Power BI skills will be straightforward.
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:
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?
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.
Power BI’s suite of functions for calculated columns and measures is far larger than Excel’s and growing.
Power Query (Power BI’s data transformation and data preparation engine) contains over 250 functions and that number is growing all the time. This page is Microsoft’s reference for Power BI functions.
Similar to Excel date and time functions the functions allow you to convert or calculate dates and times.
Using built-in knowledge of calendars and dates, you can create calculations to help compare data across time periods.
One example is year-over-year comparisons.
As the name suggests, these functions help filter and retrieve specific data. You can then create different views of your data dynamically.
These functions are logical tests that in most cases will return a TRUE/FALSE response.
Examples include functions like ISBLANK or ISNUMBER.
These are functions are your operators in your expressions such as AND, IF, OR.
Very similar to Excel, these will help you to solve specific maths questions.
These functions allow users to manage data that has a parent/child structure.
These are functions designed for aggregation.
They range from sums and averages, at the simple end, to functions for measuring and calculating statistical values like standard deviation at the more complicated end.
These functions are designed to manipulate strings such as concatenating or parsing text.
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.
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.
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.
Imagine you are a sales organization that has a physical store with sales employees.
In order to motivate your employees, you have decided to roll out scoreboards showing who was the top salesperson on any given day, week, month.
Pre-Power BI you would have had to take your sales data and aggregate it for all of the different time periods that you are looking at.
This would have required you to maintain multiple queries to run each time you want to update your dashboard which is a pain.
This is where DAX comes in handy. You can create a single formula for each of the time periods you want to have rankings for, and Power BI will dynamically rank your staff based on the criteria you have provided.
In this situation, all you would need is the raw sales transaction data, and your report developer or analyst can build the logic once for you and you’re set up.
Because the logic is built right where you can visualize it and test it, it can be a much quicker and more reliable process.
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. As 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.