Visual Calculations in Power BI - DAX Made Easy! [Full Course]

[Music] where's his audio hear me now you're good hello and welcome to another event here at pragmatic works today we have a very exciting topic for you that's going to be visual calculations with index my name is Mitchell Pearson and if you need to contact me for whatever reason after the event please feel free to reach out to me my email is on the screen that's going to be IL Pearson pragmatic works.com so let's Jump Right In and talk about the logistics for the class today we have a lot of exciting information for you first of all make sure to download the class files so the class files are going to be in the chat window and you'll be able to access that information by just going to the chat window and pulling that up if you're not able to download the class files that probably means you're behind some kind of firewall or VPN that's preventing you from downloading that information the class is recorded it's going to be recorded it's going to live on YouTube so you can come back and view that later however we do have some exciting announcements in this class that might encourage you to stay to the end also shout out to Nick Lee who is on my team Nick Lee is going to be answering any kind of technical questions in the chat so helping out with that and then should you follow along that's a great question I want to address that right now this is live sometimes when you're following along live it can be a little bit frustrating to continue to follow along so follow along at your own risk you of course need to go ahead and be on February 2024 version of powerbi desktop I'll talk about that in the next screen and you need to have the class files but if you get behind don't worry about it you can always pause the video slow it down or watch the recording later and go through the entirety of the course and then after this event is over in just a couple of weeks we're going to do a Live question and answer event specifically for visual calculations and Dax so you want to sign up for that you don't want to miss that event because it's going to allow us to answer questions that we maybe couldn't get to today but also answer questions that you might have live during that event so that'll be coming up on April 22nd all right so requirements for this class here let's see there here we go so requirements for this class you need to enable visual calculations it is currently in preview the topic that we're talking about is in preview so if you want to follow along or if you want to experience this you do need to go ahead go into your options inside of powerbi go to preview features and then turn on visual calculations now that's not it once you turn it on you'll need to close powerbi desktop and then open it back up in order to enable this feature do need to be on February 2024 or later version of powerbi desktop if you're on a newer version you're not going to be able to see this so make sure to turn that on and enable it there is some exciting information I want to announce very quickly before we jump right in and take a look at the material uh first of all as a result of this event we are going to open up our on demand learning platform and we're going to open it up so that three of our classes on our platform are free until April 14th meaning you do not need a paid license on our platform you can be on the Community Edition which is free and you can take a look at these classes that's going to be the introduction to Dax the advanced Dax as well as the Dax for paginated reportings course totally free until April 14th we're also doing a giveaway at the end of this session so if you attended the session today and you're here at the end of the session we're doing a giveaway which is going to be a free on demand learning license for an entire year and we'll be doing that at the end of the session and then finally coming soon we're going to have a visual calculations for the Dax course so we have a Dax course coming out on visual calculations that's coming to our platform coming soon we're currently recording that and then if you haven't been to our platform recently I do want to give a quick shout out to our platform and to our development team because we are now doing certifications if you haven't heard we are doing certification prep our platform is awesome it's really exciting if you haven't seen that check out some of the videos that we have on our YouTube channel they really highlight the capability and the experience and the interactivity it's really really cool so if you miss a question there's a video that pops up and says hey this is why you missed it this is the answer a quick little video it's really awesome the work that has been put into this so you definitely want to check it out um when you get in our platform all right so that's the exciting information here today we're going to be talking about visual calculations and when we're talking about visual calculations there's a couple of different things that I want to talk about around visual calculations what are they and I'm going to talk about that here in just a moment are they going to do away with Dax is Dax going away right so a visual calculation as defined by Microsoft is a Dax calculation that's defined and executed in a visual It's contained to only that visual so when you build a visual calculation you're building it on the visual and only on the columns that are currently in that visual calculation visual calculations do make it easier to create create calculations that previously might have been very difficult to create because it does remove some of the complexities from the Dax language it removes some uh things that you have to worry about with filter and row context and we're going to take a look at that as we start to dive into some of our demos here in just a moment now does this get rid of Dax is Dax going to go away well unfortunately for many of you the answer to that is no um it is a tool absolutely and it's a tool that's going to help you out a lot as you dive into parbi and you need to do some quick analysis of your data but it doesn't get rid of Dax so we still do have to write some Dax calculations from time to time still quite a bit but we're going to talk about that a lot as we're diving into the demos today and we have a lot of demos that we're going to be covering in this session today um another C just a uh more layman's term if you will of visual calculations or another definition visual calculations allow us similar capability to a tableau table so if you've worked in Tau um there's within Tableau there's table calculations that you can do or if you've worked in Excel I think we've all worked in Excel before creating an expression directly on a table inside of excel it's very similar to either one of those capabilities all right so as I said hour and a half we have a lot of demos we're going to jump into so we're going to jump right in there will be times as we're asking questions here that I will pause and stop and specifically answer questions that Nick has starred over on the right I can't keep up with all the questions that are coming through but we will be taking time to dive in and take a look at those questions so I am going to in the class files that you downloaded I'm going to open up right here the start file right so once you open up those class files you want to unzip the class files and then to follow along you're going to click on that link right there and the class files are starred or pinned to the top of the chat window and then we can also drop another Link in the chat window for everybody in the session today all right so I'm going to be starting with the start file you'll also notice that there is a completed file that completed file has essentially all of the calculations that I'm going to do today depending on whether whether I stick to my notes or not but there's a lot of completed information in there that you guys will be able to look at so once you've unzipped that file you'll be able to look at that right there all right so it is time to get started so the first thing is once you've enabled this in your preview features and I see some people still talking about it right so I'll just show it very briefly under file and then under options and settings and under options inside of parbi desktop so file options and settings options you can come to preview features right here and then you can turn it on right here if you do not see visual calculations inside of powerbi desktop that means that you're probably not on a newer version of powerbi desktop and unfortunately that means you also won't be able to see this capability right now that's okay this is recorded come back and go through all of the demos yourself later at a later time but that's where we're going to be with that now in order to take advantage of visual calculations once we're inside of powerbi desktop what I can do is I can come up here to the very top I want to select my visualization so you do have to select a visualization first and then up here at the top I can click on new calculation you'll now see that icon if you've enabled this feature and when I click on that that really kind of takes me to the focus mode of this visualization as you're going to see right now so I've selected on right here at the bottom make sure you are on the very first report page demo running sum so make sure you're on that page and then up here at the top go ahead and click on new calculation if you chose to follow along with me today and when I click on new calculation what it does is it really brings you into like Focus mode of that visual where you have the visual up at the top of the screen and down at the bottom screen we now have a new kind of formula bar that appears now here's the thing about visual calculations Microsoft has introduced some new functions that are only available in visual calculations within the visual context if you will and so we're going to see some new functions if you've never seen visual calculations before you're going to see some stuff that is new to you today now I want to show you a couple of things first and foremost if I come down here to the bottom you'll notice right here I can click on this little FX con icon and I'm going to see a bunch of templates that exist within visual calculations so you say Mitchell I want to do a running sum I want to do a moving average I want to do a percent of parent or grand total if you just want to build that out very quickly in the visual calculation and you don't want to write Dax and worry about filter context and all of the different complexities of the data model you can do it now right here and this capability really is gamechanging on many levels now I'm going to talk about some of the limitations today right so I'm going to give it to you you know the good and the bad but this is really awesome and so the first calculation that we're going to do is a running sum and so I'm going to click right here on the running sum template and it's going to pull that information in and it really kind of gives you the template like what you need to do now couple of things right I'm going to call this running total cell so let me change the name of this measure this visual calculation measure and then I have run running sum and what I want to do is I want to tell it that I want to do a running sum on total sales so I'm going to do total sales like that and that right now is going to give us a running sum across our entire table so check this out I'm going to hit enter and there we go and you'll notice this is a running total all time it doesn't start over it's not starting over each quarter it's not starting over each year it's just a running total all time and there we go now in a minute I'm going to show you how to do a year to date quarter to date and also how to do a moving sum so maybe three periods four periods Etc but this is a really quick way of looking at the running sum calculation and what you get now keep in mind you can only see this measure or this visual calculation in this visual calculation that I just did right you're not going to be able to see this anywhere else all right so what if I want to edit this visual calculation well what I would do is over over here on the right I can come over to values you'll notice it has its own little icon here and I'm going to click on running total and that brings me back into the expression here now I want to talk about a couple of other things that I'm going to highlight throughout the training today first of all if I hit comma there are optional parameters that we can touch and modify as we work through Visual calculations axis is or axis is going to tell us when I'm doing the running sum do I want want to do it across the rows or do I want to do it across the columns because we're currently in a matrix so if you had your years across the columns in The Matrix you could do the running sum across the columns instead of the rows the default is rows so if you don't specify anything here like we did not it's going to default to the rows you also get blanks what blank does is blank says how do you want to handle blanks do you want to put them first or do you want to put them last you follow me do you want to put them first or do you want to put them last the default is first if you don't spef ify anything it's going to go first so right now we would have rows and then we would have first that's the same as if we took the default the last thing here is probably what I consider to be most most important which is a reset reset tells it when to stop the calculation and restart so if you want to do a quarter to date a month to date a year to date reset is where the magic is and we're going to talk about that quite a bit as we work through the session today all right so let me go ahead and back this up a little bit to where we were originally right here which was our running sum now if I do running sum rows and I close that up you're going to notice that the result here is exactly the same because it's the default the default of that optional parameter is rows so nothing is going to change now I want to show you one more thing here I want to show you a couple of things I want to show you some limitations or some things that need to be talked about as we're working through this so if I go back to my report up here at the very top right I'm going back to my report so we're outside the visual calculation editor on the report right here if I want to edit that visual calculation you can't really edit it here it doesn't show up in a table it's not globally stored in your model it's on that visual so what I have to do is I have to rightclick down here on that visual calculation and then I have to choose edit calculation and that takes me back into that window where I can edit any calculations that are on this visual visualization specifically all right so I'll come back to that um in just a moment but that's how you get back in and that's how you edit those visual calculations the other thing I want to point out is notice that there's no way for me to click on this and go up to the top and format that visual calculation so that's a feature that's a little bit limited right now but there is a workaround for that there's a couple workarounds for formatting and I'll show you what that is here in just a moment as well so if you want to edit a visual calculation you right click on it go to edit calculation there is a limitation on formatting I'm going to show you how to format that in a moment and then the third thing here that I want to talk to you about is I want to show you right here this running total all time now pay really close attention to this part right here notice how right now when I get to 2007 the running total is $10 million in Sal so that means it's counting everything before 2007 until the current date time right in the filter context but watch what happens to that 10 million number when I click on my filter and I change it to 2007 when I change this to 2007 here it's no longer showing 10 million for January it's starting at January of 2007 this is a interesting thing to know because we always have built measures and we've always built Dax calculations and we always have to keep in mind what is the current filter context that I'm building my calculation within and what are what's the filter context that might be applied in the future and so the way visual calculations work is it's only based on what's in the visual we're not changing filter context we're not we're not modifying anything if you follow me right so what happens here is it's starting from scratch as if January 1st of 2007 was the very first day ever and then it gives us that running total so is this okay well it really depends on what your requirement is but if you give this report to an end user and that end user clicks on 2007 and it filters it down and they're like wait a minute running total all time for 2007 should be 10 million not 438,000 then that's a little bit of a problem so let me show you real quickly the the the kind of the alternative in Dax just a real quick way that if I were writing in this in Dax how I would write this in Dax right so I'm going to build a new measure here let me make sure I my internet cells table selected and then up at the top I'll create a new measure you don't have to follow along with this part I just wanted to make sure that I kind of highlight the difference here and where you might choose a measure over visual calculation you have to choose which one is right for you but I'm going to create one here we'll call this one running I'm G make this a little bigger running total sales and then we'll do measure equals and then I'm just going to do a calculate we're going to do total sales here and then I'm going to tell it that I want to return all the rows where the date is less than or equal to the maximum date in the filter context so effectively what I'm building in this Dax calculation very simple is I'm just building a running total all time right there's no limit on year there's no limit on quarter anything like that go to the date table get all the dates from the date table that are before the maximum date in the filter context and return the total sales for all those days so if you're at March of 2007 we are removing all filters that are coming from date both on this Visual and from the slicer and we're returning every single value that's there all right Ari had a good question here I'm gonna answer that question with you Ari in just a moment I'm going to go ahead and hit enter and then I will go ahead and put that calculated measure right here inside of my visual calculation and here's what you're going to see there is a drastic difference between the visual calculation and the measure that I just created which one is correct well that depends on you I prefer personally the calculated measure here because it's it's a true number of a running total cells right the visual calculation is limited to what is in the visual calculation now Ari just asked a question if you delete the visual do you lose the visual calculation is there any way to access it or is it gone forever to my knowledge it's gone forever right once you delete the visual calculation it's gone um because those visual calculations are contained in those visuals right so really good question Amy had a question how can I learn the Syntax for Dax um Dax is one of those beautiful languages you can learn about 80% of what you need to do with about you know within a week but anything after that it starts to get pretty complex diving into a lot of that um Marvin if I don't have much Dax experience should I try to learn or use visual calculations instead of Dax absolutely right start with the basics learn the basics of Dax the basic filter context leverage visual calculations as much as you can but just know that based on what I'm telling you right now there what I consider some limitations and it's not really a limitation with the calculation it's just understanding the truth of what a visual calculation is a visual calculation is performing the visual calculation based on what's inside of the the visual all right I'll answer more questions later we're going to Star a bunch of them and we'll come back and talk about those so let's talk about formatting and let's talk about one other feature real quick over here on the right hand side I'm going to rightclick on running total cells and I am going to edit the calculation let me remove my filter here at the top and then I'll go ahead and remove my measure as well and then right click and edit calculation remember I said that we don't currently have any formatting um that we can just do directly on the visual calculation back in the report well we can build some formatting into if you will we can build some formatting into the visual calculation itself and so what I can do here is I'll go down to the next line I can use the format function you can also use some other functions like the round function which has an optional parameter for formatting and I'm going to tell it I want to format this calculation and I'm just going to do a currency here so I'll do 0 comma 0000 z. z00 like that and I'm telling it hey take that data and format it in this very specific way so this is kind of the workaround right now for formatting those visual calculations until you know that becomes something that's available to us in the future which I would ECT all right so I'm going to go ahead and hit enter and then if we look down here at the bottom you'll notice we have now added formatting to that visualization all right so there's our formatting I'm going to pull it back up so you guys can see the code now the other thing I'm going to do is we're going to do another running total CES real quick because I'm going to show you something else that we need to know about visual calculations when we're working through them so here it is let's build a running profit we're going to build a running profit calculation just so you can see what we're doing here inside of this calculation so over here on the you can really come up to the top when you're inside of this kind of visual calculation editor and I can click new calculation right here at the top of my screen to add another visual calculation to this table so I'm going to click on new calculation here and then I'm going to create one called Running profit and this kind of threw me off a little bit here so running profit and then I'm going to do running sum if you remember we have this new function here called Running sum and what I want to put in here is really my profit I have a measure for profit I've created a measure for profit so I want to type in profit and what you will notice right now is inside of this running sum calculation I cannot see anything that is outside of this visual calculation I can't see any other columns I can't see any other measures those things are hidden to me even though they existed my model so a visual calculation is exactly that it is it it it only sees the rows that are in the visual calculation as I showed you a moment ago and it only sees the columns that are in the visual calculation so if you wanted to do a running profit you literally need to First add the profit either into the column here or you know into your um really you need to add it to the table and then hide it and you can hide it I'll show you right over here there's these little hide icons so if you need to add a column just for the purpose of a running or a visual calculation but you don't want it to show in the Final Table after you've created your calculation you can hide them right so let me show you real quick if I were to bring profit into this table by clicking on profit so profits now here in the table now I can come down here and I can say profit so there's my running sum right so I got a running sum on running profit right here because I added it to the table up above and now I can hide profit if I want to hide profit yes thank you Lucas you must click on the visual first if you want to be able to go in and create a new calculation all right I'm going to get rid of the profit and I'm going to get rid of running profit in this calculation all right so that was formatting that was the thing so that's what I want to show you let's dive a little bit deeper into this running sum C calculation let's talk about how do I do a quarter to dat how do I do a year-to DAT calculation how do we extend it with just one little feature here right so the next calculation I'm going to build is up here at the very top I'm going to click new calculation again so I'm inside of this kind of visual calculation editor and we're going to create one here called running total year today and we're going to take advantage of some of those optional parameters that show up that maybe you haven't seen even if you've been working with these visual calculations so we're going to do a running sum right there and then I want to tell it we're doing a running sum on total cells and then remember these parameters are all optional so the axis is automatically going to be rows that's the default so I can type rows in here and for the blanks you could you could omit if you want you don't have to put something there so I could just put another comma and skip that um or I could put in there their first or last remember the default is first which is how you're going to handle your blanks if you have blanks how do you want to handle them do you want them to be first or last I don't really up till now I haven't really needed to leverage this parameter so I just kind of omit it and I just put nothing there and say go with the default now we're on the reset parameter and the reset parameter is really awesome because what it tells it is it tells your model when to start over when to start over and so what I want to tell it here is I want to start over the highest parent all right so if I start over at the highest parent what I'm saying is I want to start over at the year level because the year is the highest parent okay let's talk about this for just a moment I'm going to hit enter and then we're going to dive into this and we'll be able to kind of talk about that quite a bit um we did have another question here I can't find it can you reference a measure within the visual calculation no remember the visual calculation only sees the columns that are in the table so if you want to reference a measure and you're in the visual calculation Tulie what you need to do is you need to add it to the visual calculation first then you perform your visual calculation all right so let me zoom in for a moment the highest parent right now is year the lowest parent which is your other option is going to be quarter and then the month right now is not a parent at all right now the month is actually the leaf level it's the child right so you have the highest parent you have the L lowest parent and then you have the leaf level now you could have multiple levels of parents you could have year and then semester and then quarter and then month and then day which means year quarter semester and month would all four be parents of day to some degree right but right now we only have two parents that was a little tricky for me when I first started learning visual calculations before there was a lot of documentation trying to figure that out I kept thinking lowest parent was month which it is not month is a child it is not a parent here all right so highest parent when I hit enter is going to now give us a year-to-date calculation and you're going to see that it does exactly that so unlike the running total sales once we get to the end of 2005 it's going to start over right here when we get to 2006 because we use that reset parameter so it's not starting over it's resetting as it gets to that year it resets because we told it inside that optional parameter for reset we said highest parent right so we took advantage of that optional parameter now optionally we could do a quarter to date calculation and I'm going to show you a couple of ways of writing that quarter to date up here at the very top I'm going to do a new calculation and so I'll do running total qtd equals and then we'll do running sum so you'll get very familiar with these very quickly here we're going to do a a running sum and we're going to do a running sum on total sales and then we are going to do rows I'm going to omit the blanks and then I'm going to tell it for reset to do the lowest parent now remember I said that currently the lowest parent is quarter all right so I'll close that up and then I'll hit enter and that is going to be let me see if I can keep the expression up there there we go and you'll notice that right here I can get most of it in the screen this is resetting every quarter so you have the first quarter 473,000 97 1.4 million boom we get to the next quarter it resets now there is something yes it resets to zero great question it starts over once we hit that next parent whatever we defined in our visual calculation couple of things to keep in mind here right I said lowest parent right now the lowest parent is the quarter but it could change right let me show you what I'm talking about and I'm going to do this part quickly just so you capture this and we captured on the recording if I go back to report and then I come over here and I add to my rows I have year quarter month if I add the date to the rows right here my lowest parent is now the month so this is not going to restart every quarter it's going to restart every month because the lowest parent is month and I'm telling it to reset at the lowest parent everybody with me that's important to know because this is not a Dax calculation where we're being very explicit and we're saying quarter to date month to date year to date we're saying reset on this level of the hierarchy and we just changed the level of the hierarchy the parent just changed so if we're looking at this this is the highest parent this would be kind of the second parent and then this is the lowest parent because now we've changed the child of that visual all right so let me go ahead and clip I'm going to go back over to the visual calculation editor by right clicking on one of these existing visual calculations and going down to edit calculation and that brings us back so here's a here's a trick for you and this is really really cool instead of lowest parent or highest parent you can actually use the ordinal position so if I would have used the ordinal position 1 2 3 4 and I would have done two that would have kind of protected me from that because quarter is number two right so if I put two there this is the first parent one this is the second parent two this has become the third parent three so if I would have put two it would have remained a running total qtd even though I added the month into that calculation all right so hopefully that part right there makes sense I am going to pause for just a moment and I'm going to look at some of the questions and then we're going to jump right back in because we have a whole lot let me hit enter too so we can see it all right so if I were to scroll all the way down far enough here to let's say September you see it's still climbing it's still climbing it's not starting over then you get to October and it started over so by by specifying the ordinal position two instead of lowest parent I have a little bit more flexibility all right let's go take a look at some of these starred questions here if you copy Dax produced by a visual calculation and paste it in a measure will it work no no generally not because remember it's only referencing The Columns that are in that visual calculation and there are new functions that are only available inside of the visual calculation the next question what about conditional formatting on those calculated columns is that possible technically kind of yes technically no conditional formatting is a limitation let me show you this if I come back to the report here you can't really click on these and set up conditional formatting however there is a little bit of a workaround that I found which is I can go to format your visual you can go down to sell elements here and under cell elements what you can do is you can choose one of your columns like let's say running year to date and then I can click on like background color and what it'll do is it will set up conditional formatting now this was a terrible option but we're getting closer to some greens up here but that's kind of how you can do it you can also go into the editor here but you'll notice that one thing you cannot do is you can't go in and specify that visual calculation as what you determine the rule on so kind of you can but not to the same degree that you can with other Dax calculations all right great question let's answer a couple more how do we remove a calculation all you have to do to remove a calculation Paul is if I go back over to my fields rightclick and go back well you can actually just click on it right here you just click the X and you can get rid of it once you get rid of it though it's gone so just keep that in mind can we use variables in visual calculations absolutely and you can use a lot of existing functions in Dax so you can absolutely do that and I'm going to show that later to get to the middle parent can you do highest parent minus one good question I've not tried that Michael so I am not sure uh I'd say probably not but I'm not sure so are visual calculations only limited to a table or Matrix no they you can do visual calculations in a few visuals but not all visuals great question all right so I answered a bunch of questions there let's dive back in and let's talk about some other things that we can do inside of visual calculations I'm going to move over here to My Demo two and what I want to show in demo two is I want to show you how to do a running sum or a moving sum a moving sum is saying hey every three periods let's drop a let's drop a month let's add a month let's drop a quarter let's add a month so I want to do like a three-month running total I want to do a 12month running total I want to do a 15-month running total there's a lot of documentation out there that says Mitchell you you can't do that that's not necessarily true I found a way to do that and I'm going to show you how to do a moving sum right now so on demo two this is a new powerbi report page at the bottom I'm going to click new calculation and due to time I won't dive too deep into this like I wanted to here but this is a good shout out for our class our Dax visual calculation class is going to be a lot longer going to be a lot more detailed a lot more conversation I get to dive into everything I want to talk about and really dissect this so make sure to keep an eye out for that coming out to our platform soon and so what I'm going to do here is call this a moving sum and then I'm going to do equal and I'm going to use a function here called range range is the magic that's going to make this work so I'm going to type range and then I'm just going to say minus one what range does and you can see the definition on my screen range is going to retrieve a range of rows within the specified axis which is going to be our rows so it's going to if I'm on March it's going to get February and March so it's going to include the current and it's going to go back one right so I'm doing minus one and then I'm going to close that up you will see that there are optional parameters so I can say do I want to include current well the default is true so it's already going to include current do I want to use the axis of of do I want to change it right now it's set to rows so I don't need to change it and then we have blanks and then we have reset which we've seen before so what we need to do here is just this now I cannot hit enter right now because if I try to hit enter right now what it's going to do and I'll flip back over to the chat window what it's going to do if I if I hit enter is it's a it's a table of results and you can't return a table of results what we can do to try to debug this or just kind of validate is I could do something like count rows this is a little bit of a debugging tip and trick for you to kind of count the rows in a table and see what does it display does it look correct so if I hit enter I expect it to say two for every month except the first month right so I'll hit enter and then if we come and look at this I'll pull it back up so we can see it you'll see that when I'm looking at March the value is two why is it two because it's grabbing all the rows from March and all the rows from February or both of those those are the two rows that it's grabbing it's grabbing the current row and the previous row now you say well Mitchell how do I know that how can I see it is there a way to visualize it I'm glad you asked that's such a great question yes there is I'm going to show you one of my favorite tricks in all of Dax you get it day it's free no charge I love this this tip that I'm about to show you what I like to do is use a function called concatenate X concatenate X I love X functions they're my favorite so if I type out concatenate X concatenate does exactly what you think it does it takes a series of string values and combines them together into a single scalar value right one row and I'm going to Loop over this table and I'm going to work over this table which is these rows that you see here right right everything that's in these rows and for each one of those rows I'm going to return the month right I'm just going to return the month and then I'm going to add a little bit of a delimiter here like so and I'm going to hit enter so if I hit enter here what it does is it kind of changes it and let me explain exactly what this does real briefly real quickly here this is returning if you're looking at March that's returning everything from March and everything from February that's in the visual calculation right we did a count we verified that's exactly what it's doing that looked great but we wanted to see it we wanted to visualize it so I said all right well for those two rows in that table return only the month from both rows and then concatenate the months together into a single value so now I'm able to visualize with my eyes a virtual table that's pretty cool in the Dax language this is one of the tricks I use all the time when I'm working with customers when we're on Virtual mentoring calls and I'm kind of coaching them and helping them solve problems I'm able to see their data and what's going on and what's happening within the filter context by using this trick so I hope that for those of you that know Dax a little bit you can appreciate that now what we want to do is I actually want to do the moving sum to show you how to do it so what I can do here is I can use sum X and I'm going to sum up the sales from those two rows right so we're going to use range like we had before I'll do minus one to go back one row and include the current and then all I'm going to do here is do total sales and that is the magic right there that is going to give me a running or a moving sum so I could do five periods if I want I could do 10 periods you could do all of those if I hit enter and we zoom in for just a moment you'll notice that when I get to February February is the running total of both January and February when I get down to April April is the running total of March and April so it's a two-month moving sum people say you can't do that here but you can you just saw it right now that's how you do it all right so that was the moving sum calculation um I'm going to dive into the next one here so I have more time for more questions which is moving average before we jump into moving average let me see if there's any new questions I don't see anything starred here absolutely Dax oh that was by pragmatic Works Dax is not deprecating trust me visual calculations are not replacing Dax and that's going to hurt a lot of people because when you write Dax you really have to be attuned to what is filter context what's going on with the data models how are those relationships built understanding row context and context transition I love it I love Dax I am in the very small minority but it's also something I love solving problems and I work with customers literally every day in Virtual mentoring calls just to fix their data models in right deck so I really do love it but yeah this is not not going away Dax is not going away but visual calculations are an awesome tool that we can leverage that help you get to Market faster all right so let me see we got a couple starred ones here let me see what those are can you use visual calculations to do a year-over-year percentage and Deltas so yes there's absolutely the capability of doing that in here um I don't think we'll get to it today but we will talk about some functions in here like first and next and things like that how do I join this group so that I can have one-on-one minut okay so um our our marketing department uh Nick will put something in the details there for you in the chat window if you're interested in one-on-one mentoring it is a service that we do a lot of work with customers on all right so let's jump into moving average this is a big one moving average is hard I'm going to show you the Dax how you write the Dax for this as well there's a couple ways you can write it but this is definitely one of those challenging ones and I'll come back to that performance question in a minute Andrew so hold that thought so so now we're going to go back to our report and I'm going to jump to the next report page that we have which is moving average right so this is really cool I'm going to go ahead and on this new report page again what are we going to do we're going to click new calculation and when I click new calculation right there Tina you are awesome thank you for sharing that Dax calculation with the group I appreciate it shout out to Tina I'm going to create a new calculation here and and this calculation is going to be our moving average all right moving average in fact you know what let's just use the template if I click on these templates here remember I can just click right here and it's going to pull up the template for what the moving average is so I will click the template you don't need to use the template right you can kind of Leverage this your own um but I'm going to do a moving average and so we'll call this one moving average I'm going to do a moving average over the total cell again I can only see what's inside of my visual calculation and then I have to tell it the window size right so it's a moving average do you want to do a three-month average or three quarter average do you want to do a three a five month average a six month average what do you want to do so I'm gonna do three all right three is good we're going to do three all right so we have a moving average here um I'm going to rename this the three period average and you're going to see why in just a moment so this is my three period average and then we're going to hit enter and that's going to bring us right here to this screen all right so this is awesome when I get here what I see is the average of you know that's all we have right this is the beginning of our sales but if we go down a little bit further in our data like maybe right here to March this is the average of those last three months so that's pretty cool now there's a couple of things with this that I want to talk about right um first of all the three period average is the average of the last three months um right now but it's a visual calculation and the visual calculation changes and is modified based on what's inside the visual calculation very important to understand that part right so let's go back and look at this in the model if I go back to my report view so when you're building visual calculations here's a question I highly recommend that you ask yourself how will my end users be interacting with these visuals once I publish and once I share have you enabled the kind of personal report view where they can modify visual calculations are they able to work with hierarchies and drill up and drill down because if they are that is fundamentally going to change these visual calculations let me show you what I'm talking about if I come over here to this three period average that we just created watch what happens when I drill up in the hierarchy when I drill up it's now a 3/4 average not a three period average it's not a three month average now it's a three quarter average if I drill up again to the next level it's now a threeyear average you see how it changed it's very recent you move up and move down the hierarchy but maybe that's not what you want right so let me show you the alternative if I will for just a second to that three period average there or that three month average how would we write this three-month average in Dax if we were wrri wri it in Dax um the other thing I want to show you here is and I've talked about this before if you look at 2007 I do want to show one more thing before I write the Dax calculation because this is something that anybody using visual calculation should know about if I come over here and click on 2007 notice how this three period average doesn't see it doesn't it's not it doesn't have access to 2006 and before so when I do a month average for January of 2007 it should be November of 2006 December of 2006 January of 2007 that should be the three months but it doesn't see it because it's limited to only what's in the visual calculation that is different than if you write your own Dax measure and that matters so if I'm going to write a new measure real quick you're going to see the difference let me create a new measure I don't I wouldn't encourage you to follow along because this is going to be quite a bit of Dax here I don't want you to be discouraged but I'm going to write a new measure here and I'm going to call this one three Monon average all right and I am monitoring the chat window as much as I can there's a lot of content in there so it's hard to see everything the first thing I want to do is I'm going to say calculate we know that we are changing filter context right so I'm going to calculate my expression I'll just put a one here for now I like to always change my filter context first and so I'm going to say dates in period I want to go back three months so we'll use the date column from our date table we're going to do uh start date is going to be last date date and then we'll do last date from the date so that's going to be our start and then I'm going to go back three months and then I'm going to do it at the month level now this is a lot of code if you have not written Dax before it's kind of intimidating and thre Monon average or a month average is kind of complex because now I've changed the filter context so if I'm looking at September I can now see everything from July through September so anything I do in the expression is going to have full visibility to all three months not just the month that I was on that's why I changed the filter context if you are not familiar with this go watch on our YouTube channel our three-hour session on introduction to Dax okay it's a very popular YouTube video we have you should go watch that if this is new to you now the next thing I want to do is I need to capture the individual months within the filter context which is the last three months so I can come in here and I can use a function called summarize on my date table and I'm going to return from the date table only the month so this will return a distinct list of all the months in the filter context now this is a table you can't return a table I can't say hit enter and return a table where a single scalar expression is expected but what I can do is I can do an X function over this table I told you X functions are my absolute favorite functions in Dax so I'm going to do an average X right and now I'll hit Escape there average X just like this and then I'm going to go ahead and tab in and I'm going to say for every one of those months return the total sales for each month so within the filter context return total sales and then once it returns this virtual table once it's done at the end what you get is you get a table that has the three months and the total sales for each month and then average says okay let me get the average of the sales that's all it does so let me show you real quick if you kind of break down that expression for September what it's doing in that virtual table right there really the whole combination of this is it winds up creating a virtual table that looks exactly like this because I'm getting the last three months so it gets July August and September that's what my summarize does The X function iterates over each month and Returns the total sales so 886 847 1 million and then average says okay from the total sales get the average so this is us personally customizing it I'm going to hit enter here just like this all right and then we're going to go look at this inside of our expression let me find that calculation there it is and you're going to notice that at the beginning here there is a difference between my calculation and the visual calculation my calculation is removing the filter from date regardless of whether it came from the slicer or if it came from the the the the the the row the year and it's able to access November of 26 2006 and December of 2006 so I get the actual the correct three-month average where the visual calculation really only sees what's in the visual so that is one thing to keep in mind as you're working with these calculations here inside of Dax all right so that was that part right there I did that scroll down scroll down all right so we have a couple more here but we are looking great on time so it is time to see if I can find some more questions does anybody know of a great Dax or PBI Forum Castro you should join our Discord Channel we have a Discord Channel at pragmatic works we just started it it's growing it's new there's not a lot of people on there our Discord channel is a great place for the community to get together and help out I don't have time to be in there all the time but we should help each other out in that Discord Channel literally just finished Ari thank you that's awesome appreciate that shout out there and then are the three period averages done at the year level in bold Oh Oh you mean up here so that right there is I would have to go back and look at exactly what we're specifying there because it looks like yes it is yeah all right for sure so you'll notice when you get to the second one um I don't think that was that was Michael Roberts when you get to the second one this is the average of um you'll see it's the average of the quarters right so if you collapse this down or go to the next year 2008 which I don't have years in here you would see the average at those levels as well so yes the answer is yes all right I don't see any other questions right now that have been starred so we'll jump into the next section make sure to keep asking those good questions and then we'll have somebody jump let me you know what Jen great question let's hit that performance one real quick I like that question performance performance performance so how are visual calculations as it relates to Performance versus Dax calculations well if you're working in a small model like we are right here visual calculations actually from every performance test I've done actually take a little bit longer but you can't really see it to the eye we're talking about milliseconds so it really doesn't make a difference right if you come over to view at the top and you come over to Performance analyzer right so if you come to the view ribbon you come to Performance analyzer and you kind of run it with the visual calc and then you run it with a Dax measure you can look at the time difference and probably because of the fact that it's you know the engine brings back the result and then the visual calculation does another calculation over that on smaller models it's a slight usually it looks like it's a little bit longer to me however if you're working with a really big data model right a big data model lots of data and I haven't tested this on a big data model word from Microsoft is that it's going to perform better with a um it's going to perform better with a visual calculation because you're not having to do that Dax measure across you know you know millions and millions of rows of data once their data is returned to the visual you're just doing it over 10 rows of data so performance technically should be better on larger data sets on smaller data sets it's so fast you're not going to see the difference now somebody else asked a question earlier and the question was I forgot about it um but it was way earlier and oh I didn't record the question was can we see the Dax that's being used by visual calculations and the answer is yes you can turn on performance analyzer you can start recording you can kind of flip back and forth to the page and then you can come in here and you can copy out the Dax query and you can take that over to the Dax query view you can put that in Dax studio so you will you'll you'll see that there and John is adding a little bit of context for the group John has an eight million row data set and visual summing for John is much faster keep in mind data models are a big part of Dax and so you know you might have eight million rows but you might also have a lot of relationships you haven't uh denormalized your model down to a star schema so that's that's an issue all right so let me jump into the next section here and let's talk about previous and last this is awesome I'm super excited for this next section here as well so I'm going to go over to demo 4 demo four is our report page at the bottom and we are going to as you can imagine from the home ribbon here we are going to to add a new visual calculation so I'm going to build a new visual calculation here and again if you click on the templates you'll notice that there are some really cool templates here for looking at the previous Row in the visual context looking at the next row in the visual context looking at the first looking at the last this is really really cool capability so I'm going to go ahead and we're going to take a look at versus previous first okay so I'm going to go over here and do versus previous and when I click on versus previous it builds out that template now I've gotten to the point that I've been using this so much that I don't really use these templates anymore but the templates are cool if you're trying to learn now I'm going to teach you another trick we've been we've been learning tricks in this class right we want to do previous based on total cell so I'm going to highlight field and then on my keyboard I'm going to click control shift L okay control shift L and what it does is it highlights all of the word that match that in your code when I wrote tsql right I still write t-sql we still do classes on that but when I was doing that as a consultant every day there's a shortcut in there to do the kind of the same behavior but control shift L will grab everything now I can type total sales and I can replace both values at the same time so that's an extra trick for you too um free no charge so this is going to give us total sales minus prior right that's really cool so if I hit enter we can now come over here and let me pull up the visual calculation so we can see the result and what we're getting here is we're getting our total sales minus the prior sales which gives me that 32,000 um $32,000 difference right so that's really really cool and it's really easy getting this before if you were trying to build this kind of calculation inside of Dax there wasn't a super great easy way to do it in a measure because because filter context can change significantly based on working with it but this always goes back to the last one so this is really really cool now the other thing you can do is I like to look at these by themselves so if I go ahead and get rid of the total sales here we have previous cell so let's just do that for quick verification I'll pull that up so this is going to be your previous total sales and you'll notice it is working perfectly now keep in mind that this will change this will change as your visual changes so as you add columns or remove columns to that hierarchy in the rows this will change based on that right so this is going to be the previous you can also do next so I'm going to build another calculation just very briefly here new calculation and we'll call this one um next so we'll keep this one very simple here equals and we're going to do next total cells so that would be grabbing the total cells in the next row kind of future or looking future um yordana said is this like earlier function in Dax it is not this is significantly different than the earlier function we don't really use the earlier function much in Dax anymore earlier function is a much deeper topic about nested row context so it's when you're you're in a row context and you create a new row context and you need to reference the outside row very deep it has nothing to do with previous row in a visual or in a table really it's it's different it's much more complex all right so next total sales I'm going to hit enter here and then I'll go ahead and zoom in and you'll see that if I'm looking at October I can see the previous and I can see the next so these are really awesome features you can go forward you can go backwards now one more thing I want to show you of course is if I go back to this visual calculation this function that we just learned about and I hit inside of here I hit comma yes it is loes this is new and this is new to visual calculations previous and next are new functions that are available right here in visual calculations another great question you'll notice that we do have some optional parameters so if I want to go forward to rows or I want to go forward three rows or I want to go back five rows you can do that you can specify the steps you can specify your axis if you want to go columns or rows and then of course you can tell it if you ever want to reset so maybe you only want to do do it within um within the hierarchy of a department and when you get to a new Department you want it to reset right then you can do that you can do something like that there if you want to we've talked about the reset parameter many times already in this course so I'm not going to kind of dive into that too much but that's that now let's do real quick kind of a sales growth calculation and so I'm going to build a new calculation real quick yes Brian we can we're going to take a look at that right now so Brian said can you do if statements with next or previous so we can build if statements individual calculations and that's exactly what we're about to do right now um and we're going to dive into this a little bit so we're going to build a sales growth calculation I'm going to show you a couple things we can do here so sales growth all right equals and then we're going to use variables that question came up earlier right can we use variables here yes we can so I'm going to build a variable and the first variable I'm going to build the first variable is going to be called previous cells so I'll do previous here cells equals and then we'll do the previous function I misspelled it there we go previous total sales boom so we created a variable I'm going to build another variable here and this one's going to be called current cells and that's just going to be total cells right there we go and then I'm going to build the result so what is the result the result is just going to be simply total CES minus previous CES so I'm going to build another variable here called result and that's going to equal underscore current cells minus underscore previous cells now could we get a percentage out of this absolutely if we wanted to get a percentage growth we would say hey take the current cells um minus the previous cells divided by previous cells right so we could do that percentage here as well I'm going to go ahead and do a return Clause now that we're done defining our variables and then I'm going to return the result now as I return the result here what I want to do is I want to format this so I'm going to format the final result right we talked about formatting earlier and so I'm going to say format the result so return the result but then I also want to format it as double quote currency let's see here um 0 comma 00.00 double quote close that up and then we can hit enter and that's going to give us our growth now we're going to answer the if statement right now right so you'll notice that I'm getting this sales growth it really it's a difference we didn't do percentage here but I can see the difference between the total sales and previous and right down here total sales versus previous that's going to be roughly around what 30,000 right now this first one I really don't want to show because there wasn't a previous so if there's not a previous maybe I don't want to to do the calculation right a basic if statement if previous is blank don't perform the calculation so can we do conditional logic inside a visual calculation absolutely yes we can all right so let me go over there and pull that up and then I'm going to come over here and let's build this if statement all right so if let me show you how to do this if underscore previous cells equals blank and there's a couple different ways to write this but if it's equal to blank then we're going to go ahead and return blank and then I want to format this a little bit like so right there we go and then I'll go to the end and close that up so if previous cells is equal to blank then return blank for this visual calculation else return this result which is current cells minus previous month cells so that right there is going to give you exactly what we want here we're able to use variables we're able to do formatting and and we are also able to do a conditional statement inside a visual calculations so that answers a lot of questions around what can we do in a visual calculation can we leverage existing functions yes can we do conditional logic check can we do formatting even though we can't really do it outside of this check we can do all of those things right here inside of the visual calculation and of course if you want to build on this expression here you can build on that as well remember if you are not able to keep up with the code here you can always let me see if I can paste it there there we go if that works I hope that worked for you guys I just Tred to paste it in the chat window it all of this is in the completed powerbi file that I provided as well all right so if you open up the completed PBI file you have everything that I'm doing in the class saved and in that file it worked it's right there in the chat window okay I did see another question that I really wanted to answer here see if I can pull it up real quick it's simply Pur yeah I don't see it now there's so many questions I apologize all right so the next thing we're going to take a look at is first and last there are some other functions here in fact while I'm transitioning from one section to another I do want to talk about the fact that on the while we're having a live event today with all the exciting upcoming things coming up we're also doing a sale so you get access to our platform until you know April 14th but we're also doing a 50% off sale for this event right now we have a special code for it marketing is going to put that in the chat window for you they'll put it on the screen we are doing a 50% off sale on our OnDemand learning platform that you can leverage you can take advantage of that is an awesome deal especially now that we have the certifications built into that definitely take a look at that and uh and and and we'd love to see you out there on our platform all right so we're going to move over and we're going to talk about a couple of other things we can do which is there's some some other functions that are really cool inside of visual calculations that we can leverage so I'm going to go back to my report and down here at the bottom I'm going to go over to demo 5 first and last demo 5 first and last so now what we can do is we can actually build out a calculation that's able to compare where we're at in the visual to the very first value in that visual or within the reset kind of property that we set within the hierarchy that we set so you're going to see that right now if I come over here and I create a new calculation on this visual this is actually going to be really really easy to leverage here and so I am going to build a new calculation down here at the bottom let me zoom in and then the first one that we're going to build is called first value in visual right so I'm going to call one this one is going to be called first value in the visual no kind of optional parameters and then we can leverage a function here called first and then of course we can see the syntax of that right here and the expression I want to pass in is going to be very simple it's going to be total sales so if you think about this what this is going to do is regardless of where I'm at right here it's always going to bring back the first value that's in this visual calculation right there 473,000 so I'll hit enter and I see that all the way down so it kind of gives you that standard that says hey this was the opening balance from the beginning of the time in this Visual and I can reference that to get a difference here right and so that is the first value in the visual you also have a function in here called last what do you think that does it gets the last right so if you were looking at only the year and you were filtered down you'd see the first value in the year and the last value in the year and you can kind of get the Delta you can get the difference very very easily with this calculation all right so that's the first value in the visual let's build another function on this and we're going to get the first value in the year or the first value in the quarter right so let's take it just a step further I'm going to go up to the very top and click on new calculation and I'm going to call this one let's go with year first so we'll do first value in year and then we'll do equal and I'm going to zoom back in and so what I want to do is I want to do first again right first and then we're going to leverage this time some of these optional parameters as you can imagine so we're going to do total sales for the axis we're going to do rows remember that's the default we've been doing rows we're going to keep doing rows here I'm going to go ahead and omit the parameter for the blanks I'm just going to put a blank there and then for reset think about this for just a moment for those of you following along what are we going to reset on if we're trying to get the first value in the year what do we want to reset on in this table feel free to put that in the chat window if you want to to kind of show us what you've learned in the class today all right I'm going to actually do moment of pause highest parent right or if I wanted I could do the value of one which is the first parent that shows up there so I can do highest parent and hit enter and what that means is that it resets every time the highest parent value changes so when we go from 2005 to 2006 it's going to change yes good job Teresa good job Gabriel you guys got it so I'm going to hit enter and we got it now so it's 473 right but as we go down you'll notice that as we transition right here to a new year the value is going to change and it's going to return the first value for that year so by leveraging that reset parameter that optional parameter we're able to really dive in and get some really cool information about what's going on in this visual calculation all right so that was the first value in the visual the first value in the year if we wanted to get the first value in the quarter we got to change one thing right so we'll build out one more quick calculation just so you can see it and get more familiar with that reset parameter and I'll Zoom back in down here I'm going to say I want to do a let's see first value in quarter equals same thing we did before you start with first and then we're going to do total sales and then I'm going to go ahead and omit the axis because it's rows by default omit the blanks and then we're at the reset property and for the reset property I can either do lowest parent because this is the lowest parent remember that or I can do two the ordinal position of that parent so one two 3 four so if I wanted to be more explicit two would actually work really well here but I'm going to do lowest parent that's fine and then close parenthe or close parentheses and then hit enter and that's going to give me the first value in the quarter so each time the quarter resets the value resets that's why it's a reset property each time it changes the value resets somebody is seconding a request by Michael what is this request it would be great to have a text file included with the student files that has all the URL links being shared that is a great recommendation you know what we're going to do Michael we'll update that we'll update the class files and so after the session you can download it again and it will have those links in there we'll do that for you that's a great great uh great feedback there all right so that was first and last we're going to jump into the last section here which is going to be on expand and collapse which is also really really cool if you guys have any questions keep putting them in there and Nick is going to keep starring them so I can find those questions quickly and answer those questions for you remember April 22nd we're going to do a live event Q&A on visual calculations in Dex so bring your questions or put them here here now and we can answer them in that live session on April 22nd so I'm going to go back I'm going to go back and we're going to go finally to demo 6 the report page here at the very bottom and we're going to take a look at how to do things like percent of parent percent of grand total very quickly very efficiently right here inside of the visual calculation which is again a lot easier and a lot faster than if you have to write your own custom Dex in side of powerbi so I'm going to go and click on this visual calculation we're going to click on of course as you can imagine new calculation right there and then I'm going to go ahead and build out now remember again you can always leverage these templates again I've gotten a little lazy with using them because I've used them for a while but if you use percent of parent or grand total they actually use some functions here called collapse and expand I'm going to build this out kind of quickly showing you what collapse does what expand does and then I'm going to show you a document that's very important that we'll also put in the class files after this event is over as well so the first thing I want to show you is something called collapse parent okay what collapse does is it actually collapses up one level in the hierarchy so if you're at the the the day level it collapses up to the month level if you're at the month level it collapses up to the quarter level so it's going to collapse up right now we're looking at geographical data so if I'm at the city level it collapse up to the state level right so I'm going to call this one collapse parent and then I'm just going to type equal and then we're going to do the function collapse and then I'm going to tell it total sales okay and what this does is it collapses up one level so that we can now easily do a percent of parent calculation which is really cool right so I'll hit enter and then it does unfortunately there is one more thing there's an like a required parameter that we have to put in here so I'm going to put rows in here and then hit enter all right and what it does is it's collapsing up to the parent and the parent here as you can see right here for New South Wells what is the total sales for New South Wells you can see it right here in the top it's 3.9 million and so when I'm looking at the city it collapses up one level so that I can see that value right there so now what is a percent of total calculation super easy right it's total sales so you build a new visual calculation that simply says take the total sales and divide it by the parent you got percent of parent right there it's done that's your percentage calculation in fact let's do that real quick so we're going to build a new now I could have put this collapse parent in a variable I'm not going to do that though I'll build a new one so we can see it and I'm going to build one here called percent of parent right so percent of parent and all we're going to do is say hey let's take I am going to format this when I'm done but I'm going to say let's take the total sales and let's divide that by the parent right so I can divide it by the parent now I do want to format it into a percentage so what I can do here is I can use format this is one I'll probably mess up but then we'll fix it afterwards it's all good and I'll tell it that I want to do double quote 0.00% and I'll format it as a percentage here and let's see if we get that yeah that looks good okay got it and let me pull that Dax calculation back up for just a minute and I will put that in the chat window for the group as well there you go all right so if you come down here and look at it we now have the percent of parent which is simply taking this and dividing it by the parent super easy super clean not a lot of worrying about what filters are being applied and context and all of that just a really easy calculation and it's Dynamic meaning whatever level you're at it always gives you the percentage of the parent above it every time now there's another thing that we can do and the other thing that we can do is we can get the percent of total for the entire table right so I could do percent of total let me kind of dive back in we're going to build one more measure two more measures we're going to build two more measures I'm going to go and click on new calculation here this one is going to be called percent of total equals and let's just start with something called collapse all okay if I do collapse all what you'll notice is it means take everything and collapse it all the way to the the the total row so if I do collapse all and I do total sales look at what the result is at the end ah I always forget most of the time the axis is uh not required but here I actually need to show something else in a minute here it is required so I'm going to collapse everything at the row level and what it's doing is it's getting the total for all countries for all states for all cities for everything in the entire table literally the entire it collapsed everything even the highest level of the hierarchy is collapsed so it's going all the way up to the very total row now the reason you say Mitchell why is it required here but not in other ones well the reason that it's required is because you actually have the ability to be more explicit when you're working with collapse and expand than you do with some of the other functions that have the axis parameter and what I mean by that is this if I go back into here instead of just putting rows or columns you can actually choose to specify a column that's in your visual so I can go outside of just rows and columns I can come over here and I can say hey you wouldn't do this on collapse but on percent of parent when I do this collapse right here let's go back to collapse parent right here what I'm might do here is instead of saying collapse rows if I know that I always want to collapse this to the state level I can specify State specifically I can have a lot of specificity there where I say collapse this at the state level instead of just letting it be one level ahead I can be very specific this is not something that's necessarily available everywhere you see axis but in collapse and expand you have very much Gr kind of granular control there and it will change the result right here right um which is what we see for New South Wales right there all right actually new South Wells we would have we' have actually done it at the lower level here we would have collapsed it right at the city so collapse the city and then it shows the state so I did that a little bit off there but um you have some some some specificity that you can do that you can't necessarily do with some of the other functions which is why it kind of requires it here all right so that's good last thing I want to show you and then we're going to talk about limitations we're not done we want to talk about limitations real quick you also have the expand capability and I'll show you when that's really cool and when that's relevant so we're going to just take advantage this time of one of the templates here I agree a visual calculation cheat sheet would be great and uh we'll make sure to take care of that I'm going to go ahead and do a let's call it an average of children an average of children right so down here under templates you'll notice there is a template for average of children this is going to be using the expand function so where collapse goes up in the hierarchy expand goes down in the hierarchy so if I'm at the year level and I want to know the average of all the months or all the quarters I can expand and do an average on top of that right so I'm going to go ahead and click average of children and then it's of course going to populate this right here for us and all we're going to do is tell it we want to expand the total sales so we want to go down one level and then we want to do this on the rows and this is awesome so let's talk about this for just a moment here I'll hit enter when I hit enter you'll notice that I'm getting the average well what does this mean let's go take a look at this back in the actual report so we're going to go back to our report and right now I'm going to make this visual a little bit smaller here there we go right now if I go up to the highest level what you'll notice is that for Australia I'm getting Australia total sales was 9 million but it says the children was 1.8 million so it's saying if you go down to the state level the average of all the states for Australia is 1.8 million that's really cool same thing for like United States the average of all the states for United States is 426,000 now if I go down in the hierarchy so we drill down and we look at let's say the state of New South Wales right here this is telling me that the average of its children at the city level is 218,000 this is powerful right this is really cool the capability that you get from a template like we didn't have to worry about filter context you don't have to know how to write Dax to leverage this so there are some really awesome capabilities and features that do exist and live with this visual calculation feature now keep in mind visual calculations are in preview Microsoft is going to be improving this product they're going to continue adding to it we're going to see a lot of new stuff make sure to subscribe to our Channel because listen we're going to be doing YouTube videos on this I've already dropped a YouTube video on this we're creating a class right now we did alarn with the Nerds event we're going to continually stay up to speed with all the new stuff coming out and we're going to keep you up to dat on that as well this is the documentation on it it is really good the documentation is really good we'll put that in the chat window for you right now if you go down to the bottom of this documentation you will see that there are a lot of great information in here good job by Microsoft but you'll notice at the bottom that there are limitations and it talks about you know the fact that you can't use it on all visual types so there are some that are not going to be supported uh and so you'll see visual properties and visual types that are not supported you'll also see that you can't re use them in other visuals you have to recreate them you're not able to reuse them in other visual calculations a visual calculation cannot reference itself or different detail level you can't apply conditional formatting I kind of showed you a tip trick to kind of work around that a little bit with cell elements but you still can't specifically say based on this right you can't reference that in the the conditional formatting powerbi embedded isn't supported for reports that use this live connections to SQL analysis Services aren't supported so there are a lot of things right now show items with no data not supported so there are a lot of items right now that are limitations but again Microsoft is working on this this is an exciting time for Dax make sure to check this out we put the documentation there and I'm going to answer a couple of questions and then we still have a giveaway that we want to do today right we want to do a free odl license for somebody who is still in the meeting today as kind of a promotion for just our platform in general so thank you for joining us let's see what kind of question that we have new indidual Cal can you state the use case of visual calcs because I could see some functions that could be done using Dax measures already yeah so when would I use a visual calc versus using a calculated measure that's going to be the fundamental question that continues to come up when we're working with this right visual calculations are simple they're easy so a lot of this is going to be determined based on your knowledge how familiar are you with Dax most people are not so for most people they're first going to leverage visual calculations before diving into something more complex like Dax Dax calculations give you more explicity you can be more specific with them as far as specifying exactly what you want it to do so that's something you can leverage Dax measures also are going to be globally available to all of your other visualizations visual calculations will not and of course as you see with the limitations right here that might alone tell you hey I need to use a Dax measure for now so there's a lot of great reasons why you might use Dax measures versus visual Cals but visual Cals do make it really easy to do some of the things that were very very difficult um and these are things like the things that I'm showing you in visual calculations are things that I used to have to write in Dax all the time when I'm on calls with customers doing one-on-one virtual mentoring sessions kind of coaching them teaching them helping them solve their real business problems visual Cals do solve a lot of those problems at least kind of temporarily if you will let's see what other questions we have collapse parent can be done using a Dax measure also right so what is the actual difference here and the use case of doing it that goes about right back to the exact same question as before it depends the cool thing about doing it here is it's fully Dynamic so inside of a visual Cal it's fully dynamic as you scale up in the hierarchy boom it's done with your Dax measure you have to be very specific about thinking about what are the filter context and how are you going to change them or modify them according to where you're at in the hierarchy so it requires a little bit more thought with a Dax measure if you added a column for semester between year and quarter would visual Cal update the ordinal position to three um no it would it semester would be two and the um I guess quarter would be three and if quarter was the lowest parent it would still be the lowest parent because it's still at the bottom right so if you went year semester quarter and it used to be year and quarter it would still be the lowest parent because it's the lowest parent before you get to the child so if you use lowest parent it would still work at the quarter level if you specified two it would now go to semester instead of quarter if that makes sense do visual calculations work the same way with tables as they do with matrices there's actually some limitations on tables actually um I've notice that working through it I can't do everything that I want to do in tables that I can do in U matrices there as well and last question are these visual calculations available the same way in the powerbi service so you can definitely create them here and publish them out there to the powerbi service so you know whatever you can generally do there you'll be able to do there as well one more question got added here will these calculations show if you document Report with external tools um I haven't tested that I'm pretty sure they would because they're in your model but that would be kind of tricky right how does it pull that information so that's actually one that I just haven't tested so that's a great question for the live Q&A on April 22nd with that being said my part here is done I'm G to turn this over to Marshall and our team and they are going to dive in now if you will to the giveaway for everybody in the group so thank you everybody enjoy and we'll see you next time all right that was awesome thank you uh let me get my screen here thank you Mitchell for that great presentation uh really chat was on fire lots of folks participating lots of interest in all the uh create uh content that you've created I do want to do a quick raffle uh for everyone so uh we have a free on demand learning subscription available uh to one lucky winner and we're going to go ahead and start the drawing and let's see who wins so here we go and the winner is DL all right DL um please reach out to us uh send a email to marketing pragmatic works.com and uh we will get that uh get you all set up with that free license so congratulations deel uh we're so thrilled that you could uh participate today um also just want to recap a couple things that we talked about that number one is we do have that sale on our annual subscription use the code Dax 2024 through April ail 14th and you'll save 50% off of subscription so normally $4.95 you'll get it for $247.50 for a year uh that also includes our brand new product cert XP which will help you prepare for all these great certifications uh we also have free Dax courses available on the on demand learning platform uh you will have to create a account and then you'll have access through April 14th for all those classes that uh Mitchell mentioned uh up to this point the last thing that we need to let everyone know is um we do have another learn with the Nerds uh next month uh Jonathan Silva will be doing a power automate beginner to Pro session uh great content you're really gonna want to watch this one and uh you can use the QR code there we're g to run a little promo at the end of this session uh to let you uh register for that but don't want to miss that that'll be on May 9th uh with Jonathan Silva and this is one of those Mac Daddy three hour sessions so this is going to be chalk full of great content you don't want to miss it and uh with that um I think that's about it Mitchell anything else to say any other words of wisdom Dax shortcuts or tips or tricks or cheat codes make sure to like And subscribe to our Channel keep up to date with all the new content coming out take a look up until April 14th you can take a look at our Dax classes for free so sign up for that thank you everybody for joining us today I loved it this was great and we'll see you in the next one all right we're going to close out with the promo for next month's lur with the Nerds see you everybody have a good one our aut taking you from noay in our session we will cover triggers conditional logic looping actions approvals robotic process Automation and some administrative work to register scan the QR code here I'll see you there with the will make you a l power on to make to to navigate from beginning to Pro let's collaborate oh [Music] yeah

Channel: Pragmatic Works

Views: 26,444

Keywords: power bi, powerbi, visual calculations, visual calcs, visual level calcs, visual dax, dax, power bi tutorial, power bi for beginners, power bi tutorial for beginners, power bi dax, power bi demo, power bi tutorials, power bi feb 2024 update, power bi update, power bi visual calculations, dax tutorial, visual level calculations, vizual level calculations, moving average, dax easy, dax for beginners, february 2024 powerbi, pragmatic works, dax in power bi, dax power bi

Id: JITM2iW2uLQ

Length: 90min 40sec (5440 seconds)

Published: Thu Apr 11 2024