DAX Fridays! #5: CALCULATE (Part 1)

[@skdonsingh]

Finally today i have understood what is the use of ALL in calculate.. Thanks for your video ❤

[@engelcrona]

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!

[@CurbalEN]

Yey!!! Thanks for the amazing feedback :) /Ruth

[@areekorn1]

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.

[@CurbalEN]

New students tell me that every day is a DaX Friday until they complete all videos! Enjoy! /Ruth

[@TheSondav]

One of the best way of teaching! Thank you very much!

[@CurbalEN]

Thank you!!! 👏👏

[@IamMiteshParmar]

Very Useful and love the way you explained and break in small videos each functions.

[@sahanseney134]

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.

[@CurbalEN]

Welcome and thanks!! /Ruth

[@maxivy3299]

I really love your explanations on such complex subjects for Power BI

[@CurbalEN]

Thanks!

[@VinayKumar-ij4eu]

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?

[@aniketkulkarni684]

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.

[@neerajjat2113]

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?

[@farmanalishah8836]

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?

[@CurbalEN]

Use && to concatenate both filters! /Ruth

[@farmanalishah8836]

Curbal Thank you so much

[@CurbalEN]

You welcome! /Ruth

[@chettoers]

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.

[@momonono83]

Hi Ruth! Thanks for your videos! Do you have any video about Filter and All functions?

[@CurbalEN]

+mah noor Hi Mah! Yes, it is schedule for this Friday! Stay tuned :) /Ruth

[@MrRakesh448]

Hi Ruth, At 13:33 of video how is the % 81 & 82 when the denominator is blank. Please explain.

[@CurbalEN]

It is not black, it is calculating the rest of the products you don’t see on the table. /Ruth

[@ivanrybalchenko7225]

@@CurbalEN hi blank rows 1-2 are products with NULL as value in Name?

[@pasqualecovelli5393]

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?

[@Applechen1967]

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.

[@CurbalEN]

+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

[@Applechen1967]

Dear Ruth, I am sorry that I do not know how to attach file.

[@CurbalEN]

+Applechen1967 Send me an email here: Curbal.com/contact and I will send you my email back, /Ruth

[@jorgearg242]

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")

[@akashsingh-mv2fq]

Hi Ruth Are you using Dark Theme of Power Bi? It would be great if you guide me the process. Thanks in advance!

[@CurbalEN]

There is no dark mode in power bi yet

[@vireshdhuri7329]

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 !

[@abdelrahmanabdou8905]

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?

[@CurbalEN]

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

[@abdelrahmanabdou8905]

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

[@CurbalEN]

I do it on all measures and I very rarely use calculated columns: m.kzbin.info/www/bejne/iZ67fZp7fb2GfcU /Ruth

[@benhalicki9749]

Love your tutorials. DAX still does my head in. :)

[@GagandeepSingh-db8eh]

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.

[@suvadipkundu152]

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

[@CurbalEN]

Yey!! Glad it was useful :) /Ruth

[@suvadipkundu152]

@@CurbalEN :)

[@nishantmuni7416]

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

[@MarkHinderliter]

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.

[@CurbalEN]

+Mark Hinderliter Yes, the devil is always in the details! Glad you made it work! /Ruth

[@RichArd-ee2qj]

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.

[@CurbalEN]

+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

[@DIGITAL_COOKING]

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

[@CurbalEN]

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

[@DIGITAL_COOKING]

great ! i did it thanks

[@rolandkc]

Thank you, Ruth, for this presentation.

[@rolandkc]

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

[@CurbalEN]

+Roland Kc and thanks to you for watching! Have a great day :) /Ruth

[@CurbalEN]

+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

[@rolandkc]

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

[@CurbalEN]

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

[@jaimeildefonsosegurapena7328]

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?

[@CurbalEN]

+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

[@karannchew2534]

Confused... So CALCULATE is a way to sum a measure, while overwriting the filter and introducing its own filter?

[@clarenavillanueva1104]

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

[@CurbalEN]

+clarena villanueva My server is down! I will email it to you. /Ruth

[@galymzhankenesbekov2924]

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

[@manuelmathewjohn5915]

In the previous video the total sales was 1.2M, how did this change ?

[@ricardotito6072]

Exclente! consulta como caluclar la tasa crecimiento % de las ventas de mes a mes de un año calendario, gracias

[@CurbalEN]

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

[@ricardotito6072]

Curbal Gracias!

[@xmwang829]

