Measures vs. calculated columns in DAX and Power BI
Вставка
- Опубліковано 2 вер 2020
- What are the differences between measures and calculated columns in DAX and Power BI? All the answers in this short video. Do you want to know more?
Start with Introducing to DAX free course: www.sqlbi.com/p/introducing-d...
Go deeper with Mastering DAX: www.sqlbi.com/p/mastering-dax...
Complete guide on how to learn DAX: www.sqlbi.com/guides/dax/?aff=yt - Наука та технологія
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
Excellent... I am now getting it and ready to learn DAX on a serious note....
Excellent video. Very clear for me. Thanks
God bless you for all the useful Tips and Tricks you are teaching to us.
Alberto sei un grande! Best explanation so far!
Very good and direct to the point. Thanks for this video.
Beautiful, this was exactly what I needed clarified! 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
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
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
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
2. Filter context is used in measure, not calculated column.
Thanks for explaining the differences.
Very nice and able to understand now between computed and measure, well done
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 Clear Explanation... Thank you...
Thanks a lot!, a really good explanation.
Great Explanation!!
Great example.. many thanks
Good information
Amazing information
Where I can find more scenarios on DAX to master it
Can you guide me in this.
OMG, this is 1 + 1 = 2 finally well explained...
May I call you professor? Thank you professor Alberto for explaining the differences.
I’ve learned from experience to avoid calculated columns for large fact tables, especially with partitions.
Can you explain why?
I really was hoping that you would show how to turn that calculated column code into a measure
Very useful. Thank you.
I'm somewhat distracted with the accent but nice explanation. No doubt that he is the pioneer of DAX.
For some reason measures have always eluded me. This has helped, but I think I still need some practice ^_^
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
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/
create video, leant alot about the differences. thanks
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.
Yes, a measure referenced in a calculated column will get the context transition from row context to filter context.
thank you. Exceptional.
Clear explaination
Thanks!
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?
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
Awesome logic 😊
wow.........am in love now DAX
How many minutes in one mile
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.
I don't understand your description, please can you try to clarify your question?
@@SQLBI Sorry for that, I was justifying the reason for seeing different values in the visual.. even though same values appear in Calculated column..
@@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.
Thank you for your video. I am curious if you have Italian accent. 🙂
My question - off the back of this video, why would you use a calculated column instead of pre-calculating this in PowerQuery?
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.
Thanks
I had a question in the interview: where is better to use a calculated column instead of measure and I stucked up. Please help🙏
It depends! But a calc column is necessary only when you use the result as a groupby or filter column.
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
See this: www.daxpatterns.com/events-in-progress/
@@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 )
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/
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?
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!
The GOAT
Calculated tables:
Take up space
Created using operators.
Measures:
No take space
Use DAX
I think this needs updating 2:14 you talk about not being able to slice by a measure, I think this is now possible. 😀
You can apply a filter on a visual based on a measure only in the filter pane, but you cannot group by a measure.
@@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.
The first two paragraphs are certainly correct. I don't completely understand the meaning of the last one. Can you clarify?
@@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 :-)
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!
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.
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]
@@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.
Better explalin: imposible
DAX A DAX A DAX A AMORE MIO
I want to eat some pizza!
"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.
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.
latino speaking english is amusing xD
Not a good explanation
Unclear
Salute