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!
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.
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])
The following are the above formula’s syntax components:
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 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.)
The following are the categories of DAX functions:
Let us have a look at few of the categories and few of its functions.
The following are some functions under this category:
The following are some functions under this category:
The following is one of the functions under this category:
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.
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 above type of measure is considered to be “simple measure” as it aggregates/summarizes a single column/table.
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.