Power BI DAX Demystified: Mastering the CALCULATE Function + All, AllExcepts, RemoveFilters & more

Рет қаралды 12,814

Күн бұрын

Easy to understand explanation to master the most powerful and the most important DAX CALCULATE function with simple examples.
If you have attempted to learn how to use Calculate, but still feeling confused…
Don't worry.. You are not alone.
This Calculate video tutorial will help you to understand the key concepts in simple english.
I will explain the two most important concept that you need to grasp in order to build a strong foundation that will help to boost your future confidence in using CALCULATE and writing your own CALCULATE function from scratch, instead of blindly copying and pasting from Google.
I will also explain a fundamental concept of FILTER CONTEXT for those of you who are still relatively new to Power BI, as this is an important concept that you will need understand, in order to avoid unexpected surprises in the future.
There will be plenty of examples of CALCULATE in actions... starting from the very simple use of CALCULATE without any filter criteria... and then building on by adding 1 criteria, multiple criteria, as well as using various combinations of other DAX formulas such as: OR criteria, RemoveFilters, All and AllExcept.
I will also explain the short way vs the long way of writing DAX filter criteria, which will give you the same outcome.
My sincere hope is that by the end of this video, you will be more familiar and more confident in using the CALCULATE function in Power BI.
Cheers,
Isabella Susilowati
#LighthouseAnalytix
#PowerBITutorialForBeginners
P.S. Next week I will post video on how we can use the CALCULATE function in Power BI to build a P&L report. More specifically how we use this function to report This Year P&L, Last Year P&L as well as YOY P&L movement.
Stay tune... and please do subscribe so that you don't miss out !!!
Timelines:
00:00 Introduction
01:47 Similarity with SUMIFS
03:03 Criteria in Calculate will override Filter Context
03:18 The meaning of FILTER CONTEXT
06:28 The meaning of OVERRIDE
06:41 Simple examples of Calculate
08:01 More complex examples of Calculate
10:13 Calculate with OR
11:08 Calculate with split lines
11:44 Calculate with RemoveFilters
13:04 Calculate with ALL
13:55 Calculate with AllExcept
15:14 Recap
15:35 The SHORT way vs the LONG way
LA48

Пікірлер: 66

@mohamedsalih1448 2 ай бұрын

Think someone is benefited out of this video even after 2 years of posting! I watched many videos to understand filter context and finally I arrived at the right place. Thanks a lot

@LighthouseAnalytix 2 ай бұрын

I am so happy to hear that you are able to get a better grasp of the filter context. All the best in your learning journey :)

@precioussimplemessagesofli3092 2 жыл бұрын

I could understand the calculate function better since its crystal clear explanation but I couldn't understand why this kind of excellent channel only have less than 1k subscribers. Guys please support it and I will recommend it to all my friends.

@LighthouseAnalytix 2 жыл бұрын

Aw... thank you for your very kind words...

@J.R.-yi2go 3 ай бұрын

Just subscribed! You are great!@@LighthouseAnalytix

@OrlandoCeleiro 27 күн бұрын

BEST explanation of calculate ever.

@LighthouseAnalytix 23 күн бұрын

Aw... thank you !!! You make me very happy reading this ... this morning.

@ralphs7711 4 ай бұрын

This is by far the most comprehensible and beginner-friendly tutorial I found online. Thank you!

@LighthouseAnalytix 4 ай бұрын

You're very welcome! So happy to hear that you find it useful

@henryfung2640 27 күн бұрын

This helps me a lot. That is the best explanation across the youtube I watched. Thank you so much.

@LighthouseAnalytix 23 күн бұрын

Glad it was helpful!

@snipelite94 9 ай бұрын

Well done, Isabella 👍😁 Using a comparison of filtered and measures tables, side by side, was the best visual explainer that I've seen yet! I've seen a number of CALCULATE vids from different content creators, but this was definitely the most accessible and visually satisfying and enlightening. Many Thanks

@LighthouseAnalytix 9 ай бұрын

Thank you! 😃Glad that you find it enlightening.

@jagans6409 22 күн бұрын

Wonderful video mam, I wish you live 200+ years. Thank you. God bless!! ❤

@LighthouseAnalytix 21 күн бұрын

Aw....So nice of you

@smallflyDE 4 ай бұрын

Most beginner friendly video for CALCULATE. Thanks! Also very nice that you explain ALL function in this context :-)

@LighthouseAnalytix 4 ай бұрын

Glad you liked it

@osoriomatucurane9511 5 ай бұрын

Sensacional tutorial on calculate superpowerful function and the filter context. Awesome break down and visual set up, turning the concept just easily digestable as taking breakfast in the morning. Bravo, Isabella, keep it up. Another way of dismytifying the concept is revealing what happens beyond the scrnes or under the hood, with the filter propagation from through the model relationship from tue dimension(lookup) tables, before the aggregation ou summarization of the filtered records takes places ou performed over the fact table). I really like the similarities with the sumifs() function and the superiority of calculate () over the sumifs() as given by its versatility, just making it all in one.

@LighthouseAnalytix 5 ай бұрын

Hi thanks for the feedback - really appreciate it.

@Adaaaaaaa. 5 ай бұрын

Thanks a lot for breaking it down this way!

@LighthouseAnalytix 5 ай бұрын

My pleasure!

@ankitpant439 3 ай бұрын

This was so easy to understand. Thank you!

@LighthouseAnalytix 3 ай бұрын

You're very welcome!

@YasirKhalid1 Жыл бұрын

Excellent! Thank you!

@LighthouseAnalytix Жыл бұрын

You are welcome :)

@sjpark6782 2 ай бұрын

I like your approach of starting with the DAX formular construct. Got the thoery right before the actual examples.

