DAX Basics in Power BI

Introduction

In Power BI, we can do a limited amount of calculations using Power Query Editor, but that is not enough to perform. For that we have DAX. DAX means Data Analysis Expression is an inbuild function which is the formula language that is used by Power BI. DAX is developed by Microsoft to interact with data. In the Data model, by using DAX functions we can perform various actions. DAX concepts are simple, straightforward to understand, and powerful. Some of the DAX functionalities are similar to Excel Functions and hence it will be an advantage if we have prior knowledge of Excel Functions.

DAX Formula Syntax

Total Sales=SUM(Sales[Amount])

From the above sample formula syntax,

• Total Sales – Is the Measure Name. The measure name explains what the formula is all about.
• = (Equal Sign) – Measure name is followed by Equal Sign operator(=), which indicates the beginning of the formula. When calculated, it will return a result.
• SUM() – After the equal sign operator, is the DAX Function. In this SUM is the DAX Function, which performs the addition of the numbers provided as arguments. DAX function should enclosed within a parenthesis ().
• Sales (Table Name) – Within the DAX function, first comes the table name. In our example, Sales is the table name.
• Amount (Column Name) – Within the table name, we have to define what column are we referring

Now lets re-write our syntax as,

Measure Name=DAX Function(Table Name[Column Name])

DAX Function Categories

• Aggregation functions
• Date and time functions
• Filter functions
• Financial functions
• Information functions
• Logical functions
• Math and Trig functions
• Parent and Child functions
• Relationship functions
• Statistical functions
• Table manipulation functions
• Text functions
• Time intelligence functions
• Other functions

Aggregation functions– These functions are used to calculate scalar values such as count, sum, average, minimum, or maximum for all rows in a column or table.

Date and time functions – These functions in DAX are similar to date and time functions in Microsoft Excel, but in Power BI the DAX functions are based on the datetime data types used by Microsoft SQL Server.

Filter functions – These functions are used to populate the data based on the Filter values.

Financial functions – These functions are used in formulas that perform financial calculations, such as net present value and rate of return.

Information functions – These functions look at a table or column provided as an argument to another function and returns whether the value matches the expected type.

Logical functions – These functions return information about values in an expression.

Math and Trig functions – Mathematical functions in DAX are similar to Excel’s mathematical and trigonometric functions.

Other functions – These functions perform unique actions that cannot be defined by any of the categories most other functions belong to.

Parent and Child functions – These functions help users manage data that is presented as a parent/child hierarchy in their data models.

Relationship functions – These functions are for managing and utilizing relationships between tables.

Statistical functions – These functions calculate values related to statistical distributions and probability, such as standard deviation and number of permutations.

Table manipulation functions – These functions return a table or manipulate existing tables.

Text functions – With these functions, you can return part of a string, search for text within a string, or concatenate string values.

Time intelligence functions – These functions help you create calculations that use built-in knowledge about calendars and dates. By using time and date ranges in combination with aggregations or calculations, you can build meaningful comparisons across comparable time periods for sales, inventory, and so on.

Conclusion

In this article, we discussed what is DAX Functions and its categories. We will discuss each category in detail in our upcoming articles. Please share your feedbacks in the comment section