I have a question, how to calculate COUNTIFS of Excel in Power BI, for example, COUNTIFS=(d:d, d2, m:m, m2)

[@CurbalEN]

Check if this helps you: curbal.com/blog/glossary/calculate-dax /Ruth

[@yoshihirokawabataify]

Nice video, CALCULATE () is very useful, but so complex. I check two videos, Part 1, Part 2, for refresh my knowledge about CALCULATE()

[@CurbalEN]

+河端善博 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

[@RS-zo8yi]

how filtered product table is created i cant see it in northwind database

[@Nowsignal23]

Hi, how can we calculate three or more according to criteria? Please I need a answer

[@zeegad445]

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

[@shyamthakur1393]

hhi.. #Ruth ...Have you made or could you some video on " how to extract data or hw to connect microsoft visual studio"

[@CurbalEN]

Connect MS studio with Power Bi? /Ruth

[@shyamthakur1393]

facing problm...to connect so...??

[@CurbalEN]

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

[@shahzebalam]

Hi Ruth, I am unable to download the power bi file. Can you please update the correct link in your website.

[@shahzebalam]

If possible can you email all the Power BI files you use in your DAX tutorial.

[@CurbalEN]

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

[@shahzebalam]

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

[@CurbalEN]

Try again, it is fixed! /Ruth

[@shahzebalam]

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

[@akin808]

Quite a good explanation. Thanks for these videos

[@CurbalEN]

+Kehinde Olaotan Many thanks for your feedback and glad it helped you :) /Ruth

[@sultanmdsabir16]

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

[@CurbalEN]

Got you, I put it in my list. /Ruth

[@jorgepaz2524]

How can I subtract values under the same column using calculate?

[@CurbalEN]

This should give you ideas: m.kzbin.info/www/bejne/oIrZo2eentinnbs /Ruth

[@hi_vishy]

Very well explained.

[@CurbalEN]

Thanks Vishesh! /Ruth

[@vybhavisivakoti490]

how to get the excels which you have used in PowerBI??

[@CurbalEN]

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

[@paultoyle6886]

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?

[@CurbalEN]

You can you use IF with string. Post the question in the power bi community to get detailed help! /Ruth

[@brandonl9362]

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.

[@CurbalEN]

Me too 😂😂!! Glad it was useful!!

[@NorbertoVeraReatigaNVR]

Gracias!!! Me esta facilitando el avance de mis conocimientos en DAX.

[@CurbalEN]

+Norberto Vera Reatiga Gracias a ti por todos los comentarios! /Ruth

[@alirezamogharabi8733]

Thanks, I have a question: what should I do if I want to ignore filters from products and years simultaneously?

[@CurbalEN]

Hi Ali, For support, please contact the Power bi community:) /Ruth

[@hi_vishy]

How simply explained...superb

[@CurbalEN]

Thanks!! Awesome to hear :) /Ruth

[@yiubili1]

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

[@CurbalEN]

My pleasure :) /Ruth

[@vibhusharma9484]

how to download only dataset?

[@Arya-mf3ey]

Why product name is empty but total sales is 864182 for the first item?

[@Arune90]

files could not download.

[@tymothylim6550]

Thank you for the help! This was helpful.

[@vijaypalmanit]

I got what I was looking for, nice explanation.

[@CurbalEN]

Awesome! /Ruth

[@ericet19]

Hi, Love your videos, thanks a million. I cannot find the file that goes with the video ? Is it still available ?

[@CurbalEN]

Hi Eric, and thanks! Yes, all the files are available now in the download center: curbal.com/donwload-center /Ruth

[@RS-zo8yi]

how dim table is created...

[@CurbalEN]

Here: m.kzbin.info/www/bejne/Y6XKYaBjjLlkrqc /Ruth

[@anandgp07]

Thanks for the video

[@Sergio-td7mn]

Thank you!

[@mathijs9365]

Calculate just ignores the external filter. So you need the filter formula.

[@CurbalEN]

I am going to make a video of this tomorrow :) /Ruth

[@eriol33]

this is a great introduction video

[@stevennye5075]

excellent!!!

[@CurbalEN]

Great! :) /Ruth

[@lifeisstr4nge]

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

[@CurbalEN]

+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

[@sergzador]

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

[@CurbalEN]

Sorry for the late answer! It has to do with the filter context, but I guess you already know by now! /Ruth

[@ravitutika1204]

Best Videos on DAX , Thanks Ruth i have kept message to you on LinkedIn can you please check