Dear Marco, Thanks for sharing your knowledge and expertise with us. Please help me solve one question if I want to find out the comparison of sales of current month v/s last month to find out who my new customers and who are the customers who didn't purchase this month from the sales table.
@@SQLBI Yes, they are listed there but it is not clearly mentioned which are local modifiers & which are global modifiers or different conditions to use in both cases.
Very good summary video indeed,specially order of execution section , thank u so much ....all clear but a request , can we have one visual section with example on order of execution the way you have visually explained in arbitrary shaped filter video ...it will be really helpful for all
Thanks to share all details about CALCULATE func. This is the fist time that a see se sequance o execution and I have a question. Why the evaluation are separeted of application of filters?
Thank you so much for this video. When applying a simple filter, CALCULATETABLE is the same as FILTER? As intermediate step on a calculated table I've had some issues with CALCULATETABLE that disappeared using FILTER
I had an issue using CALCULATETABLE that disappeared when I used FILTER but don't have it in a model I can share, just checking if at least in theory they should behave the same or not.
Hello Alberto, how are you? Great video, as always. Congratulations! I was left with a doubt: you said that every measure has a CALCULATE and also said about the order of evaluation of CALCULATE's arguments. Why in a row context do I get different results using a ready measure than writing CALCULATE directly? Example: DEFINE MEASURE Sales[# Color] = CALCULATE( COUNTROWS(VALUES(Product[Color])), VALUES(Product[Color]) ) EVALUATE ADDCOLUMNS( VALUES(Product[Color]), "@Calculate", CALCULATE ( COUNTROWS ( VALUES ( Product[Color] ) ), VALUES( Product[Color] ) ), "@[Measure]", [# Color] )
Use VAR in your measure or Earlier Function to compare single row and use ALL function to remove filter on the table to keep your denominator same in all the rows.
It performs a context transition and applies a filter context to the entire expression. It is useful in more complex expression, or when you want to remove filters. See examples here: www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/
They have different semantics. You should use CALCULATETABLE to apply filters, whereas FILTER is required whenever you have to filter a table based on the result of a measure (you need a row context that only FILTER can generate).
Great explanation! I nearly understood the whole thing. However, there is still one little confusion around the context in which the explicit filter arguments are EVALUATED....this is after having gone through the steps on dax.guide website and then listening to your explanation here. In dax.guide website, it says that the EVALUATION of explicit filter arguments in CALCULATE, itself happens within the original evaluation context (original row context + original filter context) in Step 1. Then in Step 5 line 5, the evaluation of explicit filter arguments is mentioned again, but this time it says, within original filter context instead of original evaluation context. Then in your explanation here, it seems as though the explicit filter arguments are evaluated by just converting the condition into a table of value/s without any reference to an outer/original context. This created a little confusion for me. To summarize, I guess my query is.....Are the explicit filter arguments in CALCULATE themselves EVALUATED within the original evaluation context, or original filter context, or no outer/original context at all?
I wish it would be explained in power bi itself not a dax studio. For a beginner who is really frustrated with all these counter intuitive concepts adding dax studio with all strange things around is not a good idea.
These videos are related to dax.guide, which is a guide to DAX functions, and they are not intended as a tutorial about the language. Please, follow this guide to move the first steps into DAX - most of the resources for beginners are free, in particular the Introducing DAX uses Power BI Desktop as user interface: www.sqlbi.com/guides/dax/
@@SQLBI Thank You for the answer. I am studying the ultimate Dax book. Well I did learn Photoshop, did learn a lot of 3D graphics which was pretty hard, but DAX…it is so counter intuitive and frustrating for a beginner. A road through hell to get some pretty basic results in terms of mathematics.
![CAMPปลิ้น | รวม Highlight 2024! PART [1/2]](http://i.ytimg.com/vi/6hnvNnvYloU/mqdefault.jpg)
Finally all the secrets of Calculate in one place, Bravo!
I love this man what a great explanation
but I surely love to hear him say FILTER CONETEXTA
Never missing the videos now a days to watch it to gain knowledge. Appreciate your effort sir.
This is what mastery sounds like. Thank you
A very good summary over CALCULATE
Dear Marco, Thanks for sharing your knowledge and expertise with us. Please help me solve one question if I want to find out the comparison of sales of current month v/s last month to find out who my new customers and who are the customers who didn't purchase this month from the sales table.
Hi, Alberto. Great video. I just have a request..Can you please provide the list of global & local modifiers used in CALCULATE function?
They are listed on dax.guide/calculate/ in the Remarks section.
@@SQLBI Yes, they are listed there but it is not clearly mentioned which are local modifiers & which are global modifiers or different conditions to use in both cases.
Great video! Thank you for comprehensive explanation 👍❤️
what i have seen ? Brilliant... like found the path to enlightenment.. wow!
Very good summary video indeed,specially order of execution section , thank u so much ....all clear but a request , can we have one visual section with example on order of execution the way you have visually explained in arbitrary shaped filter video ...it will be really helpful for all
It's too complex for a chart, you can find the list here: dax.guide/calculate
Thanks to share all details about CALCULATE func. This is the fist time that a see se sequance o execution and I have a question. Why the evaluation are separeted of application of filters?
Thank you for the videos, you and Marco are excellent teachers! This video helped me a lot!
I love you Mr. Ferrari
Thank you so much for this video. When applying a simple filter, CALCULATETABLE is the same as FILTER? As intermediate step on a calculated table I've had some issues with CALCULATETABLE that disappeared using FILTER
They both returns a table, but FILTER iterates with a row context, whereas CALCULATETABLE does not - the semantics is different.
@@SQLBI but then to apply a simple filter column from the same table they should be equivalent, right? I wonder what the issue was then.
Please, can you clarify the question? I don't understand the reference.
These two expressions when used to initialize a variable should always behave the same? dax.do/Hslp0K9WqQHLu2/
I had an issue using CALCULATETABLE that disappeared when I used FILTER but don't have it in a model I can share, just checking if at least in theory they should behave the same or not.
totally logical and very well presented :-) thx for this video
finished WATCHING
Hi
when do I know if the result is a scaled value or a table?
Thanks
/Thomas
A scalar value is a single string or number, a table may have more rows and columns - check the return type of the function used on dax.guide
Hello Alberto, how are you? Great video, as always. Congratulations! I was left with a doubt: you said that every measure has a CALCULATE and also said about the order of evaluation of CALCULATE's arguments. Why in a row context do I get different results using a ready measure than writing CALCULATE directly? Example:
DEFINE
MEASURE Sales[# Color] =
CALCULATE(
COUNTROWS(VALUES(Product[Color])),
VALUES(Product[Color])
)
EVALUATE
ADDCOLUMNS(
VALUES(Product[Color]),
"@Calculate",
CALCULATE ( COUNTROWS ( VALUES ( Product[Color] ) ), VALUES( Product[Color] ) ),
"@[Measure]",
[# Color]
)
With ready measure context transition steps in before the CALCULATE expression is evaluated
@@francescobergamaschi800 Could you explain it better?
Gonna get picky here. Can you add the [year] to the output table?
Otherwise, always enjoy your content!
Is there any way you can show in your video what is after the evaluate line or if you can drop this example for download? please
You can try the examples on dax.do starting from the related page on DAX Guide: dax.guide/calculate
@@SQLBI Thank you
Amazing, thanks.✍📚🧡
Excelente explicación!! muchas gracias
How to create a dax measure where individual rows it should calculate ratio and total should be sum of all the records visible ??
Use VAR in your measure or Earlier Function to compare single row and use ALL function to remove filter on the table to keep your denominator same in all the rows.
Hi Alberto l,
What is the porpuse of "Calculate table" if we can do the same work with "Filter" function?
It performs a context transition and applies a filter context to the entire expression. It is useful in more complex expression, or when you want to remove filters. See examples here: www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/
If you use more than one boolean condition then using Calculate Table is the right choice instead of Filter.
Hi, great. Do you think that calculatetable is better to use then the FILTER argument??
They have different semantics. You should use CALCULATETABLE to apply filters, whereas FILTER is required whenever you have to filter a table based on the result of a measure (you need a row context that only FILTER can generate).
Thanks for the answer guys, this ia what I allways tell to my students!!
Where can I dowload this file????
You can try the code online at dax.do or visit the dax.guide website - you can try all the examples from there.
@@SQLBI I tried. Thank you
Great minds....
Calculatable in just 5sec........Wao
thank you
Ciao friends?
haha Ty guys =)
Thanks!!
no "ciao friends" ? :)
DAX Guide videos must be short!
It is still complicated!
This is hard to understand
Great explanation! I nearly understood the whole thing. However, there is still one little confusion around the context in which the explicit filter arguments are EVALUATED....this is after having gone through the steps on dax.guide website and then listening to your explanation here.
In dax.guide website, it says that the EVALUATION of explicit filter arguments in CALCULATE, itself happens within the original evaluation context (original row context + original filter context) in Step 1. Then in Step 5 line 5, the evaluation of explicit filter arguments is mentioned again, but this time it says, within original filter context instead of original evaluation context. Then in your explanation here, it seems as though the explicit filter arguments are evaluated by just converting the condition into a table of value/s without any reference to an outer/original context. This created a little confusion for me.
To summarize, I guess my query is.....Are the explicit filter arguments in CALCULATE themselves EVALUATED within the original evaluation context, or original filter context, or no outer/original context at all?
They are evaluated in the original evaluation context (including filter/row context).
I wish it would be explained in power bi itself not a dax studio. For a beginner who is really frustrated with all these counter intuitive concepts adding dax studio with all strange things around is not a good idea.
These videos are related to dax.guide, which is a guide to DAX functions, and they are not intended as a tutorial about the language.
Please, follow this guide to move the first steps into DAX - most of the resources for beginners are free, in particular the Introducing DAX uses Power BI Desktop as user interface: www.sqlbi.com/guides/dax/
@@SQLBI Thank You for the answer. I am studying the ultimate Dax book. Well I did learn Photoshop, did learn a lot of 3D graphics which was pretty hard, but DAX…it is so counter intuitive and frustrating for a beginner. A road through hell to get some pretty basic results in terms of mathematics.