Oh my god! I would never think of avoiding using FILTER function! I have just checked on quite large model and performance improved by 10%. Thanks for raising this issue
Many thanks, I like the way you spread light on all these subtleties hidden in DAX language. As a beginner it's very pleasant and comforting to have enthusiastic people like you making us deeply understanding rather than simply imitating.
Hola! Ruth, i'm a Colombian guy and just for that i will continue write this in spanish, Espero comprendas! Creo que soy un super fan, por que con tus videos estoy aprendiendo ingles y DAX al mismo tiempo y tus explicaciones son fantasticas! eres genial!
Ruth, Thank you so much for explaining this. It was a simple but great explanation. I learn so much by watching all of your videos. Have a great weekend! 😃
Sweet! Glad to see this video! FILTER is soooo intuitive, it's hard to ignore, especially for excel users. KEEPFILTERS the DAX best practice for that case though is kinda harsh to memorize. Another tip I found reading the SQLBI guys is: never filter a TABLE if you can filter a COLUMN. Actually, that is what happens between the examples provided. cheers!
Ruth this is a revelation! Like so many other commenters below I have used SUMX/AVERAGEX etc with (and sometimes without) FILTER since day one, and never once suspected I was doing something wrong. I've just re-written a 2M row report with CALCULATETABLE and the difference is amazing......THANK YOU SO MUCH!!!
@@CurbalEN Indeed. I’ve already implemented this approach in one of the complex report and the execution time has been reduced by over 70%. Once again, many thanks Ruth!
This is good, I been using DAX on and off for two years and gravitate so much to using filter. I am going to refactor a lot of models after this :) thank you
Brilliant, completely agree with the suggestion to change Filter to FilterX. I know it's an iterator but I forget and would make it so explicit. I really enjoy the way you explain concepts and in particular the honesty when something doesn't just make sense to you, it makes me feel better as quite often I'm nodding my head in agreement. Thanks Ruth :)
Hi Ruth, it's Friday finally. Thanks for this great tip, it's really a valuable hidden treasure to improve the DAX performance. Marked and will apply it to my dashboards shortly. Have a nice weekend. :P
Great video Ruth!! Totally agree that only an X at the end (FILTERX) would make users think as the "row context" and user CALCULATETABLE rather than FILTER.
Hi Ruth, Definitely agree with you, avoid iterators (including those without X ) but trivial situations we cannot, like rankx.. query authors really need to test out various options and their execution times to optimise DAX queries in large models. I believe the syntax sugar is it uses filter with all.. Thank for the video. Happy weekend 👍 👍
About your boxing guard in the thumbnail: I just wanted to let you know that you should touch the outside of your eyebrows as if you were saluting with both hands. When you guard with your palms facing your face (such as in the thumbnail) your head is exposed and you can't block or parry your opponent's strikes. Also, keep those elbows against your ribs to protect against the body shots. Thanks for making good Power BI content!
i realise im a little late to the scene, but what do you think is more efficient - filtering a column (not a table) or using calculatetable (which presumably is bringing the entire table into the calculation) ?
Thanks Ruth again for such a informative video. Seems like a hard habit to break. Will you be making a video on how much faster it is to use CALCALATETABLE instead of FILTER?
Thanks a lot, Ruth. What about if you does not put FILTER nor CALCULATETABLE? It will also make the calculation, isn't it? Does this method also uses iteration?
Excellent video Ruth. If I'm not wrong this used to be true until the compatibility level 1500 I think now vertipaq is smart enough to trigger the context transition even when using "filter"? Did you check the query plan of the two versions? Please don't tell me that I need to optimize all my new models :)
Hola Ruth! I always tried to make a measure with a filter that gave me the last price by date in a table. Obviously is a fact table where prices for the same product change by date. And of course was not able. If you could make a video or tell us where you have one with this issue solved would be great!
@@CurbalEN nope, can you give me the link to that video, would ve amazing to watch it. Always learning so much from you and by the way, the way you express yourself its totally engaging! Many thanks for what you do, ut’s marvelous
@@CurbalEN thanks Ruth, nice solution, but still don’t get or know how to get the last price by date. Imagine I have a table of products that change price depending on the year, and i want to create a mesure that displays or lets me multiply that las date’s price for the product by units sold.... I’ve been struggling with that for several days and haven’t find a solution.
Hi Ruth, I'm new to DAX and currently I'm struggling with a problem about filter. Can you please help me🙏? I will be very appreciated if you can give me any suggestion 🙂 I have a measure total sales of this year (let's call it A) and a measure total sales last year ( B). I need to calculate the YoY difference (A-B). If I use a filter on A, it will be automatically applied on B and C as well. Because B is based on A. How can I avoid this filter on B and C?
It's Friday and I now have a headache. "Avoid using FILTER as a filter argument", that broke my head. I need to take two aspirins now and rest for a while. I will feel better when I get up. I guess no pain, no gain.
Hey Ruth... Not sure if anyone else has mentioned this, but does this mean that KEEPFILTERS(Table[Color] = "Red") is just syntax-sugar for CALCULATETABLE(Table, Table[Color] = "Red") ???
@@CurbalEN That was actually supposed to be a question...! But given the stated limitations in the documentation seem very similar on each of these DAX functions, it may be true...
Hi Ruth, Thanks for your video. I agree with have a "small" comment. I think we should be careful saying that iterators should be avoided at all times. As example I can think about SUMX being used to sum the multiplication of Sales Quantity and Sales Price of the Sales table. You can of course add a calculated column to the Sales table which contains the Sales Amount and then use that column to have a simple measure that sums this column. Here the question will be what do I need to minimize the space of storing the Sales amount in the Sales table (this can be a lot if I have millions of rows), or should I minimize the calculation time. With a measure containing an iterator we should also be aware that the in most cases the Filter context of the visual (where we use our measure) will be applied to the table before the calculations are applied for each of the remaining rows. It is exceptional that you want the sum of all sales rows. Another point we should be aware of is that CALCULATE and CALCULATETABLE apply context transition. So if you use it within a row context it might seriously impact your results .
Hi I’m a DAX expert and I believe the article you mentioned has no direct relationship with the usage of CALCULATETABLE vs FILTER it does not suggest to use CALCULATETABLE instead of FILTER Rather, it suggest to use Boolean expressions inside a calculate instead of filter To me, the reason is that a Boolean expression Product [color]=red is automatically translated in FILTER ( ALL(product[color]), Product[Color]=red ) People, on the other hand, trend to use dangerous syntax like FILTER ( ALL (Product ), Product[Color]=red ) and this may be slower and very dangerous because it removes the filters on the expanded table of product which may contains other tables for example categories etc that are related to it with a many to one relationship So in opinion there are few cases where FILTER is slower and CALCULATETABLE is faster if filter is written in the proper way Let me know your thoughts, I don’t want to disprove you 😃 I just love Dax details ! Alberto
Hi Alberto, I mentioned the article as another example of when filter might be a bad choice. Most of us are not DAX experts so writing filter expressions "the right way" does not come naturally ;) Thanks for your thoughts though!
@@CurbalEN All in all, I think you should explain Where to use ALL() and and not use it, also how KEEPFILTERS(FILTER(ALL(Table[Col]),FilterExp)) can allow yout to use multiple columns and keep the context filters in place :)
Good video. Yes Rabbit Hole... 6:00 What about split the measure using variables to create the table and then do the SUM of sales? Sales Red > 1 = VAR A = FILTER ( 'Table', 'Table'[Colour] = "Red" && 'Table'[Sales] > 1 etc, etc, or use CALCULATETABLE as a variable?
I was doing optimization on my report today. 1 mln rows in dataset, 6 measures with FILTER used in table visual, by deleteting FILTER, load time decresed from 7s to 2s ...
Filter as an x funtion threwme off the complete weekend haha. I use the Filter function in each of my calculation measures on default. I still don’t completle get how to rewrite my measures. I always use for example Week1Sales = calculate(sum([Sales]), Filter(‘PeriodTable’,[Week]=1)). Can I change the filter part with the caculatetable function, but then filter de fact table on week 1? Greetings from a great fan of all video’s 😉👍🏻
Great Video! But one question. For CALCULATETABLE it says that it can only reference ONE column. By having two conditions (colour =red and sales > 1) you reference two columns, dont you?
@@CurbalEN Hi, I don't think so. Filtering multiple columns is allowed with CALCULATE() and CALCULATETABLE() but not in the same filter argument. In this case we are using 2 filter arguments which is OK
Great video, thank you so much. "Avoid using FILTER as a filter", how very Microsoft🤣....... don't show that documentation to newbies or they may run for the hills...... 😂
Hi Ruth. Now i really wonder. Why wouldn't you use CALCULATE([All sales], ‘ Table’ [Color] = “ red” ) instead of the CALCULATETABLE() version? So use FILTER() when an iterator is needed. And just use nothing when no iterators are needed.
Hi you can also gain performance when you filter a column instead of a table red sales filter = CALCULATE([All Sales], FILTER(Table,Table[Color]="Red")) red sales filter better = CALCULATE([All Sales], FILTER(ALL(Table[Color]),Table[Color]="Red")) This Measure will perform better that letting FILTER() filter the whole table. At least that's what I learnt from Marco and Alberto. The measure above is equivalent to: CALCULATE([All Sales], Table[Color] = "Red")
Hi Ruth, maybe you can show as some "performance" statistic between different solution (for example from DaxStudio ServerTiming features?), with and without using variables: a) FILTER b) FILTER (ALL(table'Column') c) CALCULATETABEL d) TREATAS e) KEEPFILTERS f) If I had to bet I would point to TREATAS as winner
@@CurbalEN I think it's actually syntax sugar for CALCULATE( [Sales], FILTER( ALL(Product[Color]), Product[Color] = "Red" ) ) Which means that the generated filter context only includes one column (Product[Color]) instead of all Product columns, as in FILTER(Product, ...). So the syntax sugar version should be faster than the pure Filter version on the entire table.
Hi Ruth ! Well explained ... Thumbs Up ! I would always prefer FILTER ... as it is more flexible (mesuares etc..). My golden rule is: Don´t use FILTER on fact tabels and don´t nest it in other iterators ! Following this you will not have a problem on big models. Have a nice weekend.
After reading a bit ... I don'T think this is a blanket approach. The main difference between your examples is the fact that your FILTER is itterating each unique row of your table, and CalculateTable iterrate unique values of your column. If you filtered on a single ROW, you would negate that performance gain :)
As a matter of fact, just ran examples trough DAX Studio, and the Generated Queries are all the same when I do these measure on a table of 1.8M rows : WebPerf[timingAttribute] has a cardinality of 8 and WebPerf[host] has a cardinality of 177. Sum connectTimeFilterTable = CALCULATE([Sum totalTime],FILTER(WebPerf,WebPerf[timingAttribute]="connectTime" && WebPerf[host]="sub.domain.tld")) Sum connectTimeSingleFilterColumns = CALCULATE([Sum totalTime],KEEPFILTERS(FILTER(ALL(WebPerf[timingAttribute],WebPerf[host]),WebPerf[timingAttribute]="connectTime" && WebPerf[host]="sub.domain.tld"))) Sum connectTimeDualFilterColumns = CALCULATE([Sum totalTime],KEEPFILTERS(FILTER(ALL(WebPerf[timingAttribute]),WebPerf[timingAttribute]="connectTime")), KEEPFILTERS(FILTER(ALL(WebPerf[host]),WebPerf[host]="sub.domain.tld"))) They all generate that Query sent to the storage engine : SET DC_KIND="AUTO"; SELECT SUM ( 'Queries 1Month'[Timing] ) FROM 'Queries 1Month' WHERE 'Queries 1Month'[host] = 'sub.domain.tld' VAND 'Queries 1Month'[timingAttribute] = 'connectTime'; 'Estimated size ( volume, marshalling bytes ) : 1, 16'
Despite loving your videos, I think you need to review some facts in this video. Difference between Filter and CalculateTable has to do with evaluation context and order of processing arg1 and arg2 of both functions. Agg. functions like SUM are syntactic sugar for SumX.
@@CurbalEN I thought that was converted fo Filter[product[color], product[color]="Red" instead of Filter(product, product[color]="Red". The first only iterates over a column and the second iterates over all the table which is worst.
![[Review] Exponential Organizations 2.0 (Salim Ismail) Summarized.](http://i.ytimg.com/vi/QnqR93vBuy0/mqdefault.jpg)
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
Oh my god! I would never think of avoiding using FILTER function! I have just checked on quite large model and performance improved by 10%. Thanks for raising this issue
Oh wow! That is a big improvement 👏👏
Just love the simplicity you explain the complex subtle DAX ...Thank you always Ruth :D
I've always favoured Calculatetable over Filter, but it was a habit instead of for a real reason. Now I feel pleased with myself :)
You should be! I do the opposite!
Many thanks, I like the way you spread light on all these subtleties hidden in DAX language. As a beginner it's very pleasant and comforting to have enthusiastic people like you making us deeply understanding rather than simply imitating.
Fabulous to hear, thanks!
🤯 omg, WHAT? I'm simply blown away! I feel like I just took a red pill... Thank you, Ruth!
The DAX matrix 😂
Hola! Ruth, i'm a Colombian guy and just for that i will continue write this in spanish, Espero comprendas! Creo que soy un super fan, por que con tus videos estoy aprendiendo ingles y DAX al mismo tiempo y tus explicaciones son fantasticas!
eres genial!
Muchas gracias Sebastián!! Felices fieatas !
Ruth, Thank you so much for explaining this. It was a simple but great explanation. I learn so much by watching all of your videos. Have a great weekend! 😃
Same to you Joanne!
Sweet! Glad to see this video!
FILTER is soooo intuitive, it's hard to ignore, especially for excel users. KEEPFILTERS the DAX best practice for that case though is kinda harsh to memorize.
Another tip I found reading the SQLBI guys is: never filter a TABLE if you can filter a COLUMN. Actually, that is what happens between the examples provided.
cheers!
Thanks for sharing!
Ruth this is a revelation! Like so many other commenters below I have used SUMX/AVERAGEX etc with (and sometimes without) FILTER since day one, and never once suspected I was doing something wrong. I've just re-written a 2M row report with CALCULATETABLE and the difference is amazing......THANK YOU SO MUCH!!!
Soooo happy to hear ! 😊😊
I have been waiting for this for years. 👍 👍👍 Many thanks.
Ruth, you are the Queen of DAX 😉
I hope it was worth the wait!
@@CurbalEN Indeed. I’ve already implemented this approach in one of the complex report and the execution time has been reduced by over 70%.
Once again, many thanks Ruth!
Oh wow!! That is fantastic , well done!! 👏👏👏
This is good, I been using DAX on and off for two years and gravitate so much to using filter. I am going to refactor a lot of models after this :) thank you
I do the same, old habits die hard!!
Brilliant, completely agree with the suggestion to change Filter to FilterX. I know it's an iterator but I forget and would make it so explicit. I really enjoy the way you explain concepts and in particular the honesty when something doesn't just make sense to you, it makes me feel better as quite often I'm nodding my head in agreement. Thanks Ruth :)
Thanks Susan!
Hi Ruth, it's Friday finally. Thanks for this great tip, it's really a valuable hidden treasure to improve the DAX performance. Marked and will apply it to my dashboards shortly. Have a nice weekend. :P
Same to you Danny!
Great video Ruth!! Totally agree that only an X at the end (FILTERX) would make users think as the "row context" and user CALCULATETABLE rather than FILTER.
It would be great if they change the name, but a mess too...
Happy Friday!
Thanks for uploading the video Ruth!! Happy Friday and great weekend ahead !!
Same to you!!
@@CurbalEN You are always welcome !!!
I would never have discovered this without your help!
Pleasure to spread the knowledge!
Hi Ruth, Definitely agree with you, avoid iterators (including those without X ) but trivial situations we cannot, like rankx.. query authors really need to test out various options and their execution times to optimise DAX queries in large models. I believe the syntax sugar is it uses filter with all..
Thank for the video. Happy weekend 👍 👍
Agree, I should have said avoid, it use responsibly :)
About your boxing guard in the thumbnail:
I just wanted to let you know that you should touch the outside of your eyebrows as if you were saluting with both hands.
When you guard with your palms facing your face (such as in the thumbnail) your head is exposed and you can't block or parry your opponent's strikes.
Also, keep those elbows against your ribs to protect against the body shots.
Thanks for making good Power BI content!
In some weird way, it makes sense!
Let me try it again for coming videos :)
Thanks a lot for your videos Ruth. You rule.
Thanks!
i realise im a little late to the scene, but what do you think is more efficient - filtering a column (not a table) or using calculatetable (which presumably is bringing the entire table into the calculation) ?
You are the best Ruth !
Thanks!
Good stuff! Thanks! But SUM is also translated to SUMX in the engine, they are the same. Being X iterator does’t automatically mean slow...
I love your explication!!!
Thanks Ruth again for such a informative video.
Seems like a hard habit to break.
Will you be making a video on how much faster it is to use CALCALATETABLE instead of FILTER?
Check the pinned comment, it has been done already :)
Thanks a lot, Ruth. What about if you does not put FILTER nor CALCULATETABLE? It will also make the calculation, isn't it? Does this method also uses iteration?
Yes, it uses filter without you seeing it.
/Ruth
Excellent video Ruth.
If I'm not wrong this used to be true until the compatibility level 1500 I think now vertipaq is smart enough to trigger the context transition even when using "filter"?
Did you check the query plan of the two versions? Please don't tell me that I need to optimize all my new models :)
Check the comments, people are getting crazy performance improvements!
Hola Ruth! I always tried to make a measure with a filter that gave me the last price by date in a table. Obviously is a fact table where prices for the same product change by date. And of course was not able. If you could make a video or tell us where you have one with this issue solved would be great!
I have a video that does something similar but with currencies. Ha e you seen it?
@@CurbalEN nope, can you give me the link to that video, would ve amazing to watch it. Always learning so much from you and by the way, the way you express yourself its totally engaging! Many thanks for what you do, ut’s marvelous
Thanks!
Maybe this gives you ideas:
m.ua-cam.com/video/Q4hF4CwJsFE/v-deo.html
@@CurbalEN thanks Ruth, nice solution, but still don’t get or know how to get the last price by date. Imagine I have a table of products that change price depending on the year, and i want to create a mesure that displays or lets me multiply that las date’s price for the product by units sold.... I’ve been struggling with that for several days and haven’t find a solution.
Thanks Ruth, Can you please help me to know: How to use multi column filter using dax measure.
Use of logical operators
@@Bharath_PBI community.powerbi.com/t5/Desktop/Stock-Calculation/m-p/1433311/highlight/true#M603249
Thank you, Ruth! I learned something, again!
🥳🥳
Wondeful and happy Friday!
Hi Ruth, I'm new to DAX and currently I'm struggling with a problem about filter. Can you please help me🙏? I will be very appreciated if you can give me any suggestion 🙂
I have a measure total sales of this year (let's call it A) and a measure total sales last year ( B). I need to calculate the YoY difference (A-B). If I use a filter on A, it will be automatically applied on B and C as well. Because B is based on A. How can I avoid this filter on B and C?
The power bi community is the perfect place for support questions :)
It's Friday and I now have a headache. "Avoid using FILTER as a filter argument", that broke my head. I need to take two aspirins now and rest for a while. I will feel better when I get up. I guess no pain, no gain.
I know! How crazy is that?? 😭😭
Hey Ruth... Not sure if anyone else has mentioned this, but does this mean that KEEPFILTERS(Table[Color] = "Red") is just syntax-sugar for CALCULATETABLE(Table, Table[Color] = "Red") ???
Is it ?? OMG, thanks!
@@CurbalEN That was actually supposed to be a question...! But given the stated limitations in the documentation seem very similar on each of these DAX functions, it may be true...
Oh, I see... it could be, I actually don't know. Have you asked in the power bi community?
Loved it.. thank you. I will try to replace FILTER by CALCULATETABLE without breaking anything 😁
Let us know if it improves performance!
Hi Ruth,
Thanks for your video. I agree with have a "small" comment.
I think we should be careful saying that iterators should be avoided at all times. As example I can think about SUMX being used to sum the multiplication of Sales Quantity and Sales Price of the Sales table. You can of course add a calculated column to the Sales table which contains the Sales Amount and then use that column to have a simple measure that sums this column. Here the question will be what do I need to minimize the space of storing the Sales amount in the Sales table (this can be a lot if I have millions of rows), or should I minimize the calculation time. With a measure containing an iterator we should also be aware that the in most cases the Filter context of the visual (where we use our measure) will be applied to the table before the calculations are applied for each of the remaining rows. It is exceptional that you want the sum of all sales rows.
Another point we should be aware of is that CALCULATE and CALCULATETABLE apply context transition. So if you use it within a row context it might seriously impact your results .
Agree, avoid is not the correct word, use them carefully perhaps fits better.
Happy Friday!
Hi
I’m a DAX expert and I believe the article you mentioned has no direct relationship with the usage of CALCULATETABLE vs FILTER
it does not suggest to use CALCULATETABLE instead of FILTER
Rather, it suggest to use Boolean expressions inside a calculate instead of filter
To me, the reason is that a Boolean expression
Product [color]=red
is automatically translated in
FILTER (
ALL(product[color]),
Product[Color]=red
)
People, on the other hand, trend to use dangerous syntax like
FILTER (
ALL (Product ),
Product[Color]=red
)
and this may be slower and very dangerous because it removes the filters on the expanded table of product which may contains other tables for example categories etc that are related to it with a many to one relationship
So in opinion there are few cases where FILTER is slower and CALCULATETABLE is faster if filter is written in the proper way
Let me know your thoughts, I don’t want to disprove you 😃 I just love Dax details !
Alberto
Hi Alberto, I mentioned the article as another example of when filter might be a bad choice.
Most of us are not DAX experts so writing filter expressions "the right way" does not come naturally ;)
Thanks for your thoughts though!
@@CurbalEN All in all, I think you should explain Where to use ALL() and and not use it, also how KEEPFILTERS(FILTER(ALL(Table[Col]),FilterExp)) can allow yout to use multiple columns and keep the context filters in place :)
Thanks for sharing!
Congratulations TOP explanations !!!
Yey!!
Good video. Yes Rabbit Hole...
6:00 What about split the measure using variables to create the table and then do the SUM of sales?
Sales Red > 1 =
VAR A = FILTER ( 'Table', 'Table'[Colour] = "Red" && 'Table'[Sales] > 1
etc, etc, or use CALCULATETABLE as a variable?
Everything is possible as long as:
1. It gives you the correct result
2. Doesnt slow down your model
imo
Happy Friday!
I was doing optimization on my report today. 1 mln rows in dataset, 6 measures with FILTER used in table visual, by deleteting FILTER, load time decresed from 7s to 2s ...
👏👏👏👏
Bravo!!!
Omg, I use it everywhere :( Thank you very much, I understand better right now why certain reports are so slow :)
Change it and let us know how it goes!
@@CurbalEN I'm on my way! But I have more than 1 000 reports... I'll identify these where performance is very slow before doing this...
Sounds like a good plan 😊
That was my problem,Thanks
Hi Ruth, great video, that was 🤯
Thanks! 😊
Filter as an x funtion threwme off the complete weekend haha. I use the Filter function in each of my calculation measures on default. I still don’t completle get how to rewrite my measures. I always use for example Week1Sales = calculate(sum([Sales]), Filter(‘PeriodTable’,[Week]=1)). Can I change the filter part with the caculatetable function, but then filter de fact table on week 1? Greetings from a great fan of all video’s 😉👍🏻
I know! Fridays is not the best day for these type of videos!
For the case you mention, try using KEEPFILTERS
/Ruth
Great Video! But one question. For CALCULATETABLE it says that it can only reference ONE column. By having two conditions (colour =red and sales > 1) you reference two columns, dont you?
Good point, so maybe sales >1 is a filter in disguise after all!
@@CurbalEN Hi, I don't think so. Filtering multiple columns is allowed with CALCULATE() and CALCULATETABLE() but not in the same filter argument. In this case we are using 2 filter arguments which is OK
Great video!!
Glad you enjoyed it
Great video, thank you so much. "Avoid using FILTER as a filter", how very Microsoft🤣....... don't show that documentation to newbies or they may run for the hills...... 😂
Hi Ruth. Now i really wonder. Why wouldn't you use CALCULATE([All sales], ‘ Table’ [Color] = “ red” ) instead of the CALCULATETABLE() version?
So use FILTER() when an iterator is needed. And just use nothing when no iterators are needed.
Your measure uses the filter function in the background.
@@CurbalEN Aah I see, That clears things up. Thanks!
My pleasure :)
For the simple example of filtering for a dimension, Why not just use the basic calculate measure: measure = calculate ([total sales],color="red") ?
I was wondering the same. I learned to use FILTER() when evaluating expressions and use nothing when just evaluating a column value
Hi you can also gain performance when you filter a column instead of a table
red sales filter =
CALCULATE([All Sales],
FILTER(Table,Table[Color]="Red"))
red sales filter better =
CALCULATE([All Sales],
FILTER(ALL(Table[Color]),Table[Color]="Red"))
This Measure will perform better that letting FILTER() filter the whole table. At least that's what I learnt from Marco and Alberto.
The measure above is equivalent to: CALCULATE([All Sales], Table[Color] = "Red")
Hi Ruth,
maybe you can show as some "performance" statistic between different solution (for example from DaxStudio ServerTiming features?), with and without using variables:
a) FILTER
b) FILTER (ALL(table'Column')
c) CALCULATETABEL
d) TREATAS
e) KEEPFILTERS
f)
If I had to bet I would point to TREATAS as winner
Thanks! Added to my list :)
And what about such syntax?
CALCULATE( [Sales], 'Product'[Color] = "Red") ?
That is syntax sugar for:
CALCULATE([Sales],
FILTER(Product,
Product[Color]="Red"))
@@CurbalEN I think it's actually syntax sugar for
CALCULATE(
[Sales],
FILTER(
ALL(Product[Color]),
Product[Color] = "Red"
)
)
Which means that the generated filter context only includes one column (Product[Color]) instead of all Product columns, as in FILTER(Product, ...).
So the syntax sugar version should be faster than the pure Filter version on the entire table.
True! Forgot the ALL 😊
Hi Ruth ! Well explained ... Thumbs Up ! I would always prefer FILTER ... as it is more flexible (mesuares etc..). My golden rule is: Don´t use FILTER on fact tabels and don´t nest it in other iterators ! Following this you will not have a problem on big models. Have a nice weekend.
Thaanks for sharing!
After reading a bit ... I don'T think this is a blanket approach. The main difference between your examples is the fact that your FILTER is itterating each unique row of your table, and CalculateTable iterrate unique values of your column. If you filtered on a single ROW, you would negate that performance gain :)
As a matter of fact, just ran examples trough DAX Studio, and the Generated Queries are all the same when I do these measure on a table of 1.8M rows : WebPerf[timingAttribute] has a cardinality of 8 and WebPerf[host] has a cardinality of 177.
Sum connectTimeFilterTable = CALCULATE([Sum totalTime],FILTER(WebPerf,WebPerf[timingAttribute]="connectTime" && WebPerf[host]="sub.domain.tld"))
Sum connectTimeSingleFilterColumns = CALCULATE([Sum totalTime],KEEPFILTERS(FILTER(ALL(WebPerf[timingAttribute],WebPerf[host]),WebPerf[timingAttribute]="connectTime" && WebPerf[host]="sub.domain.tld")))
Sum connectTimeDualFilterColumns = CALCULATE([Sum totalTime],KEEPFILTERS(FILTER(ALL(WebPerf[timingAttribute]),WebPerf[timingAttribute]="connectTime")), KEEPFILTERS(FILTER(ALL(WebPerf[host]),WebPerf[host]="sub.domain.tld")))
They all generate that Query sent to the storage engine :
SET DC_KIND="AUTO";
SELECT
SUM ( 'Queries 1Month'[Timing] )
FROM 'Queries 1Month'
WHERE
'Queries 1Month'[host] = 'sub.domain.tld' VAND
'Queries 1Month'[timingAttribute] = 'connectTime';
'Estimated size ( volume, marshalling bytes ) : 1, 16'
Despite loving your videos, I think you need to review some facts in this video.
Difference between Filter and CalculateTable has to do with evaluation context and order of processing arg1 and arg2 of both functions.
Agg. functions like SUM are syntactic sugar for SumX.
Thanks for the feedback!!
You explain the topics very clearly.
Wonderful to hear:)
Hi everyone :) I don't understand why calculatetable or filter is needed. I normally use calculate(salesamount, product[colo]="Red")
Product = Red is converted to filter(product=red) , so you are using filter even if you dont see it.
@@CurbalEN I thought that was converted fo Filter[product[color], product[color]="Red" instead of Filter(product, product[color]="Red". The first only iterates over a column and the second iterates over all the table which is worst.
Filter should be called filterX,
wahaaa, you said it at 4.55.
Anybody else in panic at this? Just me? F*^%
If you don't know the whole story why do you confuse people with you tube videos?
I always show what I learn 😊