column vs measure

[sshweky]

Can someone explain me the practical difference between a new column & new measure?

[WillT · Accepted solution]

Put simply:

Calculated columns (and tables) are:

- Evaluated for each row in your table, immediately after you hit 'Enter' to complete the formula

- Saved back into the model so take up space

Calculated Measures are:

- Evaluated when you use it in a visual, when the visual is rendered

- Not saved anywhere (well, actually there's a cache in the report layer but it's not part of the file when you hit Save)

Generally, measures are more useful, but the trade-offs are the performance hit (report runtime vs. pre-processed), storage space, and the type of expressions you can use. For example calculated columns are often used when you want to filter on the result rather than just as a calculated result.

There's a very comprehensive explanation by the excellent Marco Russo here:

https://projectbotticelli.com/knowledge/dax-calculated-columns-vs-measures-video-tutorial

Hope that helps!

[djnww · Accepted solution]

Hi @sshweky,

Correct me if I am wrong, but when you say 'practicality' I have a feeling that you are trying to understand when you should use a Column VS Measure. It's a confusing concept. Using the wrong one will sometimes lead to unexpected results.

As a rule of thumb:

1. If you intend to apply any aggregation to a value (ie. sum, average etc) that shows a result, then stick to a Measure.

2. If you intend to use the value for formatting purposes (ie. slicers, roe/column category) then add a Column.

Some people will say, 'But you can add calculations to a column' etc. True, but you will likely run into problems during aggregation where it won't be behaving the way you expect it to. This is not a fault with Power BI. It is the same with any BI tool.

I just found an article that discusses what I'm talking about in great detail:

http://www.powerpivotpro.com/2013/02/when-to-use-measures-vs-calc-columns/

Good Luck.

[WillT · Accepted solution]

Put simply:

Calculated columns (and tables) are:

- Evaluated for each row in your table, immediately after you hit 'Enter' to complete the formula

- Saved back into the model so take up space

Calculated Measures are:

- Evaluated when you use it in a visual, when the visual is rendered

- Not saved anywhere (well, actually there's a cache in the report layer but it's not part of the file when you hit Save)

Generally, measures are more useful, but the trade-offs are the performance hit (report runtime vs. pre-processed), storage space, and the type of expressions you can use. For example calculated columns are often used when you want to filter on the result rather than just as a calculated result.

There's a very comprehensive explanation by the excellent Marco Russo here:

https://projectbotticelli.com/knowledge/dax-calculated-columns-vs-measures-video-tutorial

Hope that helps!

[zlokesh]

I did not get below point. 

" Not saved anywhere (well, actually there's a cache in the report layer but it's not part of the file when you hit Save)"

Does it mean , if i created measure to calculate percentage of total sales and i close my Power BI afetr save. Next time i will loss that measure?

[Anonymous]

calculated columns:

you pre-made 10 chicken sandwich with 10 chicken patty and 10 buns, and store it on the counter for later use. (saved in powerbi file, take up file space)

measures:

when customer demanded, you start making 10 chicken sandwich by grabing 10 patty from the fridge and 10 buns from the table.(take little space, but when measure runs, it takes time, the more measure you stack on top of each other, the longer the calculation.)

[Anonymous]

Thanks for this answer. Can you elaborate on what you mean about using a calculated column when you need to filter on the result. Would that apply to this issue I had recently?

http://community.powerbi.com/t5/Desktop/Problem-with-visual-level-filtering-on-card/m-p/31635#M10917

[itchyeyeballs]

Thumbs up Will, working on boxing day and still giving great answers 😉