Introduction to Data Analysis Expression (DAX)

This article was published as a part of the Data Science Blogathon.

Introduction

The following is an in-depth article explaining DAX which stands for Data Analysis Expression. DAX is the language developed by Microsoft to interact with data in a variety of their platforms, such as Power BI, PowerPivot, and SSAS tabular models. It is designed to be simple and easy to learn while exposing the capabilities and flexibility of tabular models. Through DAX, Power BI can reach its true potential. We will be covering the working, advantages, and disadvantages of DAX. A few of the topics which we will cover in the article are:

1. What is DAX?

2. Importance of DAX in Power BI

3. How does DAX work?

4. Function in DAX

5. Calculate measure & Calculated column

6. Conclusion

What is Data Analysis Expression?

Importance of Data Analysis Expression in Power BI

DAX is quite an important part of BI because it provides functionalities like:

• Data Visualizations
• Importing, and manipulating data that are designed for the end-user.
•  For normal report creation, a brief understanding of the dashboard is enough.
•  However, DAX helps to create sophisticated and insightful reports.
•  With several commands and measures available in DAX syntax, the generated reports are highly intuitive and discerning.

How does DAX Work?

DAX is built around three fundamental concepts:

1. Syntax
2. Functions
3. Context

For the creation of a command that fetches the desired results, these inputs are crucial.

1. Syntax: Formula syntax refers to the shape of the formula you are constructing. It is the language used to make the formula, for example, command, sign, operators, destination column or row, etc. Example: name, parenthesis, summation, name of the table.

_{Figure 1: Syntax of DAX expression}

2. Functions: A function refers to a predefined or already existing command in a system. Examples include sum, add, true, false, etc.

3. Context: The context refers to the row that has been included in the formula for data retrieval or calculation. There are two types of context: Row Context and Filter Context.

Functions in DAX

• COUNT
• DATE-TIME
• AGGREGATE
• LOGICAL
• MATH
• TEXT

COUNT Functions

1. DISTINCTCOUNT: Counts the number of distinct numbers.

Syntax: DISTINCTCOUNT()

2. COUNT: The total number of items in the column is returned.

Syntax: COUNT()

3. COUNTA: Gets the number of items in a column that isn’t empty.

Syntax: COUNTA()

4. COUNTROWS: Gets the number of rows in a given table.

Syntax: COUNTROWS()

DATE-TIME Functions

1. DATE: Gets the date in the Date-Time format.

Syntax: DATE(2022, 05, 26)

2. HOUR: Displays hours in the AM PM format.

Syntax: HOUR(  )

3. TODAY: Gets the current date.

Syntax: YEAR( TODAY()) - 2012

AGGREGATE Functions

1. MIN: Finds the minimum value in a given column.

Syntax: MIN(  )

2. MAX: Returns the maximum value in a given column.

Syntax: MAX(  )

3. SUM: The formula adds the values in a column to produce a total.

Syntax: SUM (  )

4. AVERAGE: It takes columns of data and returns the average.

Syntax: AVERAGE(  )

5. MINA: Gets the minimum value along with the total function, logical values, and text representation of numbers.

Syntax: MINA(  )

6. MINX: Calculates the minimum value after evaluating each row expression in a table.

Syntax: MINX( , )

LOGICAL Functions

1. AND: Combines 2 expressions logically.

Syntax: AND( ,  )

2. OR: This function performs the logical disjunction on 2 expressions.

Syntax: OR( , )

3. NOT: Negates the given expression logically.

Syntax: NOT(  )

4. IF: It checks IF a criterion is true and returns one value if it is, and another value if it is not.

Syntax: IF(  , [, <value_if_false])

MATH Functions

1. ABS: Returns the absolute value.

Syntax: ABS(  )

2. EXP: Returns the exponent value.

Syntax: EXP (  )

3. FACT: Returns the factorial of the number.

Syntax: FACT(  )

Other important math functions: LOG, PI, POWER, SQRT.

TEXT Functions

1. CONCATENATE: Joins two strings together.

Syntax : CONCATENATE( ,  )

2. FIXED: Rounds off numbers to a given decimal.

Syntax: FIXED( , ,  )

3. REPLACE: Replace the characters with part of a string.

Syntax: REPLACE( , , , )

Other important text functions: SEARCH, UPPER, LOWER.

Calculated column vs Calculated measure

For both measures and computed columns, DAX expression is used. The only difference is the evaluation context. A computed column is assessed at the row level of the table to which it belongs, while the measure is evaluated in the context of the cell analysed in a report or in a DAX query. The DAX query or user selections in the report determine cell context. 

Figure 2:  Navigate to table tools on Power BI desktop to create new columns, measures

_{Figure 3:  Measure created}

_{Figure 4:  Column created}

It is necessary to define measures in a table. This is one of the DAX requirements. The measure does not actually belong in the table. A measure can be moved from one table to another without losing its functionality.

Calculated columns and calculated measures both use DAX expression to manipulate data. However, the underlying formulas are different. In the calculated column type, the calculation occurs at the row level within a table. A calculated measure is calculated at the cell level in the entire report or query.

Conclusion

Data Analysis Expression is one of the most widely used expression syntaxes these days and has many applications:

References: Figure 1: https://www.tutorialspoint.com/dax_functions/dax_functions_quick_guide.htm

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.