Build a three-page Power BI report over a multi-site physical therapy clinic, with DAX measures and cross-page drill-through.
Open the provided .pbip starter project in Power BI Desktop. The tables and relationships are already in place — build the required visuals and measures on the named report pages. Submit your .pbix. Assignment: Motion Forward Physical Therapy — Insurance Coverage Report Course: Business Intelligence & Data Visualization Total Points: 100 Instructions: 1. Open the .pbip starter file in Power BI Desktop. Do not modify the existing data model relationships unless a task explicitly instructs you to verify or create one. 2. Create the four DAX measures listed in the DAX Measures section before building visuals that depend on them. Define all measures in the patient_insurers table unless otherwise noted. 3. Build each report page exactly as named: 'Patient & Insurer Overview', 'Coverage Timeline Analysis', and 'Drill-Through Patient Policy Detail'. 4. Each page must include at least one slicer as specified in the page instructions. 5. All visuals must have descriptive titles formatted in the Format visual pane (not just the default field name). 6. When finished, save your file as a .pbix and submit via the course portal. 7. Partial credit is awarded for visuals that are partially complete or incorrectly formatted but demonstrate correct intent.
Motion Forward Physical Therapy operates five outpatient clinics across a metropolitan region, helping patients recover from injuries and manage chronic pain through individualized treatment plans. As the clinic network grows, administrators need clear visibility into which insurers cover their patient population, how long policies have been active, and how enrollment trends shift over time. This report will help the billing and operations teams make informed decisions about insurer partnerships and patient intake planning.
Page 1: Patient & Insurer Overview (28 pts)
Create the Patient & Insurer Overview page with the following visuals. This page provides a high-level snapshot of patient counts and insurer distribution across the clinics. Ensure all visuals are clearly titled and formatted with consistent colors.
Visual 1: Total Unique Patients (8 pts)
Add a Card visual that displays the total number of unique patients registered in the patient_insurers table. Use the DAX measure 'Total Unique Patients'. Format the card with a bold title font (size 14), a light blue background, and no border. Position the card in the upper-left area of the canvas.
Hint: The Card visual has a 'Fields' field well — make sure you drag a measure (not a raw column) into it so aggregation is handled correctly.
Visual 2: Policy Distribution by Insurer (10 pts)
Add a Donut Chart that shows how policies are distributed across insurers. Use insurer_id as the legend and a count of patient_insurer_id as the values. Format the chart with data labels showing both the category and percentage. Set the inner radius to 50%. Give the chart a clear title.
Hint: Check that insurer_id is placed in 'Legend' not 'Values' — if both are numeric, Power BI may try to aggregate insurer_id. You may need to right-click and choose 'Don't summarize' or format it as text.
Visual 3: Primary Coverage Filter (10 pts)
Add a Slicer visual using the is_primary field from the patient_insurers table. Change the slicer style to 'Tile' so users can click between True and False. Label the slicer clearly as 'Primary Coverage?' and format the selected tile with a contrasting highlight color. This slicer should cross-filter all other visuals on the page.
Hint: If is_primary shows as 0 and 1 instead of True/False, you can change the data type in Power Query or create a calculated column that maps 0/1 to readable labels. Check the Format pane for the slicer style dropdown — it's accessible via the visual header dropdown arrow.
Page 2: Coverage Timeline Analysis (42 pts)
Create the Coverage Timeline Analysis page to help clinic administrators understand how insurance policy enrollments have grown over time and explore coverage start trends. This page requires DAX measures and cross-visual interactions. Each visual must have a descriptive title and properly formatted axes.
Visual 1: Coverage Start Year (8 pts)
Add a Slicer that lets users filter by the year of coverage_start. Use the Year level of the coverage_start date hierarchy (or a calculated column for year). Set the slicer style to 'Dropdown' to save canvas space. Format the slicer header text as bold, and ensure the slicer interacts with the line chart and bar chart on the page.
Hint: If coverage_start does not automatically expand into a hierarchy, check that the column's data type is set to 'Date' in the data model. You can verify this in the Model view or Power Query.
Visual 2: New Policies Enrolled Over Time (14 pts)
Add a Line Chart that shows the count of new policies enrolled by month using the coverage_start date. Use the DAX measure 'New Policies Enrolled' on the Y-axis and the Month level of coverage_start on the X-axis. Add a reference line at the average enrollment count. Format the line with rounded line ends, enable markers at each data point, and add a data label for the most recent month only.
Hint: The Analytics pane is separate from the Format pane — look for the icon that looks like a magnifying glass below the Visualizations icons. Reference lines are only available for certain visual types including line charts.
Visual 3: Total Policies by Insurer and Primary Status (20 pts)
Add a Clustered Bar Chart showing the total number of policies per insurer, broken down by primary vs. non-primary coverage. Use insurer_id on the Y-axis, the DAX measure 'Total Policies' on the X-axis, and is_primary as the legend. Before building this visual, verify that any relationship between patient_insurers and related tables (such as insurers) is correctly established in the Model view. Format the bars with distinct colors for primary vs. non-primary, add data labels, and sort the bars by total descending.
Hint: To verify or create a relationship, use the Model view. If is_primary displays as 0/1 in the legend, consider whether renaming it through a calculated column would improve readability. Make sure the legend field is a category, not a numeric measure.
Page 3: Drill-Through Patient Policy Detail (22 pts)
Create the Drill-Through Patient Policy Detail page. This page serves as a drill-through destination that clinic staff can reach by right-clicking a specific insurer on other report pages. It must show granular patient-level policy information. Configure drill-through correctly and include advanced formatting and measures. This page should have a 'Back' button to return to the source page.
Visual 1: Search by Policy Number (6 pts)
Add a Slicer using the policy_number field. Set the slicer style to 'Text search' so users can type part of a policy number to filter the table below. Format the slicer with a rounded border and a placeholder prompt text. Ensure the slicer filters the detail table on this page.
Hint: The text search style only appears when the field placed in the slicer is a text/string field. If policy_number is categorized differently, check its data type in Power Query or the Model view.
Visual 2: Patient Policy Detail Table (16 pts)
Add a Table visual that shows patient-level policy records including patient_id, insurer_id, policy_number, coverage_start, is_primary, and the DAX measure 'Coverage Duration (Days)'. Configure this page as a drill-through destination: in the Visualizations pane drill-through well, add insurer_id so users can right-click an insurer on other pages and drill through here. Enable conditional formatting on the 'Coverage Duration (Days)' column using a color scale (short durations red, long durations green). Add a Back button using Insert > Buttons > Back. Format the table with alternating row colors and bold column headers.
Hint: Drill-through is configured on the destination page, not the source page. The field you place in the drill-through well on this page determines what field users right-click on source pages to trigger navigation. Make sure the same field (insurer_id) exists on both pages.
Create the following DAX measures in your Power BI report:
Total Unique Patients (3 pts)
Counts the number of distinct patient IDs in the patient_insurers table, representing the total number of unique patients who have at least one insurance record.
Formula hint: Use DISTINCTCOUNT on the patient_id column in the patient_insurers table
Total Policies (3 pts)
Returns the total number of policy records in the patient_insurers table, representing all insurer associations across all patients.
Formula hint: Use COUNTROWS on the patient_insurers table to count all policy records
New Policies Enrolled (4 pts)
Counts the number of policy records within the current filter context, intended to be used with coverage_start on a time axis to show how many new policies were enrolled per time period.
Formula hint: Use COUNTROWS combined with the patient_insurers table; this measure counts rows and relies on the date filter context from coverage_start to show monthly enrollment when placed on a line chart with a date axis
Coverage Duration (Days) (8 pts)
Calculates the number of days between the policy coverage_start date and today's date, representing how long each policy has been active. Should return BLANK if coverage_start is missing.
Formula hint: Use DATEDIFF between coverage_start and TODAY() with DAY as the interval; handle cases where coverage_start might be blank using IF or ISBLANK