PowerBI : Create Model Calculations Using DAX - ITU Online

Your Last Chance for Lifetime Learning!  Elevate your skills forever with our All-Access Lifetime Training. 
Only $249! Our Lowest Price Ever!


PowerBI : Create Model Calculations using DAX

PowerBI : Create Model Calculations using DAX

Connect Power BI to Azure SQL DB

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.


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.


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

PowerBI : Create Model Calculations using DAX

Data Analyst Career Path

Elevate your career with our Data Analyst Training Series. Master SQL, Excel, Power BI, and big data analytics to become a proficient Data Analyst. Ideal for aspiring analysts and professionals seeking to deepen their data skills in a practical, real-world context.

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:

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


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.


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.


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.


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.

PowerBI : Create Model Calculations using DAX

Lock In Our Lowest Price Ever For Only $14.99 Monthly Access

Your career in information technology last for years.  Technology changes rapidly.  An ITU Online IT Training subscription offers you flexible and affordable IT training.  With our IT training at your fingertips, your career opportunities are never ending as you grow your skills.

Plus, start today and get 10 free days with no obligation.

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.

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

MIN: Returns the smallest numeric value in a column.

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.

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

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

Filter Functions

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

ALL: Removes all filters from a table or column.

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

Information Functions

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

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

Logical Functions

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

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

Text Functions

CONCATENATE: Joins two text strings into one text string.

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

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

What is DAX and how does it differ from regular Excel formulas?

DAX, or Data Analysis Expressions, is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. While it’s similar to Excel formulas, DAX is specifically designed for data modeling and reporting in Power BI, SQL Server Analysis Services, and Power Pivot in Excel. DAX formulas are capable of performing more advanced calculations and queries on data models compared to standard Excel formulas.

When should I use a calculated column, and when should I use a measure?

A calculated column is a column that you add to an existing table in the data model, and it calculates values for each row. Use calculated columns when you need to filter or calculate results based on row-level calculations and when the values don’t change often. On the other hand, a measure is a calculation that is performed on the data in your model and is recalculated as filters are applied to the report. Use measures for calculations that need to aggregate or summarize data, such as sums, averages, minimums, or maximums, and especially when the values need to dynamically change based on the report’s context.

Can I use DAX to filter data in Power BI?

Yes, DAX can be used to create complex filtering conditions on reports and visualizations. DAX includes a variety of functions that can filter data based on the conditions you specify. The CALCULATE function, for instance, can modify the filter context of a calculation, which makes it one of the most powerful and frequently used DAX functions for filtering data.

How do time intelligence functions in DAX work?

Time intelligence functions in DAX allow you to manipulate data using time periods, including days, months, quarters, and years. These functions enable you to perform time-based calculations like comparing sales from this month to the previous month or the same month last year. Common time intelligence functions include DATEADD, DATESYTD, SAMEPERIODLASTYEAR, and many others. It’s important to have a proper date table to fully utilize time intelligence functions.

What is context transition in DAX and why is it important?

Context transition in DAX refers to the transformation of row contexts into an equivalent filter context. This is most noticeable when using row context functions like EARLIER or FILTER inside a measure. When you use a row-level function inside a measure (which operates in a filter context), DAX automatically applies the values of the current row in the row context as a filter to the measure calculation. Understanding context transition is crucial for writing accurate and efficient DAX formulas, especially when working with complex data models or calculations that depend on the row context.

Leave a Comment

Your email address will not be published. Required fields are marked *

Get Notified When
We Publish New Blogs

More Posts

Project Procurement Management

Understanding Project Procurement Management

Project procurement management is often underestimated in its complexity and importance. Here’s a breakdown of the essential components and practices in project procurement management, structured

Python Exception Handling

Python Exception Handling

Mastering Python Exception Handling : A Practical Guide This blog post delves into one of the most crucial aspects of Python Exception Handling. In the

Unlock the full potential of your IT career with ITU Online’s comprehensive online training subscriptions. Our expert-led courses will help you stay ahead of the curve in today’s fast-paced tech industry.

Sign Up For All Access

You Might Be Interested In These Popular IT Training Career Paths

Web Designer Career Path

Web Designer Career Path

Explore the theoretical foundations and practical applications of web design to craft engaging and functional websites.
Total Hours
33  Training Hours
171 On-demand Videos


Add To Cart
Kubernetes Certification

Kubernetes Certification: The Ultimate Certification and Career Advancement Series

Enroll now to elevate your cloud skills and earn your Kubernetes certifications.
Total Hours
11  Training Hours
207 On-demand Videos


Add To Cart
Information Security Specialist

Entry Level Information Security Specialist Career Path

Jumpstart your cybersecurity career with our training series, designed for aspiring entry-level Information Security Specialists.
Total Hours
109  Training Hours
502 On-demand Videos


Add To Cart