PowerBI : Create Model Calculations using DAX

Diving deeper into the world of Power BI, we explore the capabilities of Data Analysis Expressions (DAX), a powerful formula language allowing you to create custom calculations and aid in data analysis. This blog will expand on each section of Module 4, providing you with a comprehensive understanding and practical examples of how DAX can be utilized to enhance your data models.

DAX Context

DAX operates within two specific contexts – row context and filter context.

• Row Context: This context is about the current row when you’re calculating a column. For example, when creating a calculated column to show each salesperson’s sales as a percentage of total sales, DAX will consider each row independently during the calculation.
• Filter Context: This refers to the set of filters applied to the data at any given time. This context is dynamic and changes based on user interactions with the report. For instance, selecting a specific year on a slicer filters the entire report to that year, altering the filter context.

Calculated Tables

Calculated tables are created by using DAX formulas to combine or modify existing tables. For example, you might create a calculated table that summarizes sales data by month or combines sales and inventory data.

Example:

SalesSummary = SUMMARIZE(Sales, Sales[SalesID], "TotalSales", SUM(Sales[Amount]))

This DAX formula creates a new table, SalesSummary, that contains a summary of total sales for each SalesID.

Calculated Columns

These are columns that you add to existing tables in your model using a DAX formula. Calculated columns are computed once when the data is refreshed and are used like any other column.

Example:

Total Price = Sales[Quantity] * Products[Price]

This formula multiplies each row’s quantity by the product’s price to calculate the total price for each sale.

Managing Date Tables

Date tables are crucial for time-based calculations in your model. You often need a separate table containing dates and related attributes like year, quarter, and month.

Example: If you don’t have a date table, you can create one using DAX:

DateTable = CALENDARAUTO()

This function generates a date table with a single column named Date, containing all the dates from your data model.

Measures

Measures are calculations that are performed on the fly based on the current context. They are used in reporting and analysis, but unlike calculated columns, they don’t get materialized in your data model.

Example:

Total Sales = SUM(Sales[Amount])

This measure calculates the total sales amount and will be recalculated whenever the filter context changes, like when a user interacts with a slicer.

Filter Manipulation

DAX provides functions to manipulate the filter context, allowing for powerful and dynamic calculations.

Example:

Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

This measure uses CALCULATE to modify the filter context to the same period last year, allowing you to compare this year’s sales to last year’s.

Time Intelligence

Time intelligence functions in DAX are used to create calculations that understand your data’s time aspects, like days, months, and quarters. This is crucial for performing calculations over time, like comparing sales month-over-month or year-over-year.

Example:

Sales YoY Growth = <br>    DIVIDE(<br>        [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])),<br>        CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))<br>    )

This measure calculates the year-over-year growth of sales by comparing this year’s sales to last year’s sales.

By exploring these concepts in depth and implementing the examples provided, you’ll be well-equipped to harness the full potential of DAX in your Power BI reports, leading to more insightful and dynamic data analysis.

Common DAX Functions

Below is a list of some of the most common DAX formulas used in Power BI for various data analysis tasks:

Aggregation Functions

SUM: Adds all the numbers in a column.

Total Sales = SUM(Sales[Amount])

AVERAGE: Calculates the average of the numbers in a column.

Average Sales = AVERAGE(Sales[Amount])

MIN: Returns the smallest numeric value in a column.

Minimum Sales = MIN(Sales[Amount])

MAX: Returns the largest numeric value in a column.

Maximum Sales = MAX(Sales[Amount])

Time Intelligence Functions

DATEADD: Moves a date forward or backward by a specified number of intervals.

Sales Last Month = CALCULATE(SUM(Sales[Amount]), DATEADD(‘Date'[Date], -1, MONTH))

DATESYTD: Returns a table of dates for the year to date.

Sales YTD = CALCULATE(SUM(Sales[Amount]), DATESYTD(‘Date'[Date]))

SAMEPERIODLASTYEAR: Returns a set of dates shifted one year back.

Sales Last Year = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(‘Date'[Date]))

Filter Functions

FILTER: Returns a table that represents a subset of another table or expression.

High Value Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > 1000))

ALL: Removes all filters from a table or column.

Total Sales All Time = CALCULATE(SUM(Sales[Amount]), ALL(‘Date’))

RELATED: Retrieves a value from another table that is related to the current table.

Product Name = RELATED(Product[ProductName])

Information Functions

ISBLANK: Determines whether a value is blank, and returns TRUE or FALSE.

Is Amount Blank = ISBLANK(Sales[Amount])

CONTAINS: Returns TRUE if values for all referred columns exist, otherwise, it returns FALSE.

Has Product Sales = CONTAINS(Sales, Sales[ProductID], “Product123”)

Logical Functions

IF: Checks a condition and returns one value if TRUE, and another value if FALSE.

Bonus Eligible = IF(Sales[Amount] > 1000, “Yes”, “No”)

SWITCH: Evaluates an expression against a list of values and returns one of multiple possible result expressions.

Sales Category = SWITCH(TRUE(),
Sales[Amount] < 500, “Low”,
Sales[Amount] < 1000, “Medium”, “High”)

Text Functions

CONCATENATE: Joins two text strings into one text string.

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

FORMAT: Converts a value to text in the specified number format.

Formatted Date = FORMAT(‘Date'[Date], “MM/DD/YYYY”)

These DAX formulas are essential for various data transformation, aggregation, and analysis tasks in Power BI. They can be combined and nested to create more complex calculations as needed.

Frequently Asked Questions Related to DAX