Introduction to Data Analysis Expression (DAX) – News Couple
ANALYTICS

Introduction to Data Analysis Expression (DAX)

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

What is Data Analysis Expression?

DAX (Data Analysis Expressions) is a syntax language that includes formulas and expressions used to manipulate data. Functions, constants, and operators are used in DAX expression. DAX is a better version of Microsoft Excel, providing high-end data manipulation and management capabilities.

Microsoft made DAX language to complement its business intelligence and data modeling tools like PowerPivot and Power BI.

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.

• 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

This article will help new Power BI users who are still exploring and learning. It is also useful for experienced developers who need a quick refresher course. This tutorial provides a foundation for creating Power BI reports.

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