Data Analysis Expression ( DAX ) in Power BI

What is DAX?

In rookie terms DAX stands for Data Analysis Expression. These are powerful formulas consisting of functions, constants, variables that are designed to create custom calculations and aggregations in Power BI reports and data models. It facilitates the generation of calculated values from the data within your model. By leveraging DAX, you can derive new insights and information that extend beyond the raw data, enhancing the analytical capabilities of your data model.

Syntax Example

Total Orders:

• This is the name of the measure being created. You can use this name to refer to the measure in your Power BI reports and visualizations.

COUNT:

• Count is a DAX function that counts the number of values in a column. It specifically counts the number of non-blank values. In this context, it is used to count the number of order IDs.

Orders[Order_Id]:

• Orders is the name of the table containing the order data.
• Order_Id is the column within the Orders table that holds the unique identifiers for each order.

This measure is useful when you want to see the total count of orders in a report or visualization, and it dynamically adjusts based on the filters applied to your data model or report.

Frequently Used DAX

In DAX (Data Analysis Expressions) usage within Power BI, several functions are routinely employed due to their versatility and essential role in data analysis and reporting.

SUM

Purpose: SUM is a DAX function that aggregates all values in a specified column to yield a cumulative total.

Total Sales = SUM(Sales[Sales_Amount])

Total Sales = SUM(Sales[Sales_Amount]) calculates the aggregate total of the Sales_Amount column in the Sales table. This results in the overall sum of sales amounts recorded in that column.

COUNT

Purpose: Counts all the non zero values of a particular column

Total Orders = COUNT(Orders[Order_Id])

The formula calculates how many non-blank entries there are in the Order_Id column. It effectively counts the total number of orders where the Order_Id is not empty.

AVERAGE

Purpose: Average is a DAX function that calculates the arithmetic mean of the values in a column. It aggregates the sum of all non-blank values in the specified column and divides by the count of these values.

Average Selling Price = Average (Sales[Selling_Price])

The DAX formula is used to compute the average selling price of items in the Sales table. This measure helps in understanding typical selling prices, facilitating better analysis and reporting within Power BI.

IF

Purpose: If is a DAX function that performs conditional logic. It evaluates a condition and returns one value if the condition is true and another value if it is false.

Average Sales Category = IF(AVERAGE(Sales[Amount])>1000, “High”, “Low”)

The DAX formula is used to categorize the average sales amount into "High" or "Low" based on a specified threshold. This formula is implemented as a measure in Power BI.

LEN

Purpose: The LEN function calculates the number of characters in a text string. It is helpful for determining the length of a string.

Description Length = LEN(Products[Description])

The DAX formula calculates the number of characters in each text entry within the Description column of the Products table. This function is valuable for text analysis and quality checks, allowing you to assess and visualize the length of product descriptions in your Power BI reports.

DISTINCT

Purpose: The DISTINCT function in DAX is used to return a one-column table containing only the unique values from a specified column. This is especially useful for eliminating duplicates and getting a distinct list of values in your data model.

Unique Customer Names = DISTINCT(Sales[Customer_Name])

Above DAX formula creates a new table that contains unique customer names from the Sales table. This formula is useful when you want to retrieve and work with a list of distinct customer names without any duplicates.

SWITCH

Purpose: SWITCH is a DAX function that evaluates expression against a list of possible values and return a corresponding result based on the first matching value.

Rating Category = SWITCH(
Products[Rating],
1, “Poor”,
2, “Average”,
3, “Good”,
4, “Excellent”,
“Unknown”
)

Above DAX formula is used to create a calculated column that assigns a textual category to each numeric rating in the Rating column of products table . This categorization provides a more intuitive understanding of the ratings by converting numerical values into descriptive labels.

DATE

Purpose: The DATE function in DAX is essential for creating a date value from separate year, month, and day components. This function is useful for constructing date fields in your data model, especially when your data is split across different columns for year, month, and day.

StartOfMonth = DATE(YEAR(Orders[OrderDate]), MONTH(Orders[OrderDate]), 1)

Above DAX formula is used to calculate the first day of the month for each date in the Orders[OrderDate] column. This is a common operation when you want to aggregate or analyze data on a monthly basis.

AND

Purpose: The AND function returns True if all the specified conditions evaluate to True . If any condition evaluates to False, the And function returns False.

HighValue Sale = AND(Sales[SalesAmount]>1000, Sales[ProfitMargin]> 0.20)

Above DAX formula is used to create a calculated column or measure that evaluates whether a sale meets specific criteria for being classified as "High Value."

OR

Purpose: The OR function returns True if any of the specified conditions evaluate to True . If all conditions evaluate to False, the OR function returns False.

HighValue Sale = OR(Sales[SalesAmount] > 1000, Sales[ProfitMargin] > 0.20)

Above DAX formula is used to create a calculated column or measure that determines whether a sale meets at least one of two specified criteria to be classified as "High Value."

RELATED

Purpose: In DAX, the RELATED function is used to retrieve a related value from another table in a data model. It is particularly useful when working with tables that are connected through relationships. The RELATED function allows you to access values from a related table based on the existing relationships in your data model.

Customer Name = RELATED(Customers[Customer_Name])

Above DAX formula is used to create a calculated column in a table that pulls in a value from a related table.

LEFT/RIGHT

Purpose: In DAX, the LEFT and RIGHT functions are used to extract a specified number of characters from the left or right side of a text string, respectively. These functions are useful for text manipulation tasks such as parsing phone numbers, codes, or any other text data where you need to extract specific portions of the string.

Pincode = LEFT(Orders[Cell_Number], 4)

The DAX formula Pincode = LEFT(Orders[Cell_Number], 4) is designed to create a calculated column that extracts the first four characters from the Cell_Number column in the Orders table.

Last ThreeDigits = RIGHT(Orders[Cell_Number, 3])

Above DAX formula extracts last three digits of the Cell_Number.

CONCATENATE

Purpose: The CONCATENATE function in DAX is used to join two text strings into a single text string. This function is useful for combining text fields or creating custom labels and descriptions by merging different columns of data.

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

he DAX formula is designed to create a new column named FullName by combining the FirstName and LastName columns from the Orders table into a single text string.

LOOKUPVALUE

Purpose: The LOOKUPVALUE function in DAX is used to search for a value in a column based on criteria provided for one or more other columns. It is particularly useful f1or retrieving a value from a related table or performing lookups in a data model where relationships might not be directly defined.

Product Name = LOOKUPVALUE(Products[Product_Name], Products[Product_ID], Sales[Product_ID])

Products[Product_Name] is the result column containing the product names.

Products[Product_ID] is the column to search in the Products table.

Sales[Product_ID] is the column in the Sales table that provides the value to match against Sales[Product_ID].

In the Sales table, this formula will add a new column Product_Name showing the names of products for each sale.

UPPER/LOWER/PROPER

Purpose: These functions in DAX are text functions used to modify the case of text strings. These functions are helpful for standardizing text data in Power BI, ensuring consistency, and preparing data for analysis.

Upper ProductName = UPPER(Products[ProductName])

Converts each value in Products[ProductName] to uppercase.

Lower CustomerName = LOWER(Customers[CustomerName])

Converts each value in Customers[CustomerName] to lowercase.

Proper Address = PROPER(Customers[Address])

Converts each value in Customers[Address] so that the first letter of each word is capitalized, and the rest of the letters are in lowercase.

These DAX functions form the core of data manipulation and calculation in Power BI. They enable users to aggregate, filter, and transform data effectively, enhancing reporting and analytical capabilities. Understanding and using these functions efficiently can significantly improve your ability to work with data in Power BI.

Thanks for reading :)