Health Outcomes Prediction Scenario - Data Exploration w/ Power BI

hello everyone welcome welcome to um business analytics week let's uh let's just gather up gather everyone for the next minute or so um just as we kick off got some some pretty interesting um things that i want to go into but just like we've been doing recently i want to try and make this as uh collaborative as possible so exactly what we're going to do i don't think we've um haven't completely decided yet because i want to get your input and see and see what you think as well my idea for today and and for the and for tomorrow as well is is really just to work through a complete example from start to finish show you as much as i can about um what you can achieve in power bi and how quickly you can achieve it and you know keep it pretty flexible you know we can we can discuss a variety of things you can let me know what you want to learn about and um we can you know really just take things from there i don't even have a set time frame that i want to go for we could go for as long as uh as long as necessary really um i love these like sort of free-flowing and um um yeah free-flowing and uh easy to um uh these creative sessions really so i've got a i've got an interesting question thanks um thanks everyone for um chiming in let's run right here um so why is there different fonts on different tabs i'm not 100 sure um what you mean there um i mean possibly it's just we've got different platforms that you're clicking on um on our on our website and the fonts are slightly different cool um how many we've got so we've got a good uh good crew um so um welcome everyone um let me know on the chat where you're calling in from um and just like always at the end of this particular session i'll be giving away a free membership um to anyone who uh subscribes to the channel comments on the video and also likes the video i really appreciate it when when everyone does that it really helps us um and yeah i'm happy to give away a free membership um to whoever um whoever wins that at the end and we also give one out uh during the week that follows as well for for anyone who comments on the video also okay um so let's uh let's switch over cool there's a few a few local australasians here and um from africa north america europe awesome okay great i love i love how we can bring so many of us together from all around the world in these in these sessions it's really great okay so i'm going to i'm just going to showcase my screen here and i'm going to show you where i'm at um now i i love to show you in these in these sessions you know really what you can do from from from nothing from from scratch right and so to be honest i have not even decided what data we're going to use i want us to pick it together i want us to get i want to get a general consensus i know we're not all going to agree a general consensus of what we should what we should work on today and and then we'll do it we'll do it i'll i'll try and build as much as i can within an hour an hour and a half and we'll go from there so i've been on um you know one of my newfound favorite sites to get data i've been on kaggle just trying to have a look around i found a good one on bitcoin information and this is actually quite a challenging one like there's a lot of different this is all the prices for bitcoin um bitcoin prices over over time right and um what i thought we could do is we maybe download like all of these files and bring them into power bi and i can show you how you can manage this much data effectively so that's one thing we can do um there's there's a health prediction data set which i thought would be quite interesting which is a data set which um is trying to understand heart failure and we could um you know we could ever play around and see what we could do in power bi with that we also have enterprise dnas knowledge base so this is at info.enterprisedna.com you can see that and we have our own demo data library here as well and we have quite a few different types of data that we can we can download here as well so why don't why don't you throw out some ideas throw out some ideas on on um on what we can what we can analyze i mean there's heaps of great stuff here as well um is there anything is there anything that really sticks out really sticks out in your mind that would be awesome going through i mean we have basically any type of data you could ever imagine we have some sort of um yeah we have some sort of data set for it so let's let's get a um so we've got heart failure from ken um yep so jim yeah for sure um everyone's everyone's in the raffle for membership so it doesn't matter where you're from um so we do i do sales quite a lot right so i like to um you know mix it up a little bit i mean we could do like a real estate one uh we could do a nhl one um yeah health industries good one okay let's okay we're getting a bit of consensus here so to me it looks like this is a data set which has a whole lot of markers like biomarkers for an individual right um and and and by the way i know i know everyone's got a few different ideas which is cool um that's absolutely fine uh looks like health is something that um that everyone is relatively interested in um in terms of these other ones we've got time tomorrow okay we've got time tomorrow to do something um and we might even do multiple if we can do this quickly also um you know a lot of these examples that you're you're writing in about there are other webinars that we've done there are other resources on our website which i can show you as well so for instance we've got a lot of great insurance examples in the last business analytics week we actually did a survey one so that's actually inside of our platform as a recording so you can watch watch it as a recording and we did a really comprehensive one on airline customer satisfaction um so that was a good one and we've got a huge amount of resources on f1 and i did a webinar on that last time we've got a lot of resources on hr i've done i've done a couple of um webinars on that as well but it looks to me like a health what would be interesting so to me general consensus is is health sales and inventory as well as we've got a lot on this um but we'll see we'll see like i definitely want to try and um do all of these for you so we'll just see how we go okay i love doing these sessions and i try and do them all the time um so um definitely if we don't cover what you want this time we can do it another time for sure okay so let's have a look let's have a look let's have a quick look at this data set by the way kaggle is a great great place just to get random data sets um now basically this to me just looks like a whole lot of markers as i say so we've got like uh male or female and then a whole lot of other things regarding um details to do with the heart now the only concern i have here is how do we actually work out like this looks like it's how do we even how do we actually know of someone or maybe this is when heart heart like heart failure so this is probably sort of working out okay you need all of these different markers um and then okay okay i'm just trying to decide if this is a good one this is a good one to really go into i mean there's this i mean there's a huge amount of information here okay let's give it a go let's give it a go and um and we will we'll go from there okay so i'll just share the link um and yeah we'll see how far we get i mean it's not a very complex data set so there's like plenty of things we can do to it um let's let's have a look at so they actually have i guess there's like a lot of tasks that they put into this by the way um we are we are um in the midst of creating our own site like kaggle on enterprise dna's platform so i'm currently working with some developers to to build something like this for the power bi user for the excel user for this sort of data analyst um because this is more generally more catered towards sort of machine learning and um data science but you know we feel like there's a need for a data analyst type site and we're we're in the we are we are building it so i'm really excited to to build something like this and and you know hopefully in the future we'll be able to basically just leverage off our own platform rather than going to two others in in um in the near term um okay so i mean we can kind of like have a look at what some other people have done um so there's like a code here okay anyway let's let's um doesn't look like i can quickly um decipher what's going on there so let's just download the data and and i'll start building and i'll show you what i how how i would approach something like this okay i mean power bi is incredibly versatile as i'm sure a lot of you are aware right and you know i always try as hard as i can to showcase that um to you during these particular sessions and you know this is really no different i wanna just i'm just loading up the starter here okay so let's have let's have a quick look at look at it in excel and try and understand okay how can we build our model and this is this is a um you know this is this is my workflow that i you know i try and cover as as often as possible or i follow as often as possible is i want to um understand how i'm going even at this stage i'm thinking okay how can i build my model right a lot of this to me is yeah this particular data set like i can already tell it doesn't doesn't require i don't think a you know too much of a model to be honest um i mean we could we could kind of create one like you know we could we could we could create you know if you look at things like age um oh okay it looks like it looks like we do have um what they're calling like a death event sorry a death event which means this is the probability that someone actually actually does die so it does pass away so there is something for us to work with there which is good now what we could do when i look at this data is we could create like almost like a patient look up table and that's probably what we want to do because we've got the age and then we've got um like uh like the sex male or female um smoking things like that so yeah and maybe even maybe even this particular column this different we could we could bring in as well um and then everything else is basically like a a fact table where you know we might we might create an index column um here and we might create one in the patient table and then um you know there's a there's there's information that we can summarize at a lookup table layer and and a fact table that okay so i think probably what's best for me to actually show you how i would possibly do it okay and let's say let's do that uh let's just check how big is this data set by the way okay it's small it's small small data set as well so this is going to be you know this probably actually is not going to be too difficult so let's just let's just jump into it okay so um i'm going to go and grab the data it's i think it's in a csv file yeah it's in a csv file so remember i'm always going to the query editor right i'm going to go csv file today so yeah it does seem like an easy data set right but let's you know there's even easy data sets we can take to another level with power bi okay it's so um you know it's just it just takes a bit of creativity right so i'm gonna um i'm gonna use this staging query concept okay because it's a small data set so it's good for me to sort of show you these techniques i'm gonna say um this is the part yeah but i said okay so naming conventions absolutely key right and i'm going to um i'm going to first group this staging query right and then i'm going to disable the load so this is just a quick you know quick bit of organization that i might be able to do um yeah without without even having to think really i'm going to use this as my base query as my staging query and then i'm going to extract the information i need out of it by going reference okay i can go duplicate too in this particular case but some in this i'm just going to use reference as a um there's a way to do it this time there is a clear difference between duplicate and reference though by the way okay so i'm just going to put this into my data model file i'm sure this is something that a lot of you don't do is that i really quickly just get things in the folders the quicker you do that um i'm going to call this one health markers okay and then i'm going to call this one here um i'm going to call this one i'm going to reference it here i'm going to call this one patient patient info yeah um i'm going to look across here and i'm going to grab just like my key patient information i'm going to select those columns because i think there's just quite a lot we can do from a um uh from a analysis perspective we get this into one table um so i'm going to select the things that i want in this case i'm basically just going to select um i'm going to select the one step you know the other thing that's interesting is that these ones i think we can actually make them more informative um and maybe actually add text instead of just having one and zero because that doesn't actually add any value to us when we're trying to visualize it and so i'm trying to think okay well all of these are sort of numbers that we're going to calculate in measures but these are actually you know this should one should be male or female or zero should be male or female or whatever smoking we could actually have text smoker non-smoker and then you could use those as dimensions and filters inside of your visualizations right but while at the moment we don't have that information we just have one and zero which isn't very helpful okay so i'm going to go remove other columns okay then i'm going to come to this one here um the first what i what i realized we probably should have we probably should have done up here is is is this um i can i can probably do it easy but easily from here even though i didn't do it to start with is we probably should create an index column in here um so i'm going to create uh an index this way okay so i'm going to create an index by going one okay um but i'm going to change this up a little bit okay so i want it to be a bit more like a like a proper index and the reason why i would do this is because we want a way to be able to link up our patient information to our health markers table right and at the moment we we we do have information on the patient but there isn't sort of like a linking column really per se to actually link up to our health markers table okay and so what i probably should have done before i did anything was i should have gone index but actually this this should flow this should flow because we referenced the great thing about a reference right is that we can make a change up here and it and it will theoretically flow through down to these secondary queries after our staging query okay so i'm going to create initially this first index okay then i'm going to go transform uh no actually add column i'm going to go column from examples here because i really love this feature okay and then i'm going to go patient index okay and then um i'm going to i'm going to call this p like p 1 0 0 0 1 okay then we'll go down to here let's see p one zero zero zero two okay so i'm basically trying to get the the the machine learning algorithm to pick up that i want this table here okay so it hasn't at the moment so if i keep going i'm hoping that it will soon figure it out okay maybe maybe it's not going to maybe maybe there's probably a better way to do it but we'll see okay no it's not going to it's not going to figure it out so what i'm going to do instead is this okay okay i'm gonna do it a different way i'm gonna i'm actually gonna multiply this column and then just basically add it add a t to it so let's let's do that so we've got this not multiply we're gonna i'm gonna add to it so we're gonna go um transform so this is a cool little tip as well actually so you can select a column right and you can go add and then you can basically add anything so i'm going to say add by 10 000 okay okay and then i'm going to um create a column of p's so yeah i mean you probably just a number of ways okay this is just how i'm doing it um so please don't you know feel like this is the only way to do it this is just my mind thinking on the spot trying to um trying to quickly work out um trying to work out how to do it so yeah i mean i could look i could honestly just use it like like this um but i'm just trying to create something like a bit more of a of a marker basically so i'm going to call this one and i'm trying i guess i'm showing you some just ways that you can do things as well okay so created just a column with p in it and then i'm going to i'm going to move the column i'm just going to move it one left okay and then what i'm going to do is i'm going to multi-select these two columns and i'm going to merge them okay no no no separator and i'm just going to call this the patient's patient index and move to beginning okay so look i've got an index now i've got it as text let's see if it's flowed through to these ones here as well it has nicely because of the reference so this that's the clear that's the clear differentiator um this one let's let's actually let's go back a step here i'm going to delete this one okay so i've got two health markers um so now we're going to use this as our as a linking column basically between our lookup tables and our fact tables okay so i'm going to i'm just going to reference this again um i'm going to call this my patient my patients info and then i can quickly do what we did before i'm just this the whole idea here right the whole idea with the query additives we're trying to build our model this is the this is the big thing that you know to me is there's something that is is still very much um forgotten and missed um and a lot of the work that i see is particularly if you're just starting out with power bi so um you know it's it's it's really key that we we get that right we get this right and hopefully just by working through various examples like this today and in other times you know it becomes it becomes clear over time how important this is okay so now i'm just going to come here and i'm going to delete those columns we created as well remove columns cool okay so now we've got the makings of a model we've got a linking column and um now we've got a uh a table here that we can you know manipulate and and and and add add to as we go right so um the other thing that i would do here is things like this so one of the things that is not helpful as i mentioned earlier is these ones and zeros right and so what i would um do here is i would replace values and i would actually add in this particular case so i'm going to go zero is let's just go mail here so maybe we need to change this to text here because remember these are the columns that we're going to utilize inside of visualizations or as slices as filters etc as legends in our in our visuals and so we need to you know we want to make these actually actual values um cool so appreciate the thoughts here that's good um okay so i'm going to do the same here so i'm just going to go replace values i need to change this to change this to text and change this one to text as well so i know i'm spending a bit of time in here but i just obviously i just cannot stress how important this is cannot stress how much time you want to spend here as well um you know you just cannot you cannot neglect this area okay non-smoker and you know the power query is is just this hidden application in the back of power bi which is absolutely incredible it is an absolutely incredible um piece of technology and just the fact that you can automate all of this work right um okay so i'm going to do the same here place values you can see how i'm just adding like a lot more um information quite quickly to the data set by by doing something like this in in in particular in a lookup table so i want to go um [Music] passed away and then i'm going to go place value 0 here i'm gonna go survivor but we could also do something like like age bands as well um you know that's something that we could we could create as well like at the moment we've got every individual age right um maybe maybe within here we want to create actual bands um so we could we could potentially do this in here um or we could we can also do it within um within a calculated column as well um probably i think a calculated column might be easier uh well maybe maybe we could do a conditional column here right let's give this a go conditional column yeah i think we can let's let's give it a go mage bands because this is an additional this is a nice additional insight um that that isn't in our data right and um we can actually then create this as a filter in our lookup table quite quickly and in fact you're doing it in the doing it in the query and this is a big thing you know we always want to push as much as you can down to the query editor so if the age is let's go greater than say 90 we will call it um over 90. okay if age is greater than 80 over 80 i'll just do a couple more it's greater than greater than or equal to 70. [Applause] campbell's just made a super a super point here um which one i i want to like add add to the chat yep that this is the big like also one of the big things about this area is it simplifies your measures like crazy if you optimize your data tables and data sets and you optimize your model your measures should be simple if you're if you're finding you have to do really complex measures there's something there's something wrong okay um and then i'll do one more is greater than or equal to 50. over 50 and then is less than 50 okay so um oh we could have done we could have done my house there but that's all right so go okay and again i've just added edit um edition i'm gonna call it patient bands age bands okay so added another neat little um column here that we can use as a filter so this is just a bit of creativity right better creativity on the side and the other only other thing i would do here is you know i would clean up you know i don't i don't want any of these underscores um etc you know i want everything in proper format yeah this is how this is the best way to have this information right and then we would come along here and um yeah i'm just i'm also thinking the other thing i'm thinking is do we want to do we want to maybe do this like do we want to unpivot columns here so this is a decision that we need to make and we need to be quite careful um it just depends on what sort of measures we ultimately want to create right and how we want to visualize it so what we you know a lot of cases what you want to do with your fact tables you want to go unpivot and you want everything to be like this generally okay um particularly if particularly if the column up here is all of sort of similar values in this case though i think that they're different enough so we might want to leave it like this um it looks like this actually this column here actually should probably be in our other table what is what is anemia is that like um i don't actually know what that is is that does anyone know is that uh like some sort of like breathing difficulties because that was pro this this looks like that one on zero instance where you know you want to probably actually push this to um to here and actually get a bit more detail about it diabetes is the same low iron okay interesting okay well let's just leave it let's just leave it in here for now uh and let's move on i think it's just let's just um let's just move on and i think that in general you've probably got you know a good idea of what we are you know what we're trying to do here and we'll we'll just see oh it looks you know you know what it looks like it looks like this time here was the index it looks like it was the index or was it no it's not yeah who knows who knows i'm not sure what this time is what this time column is okay anyway let's move on let's move on okay so let's see what we can do on the um on the visualization side i mean one of the other things i would do is i definitely you know you want to just update all of these columns um i would love i'd love there to be some sort of trans like automated transformation that does that um in in the rows here but there isn't uh at the moment you have to you have to you have to go through each different one and and and clean them up um you know and this is just a small thing that makes a makes a big difference okay let's close and apply let's get let's get to the model and see what we can do from a visualization perspective okay hopefully hopefully you're all learning a little bit um you know a little bit about what can be done um you're pre actually working in this front end part of of power bi okay so first thing i'm going to do is i'm going to check the um information here okay so first of all my power bi is horrific at this relationship of these relationships so never trust what power bi is doing um i always even though it is a one-to-one even though it is a one-to-one and you can have a multi-direction you know i always want a one-to-many and a single direction in my lock-up table this is just bet this is just best practice it's just a way for you to really understand how you want to build your reports and how the relationships and and filters flow in your model okay so i'm i'm almost at a point where i feel i need to create like a a new concept of um you know some new methodology and framework around how a model model works in power bi i feel like i definitely have felt this for a while now um that that using like the older database technolo terminology like star schemas and um you know a whole raft of different ways that you can describe a model here just don't do they just don't simplify to make and make this area intuitive enough for the general use of power bi and so i've come up with this this this term of like the waterfall technique where you have a lookup tables at the top and you have your fact tables down the bottom and that is you know to me you know as simple as you can describe it and it and it makes it as easy as possible for anyone to be able to set up their models okay and i've shown this many many times like no matter what model i work with no matter how simple or how complex i can always use the waterfall technique where i have my filters and lookups up here and then i have my calculations and my measures happening down to my fact tables okay so that's what you want to aim for as well if you possibly can okay so let's have a go let's have a go here um and we're going to have to create some measures probably right um if we have a look in our health markers you know we probably want to you know calculate you know maybe like sums or averages right um potentially and at the end of the day like what we're trying to also understand is um you know what is the predict like almost like a prediction of the of of the death event like um if did the person will the person survive or will they not survive with a whole range of biomarkers okay and so to me um cool okay that's good i really appreciate that kindle so to me we need to have a good think about okay how we're gonna how we're gonna show this effectively okay um let's have a quick drink here midi's got some good points as well um treatment medication okay interesting okay so we need it we need to create we need to create like a let's just create a measure um we'll we'll calculate we'll cr we'll calculate like um blood pressure okay and we'll go we'll go every we'll we'll use average here okay we'll use average beverages where we can um so because i think this is going to probably show average blood pressure right um and then we can just do very very simply is do this [Music] no so that is just saying if someone had blood high blood pressure or not um okay so let's just bring this in i'm going to put it into a matrix here because i think we want to actually have a look at the data and we just want to like have a look at the data in many different ways first one of the one of the things that i i want to show you as well is that you see this health markers table this is our fact table right this is what a very common thing that i do i want to i want to make sure i hide all the columns that i don't ever want to select okay and so i hide that because my patient index or my patient details right they're always going to come from this table here okay so i'm going to grab that and put that in okay and we have the death in here so that's where just trying to think why why is it zero blood pressure oh okay okay i'm with you now i'm with you um okay so yeah so we can we can i mean we can basically look at this in many different ways right so it's just it is really just deciding okay what is the way to visualize it and sometimes what i do sometimes what i do because the model is so simple here we can spend a lot more time on on looking at looking at things like this so i'm going to bring in a few things there just so we can try and work out what would be a good insight and it's almost like we need a percentage isn't it [Music] [Music] um this average blood pressure that's not really gonna work yeah this is this is a good one from stephanie there's a good one um so mini's got a uh an idea of maybe calculating ratios but i'm just trying to think i'm trying to think okay well let's actually let's turn this into a table let's turn this into a table uh we're going to get rid of that and let's place it all into a table and see if by doing that we can you know work out in some way you know what's the best point of attack from here my only my only thoughts are here is that at least ones and zeros going to really help us i'm not i'm not convinced but anyway let's just just move on um and create a few other of these calculations okay now remember my my philosophy is that you never want to use these columns okay you never want to use these columns which have numbers in them you always want to place them into measures okay good because measures are the beginnings of anything advanced right and so you want to in this particular case let's let's um let's do this so we want to do a few averages so i'll just do it for a few average 18 okay [Music] so i'm just doing we're starting simple averages and and even in and you've got to remember that the the average because of the context of in which we're placing the average it is uh it is basically just returning the result which is what we want which is what we want we can you know we could do a min we could do a max but averages um is absolutely fine in this particular case okay let's have a look what are some other ones we could do platelets let's let's try that one [Music] so [Music] it's almost like it's almost like if you think about it it's almost like we need to create a score isn't it we need a score we need to kind of say okay well if this person um has this this this and this you know we we need to score it at some in some way right and so maybe there's you know maybe maybe maybe there's some logic that we need to write around that that scoring isn't it um so i'm just trying to think how can we do how can we how can we create a score how can we create a score per person i mean this is this is generally like you know what machine learning does right but is there a way that we can do it in power bi and i believe that there probably is there probably is a way for us to um yep so this is exactly what i mentioned before yes yes it is in this particular case because of the context in which we placed it in but the reason why i've done average is because what if we place it in a different context so what if we place it in the context of say like are they male well if we want to find out what the average is we can we can drag in the average and it gives us the average and so um then maybe if we want to add a different piece of context like did they die or that if they didn't die then we get the average so that's why i've done the average in this particular case okay um yeah that's exactly right and that is that is why um there's exactly right and that's why i've used average right because it gives me the same result it gives me the result i want whenever i put it in a different context um okay so to me like the key you know almost like the key thing here is i mean couldn't couldn't we know one of the things we could do right is we we can kind of say okay what what is the what is the i mean maybe this is maybe simpler than we think like what is the average of all of these values for someone who survived and someone who passed away and then can we identify a trend based on just that okay so let's say okay so what's the average um of this level what's the average of this level and what's the average of this level okay and then if we go and and we do add the death event here right can we um we probably need to especially for these average average blood pressure we probably need to make this a decimal point going yeah that's right that's what that's what i'm thinking that's what i'm thinking we should do is something like that as we should compare the average the actual so this is where average i think it's going to work for us you can compare the actual patient score to the average in the age bracket yeah i agree i agree i agree that's that that is that is that is true that is true and that's where again this is this is a game where we can um i like it i like it a lot i like a lot so x-axis yep yep yep yep i like that so we could we could very easily yeah that is a really good one actually so okay let's let's give that a go so um i've got let's just i think that's a perfect example where we can create um we can create let's have a look at this and then we'll bring in the patience index right so this is this is this is very true this is a really great way we can visualize it so we can sort of have a look at our averages per person right average per patient and then we can bring in you know the the death event as the um as the legend right and so we can quickly see try and identify is there any particular trends in any of these um data points right and so that's why that's why again why are having these like creating these measures is really important now the only thing that i don't think would work here is like does average blood pressure really add much value because it's always going to be either one or zero and so that's not that's not immediately you know that's not that helpful to be honest and then we could maybe like look at it i'm just i'm just trying to think of my mind like as we go like how can we how can we bring all of these things together like there's no doubt in my mind that we can we absolutely can uh it's just um how you know how can we how can we bring them all together to tell it tell a decent story that is there's that is sort of what is on my mind at the moment um so probably always want to keep patient info in here i would guess and then maybe maybe we can put age bands maybe yeah so pretty quickly we can get some some interesting interesting results here i mean maybe what we could do is in the size is where you could you could also bring in say you know you could have a one or a zero for those with high blood pressure and those with low blood pressure so that's i mean that's one way you could do it i think this is looking pretty good this is looking pretty good so okay so we've decided understanding the death event like and but then trying to work out across multiple different variables right so multiple different variables through time that's that's what we need to to really nail down so how can we do this how can we do this by the way i'm really enjoying um going going through this example i know that um this isn't these aren't always the most refined sessions but they're the sessions that i enjoy the most because we're you know we're really on the call trying to figure this all out um as we go and you know usually within an hour an hour and a half you know we can create something pretty pretty impressive right and that is you know to me amazing like that it still amazes me to this day how how you know you can create such high quality things so quickly in power vi okay um so what let's have a look let's have a look at the data set to look at the data set so we've got this we need to average this one up this one up as well this one because these aren't these aren't hugely helpful these ones here yep dad exploration sometimes yeah it's really true i mean sometimes this just takes a while right it just takes a while to to actually um figure all this stuff out so i'm just trying to think okay what's my next step here how can we how can we move this forward um so okay so i'm going to i'm going to quickly create just a couple other measures just while we're waiting what we're trying to think about um what to do serum [Music] anyone got any questions anyone got any questions about about this methodology um anything that i can review or go into [Music] okay another measure here now what i would honestly love is and i think you can actually do this in tabular editor three it's been able to create all of these sort of what i do what i call core measures being able to create them a lot quicker like i would love to be able to just say okay calculate average of all of these key columns and then just start to show up and they'll be that'd be a nice little enhancement to power bi probably you know on the cards i mean i think in in tabular editor three you possibly can do that so that's something to ever look for um okay so to me this these sort of metrics here these this average blood pressure it doesn't it's not actually a really valuable um metric that is my inclination these more these more are because they're actual numbers right like this is just like a one or a zero value so yeah not 100 sure i mean yeah really that's just that's just sort of showing like how many people like the average of people rather than like some sort of like biomarker so i'm going to take that out for now so this is a good question how can you create a school that waits and accumulates all the measures you just created yeah i like it you know that's that's definitely what i i agree that's what i want to try and um i'm trying to think about how i can do i'm trying to think of it on the spot like how i can do um i just want that make sure i've got so this ejection fraction let's just let's just do this one as well because then that is all the main ones [Music] it's interesting i think that hamisab has already identified this that one other like if we even if we just have a look at this information that the um this particular you know this is our data exploration phase right like the here is a clear like there is actually quite a clear indicator here like this is a lot higher than survivors and also if you look at this that the averages here is lower for those who passed away um this one isn't as as sort of clear-cut but but yeah so intuitively it looks like you know we are identifying something i mean and we can we could also click through um you know some of these filters right to be able to sort of see is you know is is is that real like is it is it actually something that that occurs over time so it looks like you know in different age groups it's not not as prevalent for example so stephanie's got something here so let's have a look when they were set to one that means that they have that thing right like one equals high blood pressure so your largest sum the more unhealthy you are uh yep yeah yeah yeah yeah that's a that's a very good point it's a very good point so one one one okay i like it i think that's a good okay well i mean that's a and then you know there's probably there's probably you know if we look here as well there's probably smoking as well um so maybe maybe we should bring take this one back maybe we should take this back like maybe we should go back and bring smoking in there and then the higher the higher the sum of that number the we could say okay this is this sort of classifies someone as as unhealthy right um so okay let's give that a go i like it i really like it um i'm gonna i'm gonna just show you a little bit of a trick here so what i did was remember um i took out some columns i'm gonna i'm gonna try and bring back we're gonna try and bring back that column um so i'm gonna see smoking smoking smoking is up here so instead of like changing too much else i'm going to just delete that in the advanced editor and let's see if it comes back okay cool so it's come back in here okay so this is this is good that's good this is good and then um i'll leave it in here for now i mean we probably don't don't require it but i'll leave it in here for now um so we've already we've already done this one we've already we already have it as a slicer um so i've got non-smoker and i've got smoker here as well um but probably you know what we could do is is we could calculate we can let's see if we can do it dynamically let's in here in this patient info right let's calculate a score let's call let's call this let's go um basically let's just add these up let's just go health score okay so let's add this up let's add this up and this up and then we'll give them we'll calculate a health score okay so let's let's do that and this you know this is not not difficult at all okay so i'm going to go we're going to call this one the health score [Music] in this case i'm going to use average x okay i'm going to use average x because again we want to maybe we want to aggregate these up um whenever we place them in a different context but when they're at the patient level they will just be um you know they will just be the value for the patient okay so i'm going to go and go health markers and then i'm literally going to sum up those columns okay um smoking plus anemia plus plus why did an average instead of us instead of like a sum or something like that is because of this so i'm gonna bring in my patient index here okay and then i'm gonna bring in my here my health score okay and so we can now we now we have a number now we have a number that we can use in in a few different ways right um we could use this as a as a filter itself like basically you know just just look at people in in with a score of one um score of four so on and so forth right probably the best way to visualize this though is um is in a in a chart like this right trying to see you know based on based on their average score what they what what what um what the outcome was and so we can also let's have a look here let's let's bring this let's aggregate this up at a different level and get get averages okay and see if there's anything so the passed away columns rows of the first one and the second one nothing nothing sticks out too much in females but in males it definitely looks like there is a bit of a difference right which is interesting in the data set that's good there's some good outside the box thinking which i like you know we can also remember we've got all these other you know we can actually look at this by age band as well we could say okay well is there you know based on that health score is there any anything that is you know does the health band make a difference so does over 50 make a difference um over 70 over 80. so yeah we're really really starting to dig into some interesting interesting interesting insights here so how how could we how can we use this visualization let's just have a thing how can we use this um you know how can we use this even more right think of a way we can visualize it let's let's let's try start building up our visualization or enabling us to tell a bit of us like somewhat of a story so i'm gonna i'm gonna create another um page here i'm going to say health um health predictions um [Music] to me like the main story that we want to probably tell right is we want to be able to um see by death um or or not so this to me has to be a key filter and how we how we add this filter in um you know will enable the consumer um to be able to click through um and and sort of find the insights that they want okay so i'm gonna i'm gonna place this just as a as a slicer up here we might we might do it differently in the end but i want to be able to click on passed away or survivor or nothing and be able to see the insights below here okay so um maybe maybe let's start with you know some of these insights here and see if see if we by adding a few of these we can you know try and identify um identify any trends okay so i'm gonna bring these in and just change these up a little bit maybe we want to see the difference between male and female so male and female and i thought that information around sort of this was quite interesting as well um [Music] but is it you know the the thing is is that is this maybe there's a better way to actually you know there's probably a table's a bit boring right like we could probably create an interesting chart that sort of um has the age bands by male by female but has them sort of by itself so you know hit i mean here's a quick way we can do it um we can say like like for example we could go um let's let's get rid of this to begin with yeah okay so we could say let's just go female here turn this into a visualization i mean maybe maybe we could do this right we can probably like this this would be a bit more interesting um should we get rid of that yeah something something like that i think would be quite nice like looking at may of male versus female um and the the health scores um but what we need to well i guess the other thing we need to do is we probably need to you know we need to be able to compare by have they survived or they passed away don't they don't we so um maybe maybe it's best if we do it this way one of the other things we need to do as well as you see ah it depends like do if you want to um actually sort these in in a unique way we probably need to create an index which sorts those but that's um yeah so that's pretty easy to do so i'm sure i'm sure you can do that um i'm just trying to think i'm just trying to think like because we want the user we want the user to be able to sort of select like this right and you know is it is it that helpful um you know maybe maybe we want to put different metrics here we want to go like smoke a non-smoker maybe we want to bring in you know what else what else we go i would also probably like make these a little bit smaller so we could add more more in here i think i think we could find something pretty interesting here honestly that is definitely my feeling don't i don't i don't think this is i'm not i'm not 100 sure on this one um a line graph by age i'm not i'm not convinced um that is that is it this one here uh a line graph page i'm not sure um if that's a that's a great one honestly [Applause] okay let's have a thing what other ones what other ones like to me these ones are great we're we're actually you know trying to compare and you can kind of see passed away survivor um because this is a way that you can sort of see correlations isn't it um so let's let's add another couple of these and see if we can change them um let's go let's go health school here so if it makes it makes any interesting no that's not a good one okay we need to make this a little bit we need to um change around a few of these things so maybe what i would do here is um these titles are too big so i'm going to make them a little bit smaller i'm going to get rid of all of the um titles because i don't like those this just starts freeing up a bit more space for you right and the same same here we'll just make these a little bit smaller so you know that you can with multi-selecting you can more easily change lots of things in your visualization a lot quicker um okay anything else anything else that anyone can think of that would be good to have a look at i mean i'm just throwing a few things together here but i mean there's definitely other things that we could do trying to think how can we how can we really showcase it how can we really shine a light on what is what is the um what is the inside here like to me you know there's there is a like a lot of outliers that i can identify um but is it is it really giving us a you know is it really giving us an indication of what um of like almost like a prediction is it giving us the prediction that we that we think you know so i'm just going to change this i'm going to change a few things here i don't love that color but just not it's not differentiated enough nope we'll go we'll go back here for now um i'll just change these just right here items but we can we can obviously click around here so we can click on female right and see is there a is there a major difference between that and um and and mail really it's to me it's really the outliers right it's the outliers here that that's that's really what i can identify the outliers um but then how does it come into the health score like is that is it is it helping us with the the scoring or of of the house score maybe maybe these these aren't actually doing and doing us justice like these particular visualizations um [Music] i think i think probably a table honestly is a better especially with with you know we don't we just don't want too much um so i'm going to go i'm going to go on this i'm going to go death event and bring in my age bands like tables can honestly be more than enough right in a lot of cases and then i'll bring in my health score and i'll bring in my male or female as well and then what we could do here just quickly is we could just do a bit of conditional formatting with the background and see if we can identify any anything interesting just from say something like this so definitely definitely the the bigger numbers are well that makes them trying to think okay so the passed away passed away definitely has the bigger numbers right so survivor has a little bit less and i mean this is pretty obvious but um maybe maybe it was maybe it wasn't obvious 30 years ago but it is obvious yeah um that that you know the higher higher your your health score that we've we've calculated the higher your the prediction is that you're the you know you're there you'll that you'll pass away um okay cool so let me have a think what do you think i'm able to keep going if you guys are um so so this is some interesting ideas what is what about using the key influences visualization so yeah i mean you possibly could you possibly could i haven't um [Music] i haven't really actually used this virtualization much let's have a look so definitely let's just um okay let's let's just go patient data um i don't really know how to use this this visualization um to be honest so maybe maybe this is something that i need to look into a bit deeper before i try and showcase it live on a webinar i just haven't used it that often honestly um i mean can i add multiple of these things wow that's actually quite interesting let's let's let's let's have a look at this [Music] so on average when health score decreases the likelihood of death event being survivor increases wow okay wow it's it's amazing it's it's telling us the answer okay let's have a look so when the average serum creatine goes down the likelihood of a death event being survivor increases by holy crap this is amazing this is amazing if this is if this is actually right i mean it's hard for me to hard for me to actually say because i don't really know um okay let's just bring all these instead of seeing see let's see if this gives maybe we didn't need to do anything maybe we could just have uh created this um oh yeah amazing so we can actually look at it okay on average when health score increases okay so this is this is looking at the on average when house score increases the likelihood of a death event being passed away increases okay can i i want to i want to see if i can expand by six is it showing anything here wow this is this is pretty damn amazing okay so the goes down 4.1 4.41 the likelihood of a death event on average when the serum sodium decreases the likelihood of a different being passed away increases wow okay so let's let's go back to this page here and um let's just try and see that as well so it was the average serum creatine so if the average serum creatine goes down the likelihood of passing away is greater it's hard it's hard to decipher by looking at that one expand by age bands let's have a look for some reason yeah when analyze is not summarized the analysis always runs at the row level of its parent table changing this level right is not allowed okay so maybe it's got to be at the same same table this is super interesting by the way i honestly have not even used this visualization that much i don't know how many of you actually have but wow this is this is amazing let's let's actually change this from pay um patient bands okay so that's okay so let's have a look at this on average when health score increases the likelihood of patient age bands over 50 increases let's let's see by death event let's have a look no it's not it's not working almost like we need we need to understand you know the death event is the important thing to analyze and then you know it's can we filter again by something else so when the average platelets goes down the increase the likelihood of passing away increases yeah it's a good point caitlyn but it's not allowing me to actually do it so i'm not 100 sure why [Music] so it's a bit bit annoying [Music] but uh yeah thanks for thanks for bringing this to my attention this is pretty amazing the only thing is uh is can you like how how sometimes so we can click on so when the average creatine levels go down nine six eight oh it's got a tilt up as well so it says what is it is the likelihood of a different is survivor increases for example a fall of a fall of 968 leads to a 1.19 increase likelihood yeah interesting okay yeah so i think there's a good point as well like what if we add some additional filters to this page right so let's let's do that let's see if we can add a male and a female um like bar like like a pie chart or something like that okay so let's let's add this out here and we're going to go [Music] male and female and then i'm going to do a quick measure here just calculating up my total patience so maybe this actually could help so total patience [Music] arose patience it's all like that and then i'll bring that into the values okay so now we have a good breakdown of male and female um i would probably also just clean this up a little bit take it take out the legends and add a bit more in here so if we click on mail here wow okay that's cool that is cool this is pretty amazing actually so we could also we could also do this by patient age band or by any by any marker really we could do it by anything couldn't we um we could we could do a smoking indicator here as well so this is this is really still just data exploration isn't it i'm still just exploring trying to figure out the insights but i mean to me it's it's sort of doing it is actually doing a lot of the sort of prediction type work for us isn't it um and when you when you lay it when you layer it on with some of the other visualizations that we have that we can use it's pretty powerful i think and i also it's there's also some amazing tooltips that i think we could probably use as well um um you know to dive into this information even more i mean you know what would be pretty amazing is if we actually made this page into a tool tip wouldn't that be pretty amazing um which you absolutely could i mean there's no reasons why you couldn't so for example you know maybe okay let's let's just let's just give this let's just do this as an example because i i think this is this is quite awesome um so i'm just gonna bring this down here let's actually just to show you how amazing tool tips are let's use that as a tool tip for this visualization right here okay you could even do it like at the patient level so you know you could hover over this and have some sort of tool tip that gives you the prediction you know a bit more details about the individual patient which i think would be pretty amazing i mean maybe maybe actually there's a better way to just generally visualize this but okay let's let's use this let's try this let's try this i'm going to take this out okay let's okay let's make this a little bit smaller this way okay um so to to create tooltips if you aren't aware is you just got to [Music] come to this section here i believe no is it in general oh god be not selected on the video i'm sorry page information we want to turn this into a tooltip so this particular page we can we're making a tooltip now i'm just going to change the um size as well we'll do we'll do a custom so we'll go let's go 1 000 we'll do this as 600 okay and then one of the tips that you want to use is you want to go actual width so this is actually enabling to see how big it's going to be okay and then i'm going to create i'm going to create a text box here [Music] you can grip like you can do i mean you should you've got to have a look at a lot of the work that's been done in our challenges because the inspiration you can get around you know what's possible uh with tooltips is is honestly phenomenal um you can dress these up as you know in any way that you like really it's quite quite incredible but there's just as an example okay as an example okay so we've got this visual here now let's try this let's let's see if we can add this tool tip to here so that when someone hovers over it they actually get the summarized version really quickly okay so i'm going to add tool set page four okay okay so now if we were visualizing this let's see what happens here i mean that is just like unreal unreal maybe it's not the best one because you can't actually select anything inside of the um inside of the visualization but just from uh i mean it just blows my mind how quickly you can discover such key information right so if we want to just compare male versus females so quickly like that we can just we can just hover from one thing to the next amazing and the same could be said you know maybe maybe there's a way we we actually more quickly incorporate um information about the patient maybe we can have um and and sort of you know we we we need to work out the averages yeah what we probably need to do is this is this this would be something to take it to the next level right is we probably want like one visualization that shows all of the patients and then we want to work out what is the average for certain metrics for a particular patient and then when we sort of select on the particular patient right we should compare it to all of the averages across if they are a male or if they're a female and we should have it in a visualization which just quickly identifies that so you know we we're hovered over something and then a tool tip comes up and it says okay well this particular person this particular patient um has um um this that has this sort of level which is you know below the average or or way above the average or and and then it has an average creatine level which is way above the average um it's got a health score which is way above the average that we that we generated and then that's how i think on a per per-person basis we could come in here and almost sort of say okay well this person's very high risk because they're well above the averages or well above the the indicators for if um you know if they will survive or if they if they won't so i think that would that to me is as a way that you could probably you know really um really improve how that you know how how this story is told basically we could also um we've got male and female here don't we so yeah you could you can select a particular person right but that's not going to provide that much detail here so probably what you want to do is you want to think okay what is the what what is what is a page that we could create just about the particular patient and measure that one patient versus all of these key indicators right um somehow summarize it in one page because at the moment we're sort of more looking at it at the moment we're looking at a more summary level and this is how you this is to me this is how you generally want to structure any of your reports you want to you want the the user to be able to come in and look at look at your your summary right and that's sort of your main page but then you want to take them on a journey to be able to dive into very specific elements of the data so here i'm looking at a more summary level i mean i would i would probably change this around a little bit it's got it's a little bit busy for me um and it's not as sort of clear you know for for for someone looking at it to quickly identify you know the great insights but um but you want to be able to say you know the user you want the user to be able to hover over something or or click into something maybe maybe it could be a drill through maybe maybe it could just be a simple um simple tool tip to be able to drill into that patient and say okay well um we already know if this patient has survived or not but if if if we didn't have that data set and we didn't know if they survived or they passed away would we be able to take that person's metrics or markers put them on this chart and then assess you know versus all of the key metrics will they actually survive or not and so i think you know that is that is ultimately what um what we want to do from a drilling down perspective um i mean we could you can select things like this that can drill through so it's just a matter of you know just really having a think um you know we could we can also multi-select here as well so i'm just i'm just holding down control as i as a multi-select and so we can there's there's many ways that we can you know sort of get to some sort of answer or or exploration of our data so yeah yeah i think i think um i think we've probably done this data set justice we haven't we haven't done the visualization any justice i don't think like there's there's definitely a better way we can visualize that and that's probably something that we need to work on at a later point but um i think i think we might round off for today i think because i've gone for an hour and a half i've enjoyed working through this one i've been i've learned something myself which is which is brilliant um hopefully hopefully you guys feel the same way and you know i i really like that like one of the things about all these data sets that we're working on particularly in these sessions is you know and this is something that we where we're absolutely trying to um trying to achieve with our platformers is basically i want to show you more about how to start go go from the beginning and get to the get to an amazing you know end result uh with a great piece of analysis and so you know one of the one of the thick things that um we're definitely trying to do and we're trying to build upon and um as as rapidly as we can is trying to build a lot more functional labs and a lot more industry labs um and so we're only really just getting started with this but you know think about this healthcare data set i mean that would be amazing as sort of like an industry an industry lab right because we're we're actually analyzing something that's quite unique and so we've just made a start with these so we've got a couple like power bi for oil and gas and power bi for insurance reporting etc but the idea is that we expand on this you know considerably we almost have like 30 or 40 of these short courses based on all of these different industries and all of these different functions and so that's something that we're really targeting um at the moment um amongst many other things but um but yeah that's every time i do these sessions that's that's definitely what comes to mind one of the one of the other things by the way everyone and i'll mention this um tomorrow and you'll probably get an email about it for me is that um we are currently having a promotion based on us hitting 6000 members so we are we're growing like rapidly and and we want to make sure that um everyone has an opportunity to to get hold of membership so if you don't at the moment we haven't um like literally our best deal i think we've ever had um on your first year you can you can use this coupon code there's i think there's about 50 left at this level um you can use an amazing coupon code uh promotion with this coupon code and to get access um you know at a really discounted rate so so definitely check that out you know we we are um yeah we are innovating like crazy trying to improve our platform even more more content more resources more initiatives more community and support etc so definitely definitely check it out if you haven't um haven't got access right right at this moment also by the way we are looking to um sponsor any user group uh any group out there that is involved with power bi we want to sponsor you and um and we want to um partner partner with you with the enterprise under the enterprise dna brand so definitely reach out um if that is relevant to you okay so hey let's round off um i'm just going to do i'm just going to do a quick scroll because i know i've got to give away a free membership and i'm going to land on someone um giselle giselle are you there giselle if you're there congrats free membership um all you have to do is just come to go to our website and um scroll down the bottom and say that you're a winner on our um um on the um our youtube on our youtube stream or on our on our live stream today and um you can get a free membership from us so i'm also going to inform my team as well just quickly let's take a screenshot so that they know that it's you and by the way everyone if you if you have commented and you had liked this video we will also be issuing another free membership by the end of next week so definitely watch out for that we'll get we'll get in touch with you over youtube or by by some means okay [Music] okay okay any questions any questions um tomorrow we've got another day right what do you think we should do tomorrow just let's let's throw some ideas out there we've got yeah this one of cryptocurrencies which was quite complex which i thought was a good one um but we've also got you know a huge amount of um demo data as well on our website so this is our knowledge base info dot info xenon dot co but um yeah let me know what else what else do you think agriculture customer service finance government and we've got a few other health ones which is clinching hr insurance so by the way um i'll quickly just show you a few things um a lot of we've we've done a lot of past webinars etc um and i mean throughout our platform we've just got we already have like a lot of um content and uh resources on a lot of these topics right so i'm just going into the data um dashboarding and visualization course so i mean here's a few consumer goods customer deep dive inventory insights this is a you know a complete model that's built over an hour retail sites scenario analysis management dashboard employee insights is an hr one insurance one here and then also you know in the past we've done you know so many so many webinars um now i've been i've been i've been personally at this about four or five years so i've got a lot of um a lot of webinars that can be taken advantage of which covers certain things like this and these sessions are also loaded into here right so um let's have a look i think i think they are in this one here so so last time we did a survey one right so that's actually in this module here business analytics week from last month airline customer satisfaction analysis so i know someone asked about survey data um we also did some supply chain and retail locations um last time as well uh and plenty of other things i mean there's just so many different examples that we've got in here you know one of my one of my like big goals is we need to make yeah this is this is something that is just constantly on my mind just constantly on my mind is um how can we make the discovery of all of the content that we have easier yeah we we we've collected just an enormous amount of content and we're creating more content as well like there's a lot more content is coming soon and so we need to really enable the ability to drill down into all of this content um just so there's not overwhelm i've received feedback very recently on our platform that that there's so much that it's almost overwhelming and i get it i get it i totally get it i totally understand and i want to do everything we possibly can to improve that um and so i've been thinking hard about how to make the discovery of content and resources that a little bit easier so so that's something that that i'll definitely be working on one of the other things um definitely would love you all to get involved in the challenges um so we've gotta if i just quickly show you here we have two initiatives going on at the moment we have our power bi challenge um anyone can get involved in this it's it's on a consulting and time a consulting time and earnings data set so you can check out the details about it here and we also have our accelerator as well so this is all this is run by brian um who is our chief content manager at enterprise dna um really our chief content and community manager and so this is an amazing um 10 10 round uh program we're a week week five or round five at the moment and this one's all about time intelligence so definitely get involved in this one if you want to increase your power bi skills really quickly these are these are project based and also live um and then obviously the challenges is just a brilliant way to to get involved with some of the best power bi experts in the world okay so a couple of couple of other thoughts i'm just gonna i'm gonna just unshare my script here because we're gonna we're gonna wrap up um let's have a look let's have a look got some good questions so um telecommission telecommunications data with with some maps yep uh yep i like that one mapping yeah i i agree um these are these are good ones you know one of the one of the um by the way i'll actually just quickly just show this again within our platform um if i just quickly navigate there we actually do have some amazing we haven't we have a course by a geospatial expert in our platform geospatial analysis and power so anything you want to do to do with maps or or anything geospatial um this there's some amazing content in here okay what else we got so there's no resources um uh in these gym um because i'm i don't have any i'm literally you know i'm i like to be able to build these things from scratch so you know the the there is no resources uh that is that is what it comes down to um we we do make the resources available um after the session but but not before because i'm building i'm building the resources as we go long-term corporate financial planning yep so that's that's a pretty common one um yeah we'll have a have a think about that have a thing about anyone okay everyone let's wrap up we're going for an hour on 40 now um really appreciate it if you were if you were able to um stick with me a quick question from michael are these paid resources yes so so what i was showcasing before is our platform right um and we've got an incredible promotion on at the moment as well which i'll definitely remind you of tomorrow and uh with some follow-up emails but um definitely navigate to our website if you want to check that out now um yeah we we want to we want to honestly get everyone in the world using power bi integrated into our platform into our community in some ways so you know we want to make it as possible you know we want to make it as available and achievable as is as in as um as ever so definitely check that out if you haven't haven't already okay everyone i'm going to end in the stream now thank you so much for um for attending and for collaborating and um let's uh let's catch up again tomorrow look forward to tomorrow's session and we'll do do something similar once again okay take care see

Channel: Enterprise DNA

Views: 1,772

Rating: 4.9285712 out of 5

Keywords:

Id: rkVKi5a7euQ

Length: 98min 33sec (5913 seconds)

Published: Wed Sep 22 2021