DAX Fridays! #8: CALCULATE (Part 2)

[@shashindulakshitha3789]

Thank you very much Curbal. Your channel is one of the best channels for someone to learn on PowerBI and DAX. Hoping to see your channel with more awesome videos.

[@CurbalEN]

+shashindu lakshitha Many thanks for the comment, I really appreciate it! /Ruth

[@MichaelBrown-lw9kz]

Excellent, I have been studying the the CALCULATE function all week and you validated my understanding of it. Thank you so much

[@1989Bismillah]

Great videos, I am building up my DAX knowledge thanks to these. Thank you for continuing this series!

[@CurbalEN]

My pleasure and glad they are useful :)

[@gustavotpb]

Thank you very much, with videos 5,6 and 8 of the series "DAX Fridays!" are perfect. You're great! Thank you, thank you and thank you!

[@CurbalEN]

+Gustavo Bueno You welcome Gustavo!! Really happy to help 😀, /Ruth

[@davidr.7904]

Hola Ruth! I love your constant ads for Queso Cabrales :-D (and your videos). Thanks for your work!

[@CurbalEN]

They should sponsor me right? /Ruth

[@yugandharb4461]

Million Thanks... You saved my time and hard analysis. Your videos are meaningful. Thanks.👍👍

[@maryamseidnejad3280]

Hi, your videos are great. I am learning a lot from them and on my pace .

[@CurbalEN]

Glad to hear!

[@deepakkanade5041]

Hey Ruth. Both the parts of Calculate() are just WOW !!. Your way of explanation is very simplistic. I could very easily understand all the functions from #2 through #8 and could implement it in my test models, as the test data I am using is different from yours. Looking forward to going through all the further videos. These all will be equally elaborate and educative, I am sure. Thanks a lot !! _/\_

[@CurbalEN]

Thanks! And I hope you enjoy my other videos too! /Ruth

[@eriol33]

this is a very good introduction video. I have been trying to understand calculate for a while. I am going to try this on my work soon.

[@CurbalEN]

Thanks and I hope it works for you in practice :) /Ruth

[@mohitasolanki2142]

Excellent video.. very helpful.. like the way you make things look simple :)

[@CurbalEN]

Thanks ! /Ruth

[@MrVishpatel2004]

Great session very useful I managed to resolve the issue.

[@CurbalEN]

Glad it helped! /Ruth

[@tymothylim6550]

Hello! Thank you for another video :) This video was really helpful. I like how you used Dax Studio to show us the table produced by Filter(). Thank you again for the help!

[@shohruxislomov5548]

where can we find the files used in the video

[@jn7071]

The video is great, do you have any course about DAX formula?

[@NorbertoVeraReatigaNVR]

Hola Ruth!!... Muchas gracias, excelente explicación.

[@CurbalEN]

+Norberto Vera Reatiga Muchas gracias Norberto!

[@mnowako]

Thank You! It's amazing! You've helped me a lot!

[@CurbalEN]

+Mariusz Nowakowski You welcome!!! Really glad it helped you :) /Ruth

[@keithaa7444]

I would love to see Calculate #3 and #4... Perhaps how it helps with Filter vs ROW context, how it works with other measures and some advanced queries.

[@CurbalEN]

+KeithA A yes, it is due now, isn’t it?I need to find the time to do it. /Ruth

[@Rafian1924]

Thanks a lot, you helped us to skill up and learn more. Could you please make a video on a dashboard which covers many important dax functions and help us to give the handson on real project?

[@soumikmishra7288]

Thanks a ton for such useful videos :)

[@vijaypalmanit]

Thank you so much, finally I am able to understand both calculated and filter.

[@CurbalEN]

Music to my ears!!! /Ruth

[@bhagisrivally]

hi ruth.. Im getting same type of values in cal with 2 cond and cal with filter

[@johanhernansanchezvillano5212]

Excellent channel congratulations CURBAL :)

[@CurbalEN]

+johan Hernan Sanchez Villano Thanks Johan! Glad you like it and have a great weekend :) /Ruth

[@susannesenger8705]

Great, helped me solve a big problem!

[@CurbalEN]

Lovely to hear!

[@Dipakbohora]

To filter the report by calendar, is it necessary to have calendar in separate sheets or we can filter it from single sheet? I mean If I want to view the report by Year/Months, is it necessary to have the calendar in separate sheet or we can filter it from the same sheet where Sales Figure & Calendar are available in the same sheet?

[@GagandeepSingh-db8eh]

Hi Ruth, what it make any difference If I use keepfilters.

[@narianu0]

The video is very helpful to understand the basics of the CALCULATE function but I'd like to see more specific real-world examples of how you'd use it. In other words, what would be some analysis that you can only do with the calculate function?

[@CurbalEN]

+narianu0 Hi narianu, absolutely. You will see in future videos more uses of the calculate function. Stay tuned! /Ruth

