Microsoft 365 Community Community Demos, 15 Sep 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Visio, Power BI Tiles and Reports in Power Apps!

Description

In this 29-minute citizen developer-focused demo, Reza Dorrani demonstrates how to combine capabilities in Visio, Power BI tiles/Reports and in Power Apps to deliver inventory management and desk reservation solutions. Apps are sharing information and contextually aware of each other = fully interactive Power Apps and Visio diagrams inside Power BI reports and Power BI tiles or full Power BI Reports with Visio diagrams and filters inside of Power Apps using an embed URL. This PnP Community demo is taken from the Microsoft Power Apps community call – September 2021, recorded on September 15, 2021.

Demo Presenter: Reza Dorrani (Catapult) | @rezadorrani

Supporting documents:
• Videos – Power Platform Integration - | https://bit.ly/powerintegration
• Sample - Opportunity Analysis sample for Power BI: Take a tour | https://github.com/MicrosoftDocs/powerbi-docs/blob/live/powerbi-docs/create-reports/sample-opportunity-analysis.md
• Documentation – Filter a report using query string parameters in the URL | https://docs.microsoft.com/power-bi/collaborate-share/service-url-filters

Stay connected:
• Twitter https://twitter.com/microsoft365dev
• YouTube https://aka.ms/m365pnp/videos
• Blogs https://aka.ms/m365pnp/community/blog
• Call attention to your great work by using #PowerAppsCC and #PnPWeekly on Twitter.

A

All right so, let's get started, I will go ahead and share my screen and uh you should see my presentation pop up promise. I won't bore you with a lot of slides. It will be content, demos and uh live q. A so. Let's see how this goes so. My demonstration today is about microsoft, vizio power, bi and power apps, so power bi power apps.

A

Of course they are part of the power platform and they can integrate and talk to each other in one of the community calls actually maybe six months back, I demonstrated how you could have a power app residing inside the context of a power bi report and the power app and the power bi are sharing information and contextually aware of each other.

A

In the last two months I came across one very interesting aspect in power bi, and that was around a visual called microsoft vizio, wherein you can bring a vizio diagram inside of power bi.

A

So the moment I learned about that that kind of piqued my interest as to what the possibilities could potentially be and does the vizio diagram uh have any additional capabilities inside of power bi. When I can, I can perform certain actions in that visio diagram.

A

That's where I started investing more time into this and uh that's what this presentation is about. How can you integrate all of these three services together? Of course, power automate can also come in. Virtual agents can also come in. Everything can come in here.

A

So before I deep dive and start showcasing the the two solution, demos that I have prepared. The first thing I want to reiterate is that you can have a power apps visual residing inside of a power bi report. This is possible. The power app will get its data from the power bi data set.

A

You can decide which fields from the power bi data set. You would like to pass over to the power apps power. Apps will get all that data. You can perform crud operations on that data, then directly in power apps by connecting to your backend data source.

A

All of this is possible thanks to the powerapps visual. Now you can also go the other way around. You can have a power bi tile inside of powerapps, and with this option the previously you could only and only add a tile from a dashboard inside power. Apps. Now the advantages there were. Yes, you could get. You know, reporting features or or dashboarding experiences inside of powerapps.

A

Considering how limited the charts feature in powerapps is, but very recently there have been a lot of updates that have been made by the power bi team in this specific feature, and I would like to give a shout out here to to chuck uh charles sterling actually because he brought most of this to my notice and now what is possible with this control, which is still called power bi file. I would I would like this to be renamed. uh You can actually bring in an entire power.

A

Bi report experience, so the full report, along with its filters, tabs uh power, bi interactions, click drill, throughs. Everything is possible now directly inside of power apps, and not just that you can even slice and dice your data inside of power apps. When you bring this report into powerapps, you can add filters to that power. Bi report from data from within powerapps, so all of these things now are possible and there is a new setting or I'll call this a new property related to this specific tile.

A

It's called allow new api, uh which exposes some of these newer capabilities because it plugs into the new apis around power bi. So these are the two ways: power, apps and power. Bi can talk to each other.

A

Then comes the third part here, which is visio now in visio. Of course, I can create visio diagrams and I can add, shapes to my visual diagrams and with this visual you can bring that diagram inside of power. Bi now in visio you can add shapes and those shapes can have information.

