Configure Power BI dashboards

The Higher Education Crisis Financial Impact Tracker app is designed to collect data pertaining to sponsored research programs or projects. Researchers can use the app to submit projected lost effort and loss reasons organized by grant, employee, and pay period.

By using Power BI, you can analyze and visualize the data from the app. The Power BI template described in this article pulls in the data that's collected by the Higher Education Crisis Financial Impact Tracker.

These reports are intended to be used by directors, deans, and research administrators who will monitor the data collected on behalf of their respective departments, sponsors, and colleges or schools.


The Higher Education Crisis Financial Impact Tracker app pulls data from Microsoft Dataverse into this Power BI template, which empowers you to securely store, integrate, and automate the data for use with other business apps, including Power BI, Dynamics 365, and Power Automate.

To use this Power BI template, you need these prerequisites:

  • Download the free Power BI Desktop app.

  • Sign up for the Power BI service.

  • Create a Dataverse environment with maker permissions to access the portal and read permissions to access data within the entities.

You can configure Power BI dashboards in two ways:

Configure a Power BI report by using a blank report canvas

To configure a Power BI report by using a blank report canvas:

  1. Open Power BI Desktop. You might be prompted to Sign in to the Power BI service by using your work or school account.

    Power BI Desktop.

  2. Select Get data > Power Platform > Common Data Service, and then select Connect.

    Get Data.

    Get Data.

  3. Enter the Server Url that's specific to your Dataverse environment. To get the Dataverse environment URL, do the following:

    1. Open Power Apps, select the environment you're going to connect to, select Settings in the upper-right corner, and then select Advanced settings.

    2. In the new browser tab that opens, copy the root of the URL. This is the unique URL for your environment. The URL will be in the format Be sure not to copy the rest of the URL.

    Dataverse environment.

    Service URL.

  4. After you successfully connect to your environment, you can see the folders for Entities and System in the Navigator. Expand Entities, and select the check boxes for the following entities:

    • Account

    • Contact

    • msft_Campus

    • msft_College

    • msft_Department

    • msft_EmployeeCompensation

    • msft_Grant

    • msft_LossReason

    • msft_PayPeriod

    • msft_SponsoredProgram

    Choose entities.

  5. After you finish selecting the list of entitites, select Transform Data. The Query Editor window opens with the selected tables and data loaded.

    Select tables.

  6. For each entity, select Choose Columns in the ribbon to open the console and select which fields to use in the data model and in reports.

    Choose Columns.

    • The following are suggested fields for the Contact entity:

      • msft_annualbasesalary
      • msft_annualbasesalary_base
      • msft_annualtuitionreimbursement
      • msft_annualtuitionreimbursement_base
      • msft_contacttype
      • msft_department
      • msft_employmentclass
      • msft_showwelcomemessage
      • msft_userid
    • The following are suggested fields for the msft_Campus entity:

      • msft_campusdescription
      • msft_campusid
      • msft_campusname
    • The following are suggested fields for the msft_College entity:

      • msft_campus
      • msft_collegedescription
      • msft_collegeid
      • msft_collegename
    • The following are suggested fields for the msft_Department entity:

      • msft_college
      • msft_departmentdescription
      • msft_departmentid
      • msft_departmentname
    • The following are suggested fields for the msft_EmployeeCompensation entity:

      • msft_effortamount
      • msft_effortamount_base
      • msft_effortpercentage
      • msft_employee
      • msft_employeecompensationid
      • mstf_fte
      • msft_lossamount
      • msft_lossamount_base
      • msft_losspercentage
      • msft_lossreason
      • msft_name
      • msft_paygroup
      • msft_paygroup_display
      • msft_payperiod
      • msft_payrate
      • msft_payrate_base
      • msft_sponsoredprogram
    • The following are suggested fields for the msft_Grant entity:

      • msft_enddate
      • msft_grantdescription
      • msft_grantid
      • msft_grantnumber
      • msft_grantstatus
      • msft_grantstatus_display
      • msft_granttitle
      • msft_principalinvestigator
      • msft_startdate
    • The following are suggested fields for the msft_LossReason entity:

      • msft_lossreasoncode
      • msft_lossreasondescription
      • msft_lossreasonid
    • The following are suggested fields for the msft_PayPeriod entity:

      • msft_enddate
      • msft_payperiodcode
      • msft_payperioddescription
      • msft_payperiodid
      • msft_startdate
    • The following are suggested fields for the msft_SponsoredProgram entity:

      • msft_availablebalance
      • msft_availablebalance_base
      • msft_awardamount
      • msft_awardamount_base
      • msft_coprincipalinvestigator
      • msft_department
      • msft_effortlossimpactamount
      • msft_effortlossimpactamount_base
      • msft_effortlossimpactamount_date
      • msft_effortlossimpactamount_state
      • msft_effortlossimpactpercentage
      • msft_grant
      • msft_primesponsorname
      • msft_sponsoredprogramdescription
      • msft_sponsoredprogramid
      • msft_sponsoredprogramnumber
      • msft_sponsorname
  1. Select Close & Apply to close the Query Editor and apply the changes you made.

  2. You'll see the following screen in the Power BI report canvas. It might take several minutes for the queries to run.

    Close and Apply.

  3. After the changes are applied, the Power BI report canvas looks similar to the following screenshot, including the tables listed in the Fields panel on the right side of the page.

    Applied Report.

  4. Select the icon on the left side of the page to open the Model view. You will see the tables that you selected. Use the slider in the lower-right corner to adjust the view size.

    Open the Model view.


  5. On the Home tab, select Manage Relationships to open the console where you'll create new relationships among the entities.

    Report Home.

    When creating or editing relationships among entities, you select the tables and columns to be joined, in addition to the Cardinality and Cross-filter direction for the relationship.

    Create relationship.

  6. To use the suggested fields in Dataverse that are pertinent to the Power BI template, your relationship mapping between tables should look like the following.

    From: Table To: Table
    msft_College (msft_campus) msft_Campus (msft_campusid)
    msft_Department (msft_college) msft_College (msft_collegeid)
    msft_EmployeeCompensation (msft_lossreason) msft_LossReason (msft_lossreasonid)
    msft_EmployeeCompensation (msft_payperiod) msft_PayPeriod (msft_payperiodid)
    msft_EmployeeCompensation (msft_sponsoredprogram) msft_SponsoredProgram (msft_sponsoredprogramid)
    msft_SponsoredProgram (msft_coprincipalinvestigator) Contact (contactid)
    msft_SponsoredProgram (msft_department) msft_Department (msft_departmentid)
    msft_SponsoredProgram (msft_grant) msft_Grant (msft_grantid)

    Manage relationships.