@LighthouseAnalytix 2 ай бұрын

thanks for the feedback😊

@schtefel Жыл бұрын

seriously, this explanation is sooo good! 😃

@LighthouseAnalytix Жыл бұрын

Thank you :) We aim to keep things simple

@kushis_world145 Ай бұрын

Truly amazing ❤ thanks a ton !!

@LighthouseAnalytix Ай бұрын

Glad you liked it!

@NaraMeerammaTrust 2 жыл бұрын

very good presentation including your explanation. I love this video.

@LighthouseAnalytix 2 жыл бұрын

Thank you!

@rajvenogopal9317 2 жыл бұрын

Simple & excellent explanation 👌

@LighthouseAnalytix 2 жыл бұрын

Thank you 🙂

@ajayraj-xg9wj 2 жыл бұрын

Very useful, clear and precise.. Would like to learn about the iteration functions too.. It would be very much helpful

@LighthouseAnalytix 2 жыл бұрын

Coming soon! :)

@sugtipssports9512 Жыл бұрын

Excellent

@LighthouseAnalytix Жыл бұрын

Thank you so 😊

@jrick003 9 ай бұрын

great!!

@LighthouseAnalytix 9 ай бұрын

thank you !!

@dadinghadisurya 2 жыл бұрын

great explanation!!

@LighthouseAnalytix 2 жыл бұрын

Thank you!

@johnknowledge4064 10 ай бұрын

Great Presentation. I have subscribed.

@LighthouseAnalytix 10 ай бұрын

Awesome, thank you!

@punchbuddies220 2 жыл бұрын

Isabella means beauty and brains!

@LighthouseAnalytix 2 жыл бұрын

Thank you for the compliment ! *Blushing* ^_^

@ahmedshalaby9343 10 күн бұрын

i love uuuuuuu great yt channel actually

@LighthouseAnalytix 8 күн бұрын

thank you :)

@softwine91 2 ай бұрын

BRAVO !!! The best video to understand calculate and filter context. Thank you ! I have one question, all is the same as removefilters, so in order to override any filter we can simply use all(), could we use the same things with removefilters ??

@LighthouseAnalytix 2 ай бұрын

you are welcome. and yes, remove filters and all will give you the same result. Performance wise, it is better to use remove filters :)

@Jack0112358 8 ай бұрын

Thank you, nice video after 1 full day wasted on Udemy.

@LighthouseAnalytix 7 ай бұрын

Hope you finally get CALCULATE..... Once you get this concept, it will make your Power BI journey so much easier.

@CikYlang 19 күн бұрын

Hai isabella, tq for your video.. just want to ask, do you have any video on finance lease calculation example in power bi

@LighthouseAnalytix 19 күн бұрын

Hello... thanks for taking the time to leave some comments. I don't currently have a video on finance lease calculation. What type of calculation are you thinking of developing in PBI?

@CikYlang 18 күн бұрын

I am so happy got respond from you.. tqvm. I.m thinking of developing finance lease ammortization shcedule in power bi. Currently im struggle to calculate monthly finance lease liabilty for all my lease asset

@kaushikdayabar1833 Ай бұрын

Hello , can someone please tell me why even after mentioning the year as 2023 in calculate function we got values in 2022 column as well that too we got the values of year 2023 in the column 2022?

@LighthouseAnalytix Ай бұрын

because we are using CALCULATE to override the filter context (i.e. year column)..... with 2023.

@BigGollum1 3 ай бұрын

Hello, it is possible to download the dashboard file ? Thanks

@curioussusanm8157 3 ай бұрын

Great question. Would love to get a download of this file.

@LighthouseAnalytix 2 ай бұрын

Yes, it is available for download from my website lighthousenalytix.com

@romulusmilea2747 2 жыл бұрын

One important remark from my side: CALCULATE in DAX/Power BI/Power Pivot, or SUMIF, VLOOKUP in Excel are NOT formulas, they are functions. It is a very big surprise to me to see so many experts (including Microsoft MVPs) that make confusion between a formula and a function. What would be your opinion ?

@LighthouseAnalytix 2 жыл бұрын

Hello Romulus... I didn't even know the difference between formula vs function, until I read you comments & did a bit of googling. Thank you for educating me. Seems like, if we are to be precise, everything should be called functions and formulas are only reserved for (+,-,*,/,%,^). Personally, I used both terms interchangeably. I often said something like... "Wao your formulas seems long & complicated". After all, we write formulas and functions in the same place in Excel, in the 'formula bar' in Excel. I wonder why Microsoft are not calling it the the 'function bar' ?

@romulusmilea2747 2 жыл бұрын

@@LighthouseAnalytix we cannot make this incredible confusion between functions and formulas. Microsoft Excel and other applications force us (if we are not already rigorous as we must be) to be exact and consistent in being exact. I would strongly recommend you to stop making this unacceptable mistake, because it creates confusion, and many people will learn and use these terms incorrectly. All your viewers will probably never question the correctness of your lectures, they simply trust you (and many other experts all over the Internet), so if you provide wrong information, they will use wrong information. Cheers and all the best !

@osoriomatucurane9511 5 ай бұрын

I am fairly novice or newbie in the world of data analytics. There must be a tenue distinction in Excel betwee formula and Functions, the latter being predefined ot built in formulas, with or without arguments, and where we call and pass in the parameters. Some use parameters and arguments interchangebly. In programming language such as R and Python, the data analytics workflow revolves arround functions, be them built in, or user/customer defined. In fact function are generally grouped according to the category such as text/string, date and time, logic, aggregation , statistical, financial,........ Functions shine over the formulas because are reusable, avoid boring or tedious repetitive tasks.