A

Now the beauty with this visual is, you can actually map your power, bi data sets to microsoft, visio and, if the shape information, the unique key that you create for each shape if it matches one of the fields in your data set, you can actually have a relation created inside the power bi report, with your data set and microsoft visio, and not just that you can actually do a lot of things with this. You can change the colors of the shapes, you can add information to the shapes.

A

So if you look at the screenshots on the right hand, side, the first one is a desk reservation based system, wherein I have this visio diagram of an office space. I'll be honest. I stole this from the web and in this all we did is we just added a few shapes which are those seats? If you closely observe those seats, have different colors depending upon the sections they are placed in.

A

If you look at the vizio diagram at the bottom there I have an inventory management system that I'm trying to design. So I have my inventory or my storage space and each of those spaces or containers are just shapes in visio, observe how the colors are different. There are certain shapes with green color. There's orange there's red those colors are changing depending upon metadata, so it can create extremely powerful scenarios by leveraging these features of microsoft, visio.

A

So enough of slides, let's get straight to demos.

A

So before we jump into my scenarios, let me first show you a very simple uh example of how you could bring power bi into power apps. So here I have a power bi report, very simple standard power, bi report. Once again you can get the sample directly in power bi, it's called opportunity analysis, so I'm leveraging the sample now in power apps. If you need to bring in power bi, we have a control and that control is under charts.

A

It's called power, bi tile and through this typically, what we can do is select a workspace of our choice. We have to choose a dashboard, so I'm showcasing opportunity, analysis. So I'll pick this, and then you pick one of the tiles that you've pinned to that dashboard. So let's say I pick average revenue, so it gives me average revenue. So there you go. That's a specific tile and a power bi dashboard that I can bring in here.

A

But what, if I want to bring the entire report experience? Here's my report can go to file, go to embed and literally just copy. This url go back to powerapps under data there's. A property here called, of course, style. Url just go ahead and replace this with the url that I captured from there, and if I do this as simple as this, if I just increase the width, let me preview this.

A

This is the full, the full power bi report experience directly inside powerapps with interactions.

A

So, if I was to say, show me all the content where partner driven is yes, if I select that the filter is actually been applied to my power bi report, I can even switch tabs in my power bi report, so you can bring in tiles. You can bring in the entire report experience along with the filters what you can do more hey.

B

Rez, yes, sorry, questions about this one. I see one popping in chat already from lease and she says: do people have access to your app also need access to the power bi dashboard.

A

uh That's that's a good question, so in this case I'm bringing the report. Of course they would need access to that report. This is just a reference to that report, so they would need the access, and I know I'm going to get a lot of questions on licensing. So let me call this out right away. You need to have, of course, the license to access powerapps and the license to access power. Bi, so you'll need both because you're leveraging both of these services.

B

E5 has both of those doesn't it.

A

uh E5, yes, will grant you both again uh in powerapps. It depends. It depends on your connectors, of course, as long as you're using the standard connectors in that scenario, with an e5 license. You should be good with this.

B

Well sure- and another follow-up question here is this- is awesome that you can pull in the whole report now, but I'm wondering about data sources, so you know we just did a really cool thing with power bi. Actually I didn't write the code. Someone on my team did but um austin wrote some code and embedded python in the power bi report and he used that python to query devops wickle api.

B

So we could call and do some visualizations with that, um and so that's how we're reporting on on some devops things with that type of api or any other data source. Are there any limitations to data sources and what you can then embed into the power app.

A

I don't believe there are any limitations here, because you are able to get the entire report experience in here. So if, if you are getting, if, if that is working in the power bi report, I don't see a reason why it would not work in this experience.

B

That's really nice to know that. There's no limitations on that.

A

So, apart from apart from getting the report or the tile, what else can we do inside of powerapps? When I have this power, bi report experience? Well, you can actually apply filters as well. So let me show you one use case here, just one in my sample here I have something known as opportunity size, that's one of my fields. In my data set in power bi, these could be calculated measures as well by the way, so this one has three values: small, medium and large.

A

