DAX Fridays! #8: CALCULATE (Part 2)
ฝัง
- เผยแพร่เมื่อ 6 ต.ค. 2016
- 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
![ภาพนี้ก็ฮาเหมือนกันนะเนี้ย #2 SS8 [ พากย์นรก MEME.EXE ] | easy boy](http://i.ytimg.com/vi/RMhUgxxD49Q/mqdefault.jpg)
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.
+shashindu lakshitha Many thanks for the comment, I really appreciate it!
/Ruth
Excellent, I have been studying the the CALCULATE function all week and you validated my understanding of it. Thank you so much
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!
+Gustavo Bueno You welcome Gustavo!! Really happy to help 😀,
/Ruth
Great videos, I am building up my DAX knowledge thanks to these. Thank you for continuing this series!
My pleasure and glad they are useful :)
Million Thanks... You saved my time and hard analysis. Your videos are meaningful. Thanks.👍👍
Hola Ruth! I love your constant ads for Queso Cabrales :-D (and your videos). Thanks for your work!
They should sponsor me right?
/Ruth
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!
Thank You! It's amazing! You've helped me a lot!
+Mariusz Nowakowski You welcome!!! Really glad it helped you :)
/Ruth
Thank you so much, finally I am able to understand both calculated and filter.
Music to my ears!!!
/Ruth
Hola Ruth!!... Muchas gracias, excelente explicación.
+Norberto Vera Reatiga Muchas gracias Norberto!
Hi, your videos are great. I am learning a lot from them and on my pace .
Glad to hear!
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.
Thanks and I hope it works for you in practice :)
/Ruth
Thanks a ton for such useful videos :)
Excellent video.. very helpful.. like the way you make things look simple :)
Thanks !
/Ruth
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 !! _/\_
Thanks! And I hope you enjoy my other videos too!
/Ruth
Excellent channel congratulations CURBAL :)
+johan Hernan Sanchez Villano Thanks Johan! Glad you like it and have a great weekend :)
/Ruth
Great session very useful I managed to resolve the issue.
Glad it helped! /Ruth
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?
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.
+KeithA A yes, it is due now, isn’t it?I need to find the time to do it.
/Ruth
Excellent!
Great, helped me solve a big problem!
Lovely to hear!
Excellent mam
Nice deep dive
Thanks!
/Ruth
Hi Ruth, what it make any difference If I use keepfilters.
hi ruth.. Im getting same type of values in cal with 2 cond and cal with filter
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 :)
Agree :)
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?
+narianu0 Hi narianu, absolutely. You will see in future videos more uses of the calculate function. Stay tuned!
/Ruth
The video is great, do you have any course about DAX formula?
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?
i hope for you health and wellness
Thanks Walid, same to and your family!
/Ruth
Great
hola, como se calcula el importe total de cada pedido teniendo en cuenta tambien el precio del flete (Orders.Freight)?
Gracias
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?
where can we find the files used in the video
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...
Super
Excellent :)
/Ruth
hi mam,how to calculate last 12 month LTM in dax please help me out
How we do Filter with dynamic criteria
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
Hi! Could you post your question on the power bi community? It is a great place for support!
/Ruth
ALL THE CHEESES! I'm starving now...
😂😂 The northwind dataset will do that to ya!
Could you provide Calculate 2 curbal .xlsx. thanks from cabrales area
+David Fombella Pombal Haciendo patria!!
You can access the dataset yourself for free. Here is how:
Northwind dataset: th-cam.com/video/k3NMIlLffrU/w-d-xo.html
/Ruth
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! :)
Have you tried SEARCH or FIND:
curbal.com/blog/glossary/search-dax
Or
curbal.com/blog/glossary/find-dax
/Ruth
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.
+Reggie Revello Hi Reggie,
Please check this video, i think it will help you:
m.th-cam.com/video/d-Jk1j2G3nw/w-d-xo.html
/Ruth
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!
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
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!
Why the data was showing correctly for Year wise
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
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?
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?
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.
+Roland Kc Yes, you have ROUND() , ROUNDUP and ROUNDDOWN() I think, pulling from memory..
/Ruth
+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
Hi Ruth, Im kind of new in DAX, what does it means the 2 "||" in you calculate filter ?
Cheers.
OR :)
@@CurbalEN sorry, me again, what about the AND ? Is there some symbol to represent this ?
Yes &&
@@CurbalEN excelente, thank you so much!
any update pls?
hi i need urgent help on to create simple profit and loss table in power bi please
kindly can you help me to create simple profit and loss calculation by row wise in power bi and data is exported from excel
hi - can I get the .pbix file for this and the other vids you have created ? thank you.
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
thank you !
+joel .b Thanks to you for watching! :)
/Ruth
hello,
i just want to know about rankx and topn in details
Hi Nums,
I have two videos about rank here:
th-cam.com/video/SsZseKOgrWQ/w-d-xo.html
topn with rank:
th-cam.com/video/z2qzJVeYhTY/w-d-xo.html
Hope this helps,
/Ruth
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
+Nums Solanki You mean that you have blanks in your query and they appear in your top 10 list ?
/Ruth
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..
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
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
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])
Measure 3 = CALCULATE(COUNTA(FactAttendanceDaily[AttendanceDate].[Date]),FILTER(FactAttendanceDaily,FactAttendanceDaily[SchoolYear]="2017"))
This is not working even not showing any error! :(
Post in the power bi community with sample data so you can get specific help!
/Ruth
"You will feel hungry watching this video"
Say cheeeeeese 😂
you will get hungry watching this video hahahaahahahahahaha
😂😂😂
Your voice is not clear and too slow
Yeah! Was doing this videos at midnight back then. Hopefully the learnings are still there!
Bad example
Roger that!