Welcome to our post all about how to know when you should make a new column OR a new measure using DAX in Power BI, because sometimes it can be confusing about what is better.

The answer, like most things, is “it depends”.

If you want to use the same Power BI file I use in the below video and written notes, here’s the pbix file to download. You can also click the graphic below to get it.

Video Instructions:

Written Instructions:

We’ll use a simple Power BI file to show the difference between columns and measures.

Here we have a file with a very small dataset with different vehicles in it. I’ve created a table so we can see the rows in our data.

Over in our Data Pane, you can see a calculated column and a measure, both made using DAX. They do the exact same thing, using the SWITCH function to group different vehicles into a Type Category… either Ground, Water, or Air vehicle.

Want to learn the SWITCH function? Here’s the post about it.

The DAX for each of these is almost identical and we can see how they work if we add them both to our table:

They are both grouping our vehicles the same way. That’s good.

They both do the job fine if you were adding them to a table like this… so which is better?

In THIS scenario, a measure is better. It does the same thing as a column (it basically runs a calculation for each row of data in your table), but it does it a bit more efficiently.

A measure doesn’t exist in the data, so to speak. It runs when you need it (when you load up a visual table like this one, or use it to figure something out).

A column, on the other hand, becomes part of your data. If we open up the Table View of this report, we can see there’s a column for our column, but not for our measure.

So, creating a column adds to the size of your dataset. It’s not a huge deal here, but if you have a ton of data, like 100s of 1000s or millions of data rows, you are adding so much data to the dataset you are potentially slowing your report down, because Power BI will crunch through all that data as someone interacts with your report.

A DAX measure doesn’t live in your data columns. It runs when it’s needed.

This does NOT mean a measure is always better though.

Measures come up short if you need to use them in a visual. If we want to make a bar chart of the types of vehicles in our data, Power BI will NOT let you use a measure to categorize data. You can bring a measure to the Y-Axis of Bar Chart as many times as you want, but it won’t accept it.

Our column though, can be added easily.

That’s it really. You want a measure for most of your calculations, as they don’t add data to your dataset (and slow down your Power BI report), BUT measures can’t do everything Columns can.

Take care everyone,

Joe.

Like these posts but need more formal (but still engaging and fun) training in Power BI? Contact me, Joe Travers or at joe@traversdata.com. I got you.

Contact Joe | TraversData.com | People-Friendly Power BI | LinkedIn