Just found this video and what an eye opener for beginners like me. I always try to create new measure than add a calculated column. This is indeed helpful! Thank you!

Wow this is the best explanation I have seen about this difference

God bless you for all the useful Tips and Tricks you are teaching to us.

Excellent... I am now getting it and ready to learn DAX on a serious note....

Very good and direct to the point. Thanks for this video.

Alberto sei un grande! Best explanation so far!

Beautiful, this was exactly what I needed clarified! Thank you!

Thanks for explaining the differences.

Thanks a lot!, a really good explanation.

Very Clear Explanation... Thank you...

4 points summary 1. If you want to slice and dice by a column, use a calculated column 2. If you want to use a filter context, use a calculated column 3. If you want to consume less data for storage, use a measure 4. Prefer measures over a calculated column wherever possible

@cheetah9839

2 жыл бұрын

A measure is evaluated in the context of the cell evaluated in a report or in a DAX query, whereas a calculated column is computed at the row level within the table it belongs to

@cheetah9839

2 жыл бұрын

Calculated columns are computed during the database processing and then stored in the model. Measures are not stored in the model as they are computed at query time and do not use memory

@cheetah9839

2 жыл бұрын

If you need to operate on aggregate values you must create measures,If you need to operate on a row-by-row basis, you must create calculated columns

@haiyennguyen9067

Жыл бұрын

2. Filter context is used in measure, not calculated column.

Very nice and able to understand now between computed and measure, well done

Great Explanation!!

Amazing information Where I can find more scenarios on DAX to master it Can you guide me in this.

Great example.. many thanks

Ciao friends --> got the like! also who couldn't possibly like wonderfully useful content form a person with the last name of Ferrari! amazing video, thank you

Very useful. Thank you.

May I call you professor? Thank you professor Alberto for explaining the differences.

Hi, thanks for the explanation. Can you please tell me how did you enable the tabs for Measure tools/column tools? Or is it a third party add-in

@SQLBI

3 жыл бұрын

When you install the latest version of these tools (DAX Studio, Tabular Editor, Analyze in Excel for Power BI Desktop, ALM Toolkit) that tab appears automatically. Tool links available at www.sqlbi.com/tools/

I’ve learned from experience to avoid calculated columns for large fact tables, especially with partitions.

@Bexckers

Жыл бұрын

Can you explain why?

create video, leant alot about the differences. thanks

For some reason measures have always eluded me. This has helped, but I think I still need some practice ^_^

I really was hoping that you would show how to turn that calculated column code into a measure

Thank you for the explanation. Can you use a measure to create a calculated column? I am trying to get a date value from slicer, and storing the value in a measure. I want to use that date value to create a new column. But I get the same value for all the rows.

@SQLBI

3 жыл бұрын

Yes, a measure referenced in a calculated column will get the context transition from row context to filter context.

Awesome logic 😊

thank you. Exceptional.

Clear explaination

Good information

Could I ask another question, I've just bought your book The Definitive Guide to Dax (2nd Ed) and I'm reading about leveraging the performance between calculated columns and Measures, If you separate your Dataset from your visual model, would that not improve performance if you used Calculated columns vers Measures on your visuals?

@SQLBI

Жыл бұрын

From a performance point of view, it's better to keep calculated columns on the same model, not on a remote model. However, oftentimes calculated columns are not required for performance and they just increase model size and slow down refresh. Do your own evaluation!

Excellent

wow.........am in love now DAX

Thank you for your video. I am curious if you have Italian accent. 🙂

I'm somewhat distracted with the accent but nice explanation. No doubt that he is the pioneer of DAX.

OMG, this is 1 + 1 = 2 finally well explained...

Thanks!

Thanks

Thanks for the content, and just for clarification, would like to know this.. The line amount calculated column had same values in the table where as it was still getting huge different incorrect values under the filter context when used in the visual.. I Believe that this is because of the number of rows that gets evaluated in the filter context giving different wrong results, otherwise if rows are equally distributed then we would see same wrong result in the visual.. can you please confirm.

@SQLBI

3 жыл бұрын

I don't understand your description, please can you try to clarify your question?

@Bharath_PBI

3 жыл бұрын

@@SQLBI Sorry for that, I was justifying the reason for seeing different values in the visual.. even though same values appear in Calculated column..

@DJ_Q

2 жыл бұрын

@@Bharath_PBI You're seeing different values in the visual because it's being filtered by brand. In each row, it's aggregating the grand total by the total number of rows that contain each brand. So for example, if the number 10 is repeated in the calculated column, and a product of brand X appears 9 times and a product of brand y appears 10 times and there are actually 1000 rows, in this example, brand X will show 90 (9 * 10) and brand y, 100 (10 * 10) whereas the grand total of all brands will be 10,000 (1000 rows with 10 repeated) I hope this explanation helps. I'm only a year and a half into this so I'm open to correction.

My question - off the back of this video, why would you use a calculated column instead of pre-calculating this in PowerQuery?

@SQLBI

3 жыл бұрын

You're right, you should use calculated columns when Power Query would be too expensive - for example computing the value based on the content of other tables could be very slow in Power Query.

I had a question in the interview: where is better to use a calculated column instead of measure and I stucked up. Please help🙏

