DAX Fridays! #8: CALCULATE (Part 2)

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

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

50% 1

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

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

6 окт 2016

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

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

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

@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

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

@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

@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

@NorbertoVeraReatigaNVR 7 лет назад

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

@CurbalEN 7 лет назад

+Norberto Vera Reatiga Muchas gracias Norberto!

@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

@vijaypalmanit 5 лет назад

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

@CurbalEN 5 лет назад

Music to my ears!!! /Ruth

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

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

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

Glad to hear!

@mohitasolanki2142 5 лет назад

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

@CurbalEN 5 лет назад

Thanks ! /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

@MrVishpatel2004 5 лет назад

Great session very useful I managed to resolve the issue.

@CurbalEN 5 лет назад

Glad it helped! /Ruth

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

Thanks a ton for such useful videos :)

@johanhernansanchezvillano5212 7 лет назад

Excellent channel congratulations CURBAL :)

@CurbalEN 7 лет назад

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

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

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

Excellent!

@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

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

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

@bradj229 4 года назад

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

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

@bhagisrivally Год назад

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

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

@srinivastanniru6974 6 лет назад

Super

@CurbalEN 6 лет назад

Excellent :) /Ruth

@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

@ernestogonzalez6868 7 лет назад

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

@GagandeepSingh-db8eh 3 года назад

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

@jahangiralam2946 4 года назад

Great

@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

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

@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.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-d-Jk1j2G3nw.html /Ruth

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

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-k3NMIlLffrU.html /Ruth

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

where can we find the files used in the video

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

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

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

How we do Filter with dynamic criteria

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

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

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

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

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

😂😂 The northwind dataset will do that to ya!

@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

@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

@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

@vakfunnyfactstelugu9531 4 года назад

Why the data was showing correctly for Year wise

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-SsZseKOgrWQ.html topn with rank: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z2qzJVeYhTY.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

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

"You will feel hungry watching this video"

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

Say cheeeeeese 😂

@dineshvenkat8746 5 лет назад

any update pls?

@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

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