DAX FORMULAS USING IN POWER BI

 

Introduction:

Data Analysis Expressions (DAX) is a powerful formula and query language used in Power BI for creating calculated columns, measures, and advanced calculations. It provides a flexible and efficient way to manipulate and aggregate data to derive valuable insights. In this article, we will explore 22 powerful DAX formulas and functions that beginners can leverage to perform complex data manipulations and aggregations within Power BI.

1. CALCULATE:

The CALCULATE function is one of the most important functions in DAX. It allows you to modify the context in which a calculation is performed, enabling you to filter, modify, or override the default behavior of calculations.

Example:

Suppose you have a sales table with columns for sales amount, date, and region. You can use the CALCULATE function to filter the sales amount by a specific date and region. For instance, the following DAX formula calculates the total sales amount for the Western region in January 2022:

=CALCULATE(SUM(Sales[Amount]), Sales[Region]="West", YEAR(Sales[Date])=2022, MONTH(Sales[Date])=1)

2. SUM:

The SUM function calculates the sum of a specified column or expression. It is commonly used to aggregate numerical data.

Example:

Suppose you have a table with a column for sales amount. You can use the SUM function to calculate the total sales amount. For instance, the following DAX formula calculates the total sales amount:

=SUM(Sales[Amount])

3. AVERAGE:

The AVERAGE function calculates the average of a specified column or expression. It is useful for finding the average value of numerical data.

Example:

Suppose you have a table with a column for product prices. You can use the AVERAGE function to calculate the average price. For instance, the following DAX formula calculates the average price:

=AVERAGE(Products[Price])

4. COUNT:

The COUNT function counts the number of rows in a table or column that contain a value. It is helpful for determining the number of occurrences.

Example:

Suppose you have a table with a column for product names. You can use the COUNT function to count the number of products. For instance, the following DAX formula counts the number of products:

=COUNT(Products[Name])

5. MIN/MAX:

The MIN and MAX functions return the minimum and maximum values, respectively, from a specified column or expression.

Example:

Suppose you have a table with a column for product prices. You can use the MIN and MAX functions to find the lowest and highest prices, respectively. For instance, the following DAX formulas find the minimum and maximum prices:

=MIN(Products[Price])

=MAX(Products[Price])

6. DISTINCT:

The DISTINCT function returns a one-column table that contains unique values from a specified column. It is useful for eliminating duplicate values.

Example:

Suppose you have a table with a column for product categories. You can use the DISTINCT function to retrieve a list of unique categories. For instance, the following DAX formula retrieves the distinct categories:

= DISTINCT(Products[Category])

7. CONCATENATE:

The CONCATENATE function combines multiple text strings into a single text string. It is handy for creating concatenated fields.

Example:

Suppose you have a table with columns for first and last names. You can use the CONCATENATE function to create a full name field. For instance, the following DAX formula creates a full name field:

=CONCATENATE(Customers[First Name], " ", Customers[Last Name])

8. LEFT/RIGHT:

The LEFT and RIGHT functions extract a specified number of characters from

the left or right side of a text string, respectively.

Example:

Suppose you have a table with a column for phone numbers. You can use the LEFT and RIGHT functions to extract the area code and last four digits, respectively. For instance, the following DAX formulas extract the area code and last four digits:

=LEFT(Orders[Phone Number], 3)

=RIGHT(Orders[Phone Number], 4)

9. LEN:

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

Example:

Suppose you have a table with a column for product descriptions. You can use the LEN function to calculate the length of each description. For instance, the following DAX formula calculates the length of the description:

=LEN(Products[Description])

10. UPPER/LOWER/PROPER:

The UPPER, LOWER, and PROPER functions convert text to uppercase, lowercase, and proper case (capitalizing the first letter of each word), respectively.

Example:

Suppose you have a table with a column for customer names. You can use the UPPER, LOWER, and PROPER functions to format the names. For instance, the following DAX formulas convert the names to uppercase, lowercase, and proper case:

=UPPER(Customers[Name])

=LOWER(Customers[Name])

=PROPER(Customers[Name])

11. IF:

The IF function performs conditional evaluations. It returns one value if a condition is true and another value if the condition is false.

Example:

Suppose you have a table with a column for sales amounts. You can use the IF function to categorize sales as "High" or "Low" based on a threshold. For instance, the following DAX formula categorizes sales:

