20 Essential DAX Functions Every Data Analyst and Power BI Developer Should Know for a Successful Interview

1. SUM: The SUM function calculates the sum of a column or expression for the rows that meet a specified filter criteria.

Total Sales Amount = SUM(Sales[Sales Amount])

In this example, the SUM function calculates the total sales amount for the Sales[Sales Amount] column in the sales table.

2. CALCULATE: The CALCULATE function modifies the filter context of a DAX expression by applying additional filter criteria.

Total Sales Amount for 2022 = CALCULATE(SUM(Sales[Sales Amount]), Sales[Year] = 2022)

In this example, the CALCULATE function calculates the total sales amount for the Sales[Sales Amount] column in the sales table, but only for the rows where the Sales[Year] column equals 2022.

3. FILTER: The FILTER function returns a table that contains a subset of the rows from a specified table that meet a specified filter criteria.

Total Sales Amount by Category = 
    SUMX(
        FILTER(
            Product,
            [Selected Category] = "All" || Product[Category] = [Selected Category]
        ),
        RELATED(Sales[Sales Amount])
    )

In this example, the FILTER function returns a table of rows from the Product table where the category matches the selected category or where the selected category is "All". The SUMX function then calculates the sum of the related sales amount for each row in the filtered table.

4. AVERAGE: The AVERAGE function calculates the average of a column or expression for the rows that meet a specified filter criteria.

Average Sales Amount = AVERAGE(Sales[Sales Amount])

In this example, the AVERAGE function calculates the average sales amount for the Sales[Sales Amount] column in the sales table.

5. COUNTROWS: The COUNTROWS function returns the number of rows in a table that meet a specified filter criteria.

Number of Customers = COUNTROWS(Customer)

In this example, the COUNTROWS function returns the number of rows in the Customer table, which represents the number of customers in the dataset.

6. MAX: The MAX function returns the maximum value of a column or expression for the rows that meet a specified filter criteria.

Maximum Sales Amount = MAX(Sales[Sales Amount])

In this example, the MAX function returns the maximum sales amount for the Sales[Sales Amount] column in the sales table.

7. MIN: The MIN function returns the minimum value of a column or expression for the rows that meet a specified filter criteria.

Minimum Sales Amount = MIN(Sales[Sales Amount])

In this example, the MIN function returns the minimum sales amount for the Sales[Sales Amount] column in the sales table.

8. CONCATENATEX: The CONCATENATEX function concatenates the values of a column or expression for the rows that meet a specified filter criteria.

List of Customers = CONCATENATEX(Customer, Customer[Name], ", ")

In this example, the CONCATENATEX function concatenates the values of the Customer[Name] column for each row in the Customer table, separated by commas and spaces. The resulting string represents a list of customers.

9. IF: The IF function returns one value if a specified condition is true and another value if the condition is false.

Customer Status = IF(Customer[Sales Amount] > 100000, "VIP", "Regular")

In this example, the IF function checks if the Customer[Sales Amount] is greater than 100000. If it is, the function returns "VIP", indicating that the customer is a VIP. Otherwise, the function returns "Regular", indicating that the customer is a regular customer. This calculation can be used to create a new column in the Customer table that categorizes customers based on their sales amount.

10. SUMX: The SUMX function returns the sum of a column or expression for the rows that meet a specified filter criteria.

Total Sales Amount = SUMX(Sales, Sales[Sales Amount])

In this example, the SUMX function returns the sum of the Sales[Sales Amount] column for each row in the Sales table. This calculation can be used to calculate the total sales amount across all transactions in the dataset.

11. AVERAGEX: The AVERAGEX function calculates the average of a column or expression for the rows that meet a specified filter criteria.

Average Sales Amount per Customer = AVERAGEX(Customer, Customer[Sales Amount])

In this example, the AVERAGEX function calculates the average sales amount for each customer in the Customer table, and then returns the overall average across all customers. This calculation can be used to understand the average sales per customer in the dataset.

12. RANKX: The RANKX function ranks the values of a column or expression in descending order and returns the rank for each row.

Rank of Sales Amount = RANKX(Sales, Sales[Sales Amount], ,DESC)

In this example, the RANKX function ranks the values of the Sales[Sales Amount] column in descending order for each row in the Sales table, and returns the rank of each row. This calculation can be used to rank the transactions in the dataset by their sales amount.

13. ALLSELECTED: The ALLSELECTED function removes all filters from a table except those specified by the user. It is commonly used to calculate measures that should ignore certain user-defined filters.

Sales Amount (excluding current month) = 
    CALCULATE(SUM(Sales[Sales Amount]), 
    DATESYTD(Calendar[Date]) - 
    DATESMTD(Calendar[Date]), 
    ALLSELECTED(Calendar))

