Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
amien
Helper IV
Helper IV

Key from 2 combines fields : Difference between Add Calculated column or add column in query editor

I want to combine two columns within the same table. This new column can be used to link to a different table.

There are two options here:

 

1. Create a calculated column using DAX in the PowerBI Desktop

2. Add a Custom Column in the Query Editor.

 

Both will work. But what is the difference and i what cases should i use which option?

1 ACCEPTED SOLUTION
TaylorClark
Power BI Team
Power BI Team

 

Here are the basic differences:

1. Evaluation time: Query columns are evaluated at query (data refresh) time. Calculated columns are evaluated at data model processing time.

2. Language used: Query columns use a functional language known as M (Quick Reference). Calculated columns use DAX (Quick Reference), the same language used in Measures.

3. What they can reference: Query columns can reference other queries, steps in the current query, and custom functions. Calculated columns can reference data loaded to the data model.

 

When should you use each?

There aren't any hard and fast rules about when to use one option over the other. It's usually case-specific. As you start to learn DAX and M, you'll realize their overlap is actually smaller than you might have expected -- they're designed for solving different problems. M is much more flexible and "general purpose", whereas DAX is designed specifically to perform analytics on tables. As a result, here's my advice:

 

1. Use whichever feels more comfortable to you. If you can easily write the expression in the query with M, go ahead. If it's simpler to express it in DAX, use it. I expect the languages will usually make the decision for you 🙂

2. Adding a query column requires the query and any referencing queries to be refreshed. Depending on the size of your dataset, this may be prohibitive and you may lean towards calculated columns for practicality.

 

Personally, I use both gratuitously. If I'm planning ahead and know I need the column at the time that I'm writing the query, I add it in the query editor. A small benefit here is that the column is included if you decide to copy your query into another PBIX file. If I realize I need something as I'm building measures and visuals, I probably add it as a calculated column.

 

Hope this helps! Let me know if you have any other questions

View solution in original post

2 REPLIES 2
TaylorClark
Power BI Team
Power BI Team

 

Here are the basic differences:

1. Evaluation time: Query columns are evaluated at query (data refresh) time. Calculated columns are evaluated at data model processing time.

2. Language used: Query columns use a functional language known as M (Quick Reference). Calculated columns use DAX (Quick Reference), the same language used in Measures.

3. What they can reference: Query columns can reference other queries, steps in the current query, and custom functions. Calculated columns can reference data loaded to the data model.

 

When should you use each?

There aren't any hard and fast rules about when to use one option over the other. It's usually case-specific. As you start to learn DAX and M, you'll realize their overlap is actually smaller than you might have expected -- they're designed for solving different problems. M is much more flexible and "general purpose", whereas DAX is designed specifically to perform analytics on tables. As a result, here's my advice:

 

1. Use whichever feels more comfortable to you. If you can easily write the expression in the query with M, go ahead. If it's simpler to express it in DAX, use it. I expect the languages will usually make the decision for you 🙂

2. Adding a query column requires the query and any referencing queries to be refreshed. Depending on the size of your dataset, this may be prohibitive and you may lean towards calculated columns for practicality.

 

Personally, I use both gratuitously. If I'm planning ahead and know I need the column at the time that I'm writing the query, I add it in the query editor. A small benefit here is that the column is included if you decide to copy your query into another PBIX file. If I realize I need something as I'm building measures and visuals, I probably add it as a calculated column.

 

Hope this helps! Let me know if you have any other questions

Hi @TaylorClark,

Could you please share more about data refresh time and data model processing time, cause This is new concept to me and would like to be aware them.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors