DAX Fridays Battle

Подписаться 134 тыс.

Просмотров 21 тыс.

50% 1

It is time for another DAX Battle and according to your request, it is FILTER vs CALCULATETABLE
Recommended videos to watch next: Vertipaq playlist that includes the video mentioned in this video:
• Vertipaq Engine explai...
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE

Опубликовано:

3 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 125

@datamaniacs3749 3 года назад

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 3 года назад

Oh wow! That is a big improvement 👏👏

@davidcadman3562 3 года назад

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 3 года назад

You should be! I do the opposite!

@sansden 2 года назад

Just love the simplicity you explain the complex subtle DAX ...Thank you always Ruth :D

@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!

@joanneosborne2428 3 года назад

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 3 года назад

Same to you Joanne!

@juansepowerplatform 3 года назад

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 3 года назад

I do the same, old habits die hard!!

@HachiAdachi 3 года назад

🤯 omg, WHAT? I'm simply blown away! I feel like I just took a red pill... Thank you, Ruth!

@CurbalEN 3 года назад

The DAX matrix 😂

@brunof.s.8186 3 года назад

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 3 года назад

Thanks for sharing!

@EricaDyson 3 года назад

I would never have discovered this without your help!

@CurbalEN 3 года назад

Pleasure to spread the knowledge!

@sue_bayes 3 года назад

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 3 года назад

Thanks Susan!

@michaelmay5032 3 года назад

Thank you, Ruth! I learned something, again!

@CurbalEN 3 года назад

🥳🥳 Wondeful and happy Friday!

@angelgarciadatablog 10 месяцев назад

I love your explication!!!

@dannyhometown 3 года назад

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 3 года назад

Same to you Danny!

@abhishekstatus_7 3 года назад

Thanks for uploading the video Ruth!! Happy Friday and great weekend ahead !!

@CurbalEN 3 года назад

Same to you!!

@abhishekstatus_7 3 года назад

@@CurbalEN You are always welcome !!!

@Leonardo-jv1ls 2 года назад

Thanks a lot for your videos Ruth. You rule.

@CurbalEN 2 года назад

Thanks!

@mnowako 3 года назад

I have been waiting for this for years. 👍 👍👍 Many thanks. Ruth, you are the Queen of DAX 😉

@CurbalEN 3 года назад

I hope it was worth the wait!

@mnowako 3 года назад

@@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 3 года назад

Oh wow!! That is fantastic , well done!! 👏👏👏

@powerbiisrael6819 3 года назад

You are the best Ruth !

@CurbalEN 3 года назад

Thanks!

@vitorklein2559 3 года назад

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 3 года назад

It would be great if they change the name, but a mess too... Happy Friday!

@stevemorris4938 3 года назад

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 3 года назад

Soooo happy to hear ! 😊😊

@mohammadrezaei4221 3 года назад

That was my problem,Thanks

@salvatorescofano6756 3 года назад

Congratulations TOP explanations !!!

@CurbalEN 3 года назад

Yey!!

@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 !

@kennethstephani692 3 года назад

Great video!!

@CurbalEN 3 года назад

Glad you enjoyed it

@Bharath_PBI 3 года назад

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 3 года назад

Agree, I should have said avoid, it use responsibly :)

@steventhehistorian 3 года назад

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 3 года назад

In some weird way, it makes sense! Let me try it again for coming videos :)

@soy34mb 3 года назад

Hi Ruth, great video, that was 🤯

@CurbalEN 3 года назад

Thanks! 😊

@lucappelmans9056 3 года назад

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 3 года назад

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...

@9zQx86LT 3 года назад

Loved it.. thank you. I will try to replace FILTER by CALCULATETABLE without breaking anything 😁

@CurbalEN 3 года назад

Let us know if it improves performance!

@Phoenixspin 3 года назад

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 3 года назад

I know! How crazy is that?? 😭😭

@Unbox747 3 года назад

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 3 года назад

Check the pinned comment, it has been done already :)

@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 3 года назад

Omg, I use it everywhere :( Thank you very much, I understand better right now why certain reports are so slow :)

@CurbalEN 3 года назад

Change it and let us know how it goes!

@JonathanCHANAL 3 года назад

@@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 3 года назад

Sounds like a good plan 😊

@s77lom49 11 месяцев назад

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) ?

@copinhenry 3 года назад

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 3 года назад

Check the comments, people are getting crazy performance improvements!

@AlbertoGastaldo 3 года назад

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 3 года назад

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!

@akuku5555 3 года назад

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 3 года назад

👏👏👏👏 Bravo!!!

@desenterrados2022 3 года назад

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 3 года назад

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'

@suryasahoo3771 3 года назад

Thanks Ruth, Can you please help me to know: How to use multi column filter using dax measure.

@Bharath_PBI 3 года назад

Use of logical operators

@suryasahoo3771 3 года назад

@@Bharath_PBI community.powerbi.com/t5/Desktop/Stock-Calculation/m-p/1433311/highlight/true#M603249

@nehachak 3 года назад

You explain the topics very clearly.

@CurbalEN 3 года назад

Wonderful to hear:)

@Maartenmarsje 3 года назад

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 3 года назад

I know! Fridays is not the best day for these type of videos! For the case you mention, try using KEEPFILTERS /Ruth

@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 :)

@elrevesyelderecho 3 года назад

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 3 года назад

Everything is possible as long as: 1. It gives you the correct result 2. Doesnt slow down your model imo Happy Friday!

@Victor-ol1lo 3 года назад

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 3 года назад

Thaanks for sharing!

@borjaveiga 3 года назад

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 3 года назад

I have a video that does something similar but with currencies. Ha e you seen it?

@borjaveiga 3 года назад

@@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 3 года назад

Thanks! Maybe this gives you ideas: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Q4hF4CwJsFE.html

@borjaveiga 3 года назад

@@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.

@Pierre1O Год назад

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 Год назад

Thanks for the feedback!!

@lenzypetty9371 3 года назад

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 3 года назад

I was wondering the same. I learned to use FILTER() when evaluating expressions and use nothing when just evaluating a column value

@geoffrichardson7013 3 года назад

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 3 года назад

Is it ?? OMG, thanks!

@geoffrichardson7013 3 года назад

@@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 3 года назад

Oh, I see... it could be, I actually don't know. Have you asked in the power bi community?

@qaz56q 3 года назад

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 3 года назад

Thanks! Added to my list :)

@cg24356 3 года назад

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 3 года назад

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

@secanevasse 3 года назад

Hi everyone :) I don't understand why calculatetable or filter is needed. I normally use calculate(salesamount, product[colo]="Red")

@CurbalEN 3 года назад

Product = Red is converted to filter(product=red) , so you are using filter even if you dont see it.

@secanevasse 3 года назад

@@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.

@Don-ec3vc 3 года назад

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 3 года назад

Your measure uses the filter function in the background.

@Don-ec3vc 3 года назад

@@CurbalEN Aah I see, That clears things up. Thanks!

@CurbalEN 3 года назад

My pleasure :)

@frankgovers8420 3 года назад

Filter should be called filterX, wahaaa, you said it at 4.55.

@anetak6014 3 года назад

And what about such syntax? CALCULATE( [Sales], 'Product'[Color] = "Red") ?

@CurbalEN 3 года назад

That is syntax sugar for: CALCULATE([Sales], FILTER(Product, Product[Color]="Red"))

@strophariacaerulea 3 года назад

@@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 3 года назад

True! Forgot the ALL 😊