The following screenshot shows the Entity Relationship Diagram in the Model view.

Entity relationship.

Configure a Power BI report by using a Power BI template

The Power BI template contains sample data and interactive graphics in a .pbix file format that you can further edit and update in Power BI Desktop. Download the template as part of the overall solution from GitHub.

Open the Power BI template

When you open the template, you're greeted with a Power BI splash screen. You might also be prompted to Sign in to the Power BI service by using your work or school account.

When you open the Power BI template, you'll see a series of tabs at the bottom of the report:

  • Legal: Contains Microsoft legal disclaimer.

  • Home: Contains sample text that you can use and modify according to yoru preference.

  • Information: Contains general information.

  • FAQ: Contains frequently asked questions.

  • Submissions: Opens the Submissions Report page.

  • Sponsors: Opens the Impact by Sponsor Report page.

  • Department: Opens the Department view.

Your organization can add its logos to all pages in the template by inserting an image on the page and then copying it to other pages. More information: Copy and paste a report visualization

Connect to Dataverse

To use your own data collected by the Higher Education Crisis Financial Impact Tracker app, you need to update the data connection in the template. More information: Create a Power BI report using the Common Data Service connector

To change the data source, select Transform data to open Query Editor. In the Applied Steps of Query Editor, change the Source for each entity. Use the Dataverse environment URL.

Issues and feedback