Microsoft Power BI: Using the Power platform to radically change your business - BRK2016
-, yo. What's. Up I'm Adam and I'm Patrick we are going to you and. We. Are p.m.. And. We, are p.m.. On the power bi product team but. There's a cat's involved, it's cats involved I don't like cats I like dogs okay. Let. Me bring my dog I got a Dalmatian coming, today. I'm. Excited a little puppy. Yeah. See. No. You want my dog 200 pound one-year-old Great Dane bigger. Than I am yeah, he's, awesome he slobbers on my head it's great yeah. So. We work for Microsoft we. Work, on the customer, advisory team for, par bi yep and so what what is this cat team what is it where describes were a team of elite ninjas that drop in and fix, things right so we work with large customers, someone asked me I think yesterday a day before is like yeah, so how big does a customer have to get to like work with you guys and I'm like well like we do promos for customers we work with so think like Adobe, BP, those types of companies yeah, yep. It's. Pretty cool it's fun and and this talk is actually based, on a customer, engagement, that we had yep it, was very a very specific scenario. That, they wanted to involve you get into it well you, may think that we're making this like we just made this up for the talk this was a real thing real. Thing absolute. Real thing so this is not made. Up and, we're, gonna we're gonna talk about a lot of stuff Adam. Makes up a lot of stuff this is not made up all. Right all this stuff this is what we're going through today boom get ready cool 60 minutes wait wait oh yeah we have power apps on there yeah yeah. So. It's bananas. Bananas. I, don't, like this clicker all, right there we go so what's the power platform tell, me about the power platform, it's it's, a collection, power, bi was first RBI, RBI was first RBI was first and then we got some other things you know we got power apps and then we got this flow thing that doesn't have power in it apparently but it's part, of the parts part of the power platform, right so and you, know empower absinthe and Microsoft, flow those are also connected, to all of its connected underneath to the common data model and.
Uses, Dynamics, as part of that as well so we're trying to bring everything together but the power platform, really enables your business to. Do amazing things from. A low code no code possibility. Right and that's that that's the key right is, that why you name the session radically. Change. It. Up put. It turn it upside down okay all right. So. The challenge, the. Challenge so can. You do. Geographic. Or geo spatial, filters, inside, of power bi. What. Is this complex word you're talking about what, does it mean so can, Barbie I the tech, my, current location I'm. Right here. You. Know I'm right here though okay that's, two different locations okay, camp RB I detect, my current location agree, a vote we're gonna vote, yes. No. No. You guys, answer. One way don't really know power bi I think I think Adams side of the room got a little more votes, that we're correct then what Patrick I honestly think Patrick's, I did, better all. Right okay, so. Yes. Kinda. Sorta, with the mobile app maybe, it, depends, I guess greatest, answer I'd like to give. But. What's the real question what. Is the room what's the real question so that's what I access customer that's, what I asked him I was like they said hey, Patrick, and power bi do geospatial. Fields here and I was like, that's. Kind of broad what. Do you mean what's the real question so let's say you have this traveling, soar director, who, whose, region, is a city let's say Houston. Or Seattle, or Atlanta. Right it's uh we're in Atlanta say Atlanta, Atlanta, or Lindo, or some other city besides Atlanta, alright, and so let's. Say you're that person right then you visit these stores you gotta go visit these stores. And. You'd like to quickly filter. This, report on. Your, mobile app based. On, your. Location you'd, like to filter it so as soon as I open up the mobile app it does all this stuff and the map goes, right. Down to that location can it do that so, now now the question is a little more specific so, let's do the boat again can. Power bi do the mobile app or any facet. Of Harvey I do. That. Yes. They. Don't know which way you want them to go yes I'm sorry yes raise. Your hands. All. Right but it's also easier, if you tell them the answer so they know how to raise their yeah no. Okay. The. Yes is an overwhelming. Yes. Yeah. So. They said I said yes and they, say it show us how to do it because I was so excited because, I read this blog post and, it, says of course power bi can do this so let me show you course it can let me show you. Alright. So. What, you need to do and there's a series of slides in, the, slide deck that walks you through how to do this and I'm, about to show you all those slides and so the only reason I put those lives in there is people often complain when, Adam and I and present because, I like, to call Adam and I know slide presenters, I hate PowerPoint, I like. To slide. All. Right but, if I talk about all this stuff and don't give you any collateral what's, the point no pun intended, what's, the point PowerPoint, yeah anyway, okay so the first thing you need to do is, you. Need to have some data that contains, city, and state in it, in your, model okay, and what. You need to do is for, those particular. Values. You need to categorize, those, and so you can see. Right, I've, categorized, my. Address, my city and my state using. The categories categories. Categorization, feature. Oh that's, a hard word to say it's like it's her iterate. Anyway. So using, those. Using. That property using. That property you must specify it as such that is the first thing you need to do does everybody know how to do that and power bi wait who does not know how to do that in power bi someone's, got it okay so let me show you good.
Old Modeling - yeah so you click on the value. You, go to modeling, in the ribbon and then you see a choice for data category, way up top and then you choose the one you want and it'll put the little globe next. To that value, so that's the first thing you need to do this gives hints to power bi right it's it's it's letting us know what we need to do with that column yep and once. You do that then. The. Next step you need to do is make sure that on that particular table, there's not another city or state you're categorizing, as such there can only be one city and once, they categorized, that way if you have multiples, power, bi it gets confused, and it doesn't know what to do one city to rule them all one city once a to rule them all the next thing you need to do and this is really really. Important, because we're talking about this from a mobile app perspective, we're talking about an iPad. Or an iPhone or, Windows Phone I know they're so popular these days. What. About my blackberry so, true. Story talking. To this customer, I can't make this up this is I cannot make this up true story I'm talking to the customer and there's a slide that talks about the limitations, this works on the Windows Phone and iPad, or Windows Phone and iPad, or Windows Phone and an iPhone ok, and an Android and the customer goes Patrick, what about blackberry, and this is what I say they. Still exist. Yes. Oh sorry does not support blackberries it, does not support, black right. Anymore. So it, does not work on blackberries. It, definitely works on the window phone because I know all you guys have windows phones and. In. Fairness we did not test that because neither one of us have a winters. To. Say I have like four of them in my drawer at home yeah how much dust is on. And. The next thing you need to do is go to view and, you need to optimize that report, for, mobile because, if you don't optimize, it for mobile what's gonna happen when you open it up it's gonna look all funky, and not, show right but, you need to optimize it for mobile so when I open, up my mobile phone it'll, actually show, properly. Does that make sense so. Categorize. It you. Can only have one it. Doesn't work on a blackberry. And. You, must optimize, the report for mobile does that make sense you got it right and all this is in the slide that all this I spent so many hours on this slide day like 15 minutes okay. Until once, you do that you. Publish, your report out and, watch. What's gonna happen you guys have to see this magic, it's so magical, it's, so magical. Don't. Say that please don't say that all, right. Patrick's. Had enough stress between last night this morning. He's. Actually in a much better mood, in this huge. In. A much better mood, in this session than he was in the last one. You. Have to see this I want to show this from a phones. Chile. So. There's a little app that I downloaded, called. Lonely screen and. It's free they. They keep asking me to give them money I. Am. Alone the guy a, lonely. Guy in the queue and so, they. They, keep acting me for money and I keep saying maybe later and so, I. Keep. Telling that and they use the credit card buy for me but he won't do it and then you go to your party on mobile app and, you. Connect to the tenant where you deployed the report so you publish the report out and. I'm gonna go to reports let, me go back to that work find a workspace called Power Platform and I go back to my report and I go geofilter, and this was I was so excited I was sort of this was the first attempt this is the first attempt and so I was on the phone with the customer. Come. On I was on the phone with the customer and, I. Opened, it up and it had all these little dots on the map so imagine a. You. Know a report, consumer, manager, trying to figure out okay, wait a minute. So. Many stores I gotta go to but I told him I told this guy was like your store is 67. He's like 67, wait a minute and he's scrolling through this list. He's like I'm scrolling he, goes oh 67.
Right. 66. He's gotta have some very precise fingers like me and. So I click, it and there's a little drill through capability, and you can drill and you can see your store details, I was like babe I was, like. Drop. The mic. And. So this is what they say this is what they say I'm so proud of myself. They. Said that's, cute, Patrick. That's. Cute, little. Toy that you created for us but. There's. A global, company it's a global, company it's a big company. Hundreds. Of stores in. The. City and you, expect my manager. To. Scroll up and down. Remember. Memorize store numbers, and. Find it and then Patrick's sitting there going like yeah, yeah. Right. Switch back but. I said but wait I said, wait wait guys wait. Look, at this so. If you pull down on the mobile app and you click the ellipsis there's a little option there, at the bottom that says geofilter. So. If I click geofilter. You can say hey all locations. Are only. Giorgia or only, in atlanta and so I at Lana wasn't in my list right there let's see. And. So yeah, I didn't show Atlanta, it's got a refresh and so it picks up the city and state that's, in that model and I can filter down just to the city and they were like Patrick, the city, it's. Hundreds of stores in the city this is your geo filter, this, is the Geo filter, that's built into power, bi mobile, it picks, up the city and the state and that's it so. Imagine, if you're a mobile employee. And, you show up in Seattle, or Houston, right. And there's, hundreds of stores and I choose Houston, and all those little dots are there what do you do how, do you find the one that's coaching and zooming in Patrick. Oh Patrick, okay. And I tell them you're at store 67, your story scroll down find, 67, and drill they were like that's not gonna work Patrick that's not gonna work also Patrick doesn't scale Patrick, doesn't scale it's only one Patrick right, and so, I said. Okay. You got me you, got me it. Doesn't work they said well how do we capture. My. Location. How do we find where I am I want the longitude, and latitude of, where my feet are right now or at least really close to it so I thought about it I honestly. Thought about this yeah, yeah I thought and I thought and I thought and I thought I went to the shower so, I like take showers, that's where I think my best thinking and as in the shower, there's. Many showers in this story but I was like holy smokes. Microsoft. Flew come you guys are gonna flow. All. Right how many use flow. Is. There anyone, here, I need, to say this right now because I may have fin some flow people is. There anyone, here from the flow team, the, product team the, product, team no. We're okay great we're good. Perfect. Perfect. Perfect, are they recording this. Not. That you know okay perfect. Perfect perfect, I can say whatever I want to say so I called, my buddy up there's I have a friend of mine his name is John the best ylvis'. You guys never come on he's the Pacific, Northwest, flow something, something like that on Twitter oh. He changed okay so she's not that, good of a friend of mine. Handle. I called, him up and I was like John I need a way to capture. My longitude. Latitude he's like use a flow button it's, like a flow button it's, a flow button right and so, basically you can go into flow. And create. A flow, button that, when you press that button from your mobile device as long as you have location, services, enabled, it will, capture lots. Of details, about your geo city. State, longitude. Latitude all. This banana's information, I'm thinking to myself I'm about to turn geo services, off. Right. And. So what happened, was let. Me show you this so. You go out to. Flow. That Microsoft, comm I.
Thought. I already had this open my apologies. This. Is gonna be slow. Slow. Flow get, signed in because I have to connect to my I have to use my hotspot on my phone because they block the lonely app here I wonder. Why. And. So I created this flow, button. Let. Me see I have to show you this and basically. Uses. This is amazing it's amazing so you create, this button when you create the flow or search search box pops, up you type button and it, appeared I'm writing, this to a sequencer, I'm writing this to a sequel server I'm writing, this to a sequel server and so I wrote a crud stored procedure that basically says it captures my email address also I'm not sure how it does that but he kept using my email address cabbage. My email address and I, wrote a stored procedure that says if this email address exists, in the table already just, update the row right so update my longitude, and latitude if, it does not exist just insert, a new row make sense right just just across smart because I just want to keep one row for that particular person and so, I wrote this flow and then I connected, it up and. You. Can see here. I call set my location store procedure and this is just a subset of the fields, that that button will capture in, whenever. I press the flow button does that make sense right, so the end user does not know anything, about this so the first time we did this presentation, at 6 out of the Atlanta, I need to say this I need to say this before I go on there was a lady sitting like right, there and, she goes what. This. Is so complicated. And users would never, do this they, are not building any of this stuff okay and. Users in that building they're pastoring, building Patrick's. Building this right the, end user the consumer of, this is just gonna click the button look at the report and do some other stuff you see the old solution. I. Was talking about creating, the flow right, in this yeah she said the clicking the button clicking the button clicking. The button it was too difficult for her users that's what she said they can not click they can't tap things right they don't know how to tap stuff all right anyway, so, I. Created. My flow button with my store procedure so whenever I press, that button it captures, my current location does, that make sense so your data is flowing, today. My. Data is flowing to. Sequel server using, flow, right.
And So now whenever, they walk up whenever they parked their car in the parking garage they, just open their phone they, press the button it writes, the data to flow I mean to a single server so let me show you something let me show you something I was so excited can. We can we share my screen again I. Think. The lonely screen stopped though so, let's connect to the lonely screen here. Cuz. I got lonely lonely. So check this out, and it's pretty responsive, too so. When. You go to flow. Where. It's flow at on this phone. If, you guys see you tell me to stop there we go so. There's blow, on. So I install flow on the phone and you can see where it says button and I only have a single button if you have mold on, the bottom right buttons down on the bottom all the way down here right, here you see that right, so. You press buttons and then you see all the buttons that you have access to but. I wanted to get fancy because that's what I do right. I don't like anything that's too simple this was too easy so I started looking around on my phone and there's a widget. There's. A widget for, this flow. I'm. Sure BlackBerry's, have rigid widget blackberries have widgets so if you go over here you, can. See right. There's my flow button, and so, I pressed F I added the widget and I pressed that button and. Guess what happened I'm gonna press the button when I press this button here there. We go you can see it running green I was excited I'm on the call with the customer, it, went green I was like yo that's. What I do I, capture, location, smash stuff, I'm the master of this I'm, gonna leave this off and what, you can do is you can go to your flow and you can look at your flow history, and. Because. What we saw was. It. Failed I'm, on the phone with the customer I'm on the team's not. What Patrick likes to see one. Of the team's call you know I got this big ego I'm so excited about stuff. I'm doing I'm like yup and then, it failed I'm running a query on the sequel serving the locations not update it's got my home address in it and like oh these people are gonna hunt me down. What. Happened, well remember. I mentioned location, services before and this is where I kind of take a dig on the flow team cuz I actually send an email to a couple of people I'm like hey this. Widget it's not working, it's saying it can't capture my location I'm getting the location services there oh no Patrick you should totally work I said it's not working look at my flow. Obviously. You must be doing something wrong I say. That's. Not possible. So, what are you missing, what. Do you mean so the, guy says well Patrick this is what you need to do I say okay I'm paying attention to him because, I want you to unlock your phone I want. You to go back to flow and I, want you to use the flow button in flow. I'll. Go okay, but. Wait I don't want to use the flow button in flow I want, to use the widget. It's. Easier to access I can pin it he, goes yeah, don't use that right now I. Say. But that doesn't solve my problem, it, doesn't work he goes under no no no yeah we know it doesn't work I said it worked. So. Anyway long conversation, the, widget. Does not pick up location, services yeah and they know and they're trying to fix it so use the button in flow, to work and now if we go back now if this doesn't work I don't know it's not working so and I think I don't know if you explored this a little bit yeah I think you can also save, the button, to like an icon on, like.
You're Me I didn't, try that okay I think that may be a possibility and, so now you can see me shaking. Somebody. Say yes you could do that yes, okay, and so now you can clearly see that it succeeded, and, it captured, my current, location that, I'm at right now that made for a happy Patrick I was, so happy. So. You. Guys following me yes. This. Overly, complicated, for us easy, right it's a piece of cake we're not gonna show you the store procedure no I'm not gonna show you the store procedure, that is proprietary. Took. Me 16 days to write that store procedure after, about 14, hours that's 14 showers that's it 14 showers. Figure. Yeah by the way when the feild happened that's when he went back to the way back to the shower I was stuck I was stuck can you go back to the side so, we got that working I was capturing, it, capturing. All down I'm gonna show you phase 2 in a little bit and so. Once. We got that work and I went back to them and I. Showed them this and they were like but Patrick we got another problem here so I said look this works because it's all imported, everything, was an import, mode in power bi does, everybody know what that means import, mode and power bi so, everything was cashed up into, my data model right which means you have to refresh it which means I have to refresh, that data model, now for one single, person that. Works. What, what, would happen if everyone in this room tried to refresh the same dataset who. Wins whose data gets into the data model I win you win we. Don't know right, who. May be the first person that kicks the Refresh off but. Then if it finishes and somebody else does it again it never stops refreshing, it can be an endless cycle, or endless loop right so the error that says hey someone else is refreshing right yeah that's right so they, say Patrick how, do you solve that problem, and so I said well you can, use flow you, can address the API you can use that and they say but for one person so you can trigger the Refresh with these options one person, can do this but. Again. That's, cute. Patrick. That's, so cute we, have hundreds, of stores in a single, city you think we have one director for. The whole country. No, this. Is what we have but and, you just. Required, you to do it take another shower I took, several showers, during this process because, I had to think about this right so I have a problem I have a huge, problem and I was stuck and that's when I went back to the shower and.
I Started washing up and I'm trying to figure out how do I solve this problem, how do i how, do i mitigate, this because there's all of these people are sales directors, that's traveling about the unite the world so. How do we make sure that the proper data is getting into, the model you, guys have ideas, think. About it just think about it I want you to just, yeah. This. Is my presentation. Yeah. Alright keep going and, so. The third attempt came and we started talking about some really complex. Things right not, that complex. I. Mean in here I've heard of composite. Models I'm trying, to catch myself because I hear people is composite, composite, composite, confident, with them. Alright, so what we did is this, concept, of direct query and import and I know the screen you can't see me, this. Concept, of directed important. Direct query before composite. Models all if we did import, we could only do import if we, did direct query we can only do direct query but the beauty of composite. Models is that, we can do both now together, we can live in harmony and. We like that right yes and it just works magically, there's no issues ever with, this. But. You. Know there. Are some implications we got to think about right so, because. We're doing direct query and import, certain. Trace logs that you may do so if you're doing like profiler, traces or things of that nature it. Could reveal some information maybe, you don't want that to happen there's performance. Also direct query is, typical. People come up to me and they're like. Adam. I got this performance issue I'm. Like okay let, me guess using direct query how did you know. We. Get a few calls on so. It's it's really bound, by the performance, of your back-end so sequel, server or whatever you're going to. I know everyone in here use a sequel server so that's ok the number one relation oh. Wait we're using access number, two accessing, the battery. So. But typically, people's, relational. Databases aren't, set up in a way that's performant, for direct. Query but also even. If it is if there's a lot of data or whatnot it's going to be a slower response, than what you would get with imports so that's something to consider about you. Can't use live connections, with composite, models so it doesn't, exist yet it's everyone. Asks for it hopefully it'll come down the road but I can do it in paginating reports. I'm. Just saying. There's. Also limitations with direct queries so it doesn't support all the capabilities, of Dax are you limited like time intelligence functions and so you may get some issues, with, using direct query the the bottom line of this is if you go this route make sure you test it right test it don't, just throw it out there and say oh you, know don't be Patrick throw it out there oh I'm a brilliant, don't, do that, you gotta test it test, it before you throw it out there all. Right so. This was funny this is my challenge right this was my challenge they say Patrick we, need this in real time do you need it real whoa whoa. Whoa whoa whoa whoa so this is how this played out this is really obvious player how many guys bought a car before right. The worst experience, in the world car behind it's the worst experience in the world so I'm gonna be this car salesman, because that's what I do right I'm buying the cars guys buying a car right, this how it works I know how this works but I know what I want so this guy comes up to me and says real time I want real time real dough okay hang on one second let me go talk to my manager my. Manager about this and I leave for like 30. Minutes an hour I leave them sitting in the office cuz I gotta go talk to my manager. Take it right I'm. Gonna go have some coffee maybe, catch up on a little YouTube you. Know watch some Netflix and I go back and I go okay. What. About an hour no. Need. Real-time did you not listen to okay okay I'm gonna go talk to my manager so I go talk to my manager again and I've. Talked with him I talked with him I have, coffee i watch, netflix and, i make him wait an hour and a half because that's how the car deal works it and I go back okay okay okay this. Is the best I can do, I. Don't. Think you're hearing me but that's. The best I can do 30, minutes, right. And, so one. Thing I talked about somebody to somebody about this before don't give in to the real-time belief right and, the. Right query I mean composite models help us it helps it helps it gets past that because I can do this.
Delusion. That's right that, I'm giving, into you but I'm not really giving in right and so. And. So with. The right query they expose these storage modes yeah yeah, and so what, we did was we slightly. Modified. The model, a little bit just a little bit just, a little bit just a little bit and so let me show that before we go to this very, fancy oh yeah. All right until. On the on the little apps wrong one I hit. Your button on. The left you can see the original model and so it's just a traditional. Model, everything's, imported, you can see I have the table with the store, sales. On. The, right over here, and then I have my stores table, and then down here I have my my location table it's kind of a little snowflake, right the store sells is my fact then I have one dimension off of it then have another dimension. They kind of snowflake, it filters all the way up all right but that brings me back to the original problem is I have to refresh, this model, every, time someone goes to a store how. Do I get that model refreshed, because we want it real-time because we want it real-time but I'm not giving it to you real-time no, I got to talk to my manager about that okay what we did was we. Import, it the. Store sales table, and this may seem strange to, a lot of people but we imported, the store sales table because the store sales table, may. Be bigger and that's the table that you probably would traditionally directquery. Because. It probably have more data than, the my location table, okay but what I did was really like your factory right the store sales is more like my fact table and you traditionally. Would put your dimension. And maybe dual-mode, you'll probably put in a dual mode if you guys want to know about more of our storage modes and stuff come, come over to axe experts after this and we can chat about it and, then I wrote a view I created. A view because if you use that read query, way. And I left them out of the way it is direct, query limits. Dax functions, that you can use and one of the functions that's limited is related. Because, I needed to use related to get my longitude, and latitude, my current, longitude and latitude so, I can create, a Dax expression, to, look at the stores longitude, latitude to. Calculate, miles, write. The distance I am from the stores and you'll see we're using math so I do some math write some serious math some serious math and, so I created a view that, actually join my, location, to. My stores because, fortunately, they had an email address on their stores table, and I had an email address on my my location table so I created a view that joined them together and every, store that I that, I had access to also. Had my location on it and I was able to write. A Dax expression, that created, how far I am from that store and miles does it make sense so. Let's show him the Dax is very beautiful, yeah show me the access person sure. Yeah I'm a genius. And. So, I, wrote, this just, not right yeah, I took a shower and after, I got at the shower it came to me in a vision during the shower we actually, we do because we're not lazy we're very efficient, we can copy it and paste it no I one-handed, this on my keyboard I wrote it just like that control, egg. So. Please don't ask me about the math actually someone, on our team his name is Phil see Mark wrote a blog post about this I quickly. Copied, and pasted it in and, I replace his columns, with my longitude, latitude columns, in the blog he also said don't ask me about that, and. He did it in kilometres because he lives in news. Right. And I we live in the United States and a, mouse the first time I did this presentation I said don't ask me about the math this lady came up afterwards, and she said she about to explain this math to you. Right. And she proceeded, to explain about, the circumference, of the earth thing cosine and everything else and I was like wait wait wait one second what was ticket I'm.
Gonna Have a beer now okay. It, was bananas I was like I write Dax don't, do math okay, so we, got that done and we imported, it and, I. Was, able to create a I, had a column, in my table that expressed, the distance, I was from every store and then I used the little dots to, create a range. Of, you. Know if it's this and that show one to two if it's less than this less than one you guys get it right some simple Dax to, do that and then I created a slicer, out of it that I put on the report, are. You guys following me now lose anyone. You. Father was good all right and so, the, golden question the golden question posit, models. Does. It require premium, show of hands does composite, models require premium, yes yes. Smart. Wait. They already upload the slides and they. Know. So. I want, you to do me a favor we're, gonna go over to actually experts I want you to ask me about the time I presented this in Atlanta and, I let Adam Sachs and put the slides together for me and actually about the Jif that he put if whatever you call it that, he put into this slide deck so pop. Just asked me about okay actually, buy, me an axe me bodies are awful awful things I still have I still have all right let's go alright so. Let me show you so, we made a quick change to the model and this. Is what we ended up with this is the architecture that we ended up with I'm sure I think they uploaded these slides or somewhere you guys can get a copy of these slides if you want these demos I think Adam does have a drive link, somewhere, somewhere, that, he can you can get a copy of all these demos and stuff like that okay alright, so. Let me show you right so here's the. There. You go here, we go so. Let me show you a few things that we did over in this model. Just. So you can kind of understand, so we created, the. Mouse, expression. I. Guess. That won't work. I'll. Zoom into it. So. We've created the miles expression, with some really, nice math, with, cosign and, stuff like that, and variables. Used very yes use variables, and then, I created the mile range which is really simple really. Simple probably. Could have done this a little more elegantly, and. So I will say this and the lady made this point in the session when I did this she's like so I have to do this no you can create a table you can persist this to your own table, you know so you can have all this seven you can do this calculations, back on the sequel server or whatever. There's. More than one way to do this I did it here because this is what I do the axe right this is what I do but. I also added, a sort, on this because. If you don't, add. A sort, to it, it's. Just gonna show up in a wonky order yeah, wonky and, so I wanted you know less than one I want to be in a specific order and so then I have my range slicer, on this report, and it, captures, you know my current location so, you can see right here this. Is the locations, in teneal Olympic Park it's pretty close I'm pretty close to Centennial, Olympic Park you, guys walk through it a couple of times I'm sure I'll. Be weird if it showed your house right okay so my house right there because I'm not there anymore and so let me show you how this work from a. From. My phone perspective, I thought my phone died. Lonely. Died everything, died. Here. We go. Quickly. Now I've noticed I keep telling, Adam to send, them money and so, we'll go back to my power bi. And. We'll go back to the other report, here. The.
Geofilter. Composite, and, again you guys can have a copy of all this stuff and so, now, what I have the ability to do you can see right there so, look if you look at the bottom remember in the other report it said Centennial. Park but, if you look now because, I pressed the button that's. A direct query it actually updated, my location I meant, to do that I meant to do that and so if I click my range slicer, and choose less than one mile now. It, limited its down to a more, precise, list. Of stores, right. And so I'm gonna click on that particular store, yep, and then, hold it and drill through and. Then. I can see the details so, I was like, yeah, what. You got now customer. Mr. customer, but, Patrick. That's. Cute. We. Got it we got a problem. We. Got to adjust some data here my managers got to do some work and I said wait a minute that wasn't in the original scope, of, this project. No, no sir you work for Microsoft you're. Not in MCS. Here. Not a consultant, you work for the cat team you, don't pay we don't pay you you're free I was like. Interesting. Okay this. Is what they said they said hey we need to have. The ability when. I'm sitting with a manager, I've looked at the map I've looked at their information I'm sitting with Stephanie, who's, in the front row and I'm talking, to Stephanie I'm gonna pick on Stephanie a little bit I'm sitting with Stephanie, and I'm in her store, chatting, with her about her quota and things, like that stuff she's, new to sales somehow, she, got a job as a manager without ever working in any type of retail store before because she's wonderful, she's really, smart and. Seven. Got this job and I'm chatting with her and she's I really, like this job I'm a value asset to your organization. And I would really like to keep my job is there anything we can do with the quota and I go of course Stephanie, because we just hired you I know, your dad and so, we. Got, your job school. And, out. Right out of school and so of course we'll adjust your quota so you can keep your job for the next fiscal year, okay, and so, I. Was like wait a minute wait, a minute time for another shower so I went back and I started taking the shower I was washing up because is there can. You write back and don't answer it if you know the answer. Can. You write that to your data model and have, your report refresh, in. Power, bi I know, you, know tell. Me yes. Yes, yes. Yes. You can't with power apps how many you guys use power apps. How. Many you guys think it's really easy to create a power app, we. Need to talk after you set. It up you. Must be on the power apps team okay let me ask you this question yeah, there. Anyone, the power app seen here. Okay. Good. All right okay, we'll talk candidly about, power apps now okay, and so there's some limitations you cannot, want, you. When. You start the building process of this power app you cannot do this in the desktop the report must be published to the service should, create your report in the way you want it first we just wanted to look then publish, then you publish it to power bi then. You, add the custom visual, it's a custom visual the individual, section you add the custom visual and then, you configure, and build your report I will tell you this Adam, has been on me for weeks to do two videos on, how to do this I promise, you in the next two weeks I will have video showing you how to configure and set up, a power app and do right back using power apps in the next, week the next week cuz he's lazy no, all. Right yes because he's traveling cuz he's traveling so you will see that so if. You, change the data fields, that you are using that power app you, have to go into the report in the service there's a little ellipsis, in the top right click edit to integrate those new fields that you want to use if, you want to trigger refresh, and do right back the, table, that you're referencing must, be direct, query, okay.
Power, App the, power app itself needs, to be shared separately, so if I share the power bi report with you I still need to share the power app with you that's. Why I put all this stuff in slides that's why I put it here right because it's hard to memorize and I don't I'm still reading the side all right this is not supported, in power bi report, server power, bi mobile app or the famous Internet Explorer. You. Were just saying I need to use the mobile app to do all this stuff but you're saying it's not supports not supporting them all web remember you're not paying attention never pay attention when I talk you never pays attention alright this. Is when I'm sitting in the store I have my laptop open and I'm now I'm looking, at the report on my. Laptop. I'm, no longer on my mobile device, I'm. Not yes I am on a very very fine surface. Book okay alright alright next, slide what's demo okay. Okay. For once so. Maybe I will show you how it works so, let me show you first. The, report that I created. Such a beautiful it's a such a beautiful report so I was afraid I wasn't wasn't gonna be able to show this report because I didn't know when the desktop release, would be dropped and everything available to, everyone, but the June release is out so I can show you everyone's, seen it all the fancy you guys have seen I didn't go to the keynote I wasn't able to go to keynote I was a taxi expert so I couldn't see it but. Hang on a little second this opens anybody have any questions while. This opens up yes, sir question. Sure. Sure. So, what I did was and when I instead. Of having, two separate tables for the related I created, a view both, I, just flat I'd be normalized I do you normalize good question so the question was how did I overcome the limitations, of direct query when, you're going across table so I just flatten the table a little bit alright any, other questions all. Right oh yes way in the back. Yes. So, the question is, the. Person that is using the power app do, they need access right, access, to the database and the, answer. Is right. Access right. The. Developer absolutely, needs it and so the connection string that they bill as long as that account they specify, in the power app has access to right back to the database it, should work okay. Unless you're using some type of Windows or something or Azure ad are thin that person the logged in person will need it all right okay, so check this out I built my report, can. They see the screen yeah I'm. Looking at it right okay all right I don't. Know why he's prompting me to log in hey if you see it here they can see it probably. Me the log I, kind, of tell them everything. All. Right here we go no, it wants me to sign e and I don't want. Hit. Escape I did that I would do it again. Don't. See, it's. A making me Sonny let me. All. Right so, what, I did was I, created, a. Gauge, and, I used a new conditional, formatting feature to look at the quota right, so if they made their quota it's red if they didn't make their quote I mean if they made the quota it's green if they didn't make their quota it's red but what I did was I created a. Little. KPI. Color, for. Dax expression, to. Represent the, colors that I wanted, the. The. Card because now you can you, know change the color of the font, the color of the text in the car conditional, formatting using conditional formatting so I created a measure and I specify, the values, that I want and then for either the car or, the. The. Gauge here, if, you go to the, formatter and you, choose data colors and you hover over, it and I'm, going to go to conditional formatting better do that again you guys came in on that okay, I'm with fast yeah well the it's hard to see okay ready. So. If I hover over it you'll see this little vertical, these, little vertical three dots right there right. Click those and choose conditional, formatting you'll. See it's, by default it's gonna say color scale I select, field value, and then. I specify. I select, my. That. That KPI color measure that I created and then it'll automatically. Use those colors based, on the result of that value, does that make sense and I did a video on this I did a video on how to use this it's already out there on on the, YouTube somewhere, okay. Yeah. And then. I published a report when, I published the report the power app was not there because remember I said you cannot, bring.
The You can't use the power app custom visual in the desktop you only can do this in the service but you can download the report and reupload. The report and the power app will still work I, tried. It like several times cuz I remember somebody asked me that the first time I did it I was like I don't know if that'll work it will absolutely work, you can download the PPI X and you can upload the ppi X and the functionality still, works just fine alright so, once I did that I. Went. Up to my report so. I'm the manager, now I'm sitting here talking to Stephanie and. I'm. On my new report. Here. And I gave the manager a search, box here so they can search the store 74 if I, click store 74, you. Can clearly see, our. Fiscal years in the net the end of the month and Stephanie. Is not gonna sell three million dollars worth of widgets in the, next you know twenty days right. And that's not working days that's including are working on Saturdays and Sundays so, she's clear they're not gonna work it break it right but if you notice right if I uncheck this, the. Power. App adjusts, based on my selection in the report right so this is depends on your data model how you have your data model set up so I'm gonna choose this store I'm. Gonna edit, it's. Time to set Stephanie's, quota right, now her current sales at twenty three million I want to make sure she, makes her quota I'm gonna do everything I can to make sure she makes her data, quality, writer right. There. Just. This. Is this is it just for you you, did too, that's right so I'm making sure she makes her quota. I'm. Gonna set your quota at $2, 70, right no you can set it whatever you want, there. Is a function, for you guys that are new to power apps like me, the variant I show this I'm going to show this because I have time the, very first time I did this I actually, did, some typical, Microsoft. Spoken smoke and mirrors in Atlanta I will admit it was absolutely, smoke and mirrors there was also a shower and three. Showers involved, in this one the, very first time I did this when you set up your power app and you click Save it just inserts a new row by default okay. There's. A function called patch. That. You can use if you use patch, you, can find the row you. Can you just need to identify the row and in my case it's the store ID and then it'll do an update for, you using this patch function, okay if you don't use patches insert, new rows insert new rows so, I wrote this T sequel with having and grew by so, it no matter how many rows it inserted always pick the last one right it, was smoke, and mirrors but I fixed that and so if I press check. Good job thank. You so much and if, we wait blame, it, wrote back to the job Stephanie. Alright. It. Wrote back to the database Stephanie's, gonna keep her job cuz she's 23 million dollars over her quota.
She's. Like two million, percent, over so she's gonna get insane, bonus. Now but Patrick that, was how, did it up yeah, so the way it works is let me show you that this is a good question Adam, let me show of course it's a good question. Power. Apps that, Microsoft. No. Let. Me show you this. So. If you go to power apps get. Signed in I didn't. Think I would have time to show this no but you're, all happy yeah I am I'm so happy to show you guys this. So. Here's my power app right here and I'm. Not, the. Greatest, obviously. You guys can see power. Apps developer, oh my gosh is so beautiful, that's, okay that's okay single, and let me go back and. Go and edit because I want to show you one thing I just want to show you the code and not actually the data here. Ever. I said before low, code or no yeah sometimes, there's a little code and so the trick the trick to get in the power app to refresh first is that the table that you're referencing must, be in direct query mode, all right that's, why we hints, hints. The composite. Model because we didn't want everything, to be in direct. Query mode and I'll say this about composite models, you still need to maintain the cache for, the tables that are in dual or import mode so you still need to schedule a refresh to that data model just because one table is in direct query mode doesn't mean it's gonna automatically, automagically. Refresh. The cache or dual modes does that make sense so, you still need to schedule a refresh and so, what, the key to all of this is on this, little guy right here if I go to my edit. Screen, and the, event, on that check box. Basically. When, you do the reason, I think you, do the integration from, the. From. Power bi service it's because it creates this object called, power bi, integration. This object exposes, those fields, that's, available to. The, power app from power bi but, they expose a function, called dot refresh, that. Dot refresh in what it's what, actually, instantiates. The refresh it. Triggers the really triggers the Refresh of my, mobile report and when the when that report, refreshes. It's gonna trigger the direct query item. And then so goes out to get the data pulls it back in magic, gets it and there's the patch function, that I was talking about and so basically I'm saying give me the first filtered. Store ID and there's, a gallery and you could watch my video where I explain this, the selective run from that gallery and it'll do the update of the quota in the comment using that patch function, and when I click update it, just runs so this is this is how it works for the report consumer, seems like a lot of steps that we went through to get this work but this was a ginormous. Yes I said a giant normos customer, that I was working with and there was no option of failure the, the end user the report consumer, whoever this is the director, opens, their phone presses. The flow app write. The flow button opens. The power bi report, it almost, happens instantaneously, they're, actually doing a pilot of it right now and they look at it they see everything, they need to see they do the drill they set the range to limit the stores they do the drill to, see the details of that store they walk in the store pop open their wonderful surface, book they, look at the report they reset the quota I really, generalized.
This Some it wasn't just the quota that they said but they said the quota and then everything updates and refresh and we solved their problem so go back to the report yep in in, par BR bi desktop or. Yeah, so so. The items the green items the gauge and then your little KPI, cards yes those are going against a rect query yes these two guys right here are going against other items are going against the imported, cash piece yes yes, yes, all, right, any, questions. Yes. Over here on the Left I. Can't. Hear ya. How. Did you get the power up to update after the selection. Yeah. So it, that, the power every time I select something in that slicer and then, the power app updates because, of the way I built the model right there's a one to many relationship between the store that. View that table, that view that I created and the, fact sales and it just flows it that way so they're looking at the same thing they're looking at the exact same thing if I wanted to go both ways I could have said their. Relationship. To bi-directional, but I probably wouldn't do that I would use user relationship, across field to the bill my my, values to, avoid that bi-directional, relationship, any, other questions. Yes. Yes sir in the front. Who. Has a really, good question that's a really good question and you know. Your. Mic went out. You. Died, well. Not you didn't die. The. Perhaps. I. Would say no I would say no I haven't honestly, I haven't tested that but, I'm gonna say no because the way you have to turn on historical, analysis, to connect to it and modify it and it's gonna capture that date and also the way that historical, is tracked to it gets a little wonky it's, not update, just to the world yep. I'm gonna say no question. So. I into, the desktop, so. No. Because, of the fact that you wanted them to do that on their device on the phone and the, mobile the report, would the powerup item won't work in the mobile device in the mobile web power apps that won't show up in the mobile app I. Could. But I'm in the report they're looking at the report not. The power. App. So. So, when they go to get their location, they were doing it on the phone. They. Weren't a shortcut it. If. Your. Device, your laptop is using, location services, so. It's predicated on that and the, location, services for the flow buttons is a little. It. Doesn't work that great gellick windows phones windows devices it's very delicate very delicate, and blackberries, yep very delicate, very delicate, on back any other questions, more. Minutes all, right thank you guys so much for hanging out with us today and, we'll see you at the power, hey please.
Remember To fill out your evaluations we. Absolutely love, the feedback and it helps us to make, the presentation, better.