The one surprising thing about Visio Integration in Power BI
I was introduced to the new Visio custom visual for Power BI during the Microsoft Inspire convention. After a few minutes, I was impressed with the power and simplicity of it. It helped solve a problem that we’ve had when building out Power BI reports.
Telling a Complete Digital Story
In my Power BI classes, I talk about the importance of creating complete digital stories. They are complete in that you have three components, which allow the story to be understood in a standalone fashion. The three components are
- Where are you
- Where do you need to be
- What is the path or connection between the two states
Think of Visio integration as the easiest way to show your data road map. The Visio diagram can add needed context to the overall picture. Adding proper context with a great diagram makes it much easier to interpret the results, make critical decisions, and take necessary actions.
Quick Power BI Example
Imagine you are a banker and you are trying to assess the current state of your loan process. Throughput is a very important to this process and you want to avoid things getting hung up as this impacts profits. Clients also get upset when they miss closing dates as they can lose real estate deals.
Today, Showing Data without Context
Today you have a Power BI report with various visuals that provide health metrics. You can easily see things like which step has the highest average age of items. You can even see with the bubble chart the overall distribution of steps by Average Age and Item Count.
However, the story isn’t very compelling and it doesn’t answer a key question, what else will be impacted if I don’t fix process step X? Do you clearly know where to focus your attention?
Tomorrow, Your Data In Context
Compare to this report where we’ve added a Visio diagram of the process. The diagram serves as a heat map. Areas that have high aging average values will be in Red. Those in danger are in Yellow and everything else is green. I can still answer the questions I had before. However, now I can see in a glance where I have too many “old” loans in process and what will be impacted downstream.
As I click on any visual on the report, the Visio diagram will zoom to the related step. If I click on the red process step in the Visio diagram, all other visuals on the page are filtered. These behaviors encourage further exploration of the data.
Surprisingly Easy to Implement
The one thing that surprised me about this visual is how easy it is to incorporate Visio diagrams you already have into your Power BI reports. The mechanics are such to make it very easy to map data to the shapes.
I want to replace the Visio Diagram above with an existing one that I have. It shows the four major phases of the process. I want to use this diagram on an Executive version of the report, where I don’t need great operational detail.
Prepare Your Diagram
|Take your existing diagram and do this:
Design, Size, Fit to Drawing.
This helps reduce the white space around the drawing
|The canvas will appear as shown.
Save your diagram using File, Save
|If the diagram is not already in an Office 365 SharePoint folder, upload the diagram to a location that the consumers of the report would have access.
|Click on the diagram to view it in the browser
|Copy the URL as you’ll need this later in Power BI to insert the diagram.
Replace the Existing Visio Visual with a New Instance
|Open the model in Power BI Desktop
|Select the Visio custom visual that shows the existing diagram
|Go to the Visualization area and select another visual type. This resets the Visio custom visual
|Click the Visio icon in the Visualization area to change it back
|Paste in the URL of your diagram that you saved earlier.
|Click Connect and login
Map Your Data to the Diagram
There are two tasks that are generally required when adding an existing diagram to a Power BI report.
- Replace the column value in the ID field.
- Map each shape to a data value in the ID column.
The procedure below will take you through the steps to do both actions.
Update the Column Values in the ID Field
|In Power BI Desktop, go to the Fields tab for the Visio visual. Drag the new ID column value over the existing column value.
|Now Phase is in the ID Field.
Map Shapes to Data Values
|Click the < on the Field Mapping bar in the Visio Custom Visual
|You will see the ID: field highlighted in yellow
|Click the dropdown next to the ID field name. You’ll see the list of data values from the ID column shown.
|To map a shape to a data value, select the shape, then select the data value to map to it.
Repeat for each shape and data value.
|When you are done, collapse the ID field
|Review the Values Settings below.
If you want to show the actual value, change the Display As to Text
|If you want to show the value in the form of a heat map, change the Display As to Colors. Set the colors and range accordingly.
|Save and Publish Your Power BI model.
When you see your report online, you can either click any box in the Visio diagram to filter all other visuals or you can click another visual to filter the Visio diagram.
An example of this report can be found below.
As you’ve seen, the mapping feature makes it quite easy to incorporate any existing Visio diagram into a Power BI dashboard. You can now add things like Org charts, process maps or other visual data for filtering in your reports.
If you want to know more, check out these links.
- Visio in use with Power BI Via PowerBI.com
- Download Sample PBIX
- Documentation: https://go.microsoft.com/fwlink/p/?linkid=851657
- Email firstname.lastname@example.org for any feedback
- Submit ideas to Visio UserVoice for suggestions.
- See the blog report example in the Power BI Data Stories Gallery and download the pbix.
- Check out these relevant Visio Expert sessions at Ignite 2017 here, here and here.