cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sshweky Member
Member

column vs measure

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator WillT
Moderator

Re: column vs measure

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 Established Member
Established Member

Re: column vs measure

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.

5 REPLIES 5
Moderator WillT
Moderator

Re: column vs measure

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!

itchyeyeballs Established Member
Established Member

Re: column vs measure

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

Re: column vs measure

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.

caseyh Member
Member

Re: column vs measure

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

zlokesh Member
Member

Re: column vs measure

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?