DAX Fridays! #8: CALCULATE (Part 2)
Рет қаралды 53,961
7 жыл бұрынThe calculate function is the most useful measure available in DAX. Let's go through how it works in this part 2!
Calculate Part 1: • DAX Fridays! #5: CALCU...
Download Power BI file:curbal.com/blog/glossary/calc...
PREVIOUS VIDEO: • DAX Fridays! #7: ALL, ...
NEXT VIDEO: • DAX Fridays! #9: RANKX...
Looking for a download file? Go to our Download Center: 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
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
► Linkedin: goo.gl/3VW6Ky
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP
@shashindulakshitha3789 7 жыл бұрын
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 7 жыл бұрын
+shashindu lakshitha Many thanks for the comment, I really appreciate it! /Ruth
@MichaelBrown-lw9kz 3 жыл бұрын
Excellent, I have been studying the the CALCULATE function all week and you validated my understanding of it. Thank you so much
@gustavotpb 7 жыл бұрын
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 7 жыл бұрын
+Gustavo Bueno You welcome Gustavo!! Really happy to help 😀, /Ruth
@1989Bismillah 5 жыл бұрын
Great videos, I am building up my DAX knowledge thanks to these. Thank you for continuing this series!
@CurbalEN 5 жыл бұрын
My pleasure and glad they are useful :)
@yugandharb4461 4 жыл бұрын
Million Thanks... You saved my time and hard analysis. Your videos are meaningful. Thanks.👍👍
@davidr.7904 4 жыл бұрын
Hola Ruth! I love your constant ads for Queso Cabrales :-D (and your videos). Thanks for your work!
@CurbalEN 4 жыл бұрын
They should sponsor me right? /Ruth
@mnowako 7 жыл бұрын
Thank You! It's amazing! You've helped me a lot!
@CurbalEN 7 жыл бұрын
+Mariusz Nowakowski You welcome!!! Really glad it helped you :) /Ruth
@vijaypalmanit 5 жыл бұрын
Thank you so much, finally I am able to understand both calculated and filter.
@CurbalEN 5 жыл бұрын
Music to my ears!!! /Ruth
@tymothylim6550 4 жыл бұрын
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!
@eriol33 5 жыл бұрын
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 5 жыл бұрын
Thanks and I hope it works for you in practice :) /Ruth
@maryamseidnejad3280 3 жыл бұрын
Hi, your videos are great. I am learning a lot from them and on my pace .
@CurbalEN 3 жыл бұрын
Glad to hear!
@NorbertoVeraReatigaNVR 7 жыл бұрын
Hola Ruth!!... Muchas gracias, excelente explicación.
@CurbalEN 7 жыл бұрын
+Norberto Vera Reatiga Muchas gracias Norberto!
@soumikmishra7288 3 жыл бұрын
Thanks a ton for such useful videos :)
@mohitasolanki2142 5 жыл бұрын
Excellent video.. very helpful.. like the way you make things look simple :)
@CurbalEN 5 жыл бұрын
Thanks ! /Ruth
@MrVishpatel2004 5 жыл бұрын
Great session very useful I managed to resolve the issue.
@CurbalEN 5 жыл бұрын
Glad it helped! /Ruth
@johanhernansanchezvillano5212 7 жыл бұрын
Excellent channel congratulations CURBAL :)
@CurbalEN 7 жыл бұрын
+johan Hernan Sanchez Villano Thanks Johan! Glad you like it and have a great weekend :) /Ruth
@deepakkanade5041 5 жыл бұрын
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 5 жыл бұрын
Thanks! And I hope you enjoy my other videos too! /Ruth
@susannesenger8705 Жыл бұрын
Great, helped me solve a big problem!
@CurbalEN Жыл бұрын
Lovely to hear!
@macm7730 3 жыл бұрын
Excellent!
@Rafian1924 4 жыл бұрын
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?
@keithaa7444 6 жыл бұрын
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 6 жыл бұрын
+KeithA A yes, it is due now, isn’t it?I need to find the time to do it. /Ruth
@srinivastanniru6974 6 жыл бұрын
Excellent mam
@bobert13581 4 жыл бұрын
Nice deep dive
@CurbalEN 4 жыл бұрын
Thanks! /Ruth
@bhagisrivally Жыл бұрын
hi ruth.. Im getting same type of values in cal with 2 cond and cal with filter
@GagandeepSingh-db8eh 3 жыл бұрын
Hi Ruth, what it make any difference If I use keepfilters.
@ernestogonzalez6868 7 жыл бұрын
hola, como se calcula el importe total de cada pedido teniendo en cuenta tambien el precio del flete (Orders.Freight)? Gracias
@bradj229 3 жыл бұрын
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 3 жыл бұрын
Agree :)
@narianu0 7 жыл бұрын
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 7 жыл бұрын
+narianu0 Hi narianu, absolutely. You will see in future videos more uses of the calculate function. Stay tuned! /Ruth
@walidkhlil2872 4 жыл бұрын
i hope for you health and wellness
@CurbalEN 4 жыл бұрын
Thanks Walid, same to and your family! /Ruth
@tigerdataimarticus7088 2 жыл бұрын
How we do Filter with dynamic criteria
@jn7071 3 жыл бұрын
The video is great, do you have any course about DAX formula?
@shohruxislomov5548 2 жыл бұрын
where can we find the files used in the video
@Dipakbohora 4 жыл бұрын
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?
@suvadipkundu152 5 жыл бұрын
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 5 жыл бұрын
Hi! Could you post your question on the power bi community? It is a great place for support! /Ruth
@Reyu181 3 жыл бұрын
hi mam,how to calculate last 12 month LTM in dax please help me out
@4evaism 3 жыл бұрын
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?
@jahangiralam2946 4 жыл бұрын
Great
@BigDataSport 7 жыл бұрын
Could you provide Calculate 2 curbal .xlsx. thanks from cabrales area
@CurbalEN 7 жыл бұрын
+David Fombella Pombal Haciendo patria!! You can access the dataset yourself for free. Here is how: Northwind dataset: kzfaq.info/get/bejne/oZl-gKydsczKook.html /Ruth
@srinivastanniru6974 6 жыл бұрын
Super
@CurbalEN 6 жыл бұрын
Excellent :) /Ruth
@karannchew2534 2 жыл бұрын
"You will feel hungry watching this video"
@CurbalEN 2 жыл бұрын
Say cheeeeeese 😂
@jimmorrison4291 2 жыл бұрын
ALL THE CHEESES! I'm starving now...
@CurbalEN 2 жыл бұрын
😂😂 The northwind dataset will do that to ya!
@vakfunnyfactstelugu9531 4 жыл бұрын
Why the data was showing correctly for Year wise
@raphaellebeau6368 6 жыл бұрын
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 6 жыл бұрын
Have you tried SEARCH or FIND: curbal.com/blog/glossary/search-dax Or curbal.com/blog/glossary/find-dax /Ruth
@dineshvenkat8746 5 жыл бұрын
any update pls?
@reggierevello21 7 жыл бұрын
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 7 жыл бұрын
+Reggie Revello Hi Reggie, Please check this video, i think it will help you: m.kzfaq.info/get/bejne/mpN6npSbl62Xnqs.html /Ruth
@yiubili1 4 жыл бұрын
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...
@hnp250 4 жыл бұрын
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 4 жыл бұрын
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 4 жыл бұрын
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!
@rolandkc 7 жыл бұрын
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 7 жыл бұрын
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 7 жыл бұрын
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 7 жыл бұрын
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 7 жыл бұрын
+Roland Kc Yes, you have ROUND() , ROUNDUP and ROUNDDOWN() I think, pulling from memory.. /Ruth
@CurbalEN 7 жыл бұрын
+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
@dineshvenkat8746 5 жыл бұрын
hi i need urgent help on to create simple profit and loss table in power bi please
@dineshvenkat8746 5 жыл бұрын
kindly can you help me to create simple profit and loss calculation by row wise in power bi and data is exported from excel
@CarlosEliezerHernandezPintor 3 жыл бұрын
Hi Ruth, Im kind of new in DAX, what does it means the 2 "||" in you calculate filter ? Cheers.
@CurbalEN 3 жыл бұрын
OR :)
@CarlosEliezerHernandezPintor 3 жыл бұрын
@@CurbalEN sorry, me again, what about the AND ? Is there some symbol to represent this ?
@CurbalEN 3 жыл бұрын
Yes &&
@CarlosEliezerHernandezPintor 3 жыл бұрын
@@CurbalEN excelente, thank you so much!
@TheJoel8787 7 жыл бұрын
hi - can I get the .pbix file for this and the other vids you have created ? thank you.
@CurbalEN 7 жыл бұрын
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 7 жыл бұрын
thank you !
@CurbalEN 7 жыл бұрын
+joel .b Thanks to you for watching! :) /Ruth
@nupursolanki9931 7 жыл бұрын
hello, i just want to know about rankx and topn in details
@CurbalEN 7 жыл бұрын
Hi Nums, I have two videos about rank here: kzfaq.info/get/bejne/idmKpsh8tM3Wh4U.html topn with rank: kzfaq.info/get/bejne/sJihra2Hyr_MhI0.html Hope this helps, /Ruth
@nupursolanki9931 7 жыл бұрын
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 7 жыл бұрын
+Nums Solanki You mean that you have blanks in your query and they appear in your top 10 list ? /Ruth
@nupursolanki9931 7 жыл бұрын
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 7 жыл бұрын
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
@260sai 4 жыл бұрын
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 4 жыл бұрын
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])
@tejeshgour1919 6 жыл бұрын
Measure 3 = CALCULATE(COUNTA(FactAttendanceDaily[AttendanceDate].[Date]),FILTER(FactAttendanceDaily,FactAttendanceDaily[SchoolYear]="2017")) This is not working even not showing any error! :(
@CurbalEN 6 жыл бұрын
Post in the power bi community with sample data so you can get specific help! /Ruth
@SSM_33 4 жыл бұрын
you will get hungry watching this video hahahaahahahahahaha
@CurbalEN 4 жыл бұрын
😂😂😂
@user-nb3pi3et9p 7 ай бұрын
Your voice is not clear and too slow
@CurbalEN 7 ай бұрын
Yeah! Was doing this videos at midnight back then. Hopefully the learnings are still there!
@anphamhoai7531 3 жыл бұрын
Bad example
@CurbalEN 3 жыл бұрын
Roger that!
TheSoul Music Jam
Рет қаралды 23 МЛН
Français avec Nelly
Рет қаралды 10 М.