Power BI DAX Functions - Learn Data Science

DAX (Data Analysis Expressions) is a formula language used in Power BI, Power Pivot, and Analysis Services. It consists of functions, operators, and constants that enable users to create custom calculations in calculated columns and measures. DAX allows users to extract insights, perform complex calculations, and manipulate data within a data model.

What is a DAX Function?

A DAX function is an inbuilt function provided in the DAX language to enable you to perform various actions on the data in the tables in your Data Model. These functions help perform common calculations and aggregations. While some DAX functions share names with Excel functions, they are optimized for relational data and dynamic calculations.

SUM_Sales = SUM(Sales[Revenue])

This formula calculates the total revenue from the Sales table.

Excel Functions vs. DAX Functions

Similarities Between Excel Functions and DAX Functions

Certain DAX functions have the same name and functionality as Excel functions.
DAX has lookup functions similar to Excel’s array and vector lookup functions.

Differences Between Excel Functions and DAX Functions

DAX functions take entire tables or columns as input rather than cell references.
DAX date and time functions return datetime values instead of integer-based serial numbers.
DAX allows direct references to tables and relationships, unlike Excel.

Types of DAX Functions

DAX Table-Valued Functions

These functions return tables rather than single values.

FILTERED_SALES = FILTER(Sales, Sales[Revenue] > 1000)

Returns a table containing sales transactions where revenue is greater than 1000.

DAX Aggregation Functions

These functions summarize values within a column.

Total_Sales = SUM(Sales[Amount])

Computes the total amount of sales.

DAX Filter Functions

Filter functions help extract specific data based on conditions.

Top_Selling_Products = TOPN(5, Sales, Sales[Revenue], DESC)

Retrieves the top 5 products by revenue.

DAX Time Intelligence Functions

These functions manipulate data using time-based calculations.

Previous_Year_Sales = CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Sales[Date]))

Computes sales from the previous year.

DAX Date and Time Functions

Used for date-related calculations.

Today_Date = TODAY()

Returns the current date.

DAX Information Functions

Check the type or presence of data.

IsBlankCheck = IF(ISBLANK(Sales[Discount]), "No Discount", "Discount Applied")

Verifies if a discount is applied.

DAX Logical Functions

Performs conditional evaluations.

High_Sales = IF(Sales[Revenue] > 5000, "High", "Low")

Classifies sales as “High” or “Low”.

DAX Math and Trig Functions

Used for mathematical operations.

SquareRoot_Sales = SQRT(Sales[Revenue])

Computes the square root of revenue.

DAX Parent and Child Functions

Used for hierarchical relationships.

Parent_Child = PATH(Sales[EmployeeID], Sales[ManagerID])

Builds a hierarchy path for employees.

DAX Statistical Functions

Used for statistical analysis.

Avg_Sales = AVERAGE(Sales[Revenue])

Calculates the average sales revenue.

DAX Text Functions

Manipulate text data.

FullName = CONCATENATE(Sales[FirstName], " ", Sales[LastName])

Combines first and last names into a full name.

Examples of DAX Formulas

Basic DAX Example: Calculating Total Sales

Total_Sales = SUM(Sales[Amount])

Computes the sum of the Amount column.

Intermediate DAX Example: Filtering Sales by Region

Sales_USA = CALCULATE(SUM(Sales[Revenue]), Sales[Country] = "USA")

Calculates the total revenue for sales in the USA.

Advanced DAX Example: Year-over-Year Growth

YoY_Growth = 
    DIVIDE(
        (SUM(Sales[Revenue]) - CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Sales[Date]))),
        CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Sales[Date]))
    )

Calculates the year-over-year revenue growth.

Conclusion

DAX is a powerful formula language that allows for advanced data analysis and business intelligence in Power BI. Understanding different types of DAX functions and their applications helps in building dynamic and insightful reports.

By mastering DAX, you can unlock the full potential of Power BI for data-driven decision-making!