In the previous article, we were able to understand what DAX really is and where all we can apply it. We also understood its importance. However, I felt one article isn’t enough to give you all a good glance of DAX. That’s why in this article I’m going to discuss deeply it’s expressions, the outcome of those expressions, and more.
I highly recommend you to read the previous article before coming to this one as it will help you understand DAX better!
Prerequisite
The only prerequisite to learning DAX is to have basic Microsoft Excel knowledge. By that, I’m talking about the use of formulas that exist in Advanced Microsoft Excel. In this article, we are going to understand exactly how to apply DAX and its syntax.
Syntax
A syntax includes all the elements that are needed in the process of forming a formula. It basically gives us an understanding about the order in which a formula is to be written in order for it to work.
For example, the following is a DAX formula (calculated measure):
Total Sales = SUM(Sales[SalesAmount])
Syntax Elements
The following are the above formula’s syntax components:
- The first syntax being the measure name which is “Total Sales“.
- The second syntax being the equal sign operator “=” This syntax depicts the start of the formula.
- The third syntax being “SUM“. SUM is a DAX function that adds all of the numbers that are inside the “Sales[SalesAmount]” section.
- The fourth syntax being parenthesis “()” which surround an expression that contains single or multiple arguments. An argument passes value to a function.
- The fifth syntax is “Sales” that is inside the parenthesis ().
- The sixth syntax is [SalesAmount] which is in the Sales table. Essentially, the SUM function will know which column to place the result of the SUM function in.
Understanding in Detail
Let us understand the above formula in detail. Currently, I only explained the order of elements of the syntax and used one of DAX’s functions: SUM formula, as an example. DAX functions are equations that make things simpler for doing complex computations and operations in less amount of time. We also observed that the name of the column [SalesAmount] was prefixed by the name of the Sales table to which it belonged. This is referred to as a fully qualified column name since it includes both the column name and the table name.
DAX Calculation Types
DAX calculations can be done in Calculated measures, Calculated columns, Calculated tables and RLS. (If you want to further understand them in detail, head on to my previous article.)
Different Types of DAX Functions
The following are the categories of DAX functions:
- Date and Time
- Time Intelligence
- Information
- Logical
- Math and Trig
- Statistical
- Text
- Parent and Child
Let us have a look at few of the categories and few of its functions.
Date and Time Function
The following are some functions under this category:
- CALENDAR: This function returns a table with a single column named “Date” that contains a contiguous set of dates.
- CALENDARAUTO: This function is identical to CALENDAR, except that CALENDAR requires the boundaries of the collection of dates, whereas CALENDARAUTO runs through all of the dates in the data model and automatically determines the first and last year specified inside the model.
- DAY: This method returns the month’s day, as a number between 1 and 31.
- HOUR: This returns the hour as a number ranging from 0 (12:00AM) to 23 (11:00 PM)
- MONTH: This returns the month as a number ranging from 1 (January) to 12 (December).
Time Intelligence Function
The following are some functions under this category:
- ENDOFMONTH: This function returns the end date of the month in the current context for the given column of dates.
- ENDOFQUARTER: This function returns the end date of the quarter in the current context for the given column of dates.
- ENDOFYEAR: In the present context, this function returns the end date of the year for the given column of dates.
Information Function
The following is one of the functions under this category:
- CONTAINSROW: This method returns TRUE if a row of values exists or is contained in a table; otherwise, it will return FALSE.
- CONTAINSSTRING: This function will figure out whether one string contains another string. On the basis of that, it will return TRUE or FALSE.
- ISEMPTY: This function will figure out whether a table is empty or not.
Can We Create Our Own Measure?
In the previous article, we already understood calculated measures, columns, tables, and row-level security. Speaking about calculated measures, we know it is solely just a calculation that already exists in your Power BI data model. It is very similar to how VLOOKUP, MAX, MIN, SUMIF, COUNTIF, and several other functions already exist in advanced Excel. But what if you wanted to create your very own calculation, your very own measure? Thanks to Power BI, we can create our own measure under DAX.
Creating a Measure in MS Power BI
In order to create a measure in Power BI, Go to Report view > Modelling Tab > New Measure.
Once you select “New Measure“, the following will appear:
- The first thing you need to do is to enter a new measure name and replace Measure with it. It can be anything that you want to calculate such as the “Total sales” or the “previous year sales,” etc. We are going to go with the popular “Total sales” example.
- Now, you need to type the following definition of measure in the formula box: Revenue = SUM(Sales[Sales Amount])
- Once done, click Enter.
- The revenue measure is added to the sales table as a result of this. It sums the data in the Sales Amount column with the SUM DAX function.
- Next, Go to the Formatting group and change the decimal places to two. It’s critical to establish the formatting settings after defining a measure to ensure proper consistent values across all report visualizations.
The above type of measure is considered to be “simple measure” as it aggregates/summarizes a single column/table.
DAX Data Types
DAX happens to have ordinarily two kinds of data types. One of them being numeric and another being non-numeric.
The numeric data type consists of integer, floating point, and a fixed decimal number. Whereas, the non-numeric data type consists of binary objects and strings.