If I look at my power bi report- and if I look at my data set- and let's say I want to search for upper tunity size- it is in a table called opportunity and the field is called opportunity size now. Can I apply filters to the report experience directly when I come into powerapps. The answer is yes and you can add multiple filters here. All you need to do. Is this filter, so ampersand filter. I believe it's dollar ampersand dollar filter is equal to the name of the table, so opportunity case sensitive.

A

You have to get this right upper. I have to get right to opportunity. uh A space is replaced with underscore x0020 underscore reza. How do you know this? It's all in the documentation, I'll share the link so opportunity size is equal to, let's say small. Let's say I do this right show me everything where the opportunity size is small and with this style selected the power bi tile I mentioned.

A

There's this new property called allow new api, ensure that this property is turned to true right now I have just fired a filter that says get me everything from my uh power bi data set where the opportunity size is small. So now, if I was to preview the app observe how the filter is pre-applied to my power bi report, you can see that it only shows the data where the opportunity size is small and to quickly make this even more dynamic.

A

Nothing stops me from adding a drop down here in power apps with all those values, maybe coming from a data source, a small, medium large. That's my drop down. Let me pick the drop down. This is full powerapps features parity right here. I can just literally do this, which is put double quotes, building it on the fly, taking a risk selected, dot com value.

A

um I think I got something wrong here. I think I got an extra quotes wrong here. um Maybe the code says to come here there you go so, let's preview this now, I'm driving the filter based on my drop down so right now it's showcasing everything where the opportunity is small. Let's change it to medium there. You go right. So I am filtering my power bi report with controls directly inside of powerapps, and you can add multiple filters. You can add ands and ors. You can add all those you can stitch your expressions right here.

A

That is extremely powerful. One more thing you can do. One last thing you can do is: if you look at the tabs at the bottom in power bi, I can have tabs what, if I want to select a specific tab, what if I want to focus on a tab or just pre-select, a specific tab? You can even do that through the filters. I believe there is something called so page name if I'm not mistaken and report section and just give the number so let's say report section two: it should highlight tab number two.

A

You can see it right at the bottom. If I change this to three, if I preview this, it will pre-select tab number three. So I can change the selections. I can define filters. I can bring in the entire power bi report experience and take this to the next level.

B

So reza that's really slick that you're able to combine these two so easily. I noticed that your data type on this filter is a string right. Small, medium and large are those filters limited by which data type you can use.

A

They are so if you look at the documentation and power bi, the field types that are supported right now are strings numbers and dates and times. So these are the only three types right now, as per the documentation that is supported, you can also write a calculated uh column, so you can write dax queries and you can actually filter on those type of fields as well.

A

That is a possibility.

A

Okay, now moving on to my actual demo for today, which is an inventory management solution, I'll first walk through the demo and then we'll look at the different components for this, so inventory management. uh The data source in my scenario here is microsoft: dataverse it could be any data source of your choice and assume I am a fitness uh company. We have our equipments come in. We want to store all those equipments in our storage facility and it's basically an inventory management system.

A

So as an inventory manager, I would like an app that showcases my entire inventory and I can also see my office space and uh you know I can look at the containers in that storage facility. What equipments are kept in those containers? How much space is available is a container filling up xyz.

A

So here in this experience, on the left hand, side is that power bi tile experience in this power bi report, I have two tabs with two visuals. The first one is a facility map. This is the microsoft visio visual inside of power bi. Now there is no vision.

B

You started with just to make it real clear for everybody rosie, you started by creating a vizio document that represents this diagram on the left side of the page here right.

A

Absolutely and that visual document is right here, it's a plain, simple, visio diagram and all we did here is added shapes, and these are the shapes literally just boxes. There are no colors, there's no text. There's no information here and each of these shapes you can add information to shapes and visual. All we did here is added, a label called shape id and for each of these shapes we gave it a unique identity.

A

For example, this is container one so c1 container, two c2 and so and so forth.

B

I've got it queued up on my end, would you uh like me to demonstrate in vizio how you create the shape and.

A

Absolutely absolutely: let's, let's show the audience how to how you can easily create something. An experience like this.

B

Sure I'll do a much simpler version with maybe just the box or two on it. So here I am in vizio, and so you obviously will need a vizio license to pull this off too. So I'm just going to grab a basic shape like a rectangle like reza, had on his diagram. So he had one here and after I add that I just simply right click and pick data and then, if I click shape data, it opens this new window here and I can see there's no data defined here yet.

