Lab 07.1: Build Power BI dashboards

In this lab, you will build a Power BI dashboard that visualizes data about problems reported by company employees.

What you will learn

How to refine the data model and prepare it for reporting
How to create a Power BI visualization
How to embed a Power BI report in Microsoft Teams

High-level lab steps

We will follow the below steps to design and create the Power BI dashboard:

Transform the data to include user-friendly descriptions for the related rows (lookups)
Create and publish a report with various visualizations of the information about problem reports
User natural language query to build additional visualizations
Build mobile view
Embed the Company 311 Power BI report to Microsoft Teams

Prerequisites

Must have completed Lab 02.1: Data model
Permissions to install programs on your computer (required for Power BI Desktop installation)

Things to consider before you begin

Who is the target audience of the report?
How will the audience consume the report? Typical device? Location?
Do you have sufficient data to visualize?
What are the possible characteristics you can use to analyze data about the visits?

Detailed steps

Exercise 1: Create a workspace

In this exercise, you will create a Power BI report based on data from Microsoft Dataverse tables.

Task 1: Create a new workspace

Before you begin, download the problem-reports-data.pbix file and save it to your computer.

Navigate to Power BI
On the left side of your screen, select Workspaces.
Choose New Workspace.
Name the Workspace 311 Workspace.
Select Apply.
Select Upload.
From the menu that appears, select Browse.
Locate and select problem-report-data.pbix file that you’ve downloaded earlier.

TIP

This file can also be found here: F:\Instructions\Labs\05\Resources\problem-reports-data.pbix
Once data load is complete, select problem-reports-data report (will look like “…” when you hover over it).
Select Settings.
Change the Report Name to Problem Report.
Select Save.

Exercise 2: Create Power BI Report

In this exercise, you will create a Power BI report based on data from Microsoft Dataverse tables.

Task 1: Create Chart and Time Visualizations

Select the problem report with a type of Report.
Once the report is open, select Edit.
Select the Pie chart icon in the Visualizations panel to insert the chart.
Expand lh_ProblemReport table in the Data panel, drag Building Column and drop it into Legend target box.
Drag Problem Report Column and drop it into Values target box.
Resize the pie chart using corner handles so that all chart components are visible. Your report should now look like this:
Select the report’s design surface outside of the chart area (this will deselect your pie chart).
On the Visualizations pane, select stacked column chart.
Drag Problem Report Column and drop it into the Y-axis target box.
Drag Status Column and drop it into X-axis target box.
Resize the chart as required using the corner handles.
Test the report interactivity:

Select various building slices on the pie chart and observe changes on the stacked column chart.
Select various bars on the stacked column chart and observe changes on the pie report.

Save work in progress by selecting File Save.

Exercise 3: Create Power BI Dashboard

Task 1: Create Power BI Dashboard

Click on 311 Workspace. If it is not in your menu, select Workspaces and then 311 Workspace
Select the Problem report with a type Report.
Select Pin to a dashboard on the menu (depending on the layout, you may need to press … to show additional menu items).
Select New dashboard on the Pin to dashboard prompt.
Enter Problem Management Dashboard as the Dashboard name and select Pin live.
Select Go to dashboard.

Task 2: Add Visualizations Using Natural Language

If not there already, go to the 311 workspace and select the Problem Management Dashboard.
Select Ask a question about your data on top of the dashboard.
Enter funnel count of problem reports by status in Q&A area. The funnel chart will be displayed.
Select Pin visual.
Select Existing dashboard, select Problem Management dashboard, select Pin.
Select Exit Q&A to return to your dashboard.

Task 3: Build Mobile Phone View

If not already open, select the Problem Management dashboard from the Dashboards area.
Select Edit and select Mobile Layout from the drop-down box.
Rearrange the tiles as desired.
Select the down arrow next to Problem Management Dashboard in the upper left corner of your screen.
Under location, select 311 Workspace.
Ensure you select problem report with type Report.
Select File and then select Generate QR Code from the drop down box.
If you have a mobile device, scan the code using a QR scanner app available on both iOS and Android platforms.

NOTE

To access the dashboard and report, you will have to sign in on the phone as the same user.
Navigate and explore the reports and dashboards on your mobile device.

Exercise 4: Embed Power BI report

In this exercise, you will add the Company 311 Power BI report to Microsoft Teams and to the Company 311 Admin Model-driven application as a way for management and staff to be able to view the reports from directly within Teams and the Model-driven application.

Task 1: Setup Company 311 Team

In this task you will setup a Microsoft Teams team for the Lamna Healthcare Company, if you have not done so previously.

Navigate to Microsoft Teams and sign in with the credentials you have been using previously.
Select Use the web app instead on the welcome screen.
When the Microsoft Teams window opens, dismiss the welcome messages.
On the bottom left corner, choose Join or create a team.
Select Create a team.
Press From scratch.
Select Public.
For the Team name choose Company 311 and select Create.
Select Skip adding members to Company 311.

Task 2: Embed Power BI report to Teams

Navigate to Microsoft Teams.
Select the General channel of the Company 311 team.
On the top of the page, press the + symbol to add a new tab.
Search for and select Power BI from the results.
Expand the 311 Workspace and select the report you created earlier in this lab, then select Save.
You should now have your Power BI report in a tab in Microsoft Teams.

Task 3: Embed Power BI report to Model-driven app

