Rock U - Reporting - BI Family Report

Transcribed Video Content

In this video, we're gonna look at how to recreate this families report within Power BI. Now, is one of the more exciting reports and it's a very powerful tool. And the more you get in here and the more you see the capabilities, I think you're really gonna be impressed. Okay. So basically, this report is basically a map of where everybody lives within the church. And again, our demo dataset is not the richest dataset, to be looking at, but I think you'll kinda get the idea. Okay. So let's let's go ahead and recreate this. And we're gonna actually look at a lot more features, that aren't even shown here. Okay. So we create a new page on our report, call it families two. And the first thing we're gonna do, we're gonna start out a little bit differently this time. Now, normally, we're gonna report off of fact tables. But as we talked about in previous videos, you don't always have to use fact tables. You can use some of your dimension tables too. So in this case, we're gonna use the dim family current cause we want the current, address. Okay. And I'm gonna start a little bit differently. In this one, I'm gonna start by pick picking my visual. So I'm making sure that I don't have another visual selected. If I do, I commonly make this mistake. I'll have another visual on my canvas and it'll already be selected and I won't notice that and I'll actually pick to pick a new one and actually change its value. And then you just have to control z to undo that. But making sure it's blank, I'm gonna go ahead and click this, which is gonna make a nice map for us. And just gonna make this map just a little bigger. Okay. Now, making sure I have my visual selected, I'm gonna look over here at the different properties of this map. And there's a a few different ways I can get the pins on the map. I can either drag, the mailing address full up to this location field here. And I actually kind of prefer to do this because it's a it's a little bit more formatted looking. And so it's gonna basically take all of my data points and it's gonna start putting them as pins on a map. Okay. So it's a little bit different. They're all the same color. I'm gonna show you how to change that. But notice as I roll over them, it's actually gonna show me the full mailing address. And I kind of prefer to see that versus the other way which is dragging the latitude column and the longitude column into these two properties here. Okay? So just realize my map here has a latitude and longitude. I could drag those columns into here. You might actually try that with a bigger data set. It might be that mapping by latitude and longitude is faster, than mapping the full address since it doesn't necessarily have to geocode. It's already pre geocoded. But, my data set's not big enough to actually, be able to try that. Now, one of the annoying things about this map right now is it's trying to be nice and tell me that, hey, there's some features that couldn't get mapped probably because they weren't geocodable. The addresses are either blank or maybe they're invalid. And so this is really nice that it's telling me, but honestly, I don't really care and I just want it to go away. So one of the ways you can do that is you can actually drag in, say latitude down into this page level, filters and basically say it is not blank. Okay. And that's gonna, hit apply filter there and that should go away. I must have said is blank. I want it is not blank. There we go. Okay. And now it's gone away. So I'm basically saying, hey, just hide the pins that we can't map. Okay. So that that's pretty cool. We're actually making good progress. Now, as I roll over these things, , sometimes in in the course of rolling it over, there's actually two things on that item. And it's a little hard when you can't see it. But some of these pins actually have, two families at them. And when and when that happens, you can kinda you should be able to, click and roll up onto it and then, let me just keep scrolling in. You can rotate through. Now, this is not too helpful because all you see is the address. But that's very easily fixed. So under this tool types, I can start dragging in other fields. So for instance, if I wanted to see the family title in there, if I wanted to see if they're an ERA, I can drag that in here. Remember, you can put as many family attributes as you want in here. Okay. So I'm kind of building out my tool tip. And so you can kind of see that. So I can see the family title. I can see the child count as I go around. So that's a great way of doing that. Now, reason why I kind of prefer that full address, notice that whatever the location you pick shows up in that tool tip and you didn't really really have a choice for that. So if you had used latitude and longitude, you're gonna see those values, which to me is not as helpful as is pricing the full address. So that's why I preferred putting the the location in there. Okay. Let's go back over to our report and see what else we wanna do. Okay. So we wanted to have some connection status information over here, the campus, and whether or they're an ERA. Okay. Now, this should be pretty obvious if you've watched the previous videos how you're gonna do this. So we are going to and before I do that, I wanna add a connection status to my tool tips just to have. Okay. So now I'm gonna drag connection status out over here and it's gonna give me a list of all the connection statuses And I want to get a count of those because I want to see that in a bar chart. Okay. So now I have a nice numerical value and I want to turn that into a chart. And there we go. Okay. And then, let's just go back and see what those other ones. Okay. Campus and is e r a. So same thing. Try out campus name. Again, my data set, not too rich, only has one campus. And it's a terrible name, main campus. And we will turn that into a chart. Okay. And then I wanna see is ERA. So drag that out. Put account on that, and then turn that into a pie chart. Great. Now, the other charts, all of these different visualizations work together. So if I wanted to just see members, I could click on this chart and then my graph is gonna kind of update. I'm only gonna see the members. Okay? And as you can see, you can see which ones go to which campus. So in what this is basically saying is, a lot of our data set though, they don't have a campus, which, is not a good thing. But you can also see how the ERA, pie chart changes. Okay. So let's not do that. Alright. Let's just go back, to our original report and see what other things we wanna do. Obviously, we know how to do a title. We just drag the text on there. Let's really drill down and let's start looking at at this part of the chart. Okay. So, of course, we have our zoom in and zoom out, but you can use your mouse wheel to do that. So you typically don't need that. But if I use this pointer, can start pointing at these points and and selecting them. And when I do, notice everything over here is changing. So it's obviously that these people are members and they go to the main campus. So and that jives with the data we see in the tooltip. Now, of these things in the tooltip, you might say, well, I didn't I don't really the way that's formatted. You can go in here and you can, kind of rename these so that it looks a little bit nicer. So instead of family title all being one big word, you can put a space in there. So there's a ton of different ways that you can format that. The other option is to kind of do this multi select. So let's do that. And I'm just gonna say, hey, these three people. And notice how my chart is dynamically updating. So I have some attendees and some members there. And that's really, really cool. But we're just getting started. There's a ton of other things you can do with this with this map. And the way you find that is you click on these three little dots and say edit. Okay. Then we kind of go into this map editor. Now, one of the first things you might look at, and we'll look at each one of these different tabs because there's some really cool stuff in here. You might say, let's look at the base map and you can be kind of choose what color you want. Now, there's not a ton of options in here. You might choose a darker color, or some of these more colored ones. These tend to be a little overwhelming. And so, while I first saw this, I was , wow, I wanna try some of these other maps. I always tend to come back to this gray one. It just it just works better. But again, you can kinda pick and choose what you want. The location type. So this is gonna say you want points, and you can put boundaries if we have geofences in your data. I don't think any of the stuff in the template would have that. But if you want to try to do a custom thing, you could probably put, some geofences in there. The map map theme is pretty nice. So one of the things we're looking at here is this location only, but you can very quick very quickly turn this into a heat map. And again, our dataset probably not the greatest for a heat map, but your datasets, will probably be a lot, better for that. Another one that's kind of interesting, but I'm not sure how well used it would be, is this clustering. So it basically, as you get as you zoom in, it kinda just makes these clusters and tells you how many pins are in there. This is really helpful if you're looking kind of at a world view or a country view, but a city view. It's not as helpful but something to look at. Okay. So if we look at our symbol style. Now this is where you can start looking at how your symbols, the size and shape of them. So you might prefer diamonds, you might prefer squares. And if you'd , you can actually make them different colors. Okay? So over here, we have a property of color. And so if I take connection status and drag it into the colors, as you can see, it gives you different colors. Now, over here, I can actually kind of customize what those colors are. Okay. So if I didn't want members to be that color, I want them to be a different color purple. Well, it's kind of already used. So let's see what what we could do the black one if we wanted to. Allows you to kind of customize those colors. Actually, kind of the defaults. So let's keep that. And there's tons of different ways of doing this. I mean, they have kind of pre scoped out, gradients for you, but you can kind of play with that. I did, in at at the time of of doing this video, there really wasn't a way of putting a legend out here. They basically said, hey, if you want a legend, come inside here and you'll see it right here. Another way of doing it is just remember as you roll over them, it it does tell you what the different connection statuses are. So, I'm pretty sure they'll probably fix that at some point. Pins are a way of of adding specific pins hard coded to your map. So this might be a great way if you wanted to add your campuses in. At the time of doing this video, again, you could only really have one data set per map. So you can't really overlay a campus data set on top of the family data set, at this point. Drive time is pretty cool. It allows you to kind of figure out, drive times with with your dataset. So kind of pick that and say, hey, want a five time a five minute drive time, and it'll show you that. Okay. That's pretty pretty cool. Now we get to some really interesting things, reference layers. So this dataset in this in this component, this visualization is actually not a Microsoft visualization. It comes from a company called Esri, and they are the leading provider of GIS tools, geographical information systems tools in the world. And so they are gonna give you all these other data sets that you can look at. So if I wanted to apply the 2016 USA median age to my map, I can. And that's gonna kinda shade it all in there for me, which is really cool. So let's go ahead and play with this a little bit more and then we'll come back and continue going through this. Okay. So I can still come in here and I can still pick my squares, but I can also pick these areas now. Okay. So I can come in here and read about, what the median age in that little area is. And so that was, 33.5. And The US median age is 38. So this is slightly younger than the median. Another cool thing I can do with that, now that I have these different shapes on my map, I have another option here which is basically to, select locations using these shapes. So if I come in here and I pick say this one, see how it filtered all of my data. Okay. So I can actually use this instead of drawing these out and trying to see who falls within that shape. I can just select it and say, okay, these three people fall in within that shape. It's another really cool, visualization. And then once we go ahead and click off that, we should get back to our full visualization. Okay. So let's pop back in to our map and see some other options. Okay. So we left off on reference layer. Just realize there's there's quite a few reference layers in here based on population growth, densities, household sizes, ages, diversities, and income. So a lot of cool things you can do within there. If I go to the next tab, it kind of even gets richer. So at this point, I can start putting these infographics onto my map. So if I wanted to see kind of the median age, notice it's putting these little cards down the side of my map. Maybe population by gender, the diversity index, and maybe the children 14. Okay. So now I have these info cards. And if I again go back out of here, as I select these different areas, let me just these metrics are gonna change. Okay. So I picked that one little track right there and now the info cards here are gonna show me just the results for that one little area. I believe the same thing would work too if I just made my own shape. Okay? So it's gonna kind of try to calculate based on the shape I dragged out and selected. It's gonna give me this information. So this is really great, ways of of taking this data that's available from the outside and and combining it with the data on the inside. And so this makes some for some pretty rich, reporting. Now, in our in our template, we did this for families and individuals. I'm not gonna go over the individual because it's basically the same exact thing using the dim person current, dimension instead. And and it's just really kind of redundant to how we just did the family. So I think you can kind of figure that out. But this is a very powerful and a very rich way of creating mapping reports, using Power BI and the data in Rock.