DAX in Power BI made easy

In this third article of the Power BI series, we dive into the world of DAX (Data Analysis Expressions) functions. After discussing data preprocessing and data modeling in the previous articles, we now turn our attention to the analytical power that DAX brings to Power BI.

Whether you're a data analyst, business intelligence professional, or just someone interested in maximizing the potential of your data, mastering DAX (Data Analysis Expressions) is a powerful tool to have in your skillset.

DAX stands for Data Analysis Expressions, a formula language used in Microsoft Power BI, Excel Power Pivot, and SSAS. DAX allows users to create custom calculations and aggregations on data, providing enhanced insights and analytics. It involves in filtering, summarising, and manipulating large datasets, making it possible to generate dynamic and actionable reports.

DAX is designed to extend the capabilities of simple functions like those in Excel. With DAX, you can perform powerful data transformations and calculations that go beyond basic aggregation or filtering.

DAX Syntax

DAX uses a simple and intuitive syntax that will be familiar to anyone who’s used Excel formulas. If you want to create a new measure using DAX, just click on the new measure button in home and you will get a formula bar where you can write your DAX formulae. The basic syntax to write DAX functions is to write the name of your measure or your column name in case you are making a calculated column and then write your formula after the “=” sign.?

For example: if you have to calculate Total sales generated then DAX measure will be written as:

TotalSales = SUM(Orders[SalesAmount])

Here, SalesAmount is a column name and Orders is the name of the table where this column is present and SUM is a basic DAX function.

Types of DAX Functions:

• Time intelligence expressions
• Text Functions
• Logical functions
• Date and Time Functions
• Filter functions?
• Statistical functions
• Aggregate functions

Now, we will discuss important DAX functions from these, such as:

Text Functions:

• LEFT: Returns the specified number of characters from the start of a text string.

• LEN: Returns the number of characters in a text string.

• LOWER: Converts all letters in a text string to lowercase.

• MID: Returns a string of characters from the middle of a text string, given a starting position and length.

• REPLACE: Replaces part of a text string with a different text string.

• REPT: Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

• RIGHT: Returns the specified number of characters from the end of a text string.

• SUBSTITUTE: Replaces existing text with new text in a text string.

Logical Functions:

These functions include?

IF function: Return a value based on specific conditions. This is just like basic if-else condition in programming languages.?

For example: If you want to make a calculated column in such a way that if sales are more than 100 then the value will be “ok” otherwise “not ok” should be printed, then DAX function will be written as:

AND: Returns True if all arguments are True and and returns False otherwise.

For Example: If you want that “OK” will be printed if sales are greater than 100 as well as orders are greater than 50, then DAX function well be:

OR: Returns True if any of the arguments are True?

For Example: If you want that “OK” will print if either sales are greater than 100 or orders are greater than 50, then DAX function will be written as:

Aggregate Functions:

They are very useful and the popular aggregate functions are sum, average, min, max, count, distinctcount etc. They are pretty straight forward such as average will calculate the average of values of a column and min and max simply gives minimum and maximum value of a column respectively. Similarly, count will give count of all values in a column but distinctcount will count only distinct occurrences of values in column.?

There are other aggregate functions which we need to look at, such as MAX and MAXX, SUM and SUMX, AVERAGE and AVERAGEX etc.

MAX and MAXX:

MAX is already discussed as it just gives maximum value in a specific column but MAXX is more advanced and operates in an iterative manner. It allows you to define an expression or perform a calculation row by row on a table before finding the maximum result from those calculated values. For example, if you want to calculate the highest revenue based on multiplying Quantity and Price for each row in the Sales table, you would use MAXX like below:

Similarly, SUM and AVERAGE are aggregate functions and SUMX and AVERAGEX are iterative functions and are performed row by row and will give a sum and average of values after a specific expression.

Date and Time Functions:

?Although the power query editor allows a lot of date and time functions, there are many date and time functions still present here.

Based on the date column in a table, you want to create a new column or to extract some date regarding information from that column, you can use these functions. For instance, if you wanna extract the year then click on the new column then write formula:?

The DATEDIFF function is also an extremely useful function for calculating the difference between two dates. For instance, if you want to determine the number of days between when an order was placed and when it was shipped, you can use the following formula:

In this case, DATEDIFF computes the difference between the shippedDate and orderDate, and the third argument (DAY) specifies that the difference should be measured in days. For example, if the dates are 21-Sep-2023 and 24-Sep-2023, the result would be 3 days (24 - 21 = 3). If you were to specify months instead of days, the result would be 0, since both dates fall within the same month. This simple yet powerful technique allows you to cleanly calculate the difference between dates in your dataset, enhancing your data analysis.

The WEEKDAY function can be used to determine the day number for a given date. For example, if you want to know the day number for a specific date in your dataset, you can use:

In this formula, WEEKDAY returns the day number of the date found in the [Date Purchased] column. The second argument (2) is the return_type, which defines how the days are numbered. For example, if you use 1 as the return type, the numbering starts with Sunday as 1 and ends with Saturday as 7. If you use 2, Monday becomes 1, and Sunday becomes 7. This flexibility is useful for analysing patterns in your data, like determining which days of the week had the highest number of sales.

Some Most Popular DAX Functions:

FILTER function

The FILTER function returns a table that meets specified conditions, allowing you to filter data sets and use that subset for further analysis.

For instance, if you want to filter a sales table to only include sales above $100, you will write:

TOPN function

TOPN is used to return the top N rows of a table based on a ranking criterion. It's ideal for ranking data, such as finding the top-selling products.

To get the top 3 highest-selling products:

SUMMARIZE function

SUMMARIZE is used to group data by one or more columns, and it is useful for creating reports that summarize or aggregate data.

Example: To create a summary of total sales by product category:

CALCULATE function

CALCULATE is one of the most powerful DAX functions. It modifies the context of a calculation by applying one or more filters.

To calculate total sales for a specific year (e.g., 2023):

These DAX functions form the core of creating complex measures and custom insights in Power BI, enabling dynamic reporting that adapts to different scenarios and business needs.