DAX in Excel is a sophisticated formulas type language that comes in handy while working with relational data and extracting information via dynamic aggregation functions. DAX in Excel acts as a custom calculated field with additional functionalities apart from the default functions provided by Microsoft Excel.
This article helps with a decent understanding of DAX in Excel and how you can carry out practical DAX operations in Excel.
What Is DAX in Excel?
- DAX in Excel stands as an abbreviation for Data Analysis Expressions. DAX formulas are very similar to the general and default functions made available by excel. DAX Functions and Formulas also start with an equals sign, just as the default functions.
- It allows the user to perform slightly escalated, advanced, and custom calculations upon various data types like character data, date and time, time intelligence functions, etc.
- DAX in Excel helps you perform customized calculations on a row-by-row basis with different levels of data granularity based on the context.
- DAX can provide the result not just in the form of a single cell but as an entire table comprising a set of rows and columns.
After this detailed briefing of the Data Analysis Expressions in Excel, let us understand when or why we need to use DAX in Excel.
Why Do We Need DAX in Excel?
After having a generalized understanding of DAX, at this point, a question might arise. We already have Microsoft Power BI as an excellent data visualization tool and all the mathematical and logical calculation functions available on excel by default. Then, why do we need the additional DAX functions and formulae?
Pretty obvious question. The answer is simple. Power BI can import data from the source, generate valuable insights, and visualize the data. But what if the requirement of visualizing the data is escalated to a more detailed version? Several other issues similar to these arise too such as:
- What if your manager expects you to provide details of product sales based on individual sub-categories?
- What if the requirement is to provide year-on-year / quarterly/monthly sales of each product compared to its previous Year / Quarter / Month?
- What if your necessary data is located at different sources like an RDBMS and some special sources with read-only capabilities, say SalesForce.
- What if the data from different sources also have different levels of granularity?
Not just these but other similar issues can be addressed and resolved using DAX functions in Excel. In the next part, we will see the syntax of MS Excel DAX functions.
Syntax of DAX in Excel
Data Analysis Expressions in excel almost follow the same syntax as the default Excel formulae. The elements included in the DAX formula or functions are pretty simple.
For example, let us consider, we have a data table related to employee details.
Now, let’s imagine the requirement is to calculate the current age of the employees according to their date of birth. This operation can be carried out using the general Date and Time excel formulae or functions and DAX. Let us see how the formula can be implemented using DAX in excel.
=DATEDIF(Column_of_DOB_Data, Today_Function_in_Excel, “Days/Months/Years”)
Discussing the syntax will bring us now to the practical implementation of DAX.
How to Create DAX in Excel?
In this section, let us practically implement the DAX functions.
We need to switch to the "Power Pivot Mode" in Excel to get started with DAX. Everything will be explained in a step-by-step fashion as follows.
The following is the employee data that we will be using for our practicals.
Go to the Data option in the toolbar as shown below.
In the Data Ribbon, navigate to the Data-Tools group as shown below. Here you will find the option to Manage Data Model; click on it.
Excel will now turn on the Power Pivot Mode, and you can see a new window getting popped up right on your screen, as shown below.
We are currently in the Power Pivot window. Let us get connected to our data. Navigate to the top left corner of the Power Pivot window. You will locate a group that reads as "Get External Data", choose the option that reads as "From other Sources", as shown below.
In the next stage, a new "Table Import Wizard" window will appear on the screen. Scroll down the options, and you will see the "Excel File" option, select that and press "Next", as shown below.
Choose the "Browse" option and navigate to your source file as shown below. Select the source file and click on "Next."
Check in the "Table and Views" option ad click on "Finish" below.
The Data gets imported into the Power Pivot window, as shown below.
The Data in the Power Pivot window appears as follows.
The next stage is where we apply the DAX in Excel. According to the previous image, we already have a new column on the rightmost side of the Power Pivot table. If not available, you can always add one or more columns according to the requirement.
Now, rename the new column as "retirement_Date". Select any cell in the column, and you can apply the DAX formula, which will be applied to all the column cells without having to drag the formula against all cells. This is the most helpful feature of Power Pivot DAX tables.
The formula is really simple and completely similar to the Date and Time functions used as default in Excel. The only difference is that the formula is used in the Power Pivot window with a minor change in column addressing methods. The formula is shown below.
=EDATE('DAX Data'[F7],12*65)
F7 is the column having the employee DOB details.
After writing down the formula, press "enter," It should get applied to the entire column, as shown below. The final output is automatically obtained as below.
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!
Wrapping Up
MIS Report in Excel can be your next step in learning data analytics. MIS Report plays a major role in generating real-time interactive reports, crucial in real-time data visualization and analytics.
Interested to learn more about Business Analytics? Or wish to get trained and certified to become a successful Business Analyst? Then feel free to look into the Business Analytics certification course from Simplilearn. Ranked among the five topmost business analytics courses, this Simplilearn program is offered in partnership with Purdue University. It is an outcome-driven training and certification program that helps you master the fundamental concepts of statistics and data analytics.
Have any questions for us on this tutorial on "DAX in Excel"? If you do, or maybe you have doubts about our certification course, do reach out to us by sharing them as comments below. Our team of experts will address them and will be happy to answer them at the earliest.
