[Introduction] Power BI & DAX
Contents
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 is the language behind Power BI, meaning all the potential in Power BI comes from it.
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). 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.
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:
-
- Calculated columns
- 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.
Many Functions Does Power BI Have?
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.
The Key Categories Of Functions In Power BI
1) Date And Time Functions
Similar to Excel date and time functions the functions allow you to convert or calculate dates and times.
2) Time-Intelligence Functions
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.
3) Filter Functions
As the name suggests, these functions help filter and retrieve specific data. You can then create different views of your data dynamically.
4) Information Functions
These functions are logical tests that in most cases will return a TRUE/FALSE response.
Examples include functions like ISBLANK or ISNUMBER.
5) Logical Functions
These are functions are your operators in your expressions such as AND, IF, OR.
6) Maths & Trigonometry Functions
Very similar to Excel, these will help you to solve specific maths questions.
7) Parent & Child Functions
These functions allow users to manage data that has a parent/child structure.
8) Statistical Functions
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.
9) Text Functions
These functions are designed to manipulate strings such as concatenating or parsing text.
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.
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.
An Example DAX Use Case
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.
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.
