Advanced DAX: Problem, Design, Solution [Heavy Demo]

[Music] yeah all right good afternoon everybody well welcome to advance decks problem design solution what I want to do specifically in this webinar is kind of look at a few different scenarios that I've come across either while teaching or doing presentations where people have come to me with different problems and we were able to solve those problems using a little bit more advanced Dax now if you didn't see my last webinar my last webinar was a little bit more geared towards kind of transitioning yourself from introduction to Dax into the advanced statics and we talked about filter context and those sort of things and the last webinar and here we're going to kind of build on that a little bit and go into more specific scenarios where we kind of present a problem a design and then a solution on the screen here you see all of my contact information all right so just real quick a little bit about me my name is Mitchell Pearson of course I am a business intelligence consultor consultant and trainer for pragmatic works in two months I'll actually be with pragmatic works for five years so I'm looking forward to that I get a extra week of vacation so how can you not be excited about that I'm blog at Mitchell Pearson comm and then if you want you can follow me on Twitter as well at Mitchell sequel where our tweet use about once or twice a day about just power bi related kind of stuff and information alright so in this section I want to talk through a couple different tax functions now some of these don't look particularly like they're advanced and they're not but in the context in which we're going to use them here is in more of an advanced concept so we're going to talk through the contains function defined function as well as the search function all right so the first scenario I want to show you is I had a client that was actually in one of our power bi boot camps and what he wanted to do is he wanted to build a report for his executives and his actives they weren't IT folks so he wanted to give him a chart and inside of that chart he would have all the measures that they wanted to display so they were going to put all the measures inside of that chart and inside of that visual and he didn't want his executives to have to go into the different tables and the different columns and search for measures and have to pull measures into the chart and remove measures from the chart what he wanted to do instead is present his executives with a prebuilt report and then all they had to do was actually have a slicer that would allow them to click on the slicer and that would automatically remove measures from the chart or add measures back into the chart and I thought that was a really cool example so we kind of worked through a solution that would work for him on that what we're gonna do here is essentially use the contains function and the contains function is really more of an if exists function alright so if it exists then it returns true if it does not exist then it returns false so if it helps you to kind of think about the contains function in that way that's how we're going to use it here we're going to use it kind of like if it exists or does this exist it's going to return a true if the values for all the referred columns exist or if they are contained in these columns L so it returns false if the value that we're looking for in the column does not exist anywhere in the column it returns false it does not exist and the syntax for this the way that you set this up syntactically as you start with the contains function the first parameter is always going to be a table or either a table expression and then the column that you want to search so what is the column within that table or table expression that we want to search and then the value that we're searching for all right so in this example we're gonna search for profit or we're gonna search for three month rolling to see if it exists inside of the table so it doesn't make sense kind of looking at this in PowerPoint so we're gonna walk through the example kind of go through a demo here and I think at the end of that it'll make very clear sense of what we're trying to do so we're gonna jump back over to power bi real quick and we're gonna walk through an example of how to do this all right so what I want to do here is let's just add a quick little slicer actually I can't even at this point I can't even add a slicer so what I have in this chart is if I click on this chart you'll notice that I've added profit I've added rolling three months profit and everyone my rolling twelve months profit as well and if I come down here to the value section of the chart typically what your users would have to do is if they wanted to add more values or they wanted to exclude values they would have to do that right here in this value section so they'd have to come down here kind of expand these boxes you know open these up go into the value section say I don't want to see profit so they did exclude profit then you know five seconds later they want to see it so they'd have to come back up here into the search box a search for the profit measure and then drag that back down into the values and then it shows up here but what my client or customer really wanted to do is they wanted to have a slicer here unfortunately you know these measures here they're not a column that exists in the database so there is no I don't currently have a list of measures that exist in a column in a table that I can use as a slicer all right that's one of the main reasons why we create calculated columns is because we want to use those calculated columns as a way to kind of slice and dice our data so since that doesn't exist what we're going to do here is we're actually going to go ahead and create a new table that has a list of those different measures that we have inside of our our data model so up here at the top on my home ribbon I'm going to click on inner data and what inner data will allow me to do is just kind of kind of create a table on the fly here so I just want to quickly create a table so we can walk through this demo and the first one I'm gonna put in this new column here is going to be called profit so that's one of my measures that I want to add to my slicer the next one here there's going to be my three months profit and then the next one is going to be my 12 months profit there we go and then of course I need to go ahead and give this a name give this table a name so I'm going to name this table something like I'm just going to name it measure so that'll be the name of the column and then the table at the bottom I'm going to call this measures and then I know I'm going to be using this as a slicer all right and then click OK or I'm going to load that into my data model now I'm going to be using the concept of a disconnected table here when a mean by that is this table is not going to have any relationship inside of my data model so if I come over here you'll see we have this new table that we've added if we go to our relationship tab I can scroll over here you'll see that we have this new measures table that we've added to our data model and I don't have to create a relationship between this table and any other table there's tons of blogs out there about disconnected tables somebody Rob Kohli somebody Italian by Alberto Ferrari and Marco Russo Chris Webb probably has a blog on that tons of blogs on how to use disconnected tables because once you learn how to use them without needing a relationship there's really a lot of cool stuff you can do we'll do a couple demos with this concept today so I've brought this table in we can take a look at it here real quick just a look at it so this is our table with the column and the three measures and now what I can do is back on the reports view I can come over to my measure table bring that in as a slicer so all I'm doing here is just adding a slicer to my report now that I have a column that I can pull from and what I want to do is anytime I click on this slicer I want it to affect affilorama start so let me show you what I mean I'm gonna bring in this again this time I'm going to bring this in just as a table and then I'm going to make the text there a little bit bigger and notice how this table the column from this table is filtered anytime I select an item from my measures table and that's how I wanted to work with this chart whenever I select an item here I wanted to also filter my chart not just the table since this this slicer is able to filter this table now what I can do is I can say look if the value of the profit exists in the table then return it on the table if it does not exist or it's not contained in this column then don't return it and since the slicer affects the table and the table we would do our measure off of the table here then it will automatically kind of indirectly work there so that's what we're gonna do we're going to check to see if it exists in the table if it does meaning it hasn't been affected by this slicer it does exist and we want to show it in the chart else we want to remove it from the chart so the first example here is going to be profit and we'll do this calculation for really all three of our measures so I'll zoom into my profit calculation and the first thing I want to do is kind of do an if-then-else some if-then-else logic and the first thing we do with if of course is our logical test and this is what we're going to use that contains function and remember contains function kind of works like and if exists so if it exists and remember the first parameter is going to be a table so the table we want to look at here is going to be the measures slicer table that we created and then inside of that slicer table we want to look at that specific column so I'm going to look at this table I'm going to look at this column in that table and then I want to search for a specific value I'm going to just put in a literal string here going to hard-code the value but of course if you had a Dax expression that was going to return your value for you you could put that in here as well but what I'm doing here since we're looking at our profit measure as I want to search that table and say all right does profit the value of profit does it exist in that column in that table if it does exist then this is what I want to do here I want to perform this operation here so let's close that out real quick if it does exist and it evaluates to true and see why this is not working here contains if it evaluates to true it should be good but it's not picking up my intellisense so let's figure this part out first measure slicer measure that looks good the value on a search for is profit closed out contains open parentheses alright so I don't know why that wasn't before but now it is so if it evaluates to true I don't really change anything but just move the comment down but now it works alright so if it evaluates to true I want it to do this expression here if it evaluates to false we want to return a blank value and what we're doing by returning a blank value is we're saying look if profit does not exist in this table then return a blank value because it means that it wasn't selected then in this slicer here so we would then return a blank value and it would disappear from the report so notice what happens when we kind of close out this expression here I'm going to close outside of that expression and then I'm gonna click on something up in profit and if I click on something other than profit you'll notice that profit no longer shows up inside of this chart that we're working with so we've created it so that this slicer actually affects a table and we put an expression on our measures to read from that table first before actually displaying it in the chart and now we don't have to go through the process of actually coming over here and adding value to the chart or removing them because you'll see that the value of profit still exists in the chart here but it's not being displayed here right it doesn't if that little red line you saw before is no longer being displayed even though it's still in the value section making it essentially easier for those executives to be able to interact and work with this chart without having to learn all the intricacies of power bi so now we need to go ahead and just kind of modify the 3-months profit and the 12-months profit calculations real quick essentially doing the exact same things I'm gonna go and click on my rolling my rolling 3-months profit zoom into that one real quick and I'm gonna do the same thing here I'm gonna say if and then we're going to do our logical test here using contains again the table that we want to do that on is going to be measures dot slicer and then the column that we're going to check is of course going to be measures slicer measure so it's the slicer with the column within that table rather and then the value that we want to search for is going to be three months profit and really I want to make sure I'm getting the right thing here so let me roll this it down I'll make sure I'm getting the right name there we go so we're looking for three months profit and if if that exist inside of that table meaning it evaluates to true then we want to do that original calculation whatever that original calculation of was all right if it does not exist in that table then we want to return blank of course right so we're going to return blank and then we can close out that if statement and then hit enter so same exact thing that we did on the profit calculation previously so now if I come in here and I click on profit profit shows up three months does not if I click on both profit and three months they both show up now twelve months is still kind of hanging out here even though we're not selected but that's because we need to go ahead and update that measure real quick so let's go ahead get kind of the complete end-to-end example we're gonna do the same exact thing here basic if-then-else condition and we're going to use contains for our logical test so contains and then our table is going to be measure slicer the column here is going to be the measure from that table and the value is going to be twelve months rollin close that out another words if 12 months rolling does exist in this table down here so it hasn't been filtered out if it does exist here then we want to go ahead and do that original calculation else we want to return blank remember that the way blank works and this this this logic here that we're using with if-then-else and blank really works for a lot of scenarios not just this dynamic slicer but what blank does is if a measure that's in a chart or a table returns to blank it automatically hides it from that chart it automatically hides it from that table so we're just forcing the value to blank so that it automatically hides it from that chart or from that table so you can use this in a lot of different scenarios not just dynamic slicer a lot of times you might have a table that has a lot of blank values you know you know what I want to force my measure to return blanks so they get rid of all those hidden rows same concept you would do the same type of thing there that we're doing here so now let's go ahead and close out that formula bar now if I click on any of these different measures it's automatically going to make this dynamic let's see what happened here 3 months 12 months is not working so I must have I must have typed in 12 months profit in so let's fix that oh I did rolling so let's change that a profit and then try that again there we go so now I click three months I get three months if I click twelve and three months I get both and then if I multi select and select profit I see that as well so now what we've done in this example is we've created a dynamic slicer that allows your end users to really look at a list of measures that you've created and interact with the chart and with the table without having to go into the value section and change those items by simply using you know some if-then-else conditional logic along with that contains function so a really cool example that I came across from just you know an end user that was in one of our boot camps that asked me how we could essentially do something like that all right so let's go back over here to PowerPoint real quick and in the next demo I'm going to use the find function and remember that what I said here is in regards to defined function is that the find function itself is not an advanced function you've used this with more than likely if you're in this this webinar you've used this function in Dax already if you haven't used an index you've used it in some other programming language so either way this is definitely not something that is new to you what we want to do here though the problem that I got and this was actually from somebody on my last webinar they asked me how can I essentially I want to I want to do a like operator something similar to a like operator index and Dax unlike T sequel does not really have a like operator available to you so I was able to simulate that or simulate something similar using the find function what the find function does and the search function they do the exact same thing one is case-sensitive one is case insensitive but what they do is they return the starting position of one text string within another text string so I search a call on my search of texture ring for T value so I might search Mitchell for the letter T and what it does is returns the starting position of T within that that that's that string that I'm searching so it returns the starting position of T which in Mitchell would be one two three so it we turn the value of three that's what gets returned so if a value gets returned if I search Mitchell and I search for the letter T the starting position of T if a value gets returned and the value is three that tells me that T exists inside of Mitchell right it exists so it's a way of saying it would return that road because it does exist if it returns a value of zero I know that Mitchell doesn't contain a letter of let's say Z if I was searching for Z so this is a way that we're going to kind of use this you can use find and search in a lot of more advanced scenarios to kind of generate these like type operations or similar operations that you just don't have a function for it index so that's we're gonna do here in this scenario all right so let's jump over to our second demo here and then the second demo I want to walk you through kind of the problem that was presented to me like the key like I said once again this came from somebody in the last webinar who sent me a question here and what they wanted to do is they had a table that was similar to this not exactly like this but similar to this and they wanted to be able to come in here and kind of filter this down to different departments but if you look at the department goals table let me show you what this table looks like here very simple table if you look at the table you have all of your different effective departments and then you have the hiring goals so you know how many employees we're gonna hire what is our goal for hiring in that department and I can come in here and essentially create a slicer that will slice this table that will you know slice and dice this table but the problem that I have here is if I want to return all the rows from this table that contained the value or that are like IT if I want to return all of those rows I have to come in here and multi select multiple selection in my slicer and we know that this is a very tiny small result set this end user of course had many many departments and many combination of departments and they needed to create something where they didn't have to come into their slicer here and select 20 different things to return all of the different goals for their IT department or for their finance department so we're going to come up with a scenario here where instead of having this slicer that's derived from this table we're going to create a new table with a list of items here and then we're going to use that as our slicer by using the find function and some other some other things kind of in this complex Dax so similar to our last example we're going to go back up to the home ribbon and then I'm going to go up to enter data here and I'm just going to create a new table real quick kind of on the why for this example here so let me go ahead and click on enter data and then we're going to create a new table real quick and the column for this table will be called departments and then for our departments I'm going to create the first one here which will be IT HR I want to essentially create a record in this table in this column for every single distinct value that would show up so the first one would be IT the second one will be HR the third one here would be something like finance and then we would have our auditing department and then we would have our tax department alright so I've gotten every distinct value that I could find in this list of values here and then the next thing I want to do is come down here to the bottom and I'm just going to give this a name so I'll just call this table slicer so we're simply going to use this table to perform a slicer type operation here inside of our table so I'm going to load this into our table there we go and then we're really going to do the same thing here that we did before hold on one second and then I really want to do the same thing we did before we're gonna leave this as a disconnected table so once again I'm not going to create a relationship between these two tables so there's not going to be this automatic filtering that occurs through those relationships in my data model instead we're going to use a Dax expression to kind of build that relationship and say if it's selected here then it should show up here if it's not selected here then it would not show up over here alright so instead of having a slicer here on this table we'll bring this down we're gonna create a new slicer here from our new table from departments all right so let's turn that into a slicer visual will come into our items here and then go ahead and make that a little bit larger so we can see it and ultimately what we want to do is if I click on i.t I should be able to see all four records here that have i.t anywhere inside of that affected department if I click on tax I should see the one here at the bottom the final record as well as the first record if I click on multiple in the slicer then I should be able to see everything that has either HR or tax so that's how we want to build out this expression all right so I'm going to go back to my department goals and we're going to create a calculated measure for this example so if I'm my modeling ribbon at the top I'll select calculated measure and then for this example this is going to be a little bit more code than the last one here I'm going to create a new measure here and this is going to be called is filtered clean that up a little bit is filtered and like I said this is going to be quite a bit more code than in our last example but that's okay so the first thing we'll do here is we're going to have our conditional logic test so we're going to use if and then inside of if I need to use the some X function so that we can iterate our slicer table we're going to look at each individual row inside of that table and then the expression that we're going to perform in that table is going to be using the find function so remember we want to use the find function to essentially return whether the value is 0 or greater than 0 if it's greater than 0 we know it exists it's it exists somewhere in that record if it's equal to 0 it does not exist so we're going to use the find function here so I'm going to return the find function you can do this with either defined or the search function in my next demo I'm going to use the search function just to give you a look at really both of these now the find function as you can see right here is case-sensitive so because of that what I'm going to do is I'm actually going to add to this expression the kind of upper function here to really turn it into kind of an in case since it ever case-insensitive operation and the the text that I want to use here look at let's let's step back one example the first parameter for find function is the text that you want to find so what is the text that I want to find I want to get the text from my slicer table all right from slicer department store slicer departments all right so whatever values exist in my slicer Department I want to pass those values in I want to pass those values into our slicer it's not correct let me see here I want to pass those values into my Department there we go Department goals and then affected departments so let's take one more quick look at this what I'm doing is I'm going to iterate over slicer table so some ax is going to look at each individual row one by one forming this expression it's going to look at the first row and it's going to pass in the value from my slicer departments which is right here it's going to take tax because that's all that exists currently that's all we've selected as tax it's going to take tax and it's going to pass it into this column in the department goals table and it's going to look for that it's gonna say does it what is the value essentially that's going to get returned remember this isn't like the contained where it exists it just returns up value so it's going to return a value of either 0 or 1 so let's close that out there we do want to make sure that if it doesn't return a value we return just a basic value of 0 if we don't put in a not found value there it will fail and a lot of instances because of course the slicer doesn't exist in every single row all right so then the last thing we want to do is as part of our if expression is we want to say if it is greater than 0 so we need to close this one out make sure we get all of this closed properly and now we want to say if it's greater than 0 then what do we want to do if the value is true well if if it returns a value greater than 0 that means that the text did exist in that row so that means the value is greater than 0 so if it's greater than 0 and it does exist I want to return true all right I want to return true if the value is equal to 0 it's not greater than 0 equal to 0 that means that it did not it did not exist it was not anywhere inside of that expression HR or tax RIT whatever we're searching for did not exist so the value is actually equal to false all right so let's walk through this one more time some axe is going to iterate over the slicer table so those are the values here in our slicer table it's going to pass those values into the department goals affected goals and it's going to see if they are it's going to really just return the starting position of the text if the starting position of the text is greater than 0 in that row so for example we're searching for tax right if you're looking at this first row the starting position would be 1 which is greater than 0 so the value is true it does it does exist if you're looking at the second one if I was searching the second row here I T for tax it doesn't exist so it would return a grow of 0 because that's the default value that we gave it and it would do this on rrrow essentially in that column so if it does show up it will return about you on the last one here it's going to count this out so they'll be 5 6 7 8 9 10 11 12 the starting position of tax would be 12 which is of course greater than 0 so on that row it would return a value of true all right so once again it's the find function is a easier function it is an introductory function but you can use it in more advanced scenarios like this scenario here now once we create this is filtered calculated measure what we can do then is we can kind of look at this inside of our table and see what that looks like all right so let me click outside of that over here and my department goals I have this measure I'm going to drag and drop that into my is filtered table or into my table here let me expand that out a little bit and now you can kind of see what's going on with this as I'm kind of working through this if I click on Finance you'll see that the only one that's true in this table is just the fourth record if I click on HR it'll change well actually it's the same one that's true if I click on tax the first record and the last record are true if I click on i.t the bottom four are true but the top one is false and then of course I could come in here multi-select and if I multi select everything everything is true if I select IT and the tax those are really the two combinations here everything is true but now what I've done is I've created my own filter here where I can click on a filter in my departments table and then that essentially will filter the table itself and it's disconnected and we're doing that by really just using an advanced use case of the find function now unfortunately you can see that the is filtered is working here but it's not actually filtering the table right everything still shows up in the table so the next step here and the reason we created this as they calculated and measure the next step here is because we can use measures inside of our filters so I'm going to remove the is filtered we know it's working correctly I'm going to remove it from the table all right and remove it from the visual there and then I'm going to take that measure and what I want to do is I'm going to drag it into the filter section under my fields alright so in your fields for your visuals as well as your page and your report level you have options to drag fields in here the fields that you drag in here need to be essentially calculated measures if you want to bring them into the page or the report level so I'm going to find that calculated measure here I'm just going to drop this into the visual level filter so I'm going to add that in and then what I want to say is if the value is true so we're going to go to true here if the value of that row is equal to true and then display it in the table in the visual alright so we're going to apply filter if it's not equal to true then it should not be displayed so instead of actually putting that you know somewhere else in our report or putting it in the table now we're just displaying it here so now if I click on HR or i.t or tax you'll see that the values in my table are changing accordingly based on the value that I'm selecting here because we've created that calculated measure that calculated measure is being used in a filter inside of my report which is then indirectly or really directly filtering down this table keep in mind of course these filters have different scopes so you can do this at the visual level you could do this at the table level or at the page level or you can do this for the entire report which is essentially every single page inside of this P bi X file that we are working with all right so yeah and I found that build filters here are really an underutilized feature of power bi because most people simply use slicers right we all want to use slicers we enjoy slicers so we only use slicers in our report so for example let's go and get rid of this here so we have no filters they make this a little bit bigger real quick I've brought in is filtered and then I want to add is filtered as a slicer here of course I can't because it's a measure so because it's a measure I can't add it in as a slicer that's what we had to use it as a filter if you're curious about that why do I use it as a filter the reason for that is because it is a measure all right so that is the second demo that we were going to do today so let's go ahead and put back over to our demo and just to really quickly kind of review this to maybe clear up any confusion I might have caused what we're doing here is we're essentially just searching a piece of text we're searching that column in that table as they look if our the row within the column if they're row in a column if we're the text that we're searching that row with if it returns a value greater than 0 then we know it's what we want we're essentially using find and using search here as a like operator to say if it's like IT then we know it's like ITV because it returned to by you greater than zero so we're going to hard-code the value of that to true meaning that it is like IT so we want to show it in our table if it's not like ITF I t's going that we're searching on if it's not like IT every turn the value of zero so if it returns a value of zero then we're hard-coding the value as false so we're doing we're essentially using the find operation here to simulate that kind of like operator that we don't have that comes from you know TC equals something similar to T sequel all right so let's do I didn't mean to close that out so let me go find PowerPoint again and bring that back up so the next example here is going to be using search and I'm going to use it in a very similar capacity now this example or the problem that I want to solve here is really more of an advanced data modelling problem as well actually as I was earlier this week two days ago me and well cantana who is one of the guys who also works in our training department here had a problem that he came across and he came and asked me if I had seen a problem or if I've come across this problem before and it was really an advanced data modelling problem and I said hey look I just had this problem a couple months ago here's how you would solve that it's pretty easy to do so what I'm going to do is I have two separate tables here and in those two separate tables I have values that are similar but unfortunately they're not identical so in my example here I have MLS data if you were on my last webinar you saw more information around the Omanis data so you're somewhat familiar with that but I have some MLS data in one table and then I have another table that I bring in school grades data and the school grades data that I bring in also has the school name so in my MLS data I have the high school that you know is applicable for each home that is purchased and then a my school grades data that shows you know the grades for each school for the last ten years I have the high school as well but unfortunately the mos data is not consistent because it's realtors that are inputting that information and it doesn't always exactly match the high school data so I'm having difficulty here bringing the data together so that I can build a correlation in my data model that says look when a school does really well and it's in a school or a B school property values are going up and the interest in those neighborhoods are going up and new construction is going up and maybe if the schools are doing worse you know people are moving out of those areas and values are going down now I don't know that to be true based on my analysis right because I'm trying to build a data model to prove that but in order to prove that I need to be able to relate those two tables together and that's the problem that I really had here in this example so to give you a visual of what that looks like here on the left-hand side of the screen you see my MLS data and you can see there's some differences here and we'll talk through these as we go through this demo but on the left-hand side of the screen I have Allen D nice high school which is a high school here in Jacksonville Florida and on the right hand side I have Allen D nice senior high school and this is how it is how it shows up in that school data that's provided by the government all right or whatever government entity provides that information and grades all of those different schools so this is how I showed up in the CSV data that I pulled off of the web and I need to build a relationship between these two tables and unfortunately sometimes when you get data from the government websites our mainframe data or data from the web it's difficult to find relationships there because the data doesn't exactly match so like I said this is it's a little bit advanced tax but it's really more advanced data modeling as well how do I build a relationship when the values don't match here all right so what I'm going to do is I'm going to use the search function instead of the find function this search function is essentially are going to do the same thing as the find function it returns the number of the character at which a specific character or text string is found so it searches a text for another text string and it returns the starting position of that that text string if it finds that value unlike find this one is case insensitive so we don't have to use the upper function we don't have to build that out and typically from a performance perspective you know if you don't need to use fine you should go ahead and use search it's going to perform a little bit better than the fine function because it doesn't have to worry about case sensitivity the the syntax here is exactly the same where you first give it the text that we want to find we then tell it within what text we want to find it we tell it where we want it to start within that text we're just going to heart we're just going to use the default which means start from the very beginning so we're not going to put anything for that parameter and then the fourth parameter is very important as well we need to make sure we put in a value of zero or something else that's the default because if you don't put a value in there for the not found then it will generate an error whenever it searches a text and doesn't find a value it generates an error because it doesn't know what to return so this is really an upgrade index and previous versions of Dax and older versions you didn't have that optional parameter so you had to nest this inside of like an if error function say oh if it generates an error then return zero but now we can just use this not found value here the other thing that I'm going to introduce here is the substitute function the substitute function is essentially like a replace function it it replaces existing texts with new texts in his text string the reason I need the substitute function if you remember from those if we just go back real quick you notice on the screen on the Left I have Alan D period nice and on the screen on the right I have Alan D nice so it doesn't have that period so I have to remove that period essentially from both sides of you know both input columns I have to remove the period so that I can get a match so we're also going to use the substitute here just to replace any characters that we don't want to show up here inside of our data model so we're going to substitute substitute but syntax here is you tell it the text that you want to search you give it the old text the text that you want to remove you give it the text that you want to replace that with and then you know the instance the number of occurrences where you want to do that at all right so let's jump back into power bi desktop and take a look at this next demo real quick here here we go so on the left hand side I have this MLS data and what I did for this example I think I might have done this for my last webinar as I've created a a filtered table I've criticalcubing Dax which is a filtered table from my original MLS table and the reason for that is I want to come over here and MLS table has a ton of just take a ton of great data and here's some data that's not so good as well but ton of data and from a performance or troubleshooting perspective I really wanted to simplify this table to only show me that the columns that I need it I don't want to see all the columns I just want to see the columns that I need so I created a filtered version of that table for this demo or for demo purposes so we're going to be doing all our work on the MLS filter table and that's why now if I go over to my school grades table I can come over here and I want to bring in the school name so I'm gonna bring the school name in I'm gonna bring that in as a table I'll go in here and I want to just create some consistency real quick so I'm gonna create that with my bold header with flasher Rose washy rose and we can take a look at this so this is what I have this is what I'm trying to build a relationship on here I'm trying to build a relationship on Alan d-nice now there's a ton of schools in here it's probably all the schools for Florida but we can filter this down which we'll do here in just a moment when we create this joint condition but this is what we want to do so just like you saw in the PowerPoint I want to create relationship here between almond Denise - Alan D knees and from Bartram trail down to see if we can find Bartram trail real quick - Bartram trail and you'll see then in each situation here of the names just they don't match so over here we have Bartram trail high school this is really if you're looking at this this is more qualified right it's telling you if it's a high school or a middle school it's adding senior high school adding things like that whereas what's being put in the MLS a little bit blocks a date we're just entering as much as we need to enter so we can get the job done so this is what being entered in the MLS over here and then this part over here is what's being actually shown up in that government document it shows us the different grades for the different schools so just to get you familiar with its data set real quick we can come in here and take a look at this you'll see that I have the school names that exist here in this table and then if we go over a little bit you'll see that the other thing that's important here is the school grade that's really okay we also get the great year and since we get a new grade for each school for every year the relationship that I want to build between these two tables is on the school name as well as the year so I want to find out what is the grade for each year actually if I want to get more complicated here I wouldn't want to look at the year that a home sold and look at the grade for that at school the previous year right so the Greek for that school last year was in a it was an a-rated school that would explain why people are moving in this year because it was a rated but we're not going to get that complex here but that's probably what I would want to look at all right so now I need to create a relationship between these two tables and we're going to do this one using a calculated column instead of a calculated measure so I'm going to come back over to my MLS filter table and then inside of this table I'm going to come up to my modeling tab and we're going to create a new calculated column on this table and let's zoom in here and start working kind of through this example so I'm going to go ahead and call this I'll give this a name but the name here is going to be high school and let's call this my high school name we're gonna replace this when we get done with the school grade but for validation purposes I want to just bring in first before bringing in the grade I actually want to bring in the high school name to make sure that it is working correctly so we're gonna bring in the school name here and I'm gonna type in calculate I'm gonna use the calculate function here the expression I want to return is going to be the max and then that's the wrong one I want to code to my school names table school grades and then we're looking for school name there we go so I want to return the max school name from that table that's my expression and then really we need to import a couple of different filters on this so as part of my filter criteria at the calculate function I need to put three three filters on this the first one is limiting this to just high schools so in my MLS data you have three different columns you have the middle school for that home the elementary school for that home and the high school for that home so I would have to do the same type of function this advanced function this advanced relationship here I would have to do that and three three different calculated column since it's separated out which makes sense for a home I want to see what is the grade for the high school what it's great for the middle school what is the great for the elementary school because that might be more applicable to my my my kids and their grade level so the first criteria here is we're going to let's just do the year first so I want to look at the MLS and the remember you want to do filtered here sorry about that the MLS and I want to look at the sold year of a home the year that home sold and where that is equal to the school grades table so we can get that to pop up we got to do filter sorry about that got out in the filter function and where that is equal to the school grades table still not wanting to show up for me here well got to do my table of course sorry about that I'm just learning Dax myself not really but I'm nervous being on this call alright so let's finish this up so I gotta have my first filter criteria and the first filter criteria is where the MLS filtered sold year equals the year from the the school grades tape well the reason that's important is because remember you have of an entry for each individual year that takes place there so we're gonna bring in our grade year and then that's our first filter criteria we're gonna have multiple criteria so and we can use a double ampersand sign here to do an and operator or an and function and the second thing I want to do here is the school type so I'm only going to look for school types in my school grade table so I'm gonna say where school grades and then let's see I think it's called school type there it is equals three I know just because I know the data that three equals high school so I would definitely recommend that anytime you're kind of working through or doing complex decks like here add as many comments to your code as you can so you can come back and other people can look at your code and understand what's going on there so we know that's three equals high school and in the third filter criteria and this one's a little bit more complex this is where we're going to add in that search function is going to be the search function and the text that I want to find is going to come from my high school table my high school table so were going to say MLS filtered and that high school so I want to take the values from MLS filtered High School and look inside of the school grade table to see if that exists right so that's going to be my search the tech that I'm going to search within and going to be my school grades and then school name table our school name column the starting position remember we're going to go with just two default of that starting from the very first value and then we want to make sure that if it doesn't show up that we simply replace it with a value of zero all right and then I'm going to say if the value is greater than zero then it meets my filter criteria so go ahead and return that value if it is not greater than zero then it doesn't meet it so we're just going to ignore those rows all right so now we just close out our calculate statement and let's walk through this one more time that was a lot of code and I messed up in the middle there so kind of lost some continuity so the very first thing I want to do is I want to return the school name this is a calculated column I'm going to say you know if multiple names somehow get returned return the maximum school name and then my iPad my filter criteria here and I'm gonna filter the school grade table essentially filtering that down and I'm gonna say all right for the first row remember that filter is an X function or an iterator function so it's gonna iterate over the school grade table kind of row by row operation on the first the first filter criteria says where MLS sold year the soldier of the home equals the grade year that that school got that great now remember I said I would probably change this to be the previous year so I can see how home sells this year correlate to grades from last year but we're gonna keep this simple here the next thing I would do is look at only high schools because we only want to bring in the high school so the school type for high schools is a great value of three and then in our third criteria we're saying that when we do this search and we take let's say Allen Denise and we pass Allen Denise into this column here if it is exist in there or if it's like that we're simulating that like operation again then it will return a value greater than zero so if it returns a greater value greater than zero then it evaluates to true for this filter so return that as a school name and then we'll get the max school name for that road that we were operating on which should only return a distinct value and so unless something's wrong with my criteria here but we need to add that max in there so once we finished writing this out I can go ahead and click outside of the syntax here and then what I want to do is come over to my MLS filter table and then let's add our new column into this tape here see what that looks like so I have a high school name I'm going to bring that in and then it looks pretty good we're getting some pretty good results here so let's kind of take a look at this so notice that Alan d-nice still did not show up the reason for that is because of that D right there that that period this shows up in that value so we know that when we looked at Alan Denise over here in this table and that there were some significant differences between the two so if we can find that real quick there it is so an Alan d-nice on the right there's no period there's no character there so we really need to replace that from both sides and this is going to show up in a lot of different schools not just this example here so we need to clean this up a little bit more but you'll see that Bartram TRO is now getting a match Creekside High School is getting a match Mandarin high school is getting a match you will notice that some of these are not getting a match and this is just a problem with my school grades data I only brought in about eight years of data and I didn't bring in data for 2015 or 2016 so I only have data from really 2007 to 2014 and that was just the data that I could find that was readily available that was the most current data I could find from that data set but I could search a little bit harder and find better data I'm sure but that I know why these aren't showing out so I could return some kind of non applicable value it just means look I can't really build a correlation for the most current years but I can definitely build that correlation for later years to see those of school grade have an impact on home sales so now we need to go up here and kind of clean this up a little bit for Allen D nice high school so I'll go back into that column for my high school name and then really all I want to do here is I want to look at the right here in my search criteria I want to replace any of those characters that we would don't want to see here so we're gonna use the substitute function for that so let me zoom back in I'm going to type in the substitute function and then it says what is the text that you're gonna work on well this is the text and then I'll put a comma in here the old text is going to be a period and I want to replace that with an empty string simply just want to get rid of that I need to do that to both columns though all right so both strings so I'm going to type in substitute again and then that is the text that I want to kind of perform my operation on the old text or the text I wanna replace as a period then I will add in an empty string and then I need to add a comma here for the start position and make sure that looks good yep so that looks good now unfortunately if you had multiple character so let's say there's dashes and there's colons and there's semicolons and there's different characters that you need to remove and you would just have to do a series of nested functions here but using either replace or substitute to remove those different characters from that column but what this does is it removes them from this column if they ever show up if they're over here and they're not over here it removes them from this column making it easier to kind of join these two together so I'm going to hit enter click in the background now and then now if you look over here we'll see that now we're getting a match on Allen Denise so the reason I'm bringing school name into my MLS filter table is because I want to make sure and just validate that the relationship I'm building does work now I have two oxygens here I could go into my relationships tab all right fine let's see what we have here I'm gonna zoom now I'm gonna hit my camera icon here to bring everything in focus I could let me see what time it is all right we got a few minutes I could just bring in and build a relationship between these two tables now on a great name alright so I could find the school name or the school name I kept on the school name here I added the name here so I could build a relationship between these two if I wanted to do that and then they have a relationship like every other table in my data model so that's how we from an advanced data modeling perspective that's how you bring two tables together that are similar but they're not exactly the same this is not a one-size-fits-all solution this won't work for every solution you're going to find discrepancies in the model but I did the best I could with what I had I noticed that one you know one table was more fully qualified than the other said look let's just take this table on the left and try to perform a match to the one on the right for the most part this works very very good for my example and I could see where you could use this on a lot of other scenarios to kind of create the same type of functionality but what I want to do here is let's forget using the the relationship I just want to bring the grade name in instead of the school name we brought the school name in we validated it that it's working correctly here so let's change the grade name or the school name and just return the grade so the grade in that table I think is just simply great so I'm to return the grade instead of the school name I'm just gonna return that that's my new expression we turn the max grade and then for this the high school and then we are going to return grade all right so we're changing the name of the column and then we're exchanging the value that we're going to return as a result of this expression that we're building out so now I'm going to hit enter again nope so it's not great let's see which one it is here we're gonna go over to our school grades table and then we're going to navigate all the way over oh it's actually called value all right let's go over here one more time real quick and then click on value why is that not being honest about it spelling counts of course all right let's close that up there we go so now we have the high school grade for each one of these high schools for each year you'll see that the high school grade is actually changing kind of bouncing back and forth Bartram trails doing really well really really well and we can go down and we can see how these different schools are performing and look at this information I filter this table down to only show so many schools and so many columns so it's a simpler example but really cool way of really working with bad data in using really simple functions to do that so the search and find function lets can just basic functions but we can use them in advanced capacity to kind of solve this problem here all right so let's go back over to PowerPoint real quick in this section here just do a quick review and then I'll open it up to Erica to see what questions you guys have couple things here one the video is being recorded just like all of our webinars so I highly recommend I know I talk fast but I try to fit in as much as I can into a webinar so if something didn't quite click for you feel free go back and review the webinar and you can slow it down and kind of walk through these examples yourself and then - I won't have time to get to all the questions here I know some of the questions are going to be more abstract they're going to be more scenario based so feel free to you know really kind of come in there and just send me an email I don't have time to get everybody's email because sometimes I get bombarded with a lot of emails I have a lot going on but I do try to at least respond or give you a blog or give you some kind of reference to help you out kind of in the future if you need you know to point you in the right direction so if you have a question feel free to email me but performance you know give me as much information as you can so I can help you out and so we don't have this you know sometimes I have an email chain that's 15 or 20 emails long and that's just too much work as far as you know in the middle of my day kind of breaking and sending information back and forth so send me a lot of times send me just a prototype of it you know dumb your model down remove everything you don't need like I did here where I created a filter table with just a sample set of data and tell me your scenario and what you're trying to accomplish clearly and I can try to help you out with that I love helping people out it gives me a lot of examples for future blogs and webinars and it helps me kind of fine-tune my skills as well because I'm always trying to learn Dax so Erica I'm going to end it there I put my contact information on the screen but we got a couple minutes I can't see any of the questions in the chat window I don't have the organizer right but if you want to read out some of them to me yep no problem so we have about five minutes so let's so anyone who would like to ask some questions just feel free let me see here Gabriel asks what's what's the difference between find and search yeah so the difference between find and search is find is case-sensitive meaning that if I was saying I wanted to lurk look for capital m in Mitchell so I didn't want to look for lowercase seals I just want to look for capital m then I would use the find function because it's I can you I can look for something that it's case sensitive its capital or lower case specific the search function this case insensitive so it doesn't care if it's lowercase or uppercase like I said search will normally it's going to perform better because it doesn't have to worry about case sensitivity find is going to have a little bit worse performance but it's case sensitive so it allows you to do a little bit more as far as looking for very specific criteria very specific text within a string okay be new asks here where do I find more Dax functions and examples yeah so the best place to find the Dax functions is let me see if I have Chrome in here the best place for me is really just go to MSDN calm you know you go to the Microsoft web page you look for Docs functions you can really navigate through this let me pull those up real quick I'll do a quick search and kind of pull that over and show you where that is but if you just do it being search real quick and pull up MSDN Dax you can go to the Dax function reference and you can look through all the functions that exist also note that when I was writing those functions inside of power bi that power bi has some great intellisense so if you just start typing date it'll know you're trying to look for a date function it'll bring up all the relative you know date all the date functions that start with that but if you come out to the MSDN page easy way to come in here and say you know what I'm trying to do a on a date and time function you can click on that and then it shows you all the different options available here you can there's an in the month function it just brings back the end of the month there is a different function that shows you the difference between two dates and you can click on that and then it will show you it'll kind of give you a definition of what that is all right so it returns the count of interval boundaries crossed between two dates and it gives you the syntax there so that's one good way another way is follow people like myself in and some of the people in the community they do a lot of daksa marco russo and alberto ferrari chris web Rob Kohli people that are actively on Twitter anytime they either write a new blog they tells you how to go through some of these examples they'll post it on Twitter or they retweet examples that other people do as well so those are two good ways of learning more Dax awesome okay and let's see here John asks sounds like this is related to the first question I asked but he says I'm since find is case-sensitive why did you use it if you negated it by using upper yeah so it's a good question I talk really fast so I didn't really explain why I used it I only use it just to show a little bit additional functionality that you could still use find and you can just use upper there so I kind of just wanted to show an additional function and show the upper function to show what it does to introduce everybody to the upper function but definitely from a simplicity perspective I should have just use search that's all okay let's take one more question here [Music] why were you using the function max so that's that's a little bit longer to answer I'll try to answer that one inside of a inside of the block but when you're when you're using a calculator inside of calculating you have an expression calculate expects an a like an aggregation almost so it expects an aggregation like a sum or an average or a count or max it doesn't expect just a raw value so that's why I had to use max in there if you try to go back and use that calculation that I used and you remove max it won't work now inherently I really didn't need if you think about this outside of the constructs of what's expected inside of the calculate function I really don't need that aggregate because I know that the DAX is only going to return one school name on one school grade but Dax doesn't know that so you have to use it kind of like an aggregation do you want to do you want to sum up the total do you want to average it you want to return the min or the max so for our example we could have just simply use min and Max but the reason we had to do that is because we had to use calculate to generate to get that to work and calculate expects really like an aggregated type expression for the first parameter and I turned it into an aggregated expression by using max that's why but it's a little bit more of a you know a longer explanation the way I can give in 30 seconds all right well again it's noon so thank you so much Mitch for presenting today again everyone all attendees today I will be sending all of your questions over to Mitchell so he'll be able to address that and either a blog post or get back to you on an individual basis so I thank you guys all for joining our webinar today as always we will be sending all registrants a follow-up email including a link to the full recording of today's webinar you can also find the recording on our website at pragmatic works comm so we thank you again for your time and we look forward to seeing you all next Tuesday for another free training sessions all right [Music]

Channel: Pragmatic Works

Views: 18,351

Keywords: DAX, Free, Training

Id: eKgcLcrebzI

Length: 58min 33sec (3513 seconds)

Published: Tue Sep 11 2018