=IF(Sales[Amount] > 1000, "High", "Low")

12. SWITCH:

The SWITCH function is similar to the IF function but allows you to evaluate multiple conditions and return different values based on each condition.

Example:

Suppose you have a table with a column for product ratings. You can use the SWITCH function to assign a rating category based on the rating value. For instance, the following DAX formula assigns a rating category:

=SWITCH(Products[Rating], 1, "Poor", 2, "Average", 3, "Good", 4, "Excellent")

13. ISBLANK:

The ISBLANK function checks whether a value is blank or empty. It returns TRUE if the value is blank and FALSE otherwise.

Example:

Suppose you have a table with a column for order dates. You can use the ISBLANK function to identify orders with missing dates. For instance, the following DAX formula flags orders with missing dates:

=IF(ISBLANK(Orders[Date]), "Missing", "Present")

14. AND/OR:

The AND and OR functions perform logical operations. They return TRUE or FALSE based on the logical conditions specified.

Example:

Suppose you have a table with columns for product prices and quantities. You can use the AND function to check if both price and quantity meet certain criteria. For instance, the following DAX formula checks if the price is greater than $50 and the quantity is less than 10:

=AND(Products[Price] > 50, Products[Quantity] < 10)

15. DATE:

The DATE function creates a date from specified year, month, and day values. It is useful for working with date-related calculations.

Example:

Suppose you have a table with columns for year, month, and day. You can use the DATE function to create a date field. For instance, the following DAX formula creates a date field:

=DATE(Orders[Year], Orders[Month], Orders[

Day])

16. YEAR/MONTH/DAY:

The YEAR, MONTH, and DAY functions extract the year, month, and day components from a date, respectively.

Example:

Suppose you have a table with a column for order dates. You can use the YEAR, MONTH, and DAY functions to extract the corresponding components. For instance, the following DAX formulas extract the year, month, and day:

=YEAR(Orders[Date])

=MONTH(Orders[Date])

=DAY(Orders[Date])

17. FORMAT:

The FORMAT function converts a value to a specified format. It is handy for formatting numbers, dates, and times.

Example:

Suppose you have a table with a column for sales amounts. You can use the FORMAT function to format the amounts as currency. For instance, the following DAX formula formats the sales amounts:

=FORMAT(Sales[Amount], "Currency")

18. RANKX:

The RANKX function calculates the rank of a value in a specified column. It is useful for creating rankings based on certain criteria.

Example:

Suppose you have a table with a column for product sales. You can use the RANKX function to rank the products based on sales. For instance, the following DAX formula calculates the sales rank:

=RANKX(Products, Products[Sales])

19. RELATED:

The RELATED function retrieves a value from a related table based on a specified relationship.

Example:

Suppose you have a table with a column for customer IDs and another table with customer names. You can use the RELATED function to retrieve the corresponding customer names based on the IDs. For instance, the following DAX formula retrieves the customer name:

=RELATED(Customers[Name])

20. CALCULATETABLE:

The CALCULATETABLE function creates a new table by applying filters to an existing table or column.

Example:

Suppose you have a table with columns for sales amounts and regions. You can use the CALCULATETABLE function to filter the sales amounts by region. For instance, the following DAX formula creates a new table with sales amounts in the Western region:

=CALCULATETABLE(Sales, Sales[Region]="West")

21. FIRSTNONBLANK/LASTNONBLANK:

The FIRSTNONBLANK and LASTNONBLANK functions return the first or last non-blank value, respectively, from a specified column or expression.

Example:

Suppose you have a table with a column for product sales. You can use the FIRSTNONBLANK and LASTNONBLANK functions to retrieve the first and last non-blank sales amounts, respectively. For instance, the following DAX formulas retrieve the first and last non-blank sales amounts:

=FIRSTNONBLANK(Products[Sales], 0)

=LASTNONBLANK(Products[Sales], 0)

22. DIVIDE:

The DIVIDE function divides two numbers and handles zero or blank values gracefully by returning a specified alternate value.

Example:

Suppose you have a table with columns for sales and units sold. You can use the DIVIDE function to calculate the sales per unit, handling the case where units sold is zero. For instance, the following DAX formula calculates the sales per unit, returning zero when units sold is zero:

=DIVIDE(Sales[Amount], Sales[Units Sold], 0)