Power BI gives developers the ability to add New Columns or New Measures. On the surface, these two features may seem interchangeable, but there are some key differences you should be aware of to choose the right method for your dashboard.
Column and Measures serve different purposes in Power BI, and you may have even come across not being able to choose a specific column or field that you expected when attempting to create a new measure.
We’ll explain the differences between the two, and when to use which one.
Let’s jump in!
Table of Contents
Creating New Columns with Power Query vs DAX
Power BI can create New Columns using DAX formulas or using the Power Query Editor. In addition to using different formula languages, DAX vs Power Query M, the two methods are calculated at different times. Power Query columns are created during a data refresh. DAX formulas are calculated when users interact with reports.
Because of the difference of when the calculations occur there can be important implications for report performance. For large datasets, we recommend using Power Query to create new columns to pre-calculate columns for a better end-user experience.
For small datasets, we often use the New Column feature to save time and avoid having to launch the Power Query Editor.
Creating New Measures and New Columns in Power BI
New Measures and New Columns can be created in Power BI using the Table Tools menu from the Power BI Ribbon. The Table Tools ribbon section is available when selecting a table or existing field from the data panel on the right side of the Report View.
After selecting New Measure or New Column, a formula bar will appear below the Power BI ribbon that will let you type in formulas.
Both measures and columns will start with the new measure or column name, followed by an equal sign, which is followed by a DAX (Data Analysis Expression) formula.
After completing a formula, you can click the check mark to the left of the formula to accept it.
Power BI Measures Explained
Power BI measures are calculated fields that aggregate data dynamically based on slicer and filter context. For example, measures for summing revenue, counting customers, or averaging sales would recalculate as you interact with reports.
Measures use DAX formulas to aggregate data. The aggregations are dynamic, in that they re-calculate as report viewers choose to expand data, drill up, drill down, and explore it. Without being a dynamic calculation, the reports would be much more difficult to setup and would not provide the level of flexibility that Power BI is known for.
Power BI Columns Explained
Columns in Power BI work largely the same as they would in an Excel spreadsheet. Text or numbers are stored in columns and rows, and formulas are used to add, subtract, concatenate etc. Formulas reference column names and are applied to all rows in a dataset automatically.
While DAX dynamically calculates New Column values when using DAX formulas, report viewers are not typically exploring all rows of a dataset one row at a time, but the calculations that occur in a New Column DAX formula can feed into Measures which would then require multiple levels of calculation to occur before being able to return the correct result to a report viewer.
Power BI Measures vs Columns Explained
The primary difference between measures and columns are what the calculations are based off of. If we use a simple example of addition, a measure will add together values stored in rows, while columns will add values together from other columns.
The following example highlights the difference between Columns and Measures. The orange subtotal is a Measure, it aggregates or adds the rows above it. The yellow Column adds the Sales Amount and Discount Amount column to create a new column.
In this example, a new column adds other columns together, while a measure adds rows together.
For more information on optimizing Power BI reports for performance when working with large datasets, or general best practices we recommend checking on the following article from Microsoft Learn: Optimization guide for Power BI – Power BI | Microsoft Learn
When to Use Columns vs Measures in Power BI
Columns and measures are both necessary for creating Power BI reports, but they serve different purposes. Here are some highlights to explain some use cases for each one, and when to use one over another.
Power BI Columns
Create New Columns in Power BI when calculates need to be performed one row at a time. Many times, the columns that are being worked on would make no sense to aggregate. For example, you would not aggregate text columns together or a column full of date by summing or averaging them.
- Static Calculations – calculations that are unlikely to change based on how they are being filtered or explored by a report viewer.
- Row-Level Calculations – data that needs to be calculated one row at a time.
- Quick Calculations – sometimes you just need to add a couple of columns together or perform some other simple task on a smaller dataset.
For data with large numbers of rows, we also recommend using the Power Query Editor to offload some of the calculations in case they’re used in subsequent measures. However, it’s worth point out that columns created using DAX formulas will take up less space in your semantic model which can be beneficial if you’re running into capacity limitations.
Power BI Measures
Measures in some ways are the default calculation method for many Power BI reports. So much of what is presented needs to be setup in a way that it will re-calculate based on a user’s interaction with the report. For example, sales values will be sliced and diced different ways, across various time periods, product categories, and regularly interacted with.
- Dynamic Calculations – Use when you need dynamic calculations that change based on filters or slicers.
- Aggregate Data – Averages, sums, mins, max, counts are all aggregation functions requiring Measures.
- Advanced Calculations – DAX measures let you modify filtering context, with calculate functions and also let you reference fields across multiple tables.
- Visuals – Measures are most commonly used as the field values for visuals because they support the ability to automatically re-calculate as a report viewer modifies the filters and slicers on page.
When considering the difference between Power BI measures and columns it’s important to keep in mind that the two are not mutually exclusive. While each one has its own use case, there are plenty of times that it’s required to create a new column as a step that becomes part of a new measure as a second step.
Measures can be used to aggregate the rows that make up a column.
When deciding on whether to create a new column or a new measure think about how the data is going to be used in the report. Measures are used whenever data needs to be aggregated or re-calculated as a report viewer adjusts filters and slicers while columns are often created when data needs to be calculated one row at a time.
Don’t forget to consider the implications of creating new columns in Power Query vs creating new columns in DAX. There are key performance differences between each method, and while DAX could take longer to execute, it also requires less storage space because the column values are calculated on demand instead of being pre-calculated as part of a full dataset.