Basic DAX Functions and Formulas in Power BI

DAX (Data Analysis Expressions) is a powerful language for creating and manipulating data in Power BI. It allows us to perform calculations, transformations, and analysis on our data. We can use DAX to create measures, calculated columns, calculated tables, and more.

In this blog post, we will explore some of the basic DAX functions and formulas that we will use in Power BI. We will cover the following topics:

• Common DAX functions and
• DAX operators

Common DAX functions

DAX has a lot of functions that we can use for various calculations, such as aggregation, filtering, logic, text, date and time, and more. DAX syntax is the set of rules that define how to write DAX formulas. DAX syntax is case-insensitive, meaning that we can use uppercase or lowercase letters.

Aggregation functions

Aggregation functions are used to perform calculations on a set of values, such as sum, average, count, min, max, etc. Some of the aggregation functions are:

• SUM: Returns the sum of the values in a column or an expression
• AVERAGE: Returns the average of the values in a column or an expression
• COUNT: Returns the number of values in a column or an expression
• MIN: Returns the minimum value in a column or an expression
• MAX: Returns the maximum value in a column or an expression

Logical functions

Logical functions are used to perform conditional logic, such as if-then-else, and, or, not, etc. Some of the logical functions are:

• IF: Returns one value if a condition is true, and another value if it is false
• AND: Returns true if all the arguments are true, and false otherwise
• OR: Returns true if any of the arguments are true, and false otherwise
• NOT: Returns the opposite of the argument, true if it is false, and false if it is true

Text functions

Text functions are used to manipulate text values, such as concatenating, formatting, extracting, replacing, etc. Some of the text functions are:

• CONCATENATE: Returns the concatenation of two or more text values
• FORMAT: Returns a text value formatted according to the specified format string
• LEFT: Returns the leftmost characters of a text value
• RIGHT: Returns the rightmost characters of a text value
• REPLACE: Returns a text value with some characters replaced by another text value

DAX operators

DAX operators are symbols that perform operations on one or more values, such as arithmetic, comparison, logical, and text operators. We can use DAX operators to create complex formulas that combine multiple values and functions. Some of the DAX operators are:

Arithmetic operators	Comparison operators	Logical operators	Text operators
Perform mathematical operations, such as addition (+), subtraction (-), multiplication (*), division (/), exponentiation (^), and modulus (%)	Compare two values and return true or false, such as equal to (=), not equal to (<>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=)	Perform logical operations, such as and (&&), or (||), and not (!)	Perform text operations, such as concatenation (&)

Exercise Tasks:

Download the excel file with data about EmployeeID, FirstName, LastName, Department, Salary, JoiningDate. Import the file to Power BI and perform the following exercise tasks. Or you can refer to this Power BI file where I have completed all the tasks below for your reference.

1. Total Salary: Use the SUM function to find the total salary of all employees.

Total Salary = SUM(Employee[Salary]) (Result: 483000)

2. Average Salary: Apply the AVERAGE function to calculate the average salary of employees.

Average Salary = AVERAGE(EmployeeData[Salary]) (Result: 48300)

3. Employees in IT Department: Use the COUNT function to find the number of employees in the IT department.

Employees in IT Department = COUNTROWS(FILTER(EmployeeData, EmployeeData[Department] = "IT")) (Result: 3)

Explanation: COUNTROWS is counting all the rows in our table, but we only want employees in IT department so we used Filter function.

4. Minimum and Maximum Salary: Apply the MIN function to find the minimum salary.

Use the MAX function to find the maximum salary.

Maximum Salary = MAX(EmployeeData[Salary])

Minimum Salary = MIN(EmployeeData[Salary])

Explanation: In the report view I have Multi-row Card visual to see minimum and maximum salary in each department.

5. Evaluate Increment Status:

Create a new column, 'Increment Status,' using an IF-THEN-ELSE statement:

If the Employee Salary is less than 25000, give status as 'Increment Required'

Else give status as 'Increment Done'

Increment Status = IF(EmployeeData[Salary] < 25000, "Increment Required", "Increment Done")

6. Evaluate Joining Date Conditions:

Create a new column, 'Seniority,' using an IF-THEN-ELSE statement:

If the Joining Date is before 2020-01-01, label as 'Senior.'

If the Joining Date is between 2020-01-01 and 2021-01-01, label as 'Mid-Level.'

If the Joining Date is after 2021-01-01, label as 'Junior.'

Seniority = IF(EmployeeData[JoiningDate] < DATE(2020,1,1), "Senior", IF(EmployeeData[JoiningDate] < DATE(2021,1,1), "Mid-Level", "Junior"))

Explanation: I have tried to take a step above for IF function in this task and even used DATE function.

7. Combine First and Last Names: Create a new column, 'FullName,' using the CONCATENATE function or you can use & operator as well to achieve the same results.

FullName2 = EmployeeData[FirstName]&" "&EmployeeData[LastName]

FullName = CONCATENATE(EmployeeData[FirstName], EmployeeData[LastName])

Explanation: When we use CONCATENATE function it is restricted to only 2 text columns so you can see in the screenshot attached FullName column does not have any space between FirstName and LastName like Fullname2 Column where we are using '&' DAX operator.

8. Format Salary with Thousands Separator: Use the FORMAT function to display the Salary column with a thousands separator.

Formated Salary = FORMAT(EmployeeData[Salary], "#,###")

Suggestion: There is an another way to format numbers with thousands separator is to change its format to whole number as shown in the screenshot below.

9. Extract First Three Characters of Department: Create a new column, 'DeptAbbreviation,' using the LEFT function to extract the first three characters of the Department names.

Dept Abbreviation = LEFT(EmployeeData[Department], 3)

10. Extract Last Four Characters of EmployeeID: Create a new column, 'EmployeeIDSuffix,' using the RIGHT function to extract the last four characters of the EmployeeID.

EmployeeIDSuffix = RIGHT(EmployeeData[EmployeeID],3)

Conclusion

DAX is a powerful language for creating and manipulating data in Power BI. I hope this blog post is helpful and informative for you. Thank you for reading!