For newbies to Power BI, an important concept to understand are DAX (Data Analysis Expressions) and how they are used to make Measures and Columns.
Whether you're building dashboards or simply exploring data trends, understanding DAX is essential for creating custom calculations that go beyond basic visuals.
In this post I will be breaking down the basics of DAX and show how to use it to create measures and calculated columns in Power BI.
What is Dax?
DAX is a formula language used in Power BI to define custom calculations. It is similar to Excel formulas but designed specifically for working with data models.
DAX helps:
- Create new columns based on existing data
- Define measures for aggregations like sums, averages, or more complex calculations.
- Build dynamic and context-aware formulas
What is a Measure?
A measure is a dynamic calculation that is evaluated based on the filter context, therefore it changes depending on slicers, filters, and visuals in the report. Common use cases would be aggregations like totals, averages or percentages.
To do this you have to right click on the table you want to add the column to and select new column and enter the DAX formula you want.
This is the symbol you should see next to the new measure you have created:
For example if we wanted to create a Profit Ratio measure we would use the following DAX calculation remembering to aggregate first:
What is a Calculated Column?
A calculated Column is a new column added to a table using a DAX formula. It is calculated row-by-row, meaning each row gets its own value based on the formula. A common use case would be deriving values such as profit.
To do this you have to right click on the table you want to add the column to and select new column and enter the DAX formula you want.
This is the symbol you should see next to the new column you have created:
For example, if we wanted a Profit Ratio at a row level we would use the following DAX calculation:
Common DAX Functions for Beginners
Here are a few useful DAX functions that are good to remember:
SUM(): Adds up values.AVERAGE(): Calculates the mean.COUNT(): Counts rows.IF(): Conditional logic.CALCULATE(): Changes the context of a calculation.RELATED(): Pulls data from related tables.