In this example, the ALLSELECTED function is used to remove all filters on the Calendar table except for those specified by the user. This calculation can be used to calculate the sales amount for the current year to date, excluding the current month.

14. SWITCH: The SWITCH function is used to evaluate a list of conditions and return a corresponding result for the first condition that is true. It is similar to an IF statement, but can handle multiple conditions.

Region Category = SWITCH(Table[Region], 
    "NY", "East", 
    "CA", "West", 
    "MA", "North", 
    "TX", "South", 
    "Other")

In this example, the SWITCH function evaluates the Table[Region] column and returns a corresponding value based on the region name. If the region is "NY", the function returns "East". If the region is "CA", the function returns "West", and so on. This calculation can be used to group regions into broader categories for analysis.

15. CALCULATETABLE: The CALCULATETABLE function is used to evaluate a table expression, such as a filter or a join operation, within the context of a specified filter context. This function is useful for creating dynamic tables that change based on user-defined filters or slicers.

Top 10 products by sales = 
    TOPN(10, 
        CALCULATETABLE(
            VALUES(Product[Product Name]),
            FILTER(Sales, Sales[Sales Amount] > 0)
        ), 
        [Sales Amount]
    )

In this example, the CALCULATETABLE function is used to evaluate a table expression that returns all distinct values of the Product[Product Name] column, filtered to only include products with positive sales amounts in the Sales table. The resulting table is then sorted by the [Sales Amount] column, and the top 10 products are returned using the TOPN function. This calculation can be used to create a dynamic table of the top-selling products based on user-defined filters.

16. SUMX: The SUMX function is used to iterate over a table or an expression and return the sum of a specified expression evaluated for each row in the table or expression. This function is useful for calculating a weighted average or performing other calculations that involve iterating over a table or expression.

Weighted average price = 
    SUMX(
        Sales,
        Sales[Sales Amount] * Product[Price]
    ) / SUM(Sales[Sales Amount])

In this example, the SUMX function is used to iterate over each row in the Sales table and return the sum of the [Sales Amount] column multiplied by the [Price] column in the Product table. The resulting value is divided by the total sales amount in the Sales table to calculate the weighted average price of all products sold. This calculation can be used to calculate a weighted average price or other weighted metrics based on the sales data.

17. DIVIDE: The DIVIDE function is used to divide one number by another number and handle any errors that may occur during the calculation. This function is useful for avoiding divide-by-zero errors or other errors that may occur when dividing numbers.

Average profit margin = 
    DIVIDE(
        SUM(Sales[Profit]),
        SUM(Sales[Sales Amount]),
        0
    )

In this example, the DIVIDE function is used to calculate the average profit margin of all sales transactions. The function takes three arguments: the first argument is the numerator of the division (the sum of the [Profit] column in the Sales table), the second argument is the denominator of the division (the sum of the [Sales Amount] column in the Sales table), and the third argument is the value to use if the denominator is zero (in this case, zero is used to avoid a divide-by-zero error). The resulting value is the average profit margin of all sales transactions.

Sure, here's another DAX function:

19. LOOKUPVALUE: The LOOKUPVALUE function is used to return a value from a column in a table that matches a specified set of criteria. This function is useful for looking up a specific value in a related table or for retrieving a value from a table based on a user input or parameter.

Salesperson name = 
    LOOKUPVALUE(
        Salesperson[Name],
        Salesperson[ID],
        Sales[Salesperson ID]
    )

In this example, the LOOKUPVALUE function is used to return the name of the salesperson associated with each sales transaction in the Sales table. The function takes three arguments: the first argument is the column to return a value from (in this case, the [Name] column of the Salesperson table), the second argument is the column to match the value against (in this case, the [ID] column of the Salesperson table), and the third argument is the value to match (in this case, the [Salesperson ID] column of the Sales table). The function returns the value from the [Name] column of the Salesperson table that matches the specified [ID] value in the [Salesperson ID] column of the Sales table.

Sure, here's another DAX function:

20. CONCATENATE: The CONCATENATE function is used to combine two or more text strings into a single string. This function is useful for creating calculated columns that combine information from multiple columns or for formatting text values for display purposes.

Full name = 
    CONCATENATE(
        Customer[First Name], " ", Customer[Last Name]
    )

In this example, the CONCATENATE function is used to create a full name column in the Customer table by combining the [First Name] and [Last Name] columns. The function takes two or more arguments, which are the text strings to be concatenated (in this case, the [First Name] column, a space character, and the [Last Name] column). The resulting value is a single text string that represents the customer's full name.

Difference between CONCATENATE and CONCATENATEx -