@SQLBI

Жыл бұрын

It depends! But a calc column is necessary only when you use the result as a groupby or filter column.

How many minutes in one mile

i have a customer contract table with start and end date. and i want to sum the sales from the sales fact table ( bêtween the start -end date) for each contract, do you have any suggestion , tried many articles but still not yet have soltuoons

@SQLBI

2 жыл бұрын

See this: www.daxpatterns.com/events-in-progress/

@luuminhvuong

2 жыл бұрын

@@SQLBI but the example given is only 1 fact table so might be easier to do , here I have a 2 table separately : sales orders table , and the other is the "contract table" ( which is like a campaign with start and end date , wanna see the sales during each campaign )

@SQLBI

2 жыл бұрын

It really depends on many details about the report you want to produce. If there are no multiple active campaign for the same date, you should apply the active campaign to the transaction, if there could be multiple campaigns active for a single transaction you should keep the calculation at runtime. There are several discussions about similar problems in our book, even though not exactly the one you asked for: www.sqlbi.com/books/analyzing-data-with-microsoft-power-bi-and-power-pivot-for-excel/

@luuminhvuong

2 жыл бұрын

I'm going to buy this book and learn more, don't know if it helps since the problem not quite the same as you said. Between this book and the defenitive guide to Dax ? which would u recommedn more?

@SQLBI

2 жыл бұрын

The Definitive Guide to DAX is about the DAX language. Analyzing Data with Microsoft Power BI and Power Pivot for Excel is about data modeling. They cover different topics, you need knowledge on both areas. If you already know dimensional modeling (Kimball's methodology) then you only need DAX, but the question you raised is a typical data modeling question!

I think this needs updating 2:14 you talk about not being able to slice by a measure, I think this is now possible. 😀

@SQLBI

Жыл бұрын

You can apply a filter on a visual based on a measure only in the filter pane, but you cannot group by a measure.

@matheww9944

Жыл бұрын

@@SQLBI Many thanks for the clarification

Hi Alberto - my two cents: A measure is a closed unit of aggregation that returns a scalar result. The input of a measure is the current evaluation context. No row context exists prior to executing a measure. A calculated column is the result of a DAX expression. The input of a calculated column is a single row row-context. No filter context exists prior to executing the calculated column. No number of CALCULATEs can make a DAX expression into a measure. Only the DAX engine can do that. It ‘s a closed deal so to speak.

@SQLBI

3 жыл бұрын

The first two paragraphs are certainly correct. I don't completely understand the meaning of the last one. Can you clarify?

@jessamhan

3 жыл бұрын

@@SQLBI What I mean is that there is a notion circulating that if you embed a DAX expression in two CALCULATEs that is the equivalent of a measure. CALCULATE(CALCULATE(expression, filter_1, … filter_n)) The justification for this would be that no row contexts are visible to neither the expression nor the filter arguments. If this was correct you should be able to reference a variable declared outside a measure from within a measure. So if you have a calculated column with this DAX expression: VAR aVariable = something RETURN CALCULATE(CALCULATE(expression * aVariable , filter_1, … filter_n)) and you actually could save CALCULATE(CALCULATE(expression * aVariable , filter_1, … filter_n)) as a measure you should be able to do this: VAR aVariable = something RETURN [expressionAsAMeasure] Obviously you can't. A DAX expression becomes a measure when you save the expression as a measure from a client tool that adds the appropriate Meta data AND the DAX expression is isolated from the outside world except for the current evaluation context. Measure = { measure metadata + evaluation context + DAX expression } So a measure is a closed deal (a Closure?). I probably shouldn't have written the last paragraph in my original comment :-)

@SQLBI

3 жыл бұрын

Yes and no. You can replace any measure with CALCULATE ( ) However, you cannot replace CALCULATE ( ) with [m] where m := , because the visibility of the variables is different. This has nothing to do with the filter context and the row context, but just with the scope of the variables, that is always local to an expression. I hope it helps!

The GOAT

Calculated tables: Take up space Created using operators. Measures: No take space Use DAX

Better explalin: imposible

I really don't see the point of measures. I would always want to see row by row calculations, and if I wanted to see a sum the pivot tables would show that anyway.

@SQLBI

2 жыл бұрын

For example, this calculation should be in a measure, otherwise it cannot be aggregated correctly in a grand total if calculated row by row: Margin % = [Margin] / [Sales Amount]

@jacobmurray6731

2 жыл бұрын

@@SQLBI I use Power Pivot exclusively for outputting tables and pivot tables to the workbook and so far cannot find a use for measures. Maybe this is different in Power BI reports.

DAX A DAX A DAX A AMORE MIO

I want to eat some pizza!

latino speaking english is amusing xD

"you can not use the same code for a Measure and a Column" !! This is why DAX and Power Query didn't turn into a success! No matter how hard MS pushes. Why should a learn 2 langs and techniques for one task ??? Microsoft's failure to see obvious things. Otherwise this is the best, most relevant and robust tool for any Big Data/Data Science.

@human_hope

Жыл бұрын

Well that's frustrating but the fact that power query can't store measures there had to be probably different languages. Also the syntax for measures is quite different from columns which is another annoying thing.

Not a good explanation

Unclear

Salute