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
@CurbalEN
4 жыл бұрын
Oh wow! That is a big improvement 👏👏
@davidcadman3562
4 жыл бұрын
I've always favoured Calculatetable over Filter, but it was a habit instead of for a real reason. Now I feel pleased with myself :)
@CurbalEN
4 жыл бұрын
You should be! I do the opposite!
@HachiAdachi
4 жыл бұрын
🤯 omg, WHAT? I'm simply blown away! I feel like I just took a red pill... Thank you, Ruth!
@CurbalEN
4 жыл бұрын
The DAX matrix 😂
@sebastiencrepel5032
2 жыл бұрын
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.
@CurbalEN
2 жыл бұрын
Fabulous to hear, thanks!
@sebastianvelezcardeno8599
3 жыл бұрын
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!
@CurbalEN
3 жыл бұрын
Muchas gracias Sebastián!! Felices fieatas !
@sansden
2 жыл бұрын
Just love the simplicity you explain the complex subtle DAX ...Thank you always Ruth :D
@brunof.s.8186
4 жыл бұрын
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!
@CurbalEN
4 жыл бұрын
Thanks for sharing!
@stevemorris4938
4 жыл бұрын
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
4 жыл бұрын
Soooo happy to hear ! 😊😊
@vitorklein2559
4 жыл бұрын
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.
@CurbalEN
4 жыл бұрын
It would be great if they change the name, but a mess too... Happy Friday!
@juansepowerplatform
4 жыл бұрын
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
@CurbalEN
4 жыл бұрын
I do the same, old habits die hard!!
@joanneosborne2428
4 жыл бұрын
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! 😃
@CurbalEN
4 жыл бұрын
Same to you Joanne!
@Leonardo-jv1ls
2 жыл бұрын
Thanks a lot for your videos Ruth. You rule.
@CurbalEN
2 жыл бұрын
Thanks!
@copinhenry
4 жыл бұрын
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 :)
@CurbalEN
4 жыл бұрын
Check the comments, people are getting crazy performance improvements!
@Bharath_PBI
4 жыл бұрын
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 👍 👍
@CurbalEN
4 жыл бұрын
Agree, I should have said avoid, it use responsibly :)
@mnowako
4 жыл бұрын
I have been waiting for this for years. 👍 👍👍 Many thanks. Ruth, you are the Queen of DAX 😉
@CurbalEN
4 жыл бұрын
I hope it was worth the wait!
@mnowako
4 жыл бұрын
@@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!
@CurbalEN
4 жыл бұрын
Oh wow!! That is fantastic , well done!! 👏👏👏
@sue_bayes
4 жыл бұрын
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 :)
@CurbalEN
4 жыл бұрын
Thanks Susan!
@lucappelmans9056
4 жыл бұрын
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 .
@CurbalEN
4 жыл бұрын
Agree, avoid is not the correct word, use them carefully perhaps fits better. Happy Friday!
@mshparber
3 жыл бұрын
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...
@EricaDyson
4 жыл бұрын
I would never have discovered this without your help!
@CurbalEN
4 жыл бұрын
Pleasure to spread the knowledge!
@steventhehistorian
4 жыл бұрын
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!
@CurbalEN
4 жыл бұрын
In some weird way, it makes sense! Let me try it again for coming videos :)
@powerbiisrael6819
4 жыл бұрын
You are the best Ruth !
@CurbalEN
4 жыл бұрын
Thanks!
@dannyhometown
4 жыл бұрын
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
@CurbalEN
4 жыл бұрын
Same to you Danny!
@Unbox747
4 жыл бұрын
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?
@CurbalEN
4 жыл бұрын
Check the pinned comment, it has been done already :)
@Phoenixspin
4 жыл бұрын
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.
@CurbalEN
4 жыл бұрын
I know! How crazy is that?? 😭😭
@abhishekstatus_7
4 жыл бұрын
Thanks for uploading the video Ruth!! Happy Friday and great weekend ahead !!
@CurbalEN
4 жыл бұрын
Same to you!!
@abhishekstatus_7
4 жыл бұрын
@@CurbalEN You are always welcome !!!
@angelgarciadatablog
Жыл бұрын
I love your explication!!!
@mohammadrezaei4221
3 жыл бұрын
That was my problem,Thanks
@9zQx86LT
4 жыл бұрын
Loved it.. thank you. I will try to replace FILTER by CALCULATETABLE without breaking anything 😁
@CurbalEN
4 жыл бұрын
Let us know if it improves performance!
@AlbertoGastaldo
4 жыл бұрын
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
@CurbalEN
4 жыл бұрын
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!
@joelguay4452
3 жыл бұрын
@@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 :)
@CurbalEN
3 жыл бұрын
Thanks for sharing!
@michaelmay5032
4 жыл бұрын
Thank you, Ruth! I learned something, again!
@CurbalEN
4 жыл бұрын
🥳🥳 Wondeful and happy Friday!
@akuku5555
4 жыл бұрын
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 ...
@CurbalEN
4 жыл бұрын
👏👏👏👏 Bravo!!!
@salvatorescofano6756
4 жыл бұрын
Congratulations TOP explanations !!!
@CurbalEN
4 жыл бұрын
Yey!!
@s77lom49
Жыл бұрын
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) ?
@bertroos7707
3 жыл бұрын
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")
@JonathanCHANAL
4 жыл бұрын
Omg, I use it everywhere :( Thank you very much, I understand better right now why certain reports are so slow :)
@CurbalEN
4 жыл бұрын
Change it and let us know how it goes!
@JonathanCHANAL
4 жыл бұрын
@@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...
@CurbalEN
4 жыл бұрын
Sounds like a good plan 😊
@desenterrados2022
4 жыл бұрын
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?
@CurbalEN
4 жыл бұрын
Yes, it uses filter without you seeing it. /Ruth
@joelguay4452
3 жыл бұрын
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 :)
@joelguay4452
3 жыл бұрын
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'
@qaz56q
4 жыл бұрын
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
4 жыл бұрын
Thanks! Added to my list :)
@geoffrichardson7013
4 жыл бұрын
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
4 жыл бұрын
Is it ?? OMG, thanks!
@geoffrichardson7013
4 жыл бұрын
@@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...
@CurbalEN
4 жыл бұрын
Oh, I see... it could be, I actually don't know. Have you asked in the power bi community?
@kennethstephani692
4 жыл бұрын
Great video!!
@CurbalEN
4 жыл бұрын
Glad you enjoyed it
@soy34mb
4 жыл бұрын
Hi Ruth, great video, that was 🤯
@CurbalEN
4 жыл бұрын
Thanks! 😊
@Maartenmarsje
4 жыл бұрын
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 😉👍🏻
@CurbalEN
4 жыл бұрын
I know! Fridays is not the best day for these type of videos! For the case you mention, try using KEEPFILTERS /Ruth
@elrevesyelderecho
4 жыл бұрын
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?
@CurbalEN
4 жыл бұрын
Everything is possible as long as: 1. It gives you the correct result 2. Doesnt slow down your model imo Happy Friday!
@suryasahoo3771
4 жыл бұрын
Thanks Ruth, Can you please help me to know: How to use multi column filter using dax measure.
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
2 жыл бұрын
Thanks for the feedback!!
@binbinzhou6938
Жыл бұрын
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?
@CurbalEN
Жыл бұрын
The power bi community is the perfect place for support questions :)
@cg24356
4 жыл бұрын
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
4 жыл бұрын
Good point, so maybe sales >1 is a filter in disguise after all!
@bertroos7707
3 жыл бұрын
@@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
@borjaveiga
4 жыл бұрын
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
4 жыл бұрын
I have a video that does something similar but with currencies. Ha e you seen it?
@borjaveiga
4 жыл бұрын
@@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
4 жыл бұрын
Thanks! Maybe this gives you ideas: m.kzitem.info/news/bejne/smqerGh5sHykeXo
@borjaveiga
4 жыл бұрын
@@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.
@Don-ec3vc
4 жыл бұрын
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.
@CurbalEN
4 жыл бұрын
Your measure uses the filter function in the background.
@Don-ec3vc
4 жыл бұрын
@@CurbalEN Aah I see, That clears things up. Thanks!
@CurbalEN
4 жыл бұрын
My pleasure :)
@lenzypetty9371
4 жыл бұрын
For the simple example of filtering for a dimension, Why not just use the basic calculate measure: measure = calculate ([total sales],color="red") ?
@Don-ec3vc
4 жыл бұрын
I was wondering the same. I learned to use FILTER() when evaluating expressions and use nothing when just evaluating a column value
@secanevasse
4 жыл бұрын
Hi everyone :) I don't understand why calculatetable or filter is needed. I normally use calculate(salesamount, product[colo]="Red")
@CurbalEN
4 жыл бұрын
Product = Red is converted to filter(product=red) , so you are using filter even if you dont see it.
@secanevasse
4 жыл бұрын
@@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.
@Victor-ol1lo
4 жыл бұрын
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.
@CurbalEN
4 жыл бұрын
Thaanks for sharing!
@nehachak
3 жыл бұрын
You explain the topics very clearly.
@CurbalEN
3 жыл бұрын
Wonderful to hear:)
@frankgovers8420
4 жыл бұрын
Filter should be called filterX, wahaaa, you said it at 4.55.
@anetak6014
4 жыл бұрын
And what about such syntax? CALCULATE( [Sales], 'Product'[Color] = "Red") ?
@CurbalEN
4 жыл бұрын
That is syntax sugar for: CALCULATE([Sales], FILTER(Product, Product[Color]="Red"))
@strophariacaerulea
4 жыл бұрын
@@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.
@CurbalEN
4 жыл бұрын
True! Forgot the ALL 😊
@b123kotey
10 ай бұрын
Anybody else in panic at this? Just me? F*^%
@psz34
Жыл бұрын
If you don't know the whole story why do you confuse people with you tube videos?
Пікірлер: 125