DAX (Data Analysis Expressions) is a functional language in Power BI to solve basic calculation and data analysis problems based on a collection of functions, operators, and constants.
Here are the main reasons that make DAX so powerful.
1. Standard functionality like formulas in Excel
Most users know formulas in Excel like SUM, SUMIF, VLOOKUP etc. DAX offers similar formulas and further functionality to accomplish all kind of calculations in Power BI. Nevertheless, it is not the same as Excel formulas work over cells and Power BI works with tables and columns.
Seriously, for sure there is a learning curve in order to get the concepts and the ideas of DAX, but it is doable and after a short time you will discover a structure and logic in it. Once you have understood these basic concepts, you don’t want to miss it anymore.
3. It solves business problems
Whenever you face a data challenge in Power BI there is a good chance that DAX provides the solution. A lot of calculations are possible in DAX and I would always recommend searching for something in DAX via your favorite search engine or the Power BI community (https://community.powerbi.com/).
4. It improves the Data Model
If you use DAX youmusttake care of your underlying data model. Data modelling is sometimes considered as art, but once you understand the basic concepts like dimensional model, star schemas etc. it is not only fun, but will significantly improve your data model and maintainability of it.
Normally you run sooner or later in the situation that your data doesn’t contain a certain field you need. This is where calculated columns can be created to define a column’s value, e.g. putting text values together or calculating a numeric value from other values.
In the screenshot below a calculated column ‘ZipCountry’ has been created which concatenates the zip code and the country of table ‘Geography’.
Measures are used in some of most common data analyses, e.g. calculation of sums, averages, minimum or maximum values. You can also create more advanced calculations with DAX. The calculations of measures are done on-the-fly, i.e. they change depending on your interactions in your reports and allow dynamic ad-hoc data exploration.
In the Screenshot below a measure ‘Sales’ is created which sums up the revenues by using the DAX-formula ‘SUM’.
Hmm, sounds like calculated columns? 🤷♂️ Indeed, both can be used for creating additional fields which can be used in the reports, but the following rule of thumb will help you to determine when to use what.
Another way of distinguishing them: Calculated columns should be used when calculations shall be applied for all rows, but measures are the best fit when aggregations (e.g. sum, average, ratios etc.) shall be applied.
7. Easy modelling of Date-Dimension
In almost every underlying data model you have a date dimension which allows you to filter for days, months, quarters, years etc. This is especially important for drill-down functionality in your reports. Often the data sources (e.g. a CSV-file, database etc.) do not have a dedicated date dimension. In such cases you can use the CALENDAR DAX-function to create such a date dimension as a table in Power BI. You can even set start and end values dynamically based on the data in your model (e.g. Sales tables start in 2010 and lasts until 2019).
In the Screenshot below a table ‘MyDateTable’ is created which contains dates from 2017-01-01 until 2019-12-31.
Note: In this example a ‘;’ is used as delimiter between parameters, but dependent on your regional settings a ‘,’ might be used instead.
8. CALCULATE is powerful
CALCULATE is one of the most powerful functions in DAX. When you start to create more complex formulas, you will likely use this function many times. Once your knowledge grows, pay special attention to this function as this will help you to master more complex use cases.
9. Evaluation Contexts
Evaluation contexts are difficult to understand at the beginning but are the basis of the advanced features of DAX. They are often used together with CALCULATE and make it possible to produce different calculation results depending on the context. Why is this useful? Because you can control which values are used in the calculation, i.e. you control the row context and filter context.
These functions help you create calculations which involve calendars and dates. This allows you to build meaningful comparisons across comparable time periods (e.g. calculations of year-to-date, comparison with last period, previous month etc.).
Below you find an example with a measure ‘PY Sales’ which calculates the previous year sales by summing the revenues of the same period of last year.
How to get started?
As always there are multiple ways to get started. You can start with the greatPower BI documentationor order some good literature (highly recommended are books from Alberto Ferrari and Marco Russo).
If you want to take a shortcut, you can also visit ourObungi DAX workshopwhere you learn all the fundamentals, a lot of best practices and Pro Tips in just one day.
A lot of people do not know about the possibilities of DAX or are afraid about the complexity. Although it is important to learn the basics about it, it is not rocket science. You don’t need to be a programmer and you can learn it without a deep technical background. Try it out and be positively surprised about the new possibilities with it in Power BI – once you passed that point, you will love DAX.
What do you think about DAX? Looking forward to your feedback or to see you in ourworkshop.