[@4evaism]

I'm watching this 11:42 PM, and I can't really wrap my head around the FILTER function used as [filter] argument of the CALCULATE function. Does the behavior have anything to do with the relationship between FilteredProductsTable and Order_Detail?

[@raphaellebeau6368]

I am trying to calculate if the text contains some characters. any trick to do this? Thank you for your videos they are quite helpful! :)

[@CurbalEN]

Have you tried SEARCH or FIND: curbal.com/blog/glossary/search-dax Or curbal.com/blog/glossary/find-dax /Ruth

[@suvadipkundu152]

Hi Ruth, I have come across a scenario where DATEADD function has been used within Calculate (as a filter of sorts) to arrive at past period values (e.g. returns)... the syntax goes as below Prev_mnth_retrun_Qty = CALCULATE(AW_Returns[Return_qty],DATEADD(AW_Calendar_Lookup[start of month],-1,MONTH)) When I use this measure against start of month (a column in AW_Calendar_Lookup table created in the ETL stage). The measure dutifully shows the cumulative returns for the past month against each start of month row context. My challenge is that I am unable to follow thru' the calculation order/filter context, if you can explain or guide me to a resource which can help me clarify my doubts, I will be much obliged. Cheers and a belated wish for a great year ahead. suvadip

[@CurbalEN]

Hi! Could you post your question on the power bi community? It is a great place for support! /Ruth

[@ernestogonzalez6868]

hola, como se calcula el importe total de cada pedido teniendo en cuenta tambien el precio del flete (Orders.Freight)? Gracias

[@BigDataSport]

Could you provide Calculate 2 curbal .xlsx. thanks from cabrales area

[@CurbalEN]

+David Fombella Pombal Haciendo patria!! You can access the dataset yourself for free. Here is how: Northwind dataset: kzbin.info/www/bejne/oWSxfnyigcuZqLc /Ruth

[@reggierevello21]

Thanks for this great tutorial on Calculate. I need some help as I'm very new to DAX. For example, I have 5 salesman, Salesman1, Salesman2 and so on. Each of them has sales by day. I need to get the cummulate sales by salesman as of today. I'm doing something wrong. I can´t get the correct result. My measure is: fCummSales = CALCULATE(sum('Table1 (2)'[Sales]),FILTER(ALL('Table1 (2)'), 'Table1 (2)'[Salesman]])) Table Name is "Table1(2)" Sales column is "Sales" Salesman name column is "Salesman" Thanks in advance. Reggie.

[@CurbalEN]

+Reggie Revello Hi Reggie, Please check this video, i think it will help you: m.kzbin.info/www/bejne/ml6tnGSgZ6xmpNk /Ruth

[@CarlosEliezerHernandezPintor]

Hi Ruth, Im kind of new in DAX, what does it means the 2 "||" in you calculate filter ? Cheers.

[@CurbalEN]

OR :)

[@CarlosEliezerHernandezPintor]

@@CurbalEN sorry, me again, what about the AND ? Is there some symbol to represent this ?

[@CurbalEN]

Yes &&

[@CarlosEliezerHernandezPintor]

@@CurbalEN excelente, thank you so much!

[@bobert13581]

Nice deep dive

[@CurbalEN]

Thanks! /Ruth

[@hnp250]

Excellent video!! Question for Curbal. Lately I have discovered that I can use CALCULATE also in this manner and it works: CALCULATE( Table1[ Measure1 ] , Table2 , Table3 ...) Could you please explain this behavior? Thank you very much!

[@CurbalEN]

Hi! You can always write table[measure], but it is a best practice not to do it so it doesn’t get confused with a calculated column. Hope this explains it :) /Ruth

[@hnp250]

