Thanks. Just the kind of video training i wanted. Pls continue to create this type of videos. If i may request pls create one for auto refreshing the reports. What ways can we do that especially without using dedicated servers. Thanks
I know how to use this function, but this tutorial taught me exactly how I would have liked someone to teach it to me. Awesome job. Most people just tell you how to do it, but putting in examples of when you would use it and variations is exemplary. Well done.
First time I have gone through sensible explanation on UA-cam about power BI. I am new to this tool and comparison you show by applying filter on excel and match the amount that was Awesomeness 👌Sir. THANK YOU PLS UPLOAD MORE video I WOULD tag your content on insta so that more people take most out of it.
Raghu N, Thanks for stopping by. I usually share the content link in the video description. Here is the link for reference. drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing
Juan, Thanks for your support. What you can do is to create relationships between tables and then create a measure to sum numeric fields from another table. I hope this helps. Cheers!
Thank you so much, I was searching for this query for a long time. Your videos are awesome. Looking forward to more such amazing videos from you. Thanks once again. :)
Hi I know that this might be a bit late commenting on an old video but how do I show still the whole Sales Table but a specific customer has a filter context based on a column? like say in your example Making a Table showing The Claim Costs of all the Cities but I want the claim cost under the city name "Knoxville" to be filtered by only the claim costs under the delivery company "FedEx" reflecting a table that has all the claim costs of each city but one of them is filtered
Kimmy, Thanks for stopping by. I think you are trying to add multiple filters to your calculation. The example below might help: Measure:= CALCULATE( [Total Claim Cost], Claim[City] = "Knoxville" && Claims[Delivery Company] = "FedEx" )
Sangy Jap. Thanks for stopping by. The first part of the calculate function should be any expression. For the example, we use a measure that adds all the claim cost. However, you can also add other type of measure/calculation. This must be an expression, it cannot be just a numeric column. I hope this solves your question.
Thanks for stopping by, David. The && is an AND operator and for the example, I wanted to use an OR operator. I could have used IN in the filter argument as well. For example: Claim[city] IN {"Nashville", "Phoenix", "Greenville"}
Simmi, Thanks for checking the content. As far as I know, yes, the CALCULATE returns a single value. If you want to get a table, you might want to use CALCULATETABLE. Thanks!
i have a question, can you please explain the way dax evaluates, like in the first eg we have CALCULATE( expression, FILTER(ALL(table, exp))), so which exprssion gets evaluate first, what it returns, how the result gets interpreted inside brackets. I hope u get it.
Bidhan, Thanks for your question. CALCULATE computes the filter argument first and then the expression. For this example, the filter argument is a table that uses the FILTER function. The ALL function scans all the values in a specific column. I hope this helps! Cheers!
i write the below function to get total of the bank sector Bank sales = CALCULATE([sales],FILTER(Orders,Orders[Manger]="9BNK")) here i did not happen to use ALL function. but it worked. here [sales] is a measure. could you please explain this
Kusal, Thanks for stopping by. We usually use ALL as a filter modifier to remove all filters from a column or table. Initially, if that table or column doesn't present any filters (usually used in a slicer), the result could be the same by using or not using the ALL function. I hope it helps.
Thanks Nestor the video was very knowledgeable. I have one question, When i am drwing a date claim column to axis of stacked column chart .its not filtering according to dates. And also in the report section i am unable to get a date symbol before date claim as you have
Harshal, Thanks for stopping by. I highly recommend to create a calendar table and create a relationship based on the date column with the Claims table. After that, use the date column from the calendar table as part of the axis of any chart that you are building. For your second question, you might want to go to the "DATA" section of your report, then "Column Tools" and select the right Date Type. I hope it helps. Cheers! This tutorial about CALENDAR tables might also help: ua-cam.com/video/IU_2rkTFrLY/v-deo.html
Good question! I used the FILTER function to apply complex filter conditions. Also, this video was recorded in 2020 when the new CALCULATE filters feature wasn't available yet. You might want to check the following tutorial out to learn about this new feature. I hope it helps. Cheers! ua-cam.com/video/KDnkGraX5c8/v-deo.html
I am new to Power BI, coming from long xls experience. What I dont understand is the usage of rows (Shift+Enter) while typing the DAX formulas. What is the purpose of splitting the code in rows and then tabbing? is it just esthetics? or there is functionality for that???
Fah Kem, Thanks for stopping by. These are good questions. By doing so, we can improve readibility of the DAX code. Also, if we make mistakes, it is easier to find and fix them. I hope it helps! Cheers!
@Nestor Adrianzen Thanks Nestor for quick response. May I ask you a bit more. I assume the readibility for ourselves not the machines right? I kind of understand that mistakes are highlighted by red colod code on the right of the row, but why we need to use Tab then? I am tottally puzzled after seing at 12:20 of your tutorial paranthesis placed on different rows and on different tab positions, and I cannot think of a potential mistake for paranthesis that one could make. Hope you can help me with my doubts.
Yeah. I see your point. In reality there is no standard to write DAX code in Power BI. However, when we create more complex formulas, it’s better not to write them in just one line, because sooner than later we will get lost. You might want to check the following website for reference. Cheers! www.daxformatter.com
Sudip, Thanks for following our content. We usually share the link content in the video description of each tutorial. Here it is as well. Cheers! drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing
Prateek, Thanks for following our content. Please see the link below for the files. You can also find the link in the video description for every lesson. drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing
thank you, I'm new to this and hope this video will end up being my power BI foundation using it to build financial statements. I've followed your steps using my data which is formatted by department, by month and and amount. In trying to filter for just Dept 1. I get the correct sum for that dept 1, however the Dept1's name is blank and every other department is listed with the Dept 1's cost. Using a matrix view. If I add additional departments using || Dept[DeptName] = "dept 2")) I get the aggregate total cost for both departments, this is then displayed as the total cost for each of my departments. Really need a suggestion on what I could be doing wrong.
Steve, Thanks for stopping by. I'm not sure if followed the whole story here, but it seems that it's related to the "context filter" with the CALCULATE function. Remember that the logical operator || takes one option or the other. Whereas, for the logical operator &&, both options need to be correct. Also if you want to keep active some filters, perhaps this tutorial can help. Cheers! ua-cam.com/video/s_9TYgTRsXY/v-deo.html
Hi Nestor, could you please help me as I am trying to get percentage month wise for leaver, but outcome is coming in descending order that is incorrect. I have three tables First is a transaction table(Base Data), second is a fact table with starter, leaver date and status(ALL Employee), last one is a calendar table which is having just date, month, year etc(calendar date Table). Fact tables are having relationship with transaction table. The DAX, which I wrote is leaver count from fact table and divide by total number of employees. But the answer is wrong. (Leaver Count = CALCULATE(COUNT('ALL Employee'[Leaver Info]), FILTER('ALL Employee','ALL Employee'[Leaver Info]="Leaver"))) (HeadCount = DISTINCTCOUNT('Base data'[Employee Code]))
Vikram Singh, Thanks for stopping by. For percentage calculations, you might want to check the following tutorial out: ua-cam.com/video/9IdFq2aPxmY/v-deo.html. If you are getting the right results, but the order is incorrect, you will need to add a "month" # column to your calendar table. Then sort the date column (e.g., the date column that is part of the axis of your visual) by using the "Sort by column" option and select the new column #. Finally, sort by selecting ascending or descending from your visual. I hope it helps! Cheers!
Hi , Need your help. I want to create a column with month name + number of days in a month. Number of days in a month should change when we select month and year from the filter. Some cases we have leap year in that February month should show 29 and like wise non leap year February month should show 28 days. Output should be in this way . Jan 31 Feb 28 Mar 31 Apr 30 When I select year as 2020 from filter output should be like this Jan 31 Feb 29 Mar 31 Apr 30. Thanks in advance.
Eswar Sonu, Thanks for taking a look at the content. My suggestion would be to create a Calendar table. The date column will have its own hierarchy [Year, Quarter, Month and Day] that you can use to filter any date. This tutorial will help you create a calendar table. I hope it helps. Cheers! ua-cam.com/video/IU_2rkTFrLY/v-deo.html
Great content! I just have a question - is it possible to have multiple calculations in one measure, based on different values? So for example, I may want to do a sum if city is Nashville, and a count if city is Phoenix. How do I apply this in one measure? Thank you!
Your International Experience, Great question! Yes, it is possible. You might want to use a nested IF function to do so. See below an example: Measure = IF( VALUES(Claim[City]) ="Nashville", SUM(Claim[Claim Cost]), IF(VALUES(Claim[City])="Phoenix", COUNTROWS(Claim) ) )
Great video as always ! I have a question though: Is there a way to filter by using only part of the value and not the whole word? For example instead of finding the Cost for [City] ="Nashville", can we find the Cost for every [City] that contains the "*ville" somewhere in its name?
Yes, you can! One of the filters of the CALCULATE function can be like the following: CONTAINSSTRING(Claim [City], "*ville") This tutorial will help: ua-cam.com/video/Y82DHwlxAkI/v-deo.html
![DAX Basics in Power BI Desktop [Part 1]](http://i.ytimg.com/vi/QUcdzJIKY8Q/mqdefault.jpg)
![DAX Basics in Power BI Desktop [Part 1]](/img/tr.png)
Thanks. Just the kind of video training i wanted. Pls continue to create this type of videos. If i may request pls create one for auto refreshing the reports. What ways can we do that especially without using dedicated servers. Thanks
Ed D Excellent! Tanks for your suggestions and keep up the great work!
Very well done. This video shows you how to drive the CALCULATE car without trying to take you under the hood at the same time. Thanks
Sonoftoid, Glad it was helpful! Cheers!👍
I know how to use this function, but this tutorial taught me exactly how I would have liked someone to teach it to me. Awesome job. Most people just tell you how to do it, but putting in examples of when you would use it and variations is exemplary. Well done.
Glad to hear that. Cheers!
I am so lucky to find your channel, I have learn a lot more!!!!!👏
Glad to hear that. Cheers!
I have searched for many videos and after that i have found your video.
Exact to the point. Thanks man for your help.
Appreciate your efforts.
Awesome! Glad you found the content helpful.
Thank you for the most clear explanation I have watched. I immediately subscribed.
Glad to hear that! Cheers 👊
The first time I understand the calculate function and the formatting of DAX! Thank you:-)!!
Great to hear! Keep it up, Ben K.
Thank you Man, it's very clear cut to understand
Glad it helped! Cheers!
First time I have gone through sensible explanation on UA-cam about power BI. I am new to this tool and comparison you show by applying filter on excel and match the amount that was Awesomeness 👌Sir. THANK YOU PLS UPLOAD MORE video I WOULD tag your content on insta so that more people take most out of it.
Appreciate your feedback, Excel with ADeep! Glad it was helpful. Keep it up!
Superb video Nestor. The very best of CALCULATE. Thank you for your examples.
Thanks for your support, Iván. Keep up the good work!👍
Thank you so much ! My first DAX experience
Great to hear that, Sandra!
You are a good teacher.. sweet and simple, clear, and on point.. luv this👍💖💞
Wow, thank you!
Thank you vary much Mr Nestor Adrianzen this is vary helpfull and informative lession good luck
Glad it was helpful! Keep up the good work, Zafar. Cheers!
Thank you. Very helpful explanation of the concept.
Glad it was helpful!
Awesome demo and explanation!
Glad you found the content helpful!
Thanks Nestor. Very Clear Tutorial 👍👍👍
Glad it was helpful! keep up the good work, Kebin Cui.
Excellent explanation..thoroughly enjoyed the video...teaching is pure art...Great job
I appreciate your feedback, Irfan. Keep it up👍
Hi Nestor..where do i find the claims data sample..can you please share?
Raghu N, Thanks for stopping by. I usually share the content link in the video description. Here is the link for reference.
drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing
Very well explained. Great thanks Nestor!
Glad it was helpful!
one of the best ever explanation with a very good example, perfect, thanks you !!
Glad it was helpful! Keep up the great work, Waheed. Cheers!
You are the best. Thank you so much.
Glad it helped and thanks for your support. Cheers!
awesome demo on calculate function. enjoyed following through. Thanks
Glad it was helpful. Keep it up, Tinashe! 👊
Thanks Nestor for this excellent video. Please share some interview question which usually ask in interview. thanks
Glad it was helpful, Indian Pigeon! I’ll keep in mind your suggestion. Thanks 🙏
Can we use" In operator " for multiples cities apart from OR operator
Yes, you can also use "IN". See below as reference.
FILTER(
ALL(Claim[City]),
Claim[City] in {"Nashville","Phoenix","Greenville"}
)
@@NestorAdrianzen Thank u very much
You briefed calculate function very smooth way, thanks,
Jamal Qamar, I appreciate the feedback. Keep up the great work👍
Great video, easy to follow and understand Thank you for sharing your knowledge.
Glad you found it helpful, Non Nguyen. Keep up the good work.👍
What a explanation by you really it's ausume.
Thanks for your support, Chandrashekhar! Keep it up!👍
Hi! How could you sum columns of different tables?
Juan, Thanks for your support. What you can do is to create relationships between tables and then create a measure to sum numeric fields from another table. I hope this helps. Cheers!
As you have used All function in the Calculate Filter function, how are the filters working in the visuals?
Vinayak, Thanks for stopping by. The ALL function will remove any filter available within a column or table.
Thank you so much, I was searching for this query for a long time. Your videos are awesome. Looking forward to more such amazing videos from you. Thanks once again. :)
@Priyadarshini, Thanks for your kind words. Keep up the great work!
Hi I know that this might be a bit late commenting on an old video but how do I show
still the whole Sales Table but a specific customer has a filter context based on a column?
like say in your example
Making a Table showing The Claim Costs of all the Cities
but I want the claim cost under the city name "Knoxville" to be filtered by only the claim costs under the delivery company "FedEx"
reflecting a table that has all the claim costs of each city but one of them is filtered
Kimmy, Thanks for stopping by. I think you are trying to add multiple filters to your calculation. The example below might help:
Measure:=
CALCULATE(
[Total Claim Cost],
Claim[City] = "Knoxville" && Claims[Delivery Company] = "FedEx"
)
Why can't I choose other data except the just created data(Total Claim Cost). Is it possible to select another data column as well?
Sangy Jap. Thanks for stopping by. The first part of the calculate function should be any expression. For the example, we use a measure that adds all the claim cost. However, you can also add other type of measure/calculation. This must be an expression, it cannot be just a numeric column. I hope this solves your question.
Cool man!!! You have simply demonstrated 👍
Glad you found it helpful! Cheers!
you are good teacher sir...thanks..
I appreciate your feedback, Alimatul. Keep up the good work!
Why did you not use && instead of || when selecting the 3 cities?
Thanks for stopping by, David. The && is an AND operator and for the example, I wanted to use an OR operator. I could have used IN in the filter argument as well. For example:
Claim[city] IN {"Nashville", "Phoenix", "Greenville"}
very nicely made. Thanks
Thanks for your feedback, Mihir!
Brillinat! Thank You
Glad to hear that👍
Is calculate function always return single value like aggregate function ?
Simmi, Thanks for checking the content. As far as I know, yes, the CALCULATE returns a single value. If you want to get a table, you might want to use CALCULATETABLE. Thanks!
AWESOME, YOUR VIDEOS ARE AMAZING
Excellent! Keep up the good work.
Awesome ! Thanks for the video and please keep sharing more 🙂.
Glad it helped! Keep up the good work, Sai!
i have a question, can you please explain the way dax evaluates, like in the first eg we have CALCULATE( expression, FILTER(ALL(table, exp))), so which exprssion gets evaluate first, what it returns, how the result gets interpreted inside brackets. I hope u get it.
Bidhan,
Thanks for your question. CALCULATE computes the filter argument first and then the expression. For this example, the filter argument is a table that uses the FILTER function. The ALL function scans all the values in a specific column.
I hope this helps!
Cheers!
The link to the downloadable file is broken. Could you fix it please?
Amit, Thanks for letting me know. I just updated the link. Here you go! drive.google.com/file/d/1kASvVjUiWeGSkZPamop5omITg7OuQ8VB/view?usp=sharing
This video is very good!!! and Thanks
Glad you liked it! Keep it up, I like Digital Logic.
Nice Explanation
Thanks for you support, Liladhar. Keep it up 🆙
i write the below function to get total of the bank sector
Bank sales = CALCULATE([sales],FILTER(Orders,Orders[Manger]="9BNK"))
here i did not happen to use ALL function. but it worked. here [sales] is a measure.
could you please explain this
Kusal, Thanks for stopping by. We usually use ALL as a filter modifier to remove all filters from a column or table. Initially, if that table or column doesn't present any filters (usually used in a slicer), the result could be the same by using or not using the ALL function. I hope it helps.
@@NestorAdrianzen got it.thanks
great content , but why it is difficult /impossible to download the data file ?
Thanks for stopping by. Sorry to hear that. You might want to refresh your browser.
Thanks Nestor the video was very knowledgeable. I have one question, When i am drwing a date claim column to axis of stacked column chart .its not filtering according to dates. And also in the report section i am unable to get a date symbol before date claim as you have
Harshal, Thanks for stopping by. I highly recommend to create a calendar table and create a relationship based on the date column with the Claims table. After that, use the date column from the calendar table as part of the axis of any chart that you are building. For your second question, you might want to go to the "DATA" section of your report, then "Column Tools" and select the right Date Type. I hope it helps. Cheers! This tutorial about CALENDAR tables might also help: ua-cam.com/video/IU_2rkTFrLY/v-deo.html
Very informative, but why do you prefer the filter method on the ‘left’. Looks more complicated
Good question! I used the FILTER function to apply complex filter conditions. Also, this video was recorded in 2020 when the new CALCULATE filters feature wasn't available yet. You might want to check the following tutorial out to learn about this new feature. I hope it helps. Cheers! ua-cam.com/video/KDnkGraX5c8/v-deo.html
I am new to Power BI, coming from long xls experience. What I dont understand is the usage of rows (Shift+Enter) while typing the DAX formulas. What is the purpose of splitting the code in rows and then tabbing? is it just esthetics? or there is functionality for that???
Fah Kem, Thanks for stopping by. These are good questions. By doing so, we can improve readibility of the DAX code. Also, if we make mistakes, it is easier to find and fix them. I hope it helps! Cheers!
@Nestor Adrianzen Thanks Nestor for quick response. May I ask you a bit more. I assume the readibility for ourselves not the machines right?
I kind of understand that mistakes are highlighted by red colod code on the right of the row, but why we need to use Tab then? I am tottally puzzled after seing at 12:20 of your tutorial paranthesis placed on different rows and on different tab positions, and I cannot think of a potential mistake for paranthesis that one could make.
Hope you can help me with my doubts.
Yeah. I see your point. In reality there is no standard to write DAX code in Power BI. However, when we create more complex formulas, it’s better not to write them in just one line, because sooner than later we will get lost. You might want to check the following website for reference. Cheers! www.daxformatter.com
Awesome video! Can you please upload or link of datasets under this video??
Sudip, Thanks for following our content. We usually share the link content in the video description of each tutorial. Here it is as well. Cheers! drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing
Could you please share this excel sheet
Prateek, Thanks for following our content. Please see the link below for the files. You can also find the link in the video description for every lesson. drive.google.com/file/d/1E_lzwZcgLaiGuhcBG-_9lcIQ1H5JNmNO/view?usp=sharing
Post more DAX videos, really helpfull to me
Thanks for stopping by Anji! Keep up the good work!
Your way of explanation, making notes and teaching by step by steps are awesome....
Glad you found it helpful 👊
Wow thanks sir
Keep up the great work, Mohand!
thank you, I'm new to this and hope this video will end up being my power BI foundation using it to build financial statements. I've followed your steps using my data which is formatted by department, by month and and amount. In trying to filter for just Dept 1. I get the correct sum for that dept 1, however the Dept1's name is blank and every other department is listed with the Dept 1's cost. Using a matrix view. If I add additional departments using || Dept[DeptName] = "dept 2")) I get the aggregate total cost for both departments, this is then displayed as the total cost for each of my departments.
Really need a suggestion on what I could be doing wrong.
Steve, Thanks for stopping by. I'm not sure if followed the whole story here, but it seems that it's related to the "context filter" with the CALCULATE function. Remember that the logical operator || takes one option or the other. Whereas, for the logical operator &&, both options need to be correct. Also if you want to keep active some filters, perhaps this tutorial can help. Cheers! ua-cam.com/video/s_9TYgTRsXY/v-deo.html
Excellent
Thank you! Cheers!
Hi Nestor, could you please help me as I am trying to get percentage month wise for leaver, but outcome is coming in descending order that is incorrect. I have three tables First is a transaction table(Base Data), second is a fact table with starter, leaver date and status(ALL Employee), last one is a calendar table which is having just date, month, year etc(calendar date Table). Fact tables are having relationship with transaction table. The DAX, which I wrote is leaver count from fact table and divide by total number of employees. But the answer is wrong.
(Leaver Count =
CALCULATE(COUNT('ALL Employee'[Leaver Info]),
FILTER('ALL Employee','ALL Employee'[Leaver Info]="Leaver")))
(HeadCount = DISTINCTCOUNT('Base data'[Employee Code]))
Vikram Singh, Thanks for stopping by. For percentage calculations, you might want to check the following tutorial out: ua-cam.com/video/9IdFq2aPxmY/v-deo.html. If you are getting the right results, but the order is incorrect, you will need to add a "month" # column to your calendar table. Then sort the date column (e.g., the date column that is part of the axis of your visual) by using the "Sort by column" option and select the new column #. Finally, sort by selecting ascending or descending from your visual. I hope it helps! Cheers!
@@NestorAdrianzen Thank you so much for this help
Hi , Need your help.
I want to create a column with month name + number of days in a month. Number of days in a month should change when we select month and year from the filter. Some cases we have leap year in that February month should show 29 and like wise non leap year February month should show 28 days.
Output should be in this way .
Jan 31
Feb 28
Mar 31
Apr 30
When I select year as 2020 from filter output should be like this
Jan 31
Feb 29
Mar 31
Apr 30.
Thanks in advance.
Eswar Sonu, Thanks for taking a look at the content. My suggestion would be to create a Calendar table. The date column will have its own hierarchy [Year, Quarter, Month and Day] that you can use to filter any date. This tutorial will help you create a calendar table. I hope it helps. Cheers! ua-cam.com/video/IU_2rkTFrLY/v-deo.html
was alsomost quitting power BI until i watched this video. i still have challenges on how to compare daily revenue figures against monthly targets
I appreciate your feedback, Justin. Keep up the good work. Cheers!
tTHANK YOU !
You are very welcome!
Great content! I just have a question - is it possible to have multiple calculations in one measure, based on different values? So for example, I may want to do a sum if city is Nashville, and a count if city is Phoenix. How do I apply this in one measure? Thank you!
Your International Experience, Great question! Yes, it is possible. You might want to use a nested IF function to do so. See below an example:
Measure =
IF(
VALUES(Claim[City]) ="Nashville", SUM(Claim[Claim Cost]),
IF(VALUES(Claim[City])="Phoenix", COUNTROWS(Claim)
)
)
Thank you very much for your prompt response!
Great video as always !
I have a question though: Is there a way to filter by using only part of the value and not the whole word? For example instead of finding the Cost for [City] ="Nashville", can we find the Cost for every [City] that contains the "*ville" somewhere in its name?
Yes, you can! One of the filters of the CALCULATE function can be like the following:
CONTAINSSTRING(Claim [City], "*ville")
This tutorial will help: ua-cam.com/video/Y82DHwlxAkI/v-deo.html
Brother kindly continue dax we are waiting
Will do!
Great
Glad to hear that. Cheers!
Hi Nestor I have a small doubt why
Thanks for stopping by, Sadineni! What is your doubt?
great
Glad it was helpful. Cheers!
"And right here" is a good comment.
Jay, I hope you are enjoying the content. Cheers!
Obrigado pelo conteúdo! Pode me meu canal também? Sou do Brasil.
Power BI na Real, Excellent! Keep up the good work.
sorry for my previous message, i got the file
Great. Keep up the great work 👍
Vedio is not clear.
Ashwini Zare, Thanks for your feedback. t. I got a new screen recording software about 2 weeks ago, so hopefully the new tutorials look more legible.