DAX Fridays! #5: CALCULATE (Part 1)
Vložit
- čas přidán 1. 07. 2024
- Let's go through CALCULATE function available in DAX!
PREVIOUS VIDEO: • DAX Fridays! #4: COUNT...
NEXT VIDEO: • DAX Fridays! #6: FILTER
Download Power BI file: curbal.com/blog/glossary/calc...
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
Finally today i have understood what is the use of ALL in calculate.. Thanks for your video ❤
Of all the descriptions and help I've perused, this is the one that finally made sense. I GOT IT the first time you ran through. Gusen tack!
Yey!!! Thanks for the amazing feedback :)
/Ruth
Very Useful and love the way you explained and break in small videos each functions.
Love your tutorials. DAX still does my head in. :)
Started to Learn Dax today - survived from DAX friday 1 to this now ..very well explained . Trying to catch up all the 121 video .Amazing easy to understand.
New students tell me that every day is a DaX Friday until they complete all videos! Enjoy!
/Ruth
One of the best way of teaching! Thank you very much!
Thank you!!! 👏👏
Thank you for the help! This was helpful.
Quite a good explanation. Thanks for these videos
+Kehinde Olaotan
Many thanks for your feedback and glad it helped you :)
/Ruth
I really love your explanations on such complex subjects for Power BI
Thanks!
Gracias!!! Me esta facilitando el avance de mis conocimientos en DAX.
+Norberto Vera Reatiga Gracias a ti por todos los comentarios!
/Ruth
Nice video,
CALCULATE () is very useful, but so complex.
I check two videos, Part 1, Part 2, for refresh my knowledge about CALCULATE()
+河端善博 Thanks Yoshihirok and I agree with you , calculate() needs practice to fully understand it. I will do in the future a lot more videos about it, combined with other functions so stay tuned!
/Ruth
this is a great introduction video
I got what I was looking for, nice explanation.
Awesome!
/Ruth
Thanks Curbal for this great video. I hated the CALCULATE function because I was always trying to use it as a calculated column instead of a measure. MY MISTAKE. When you made that clarification in the video I thought, "OK, now I know the problem." I think I will now 'love' the CALCULATE function. Again, big thanks (and yes if you couldn't tell, I'm new to DAX). By the way, DAX, where have you been all my life?! Big fan, thx.
+Rich Ard You will adore calculate from now on, it is where most of the Magic happens!
...and I can feel your pain as I tried to the same at the beginning :)
Keep at it, it takes time to learn but the rewards are immense!
Happy Friday :)
/Ruth
How simply explained...superb
Thanks!! Awesome to hear :)
/Ruth
Hi Ruth, How filter works in calculate. if we have multiple filters,does filtering start from the last filter or the first filter?Or all filters applied simultaneously?Does order of filter add to performance?
Hello,
Very nice video, had one question though, is it possible to ignore slicers current filtering and use filter during calculate to apply filter to retrieve any value to add in current calculation? Please help me with this.
Thanks for your video, it is very useful to follow with your material provided and do it at my desktop, thats very considerate of you :)
My pleasure :)
/Ruth
Very well explained.
Thanks Vishesh!
/Ruth
Ruth, Always, THANK YOU. In this case though, thanks for the Henry Ford "as long as its black" reference. You might not realize it, but, now, I'll always recognize this problem in my own reports with, "Oh... Hey Henry!" Actually... you might realize that.
Me too 😂😂!! Glad it was useful!!
The session on DAX- Calculate has been thought provoking :):).
Often in business performance analysis, we are required to consider relative performance of products (or such other dimensional parameter) across single/multiple metrics (sales/revenue/pipeline size).
The behaviour of DAX- calculate function that allows overwriting filter context presents a rather unique and easy way to create the aforesaid KPI/Metric as a measure on the go.
E.G.in the example you have demonstrated, the 'cal w filter' measure can be used as the denominator against the 'CAL tot sales' as numerator to arrive at the aforesaid metric.
The alternate solution would require us to create a calculated column (using if) which is clearly sub-optimal computationally.
Thanks a bunch for the insight. I will be sure to go thru' the videos in this series in detail to pick up such insights. Do please keep up the good work.
cheers
Yey!! Glad it was useful :)
/Ruth
@@CurbalEN :)
Thank you, Ruth, for this presentation.
One question: CAL Tot Sales [no filter applied] yields the same result as CAL w ALL products. Please explain the difference between the 2 in the way HOW the result is arrived at...
+Roland Kc and thanks to you for watching! Have a great day :)
/Ruth
+Roland Kc Hi Ronald,
It is called syntax sugar:
When you write this:
CALCULATE( expression, table[column]) you are actually writing:
CALCULATE( expression, FILTER(ALL(table[column], table[column]))
/Ruth
I am still confused. When you wrote CAL Tot Sales, there was no filter applied [i.e. CALCULATE(expression) with NO filter being used. Do you mean to say CALCULATE( expression) is equivalent to CALCULATE( expression, FILTER(ALL(table[column], table[column]) and NOT CALCULATE( expression, table[column]) is the same as CALCULATE( expression, FILTER(ALL(table[column], table[column])). Please confirm as I need to get pass this hurdle in my understanding of the CALCULATE function
Hi Roland,
CALCULATE( expression, table[column]) is the same as CALCULATE( expression, FILTER(ALL(table[column]), table[column])
Check out this blog post, it might help you:
www.sqlbi.com/articles/filter-arguments-in-calculate/
/Ruth
Hi Ruth! Thanks for your videos! Do you have any video about Filter and All functions?
+mah noor Hi Mah! Yes, it is schedule for this Friday!
Stay tuned :)
/Ruth
Thanks for this video, Ruth...Can we do one thing, Can we get Total Sales for all the product in one column except Queso Cabrels or BLANK for Queso Cabrels?
Thanks for the video
Hola Ruth gracias por este gran esfuerzo y material de excelente calidad he aprendido mucho con tus videos. ¿Pero cómo se consiguen los archivos para seguirte en el video? no logro ubicarlos.
Gracias! Saludos.
Hi Ruth. I have a question. If you re using ALL to remove all filters why is it when you drop the year column in, it’s responding to the year filter?
He empezado a ver los videos. Muy interesantes. Tengo algo, que me deja un poco intrigado.
He ido actualizando PowerBI y quizas sea algo que cambió en la última actualización (no tengo como comprobarlo pues nunca abrí este archivo con la versión anterior), o es algo que está mal configurado (pero no puedo imaginar que podría ser).
He bajado los archivos ya resueltos, tal como los has colgado, y lo que veo en la matriz es diferente a lo que se ve en el video.
En Cal w filter ya no aparecen los totales repetidos en los productos que no son QuesoCabrales (se ven en blanco). En All si aparecen en todos los productos.
En el video de la segunda parte ocurre algo parecido, ya se han filtrado (no se visualizan) los totales que estan en productos no relacionados con los filtros.
Ha cambiado la funcion DAX, ha cambiado el visual o hay algo mal configurado?
+Jaime Ildefonso SEGURA PENA
Hola Jaime, te explico;
Esos vídeos son muy antiguos y al principio no tenía pbix para descargar. Es muy posible que esos archivos los haya recreado después y de ahí las diferencias.
Espero que aún te sirvan!
/Ruth
Thank you!
I've searched far and wide, listen to many a ramblings before I stumbled upon your channel. Massive thank you for the delivery, content and presentation.
Welcome and thanks!!
/Ruth
Hello, what if I want to use the measure that I have created using the calculate and the filter later on in order to create another measure but somehow ignoring the filter again.
For example
1 a = 10
2 a = 20
3 a = 30
4 c = null
5 b = 1
6 b=2
7 b = 3
8 c = -10
So i want to calculate the value of b from a
how can I do this? I have managed to sum the values for a and the values of b, but when I do a/b it keeps the filter, I want to get the value of b and the ignore the filter later on so I can use it as a sum in my calculations later on without the filter
Question: In this ex. when Calculate with filter or Calculate All, why did it not populate any values for the blank product name (1st row). But when %ofSales was done, it showed the correct %. Just curious... Thanks in advance
It took me a few minutes to figure out how to wire up the FilteredProducts table you have. Creating the table wasn't too hard, but then I had to tweak the relationships so everything was joined to the filtered table.
+Mark Hinderliter Yes, the devil is always in the details!
Glad you made it work!
/Ruth
Thank you Ruth. As you Cal with Filter "Queso Carbales" How to add another Product? Suppose i want to show two products " Chai and Chang" How to filter both?
Use && to concatenate both filters!
/Ruth
Curbal Thank you so much
You welcome!
/Ruth
Hi, Love your videos, thanks a million. I cannot find the file that goes with the video ? Is it still available ?
Hi Eric, and thanks!
Yes, all the files are available now in the download center:
curbal.com/donwload-center
/Ruth
Hi Ruth
Are you using Dark Theme of Power Bi? It would be great if you guide me the process.
Thanks in advance!
There is no dark mode in power bi yet
excellent!!!
Great! :)
/Ruth
thanks for the video. I have a question. So, my measure calculates number of people correctly, but does not show the number of cars (corresponding to these people ) correctly. I mean in slicer. I used calculate (distinctcount(_,_))
could you please help me? thanks
Thank you. Ruth. I am a new user and learn PBI from your video a lot. In this presentation, I get two things which is different from your presentation. (1) Calculate with filter only return with total of one product. For the rest of products, it returns with blank. Did I do something wrong? (2) Calculate with all products, the results is the same with sum of total. Can you tell me more about it? Thanks in advance.
+Applechen1967 Hi! Glad my videos are helpful :)
Could you send me your power bi file? I can look at it and let you know,
/Ruth
Dear Ruth,
I am sorry that I do not know how to attach file.
+Applechen1967 Send me an email here: Curbal.com/contact and I will send you my email back,
/Ruth
You have to make a change in the filter, it refers to another table.
The measure would be:
CAL w FILTER TotSales = CALCULATE([SUM TotSales];FilteredProductsTable[ProductName]="Queso Cabrales")
good explanation, please can you tell me how to make the white area bigger in power bi desktop because i notice in the video that your area where you put tables is bigger than mine
I dont have Powe bi in front of me, but if you click on the white cavas and then the brush icon, you can set the size of the canvas there :)
great ! i did it thanks
Hi Ruth, Again I have some query for knowledge purposes "Tot sales" look good but you minus 1 from a discount I can understand if the discount is 0 then all result will be 0. If there has some discount 2 or 3 then we are subtracting 1 from it and give an incorrect result. moreover, If the discount is 1 then it will become 0. Please correct me if I am wrong.
Hi, how can we calculate three or more according to criteria? Please I need a answer
how filtered product table is created i cant see it in northwind database
Hi Ruth,
At 13:33 of video how is the % 81 & 82 when the denominator is blank. Please explain.
It is not black, it is calculating the rest of the products you don’t see on the table.
/Ruth
@@CurbalEN hi blank rows 1-2 are products with NULL as value in Name?
I'm not getting the excel file , the link in the description is not valid maybe it's not responding..
Please make it available , your teachings are so understandable and helpful but If i could get the data it'd be more helpful to me , Thankyou !
Hi Ruth/ I am waiting for a video for new feature called incremental refresh. where we can have dynamic parameter .Example Start date > max(SalesDate from existing Table) and end Date will be some future date.
Regards Sabir
Got you, I put it in my list.
/Ruth
Hi Ruth, Thanks for the great video. I still don't understand why we create the calculated column Tot_Sales. You are saying on the video that this is to make sure the measure doesn't get invalid when the name of the calculated column changes, but I still don't get it. Can you clarify more please?
IF you change the name of the column, all measures that use the column name will break, but if you put the column name on a measure and then reuse the measure, when the column name changes you only update it once.
Hope it makes sense now,
/Ruth
@@CurbalEN Oh. Ok. That makes sense now. Thanks a lot for your reply. But do you usually do this to all the columns that you use in measures, or do you just do it to calculated columns? I'm guessing you do that only to calculated columns.
I do it on all measures and I very rarely use calculated columns:
m.czcams.com/video/SmXLgEHXSGc/video.html
/Ruth
Please if no possible download the file the day friday the link is broken, how made for download the files, is very important for study the function dax.
THANKS A LOT, i wirte from cali colombia
+clarena villanueva My server is down! I will email it to you.
/Ruth
Confused... So CALCULATE is a way to sum a measure, while overwriting the filter and introducing its own filter?
Exclente! consulta como caluclar la tasa crecimiento % de las ventas de mes a mes de un año calendario, gracias
Gracias Ricardo, podrías poner la pregunta en la comunidad de Power Bi? Pon un ejemplo de tus datos para recibir ayuda rápido!
/Ruth
Curbal Gracias!
In the previous video the total sales was 1.2M, how did this change ?
I have a question, how to calculate COUNTIFS of Excel in Power BI, for example, COUNTIFS=(d:d, d2, m:m, m2)
Check if this helps you:
curbal.com/blog/glossary/calculate-dax
/Ruth
CAN YOU PLEASE TELL ME HOW CAN I USE IF STATEMENT WITH STRING OR IF THERE IS ANOTHER WAY OR FUNCTION THAT CAN REPLACE IF STATEMENT TO USE WITH STRING?
You can you use IF with string. Post the question in the power bi community to get detailed help!
/Ruth
how to get the excels which you have used in PowerBI??
All the files are available for download at the download center on Curbal.com.
If you don’t find it there, it doesn’t exist I am afraid!
/Ruth
hhi..
#Ruth ...Have you made or could you some video on " how to extract data or hw to connect microsoft visual studio"
Connect MS studio with Power Bi?
/Ruth
facing problm...to connect so...??
Have no idea how to connect to visual studio, can you ask in the power bi community to see if anybody has done it before?
/Ruth
Thanks, I have a question: what should I do if I want to ignore filters from products and years simultaneously?
Hi Ali,
For support, please contact the Power bi community:)
/Ruth
how to download only dataset?
How can I subtract values under the same column using calculate?
This should give you ideas:
m.czcams.com/video/jYvr4histgY/video.html
/Ruth
Calculate just ignores the external filter. So you need the filter formula.
I am going to make a video of this tomorrow :)
/Ruth
Why product name is empty but total sales is 864182 for the first item?
Hi Ruth, I am unable to download the power bi file. Can you please update the correct link in your website.
If possible can you email all the Power BI files you use in your DAX tutorial.
I have created a download center where you can get all my pbi files in one place. Here it is:
curbal.com/donwload-center
/Ruth
@@CurbalEN Getting the below error :
Not found, error 404
The page you are looking for no longer exists. Perhaps you can return back to the site's homepage and see if you can find what you are looking for. Or, you can try finding it by using the search form below.
Try again, it is fixed!
/Ruth
@@CurbalEN I am able to see the page. But when i click on the link its taking too long to respond and no results:
This site can’t be reached
curbal.synology.me took too long to respond.
files could not download.
how dim table is created...
Here:
m.czcams.com/video/-tg0m-WT1xE/video.html
/Ruth
Best Videos on DAX , Thanks Ruth i have kept message to you on LinkedIn can you please check
Hi. Love your videos. I wonder why it gives different outcome if I filter by Queso Cabrales in "Product" table. Is it because it has direct relationship with "Order details" table? Here is the screenshot: prntscr.com/igd2rz
Sorry for the late answer! It has to do with the filter context, but I guess you already know by now!
/Ruth
How do I calculate values from a different date? I want to calculate the difference of an earlier measure vs today()'s measure. How the fuck do I do that???
+xTroop18546x Post an I example of what you are trying to do in the power BI community, you will get help in no time :)
/Ruth