Hi Ruth. Thanks for the answer! My example wasn`t very clear i guess ha ha... Actually what i was trying to show is this: CALCULATE( [Measure] , Table ) or CALCULATE( [Measure] , Table1 , Table2 ) where a whole Table or Tables are used as filter arguments instead of using columns conditions. I'm comfused about what CALCULATE does when i use just a Table as a filter condition. Thank you very much!

[@Reyu181]

hi mam,how to calculate last 12 month LTM in dax please help me out

[@vakfunnyfactstelugu9531]

Why the data was showing correctly for Year wise

[@macm7730]

Excellent!

[@rolandkc]

This video is definitely more challenging than the first one on CALCULATE. I have a no of questions which I will put in separate comments

[@rolandkc]

In CAL w 2 cond, we have 7301+6912=14213 and 3466+4384=7850 but results show 7849, can you, in DAX, use rounding so that we do not have rounding issues on reports?

[@rolandkc]

Quest 2: is it possible in the example with 3 measures - CAL w cond, CAL w 2cond, CAL w ALL 'filter' [without using the function FILTER] - to show subtotals for year 1997 and 1998?

[@rolandkc]

Quest 3: with the measure fCAL w ALL FILTER [this time with the FILTER function], I do not understand that you have used ALL FILTER when you are referencing 2 products [2 cheeses]. This is confusing for me. Can you elaborate in a different way so that I can understand this. For me, intuitively when you refer to ALL FILTER, it means do NOT apply filter at ALL, by this I understand that there is NO filter being applied and that therefore ALL products are included in the calculation.

[@CurbalEN]

+Roland Kc Yes, you have ROUND() , ROUNDUP and ROUNDDOWN() I think, pulling from memory.. /Ruth

[@CurbalEN]

+Roland Kc Hi again, the CALCULATE vídeos where one the first videos I did. I have been asked since then to make another one explaining more about it, so I will compile all your questions and make a new video as soon as time allows. You are going to do well with Power BI 😄 /Ruth

[@srinivastanniru6974]

Excellent mam

[@bradj229]

Thanks for explaining why the Year filter still applied, but the ProductName didn't. Calculate is pretty cool overall because it allows you to use Filters :)

[@CurbalEN]

Agree :)

[@TheJoel8787]

hi - can I get the .pbix file for this and the other vids you have created ? thank you.

[@CurbalEN]

Hi Joel, I have now added the files for download on the description box, thanks for letting me know. If you find other videos with missing files, let me know, /Ruth

[@TheJoel8787]

thank you !

[@CurbalEN]

+joel .b Thanks to you for watching! :) /Ruth

[@dineshvenkat8746]

hi i need urgent help on to create simple profit and loss table in power bi please

[@dineshvenkat8746]

kindly can you help me to create simple profit and loss calculation by row wise in power bi and data is exported from excel

[@nupursolanki9931]

hello, i just want to know about rankx and topn in details

[@CurbalEN]

Hi Nums, I have two videos about rank here: kzbin.info/www/bejne/iaS9pJiBhMyljbM topn with rank: kzbin.info/www/bejne/sGPUq32Mmr6birs Hope this helps, /Ruth

[@nupursolanki9931]

i have seen this video i have one query on cllicking top10 we get top 10 records and other are blank ,can i get only top10 record by clicking top10

[@CurbalEN]

+Nums Solanki You mean that you have blanks in your query and they appear in your top 10 list ? /Ruth

[@nupursolanki9931]

No, i mean that on clicking top10 we get 10 record by rank and other rank sales are blank i dnt want that blank record when i click on top10 just top10 rank sales etc..

[@CurbalEN]

To be able to help you, I need a sample file. Please send me an email here: curbal.com/contact so I can give you my email address. /Ruth

[@dineshvenkat8746]

any update pls?

[@yiubili1]

that topics is very very complicated, despite it is quite clear with the table provide, but i am sure that i still cannot understand it quite well...maybe only understand 40% or less....Anyway, thanks for your video, at least i got sth that i could refer to, when i meet the filter and calculate issue next time...

[@walidkhlil2872]

i hope for you health and wellness

[@CurbalEN]

Thanks Walid, same to and your family! /Ruth

[@jimmorrison4291]

ALL THE CHEESES! I'm starving now...

[@CurbalEN]

😂😂 The northwind dataset will do that to ya!

[@260sai]

hai Total sales = SUMX(Purchase,[Price]*[Quantity]) Sales for product = CALCULATE(SUMX(Purchase,[Price]*[Quantity])) why i am getting different results even though formula looks same. please don't answer like please post on power bi blog

[@HarshNathani]

Not sure why you are getting different values. Try the below measures in the pbix file provided. You should get the same values when you drag ProductName from Product Table and these 2 below measures. Total_Sales_Cal = CALCULATE(SUMX(Order_Details, Order_Details[UnitPrice]*(1-Order_Details[Discount])*Order_Details[Quantity])) Total_Sale_SumX = SUMX(Order_Details, Order_Details[UnitPrice]*(1-Order_Details[Discount])*Order_Details[Quantity])

[@karannchew2534]

"You will feel hungry watching this video"

[@CurbalEN]

Say cheeeeeese 😂

[@srinivastanniru6974]

Super

[@CurbalEN]

Excellent :) /Ruth

[@tejeshgour1919]

Measure 3 = CALCULATE(COUNTA(FactAttendanceDaily[AttendanceDate].[Date]),FILTER(FactAttendanceDaily,FactAttendanceDaily[SchoolYear]="2017")) This is not working even not showing any error! :(

[@CurbalEN]

Post in the power bi community with sample data so you can get specific help! /Ruth

[@jahangiralam2946]

Great

[@SSM_33]

you will get hungry watching this video hahahaahahahahahaha

[@CurbalEN]

😂😂😂

[@AmnaVlogs1231]

Your voice is not clear and too slow

[@CurbalEN]

Yeah! Was doing this videos at midnight back then. Hopefully the learnings are still there!

[@anphamhoai7531]

Bad example

[@CurbalEN]

Roger that!