Power BI DAX(Data Analysis Expressions)
- mailelavarasi
- Sep 2
- 4 min read
Power BI is a business intelligence/ analytics tool by Microsoft that provides interactive visualizations, allowing users to connect to various data sources, transform and clean data, create reports and dashboards, and share insights to support smart decision-making.
Key Features of Power BI
Data Connectivity: Connects to a wide range of data sources, including databases, spreadsheets, cloud services, web pages, etc.
Data Transformation (Power Query): Provides the Power Query Editor for cleaning, shaping, and transforming data before it is used in the reports.
Interactive Visualizations: Enables users to create dynamic charts, graphs, and other interactive visuals to explore.
Report & Dashboard Creation: Users can build interactive reports and dashboards to visualize data and communicate insights to others.
Data Modeling: Allows for the creation of data models to show relationships between different data sources.
Introduction
One of the Microsoft BI tools is the Power BI which is widely used for creating interactive dashboards and reports. DAX (Data Analysis Expressions) in Power BI is a formula language that allows you to create custom calculations, summarize data, create calculated columns and measures, use functions designed for relational data and analyze trends.
DAX Explained
DAX (Data Analysis Expressions) is a set of functions, operators, and formulas used in Power BI, Excel Power Pivot, and Analysis Services. It is designed for working with relational data and large models.
DAX is used to create:
Calculated Columns is a Row-by-row calculations stored in your table.
Measures is a On-the-fly calculations used in visuals.
Tables is a virtual tables used in queries or advanced logic.
A calculated column is a new column that is created inside the data model in Power BI using a DAX formula. Unlike measures (which are calculated only when you slice/filter), calculated columns are computed row by row and stored in your table. We can think it as, If you are adding an extra column to your table, just like in Excel, but instead of typing values, you write a DAX formula.
They are stored in your data model and take up memory.
They are evaluated row by row.
Useful for categorization, grouping, or creating new fields.
DAX query for a calculated column:
We have 2 columns FirstName and LastName and we create a new calculated column called “FullName”
FullName = Employees[FirstName] & " " & Employees[LastName]
A Measure is a calculation you create in Power BI (or other DAX tools) that’s evaluated dynamically based on the filters, slicers, and context of your report. A measure is not stored in the data model. Instead, it is calculated in real time whenever you use it in a visual.
They are dynamic (change with filters & slicers).
They are not stored in the data model (lighter than calculated columns).
Perfect for aggregations: SUM, AVERAGE, COUNT, MAX, MIN, ratios, KPIs, etc.
Think of a measure as a dynamic formula that answers questions like:
What’s the total sales?
What’s the average profit per region?
How many employees do we have right now?
DAX query for a measure:
TotalSales = SUM ( Sales[Amount] )
Now, when you drag TotalSales into a visual:
If you filter by 2024, it shows sales for 2024.
If you filter by California, it shows sales for California.
It shows the grand total, if there are no filters.
That’s the magic of measures, they adapt automatically to context.
In short, A calculated column adds new data to your model. A measure summarizes or calculates from that data when you view it.
DAX Functions
Following are some of the DAX functions and its examples used in the Power BI
1. Aggregations
Aggregate functions calculate values such as sum, average, count, product, minimum, or maximum for all rows in a column or table as defined by the expression.
These are the building blocks of reports:
TotalEmployees = SUM ( 'HR Analytics Data'[EmployeeCount] )
AverageEmployees = AVERAGE ( 'HR Analytics Data'[EmployeeCount] )
EmployeeCount = COUNTROWS ( 'HR Analytics Data' )
2. Logical Functions
Logical functions is an expression to return information about the values or sets in the expression. Logical tests help you classify or filter data. You can also use AND, OR, and NOT for multiple conditions.
ProfitStatus = IF ( 'Sales'[Profit] > 0, "Profit", "Loss" )
3. Time Intelligence
DAX is built for date-based analysis, making time-period comparisons easy:
Sales_YTD = TOTALYTD ( SUM ( 'Sales'[Amount] ), 'Sales'[Date] )
Sales_LastYear = CALCULATE ( SUM ( 'Sales'[Amount] ), SAMEPERIODLASTYEAR ( 'Sales'[Date] ) )
4. Filters and Context
One of the most powerful features of DAX is CALCULATE, which changes the filter context. This lets you analyze specific segments of your data, like high-value transactions or sales by region.
HighValueSales =
CALCULATE (
SUM ( 'Sales'[Amount] ),
FILTER ( 'Sales', 'Sales'[Amount] > 1000 )
)
DAX Queries in Action
Beyond measures and columns, you can write full queries (e.g., in DAX Studio) to explore your data.
EVALUATE
SUMMARIZE (
Sales,
Sales[Region],
"Total Sales", SUM ( Sales[Amount] )
)
Here, SUMMARIZE works like SQL’s GROUP BY, returning sales totals by region.
Best Practices
Use measures instead of calculated columns when possible — they are more efficient. Learn the difference between row context and filter context. Use variables (VAR) to simplify complex expressions.
Example with variables:
ProfitMargin =
VAR Sales = SUM ( 'Sales'[Amount] )
VAR Cost = SUM ( 'Sales'[Cost] )
RETURN DIVIDE ( Sales - Cost, Sales )
Conclusion
DAX is the brain behind Power BI’s visualizations. With it, you can move beyond simple sums and averages to create dynamic insights. Start small with aggregations and IF statements, then explore filters and time intelligence functions. Mastering DAX will turn Power BI from a reporting tool into a true analytics powerhouse.