Navigate to Power Apps maker portal and make sure you are in your practice environment.
Select Solutions and open the Company 311 solution.
Select + New and select **Dashboard Power BI embedded**.
Enter Problem management for Display name, select Power BI report for type, uncheck Show reports in this environment only and select 311 Workspace for Power BI workspace, select Problem Report for Power BI report and select Save.
Select Publish all customizations and wait for the publishing to complete.
While still in the Company 311 solution, open the Company 311 Admin Model-driven application.
In app designer, make sure you have Manage Problems area selected. If not, click on the arrows and select Manage Problems.
Select the ellipsis (…) next to Navigation title and select New group.
Go to the Properties pane and enter Reports for Title.
Select the Reports group you just created, select + New.
Select Dashboard for Type and click Next.
Under Power BI dashboards check the checkmark next to Problem management and then select Add.
Select the ellipsis (…) next to the Reports group and select Move up.
Select Save, then select Publish, wait for the publishing to complete and then select Play.
The report should load. Interact with the report and make sure it behaves as expected.

Exercise 5: Power BI embedded canvas app

In this exercise, you will add a canvas application to Power BI as a visual.

Task 1: Add canvas

Navigate to Power BI.
Select Workspaces and then select to open 311 Workspace.
Open the Problem Report.
Select Edit.
Resize and reposition the visuals as shown below.
Select an empty area of the canvas, go to the Visualizations pane and select Power Apps for Power BI.
Select the Power BI visual you just created, expand the lh_problemreport table and select Problem Report column. Drag the Problem Report column into Add data fields here under Visualizations » PowerApps Data
Select your practice environment and select Create new.
A new browser tab should open and load the Power Apps Studio.
Do not navigate away from this page.

Task 2: Customize the app

Right-click on the Gallery and select Delete.
Select Settings from the toolbar.
Select the Display tab.
Change the Orientation to Landscape and select Apply on the popup.
Close the Settings window.
Select Data and select Add data.
Select the Problem reports table.
From the Tree view, select the App object.
Select the OnStart property of the App object (found in the Advanced tab) and set it to the formula below. This formula will create two variables one to keep track of the current index of the reports table and another to keep track of the current item row.

Set(currentIndex,1);Set(CurrentItem, LookUp('Problem Reports', 'Problem Report' = GUID(Last(FirstN([@PowerBIIntegration].Data,currentIndex)).'Problem Report')))
Select the + Insert button, expand Media group, then select Image.
Set the Image value to the formula below.

CurrentItem.Photo
Select the … menu button of the App object and select Run OnStart.
The photo from the Problem Report should load. If you are not seeing the photo, then go to the Admin Model-driven App and add photo to any Problem Report records where the Photo field is empty.
Set the X value of the image to 0.
Set the Y value of the image to 0.
Set the Width value of the image to the formula below. (You will need to enter it using the Formula bar)

Parent.Width
Set the Height value of the image to the formula below. (You will need to enter it using the Formula bar)

Parent.Height
The image should fill the screen.
Do not navigate away from this page.

Task 3: Add controls

Select the Insert tab and select Text label.
Select Label1 from the tree view and set the Text value to the formula below.

CurrentItem.Title
Set the Height value of the label to 60.
Set the X value of the label to 0.
Set the Y value of the label to formula below.

Parent.Height - Self.Height
Set the the Width value of the label to formula below.

Parent.Width
Set the Fill value of the label to RGBA(0, 108, 191, .5).
Set the Color value of the label to RGBA(255, 255, 255, 1).
Set the Align value to the formula below.

Align.Center
If you don’t see the title, select the … button of the App object and select Run OnStart again.
Select + Insert, enter next in the search box, then select Next arrow under Icons.
Double-click on the name of the icon you just added and rename it Next icon.
Select + Insert, enter back in the search box, then select Back arrow under Icons.
Double-click on the name of second icon you just added and rename it Back icon.
Drag and place the Next icon above the right side of the label.
Drag and place the Back icon above the left side of the label.
The icons should now look like the image below.
Select the Next icon and set the OnSelect value to the formula below.

```UpdateContext({CurrentItem: LookUp('Problem Reports', 'Problem Report' = GUID(Last(FirstN([@PowerBIIntegration].Data,currentIndex+1)).'Problem Report'))});UpdateContext({currentIndex: currentIndex+1})```

Set the DisplayMode value of the Next icon to the formula below.

```If(currentIndex = CountRows([@PowerBIIntegration].Data), DisplayMode.Disabled, DisplayMode.Edit)```

Select the Back icon and set the OnSelect value to the formula below.

UpdateContext({CurrentItem: LookUp('Problem Reports', 'Problem Report' = GUID(Last(FirstN([@PowerBIIntegration].Data,currentIndex-1)).'Problem Report'))});UpdateContext({currentIndex: currentIndex-1})
Set the DisplayMode value of the Back icon to the formula below.

If(currentIndex > 1, DisplayMode.Edit, DisplayMode.Disabled)
Select + Insert, enter check in the search box, then select Check under Icons.
In the tree view, double-click Icon1 and rename the Check icon to Complete icon.
Move the Complete icon to the top right of the screen.
Set the OnSelect of the Check icon to the formula below. This formula will update the status of the row to completed and then refresh Power BI.

Patch('Problem Reports', CurrentItem, {'Status Reason': 'Status Reason (Problem Reports)'.Completed}); PowerBIIntegration.Refresh()
Select Play.
Select the next and back icons and make sure the image changes.
Close the preview.
Select the Save button.
Enter Power BI embed app for Name.
Select Save.
Select the Publish button. Select Publish this version.
Close the app studio browser window or tab.
You should now be back on the Power BI report. Select Refresh on the top header.
Select the Next and Back icons to make sure the application loads the images.
Select the Completed column of the stacked column chart and make a note how many rows are completed.
Select any column of the stacked column chart apart from Completed. Select the next icon to see the next image.
Select the Complete icon.
The completed count should increase. If the completed count doesn’t increase, select refresh and wait for the visuals to be refreshed.
Save the report.