B

But if I go to data and I say, define shape data- well, guess what I can do so to match res example: you had shape id right and then you had those different data types and let's say this: one was id one.

B

So now you can see when we click on it, shape id1 and to really quickly replicate them throughout your drawing like reza. Had you know that? U shape you could come here and then now that one is shaped by d2 and that one's three yeah.

A

So that's how easy it is to create these visual diagrams. I want to add one thing uh todd you mentioned about licensing the maker, the maker, the person who's, creating that visio diagram and putting it in power bi needs a license. They need a vizio plan license.

A

Microsoft did announce that vizio will be a part of your office subscription that is coming soon. It will be offered for free by the way the consumers of the power bi report do not do not need a visio license. I just want to highlight that so only one person, which is the creator, actually needs the license as of today, and that will also go away very, very soon, nice, okay, so that's my vizio diagram and all I did is I took that visio diagram and put it in power bi.

A

I will show you how and inside power bi once I have that visio diagram, I connected it to my data set, which is microsoft dataverse now, as we can see right here, this is showcasing the storage facility and for each of those shapes I have certain information, the name of the space, the amount of space is available and the square footage of that specific space, and all of these data points here that are being applied to those shapes are coming in from my data source.

A

The colors of the shapes are also changing based upon based upon the data. So let's take an example: let's take space k right here. This is space k hundred square footage. It can add around 50 items so as a store manager or as an inventory manager. I can see space k has 50 spaces available and this by the way, is just the gallery in power apps on the right hand side.

A

So, if I select this, it will take me to the space and tell me that right now for this space, there's nothing in the inventory it's empty. Do you want to add things to it? So I'm going to say I would like to add: I am a fitness company, so these are the items that my company is potentially going to get in this inventory, so I already have them set up. Let's say I need the olympic barbell dumbbell set and I need 30 of these to go in space case.

A

So if I do this and hit submit that gets assigned to that specific inventory.

A

But now, if I go back to my previous screen, my home screen space k now only has 20 spaces available out of the total space size which is 50.. So if you observe here the color of that space or the color of that shape has changed and it's changed to orange. So it's giving me an indicator that this space now has started to fill up at the back end. I have an availability column, a calculated column that checks how much space is available.

A

Total space available space just calculate the percentage and based on that percentage, I'm changing the color I'll show you how I did it in the power bi diagram, but that's basically all to this there's nothing more than that to it and I showcased filters earlier well, let's apply that same logic here. This is a power bi report. I can use that filtering mechanism. I showed earlier so check this out here. You can even perform multi filters so like an in operator, so show me everything for space, a d e and f four filters.

A

I applied right here. I'm saying showcase these four filters now the moment I pick these and if the demo gods are with me, which they seem not to be okay, they are what's gonna happen is that power bi report will refresh because I'm passing filters to it and the filters are space. A d e f now check this out. It's going to completely strip out all the formatting for the other shapes, because the data set that is being passed to power bi, which is my visual and power.

A

Bi vizio only has those four data points so now, as an inventory manager. If I'm focusing on just these four spaces, I can clearly see what's going on right here. So that's the beauty of this solution. Now, if I go to my power bi report, this is my power bi report that has those two tabs. I have another one with a visual as well, but I want to show you the visio diagram.

A

So if I edit this, this is a visio visual and all you have to do literally is go here, go and just import that visual and if you, if, if it's already there just click it- and it will add the visual here next, it will ask you on drive, please bear in mind. I will have to ensure that wherever this diagram is located, anyone who is accessing the power bi report needs to also have access to the space.

A

Where we store this visio file in it could be sharepoint, it could be onedrive or any other any other storage space.

A

So once you have that linked, what are the properties of this specific visual? So, let's check this out. This visual has two key properties. One is id id. Is that key property that defines the mapping of the shape to your data set in my case here, if you observe the id property and let me try and open this app called zoom it. So I can zoom and show you things as well.

A

So if I pick this visual and right here, if you observe the id property of this shape, comes from my data set and there's a field called shape id. That means, if I go to my data set, which in my case is microsoft dataverse I have a table called spaces and in this table, if I go to data and quickly go to custom columns here, I have all my shape. Ids defined right here, and this is exactly the same ideas that I gave in those shapes in vizio. So what todd was showcasing earlier?

A

You created that property put the id. This has to exactly match once it matches. It knows which field data point matches to which shape, and once it understands that, then I can show data and change colors.

A

So if you look right here back in my power bi report, once I have my id mapped, then I can showcase values here, I'm showing the text, which is the name of the space. That's why it places it right here. You will see the name of the space right here and I can change this. I want to position this. Let's say on the right hand, side. So guess what the that text value now moves to the right most position of that shape, so you can define where they go. You can change the font size.

A

You can add basic formatting options are available here, but the key thing was that color change. So how am I doing that?

A

Well, one of the fields in the values property you can define as colors, so I have a column called availability that tells me how much space is available and it's a it's a calculated column that calculates the percentage space so 0 to 100.

A

So if the space available is between 80 and 100, that means there's a lot of space. Put the color green. Now, let's say I want to change this color. If there's a lot of space available, I want to go to a light. Lighter shade of green you'll, see that color change live on the visual diagram, but at the bottom you see as I move it will change that color right there.

A

So I can pick the range and I can pick the color. That is extremely powerful, because this is coming from my data set and that's exactly what I leveraged for this uh specific scenario right here to define my colors.

B

Raza getting back to your vizia file and how you were walking through the different properties, so you had the shape id and then you also have the property for the square footage and the size of it and what's available. So when you made those properties, you did the same thing I showed on my screen right. You just made more data point for that shape.

B

A

Then you're saying.

B

Your data binding happens right here in power, bi there's no code. You have to actually physically write to say I'm going to take this data from my data source and bind it to this shape. It's it's all right here.

A

It's that easy, literally one last quick demonstration. Now I can go the other way around as well. In the previous example, I showed power bi, which has that visio and I put it into power apps by leveraging that power power, bi tile uh control, and I played with it right with filters and refiners and all that good stuff, but you can go the other way around. I can have a power app, and this is my powerapp that is living inside a power.

A

Bi report there's a flow as well here by the ways I have the flow visual here. Once again, I have the visio diagram now check this out here I added shapes my shapes are nothing but seats that the users can reserve it's a desk reservation solution. So, let's check this out. Let's say I want to reserve desk number 12. now focus on this part, I'll click this shape, I'm selecting a shape. You know what's happening now it zoomed in in visio. Now that selection also triggers a filter.

A

If I have, if I'm using this in the context of power bi the power bi data set knows now that I'm filtering on that desk, because powerapps is contextually aware of the data in power bi, it's going to take a little bit of time, but power apps now will change its behavior. You will see that the power apps will go to the next screen automatically and showcase that you're trying to reserve desk number 12. there you go. I selected desk 12. It knows about it. The entire power bi report.

A

Also the data set has filtered you see all the all the visuals are now filtering along, and here I can say I would like to reserve this. For the third year of september, I say, save and here comes more power. This is going in my data source.

A

Powerapps has the ability to refresh the power bi data set so live it initiates a refresh all of the metrics here in the power bi report. Will change live it's a live experience for the user plus they have this visual right here, where they can make selections, and it's not just an image. It's a clickable image. It's it's dynamic! You can change the text, you can change the colors, so I can think of so many more possibilities. uh Stadium sections you want to book.

A

You want to book seats in an auditorium, uh so many so many options here open up with these with these powerful experiences, so that was it for.

B

My doubt about it great presentation, raza, really fun things you're building here, uh question from rob. What about autodesk files has vizio got a connector where we can bind up data to them. Like you did I mean? Does power bi have a connector where we can embed that too. I.

A

Am not sure about whether power bi has that uh from a vizio standpoint, because I've spoken to the product team at vizio as well who've built this visual as well. I think it only supports the diagrams and up to a certain version as well so uh autocad files possible. uh Maybe I don't know if there is any visual and power bi that gives that cool.

B

Very nice well done. I like how you have two different use cases for it here, too, really cool stuff. Thank you. Thanks again for sharing and if anybody's wondering reza's got a video where he does the same thing on his youtube channel, we have a link to it in the slide deck and uh I'm sure it'll pop its way into chat here in just a bit as well.

B

Thanks again reza come on back anytime. You've always got cool things to show. We appreciate it. Thank you. You're welcome.