Instructor Guide: Northlake Therapy — Clinic Performance Report

• Dragging the raw patient_id column into the Card visual instead of the [Total Unique Patients] measure, resulting in a SUM or COUNT rather than DISTINCTCOUNT.
• Placing insurer_id in the 'Values' field well of the donut chart instead of 'Legend', causing Power BI to attempt to sum insurer_id numerically.
• Forgetting to set 'Don't summarize' on insurer_id when it is used as a category, so the chart aggregates it as a number.
• Not changing the slicer style from the default list to 'Tile' for the is_primary slicer.
• is_primary displaying as 0/1 instead of readable True/False labels, with no attempt to fix it.
• Background color not applied or applied to the wrong element (e.g., the page background instead of the card background).
• Title font size not set to 14 on the card, or the title toggle is left off.
• Slicer not cross-filtering the other visuals because Edit Interactions was not verified.

• If a student is stuck on the Card visual: 'What is the difference between dragging a column directly into a visual versus creating a measure first — and why might that matter for counting unique values?'
• If a student is stuck on why insurer_id looks wrong in the donut chart: 'Power BI sees insurer_id as a number. What does that mean for how it will behave by default when placed in the Values well? What field well is meant for categories that group slices?'
• If a student is stuck on the slicer showing 0 and 1: 'What does the is_primary column actually contain in the source data? If you wanted a user to see True and False instead, where in Power BI could you change how that data is represented?'
• If a student is stuck on slicer style: 'Where in the Power BI interface can you change how a slicer looks — for example, from a list to clickable tiles? Is it in the Format pane or somewhere on the visual itself?'
• If a student is stuck on cross-filtering: 'If clicking a slicer value does not seem to affect the other visuals, what Power BI feature controls which visuals a slicer interacts with, and where would you find it?'

VISUAL 1 — Total Unique Patients Card: (1) Create the DAX measure first (see DAX section). (2) In the Visualizations pane select the Card icon. (3) From the Fields pane expand patient_insurers and drag [Total Unique Patients] into the 'Fields' well. (4) In Format visual > General > Title: toggle On, type 'Total Unique Patients', set font size to 14, bold. (5) In Format visual > Callout value: set font size to 20, bold. (6) In Format visual > General > Effects > Background: toggle On, pick a light blue color (e.g., hex #D6EAF8). (7) General > Border: toggle Off. (8) Resize to roughly 2x1.5 inches and position upper-left of canvas. VISUAL 2 — Policy Distribution by Insurer Donut Chart: (1) In Visualizations pane click the Donut chart icon. (2) Drag patient_insurers[insurer_id] into the 'Legend' well. Right-click insurer_id in the well and choose 'Don't summarize' if Power BI tries to aggregate it. (3) Drag patient_insurers[patient_insurer_id] into the 'Values' well; confirm it reads 'Count of patient_insurer_id'. (4) Format visual > Detail labels: toggle On, set label contents to 'Category, percent of total'. (5) Format visual > Slices > Inner radius: drag slider to 50. (6) General > Title: toggle On, type 'Policy Distribution by Insurer', font size 14. (7) Resize and position centrally. VISUAL 3 — Primary Coverage Filter Slicer: (1) In Visualizations pane click Slicer icon. (2) Drag patient_insurers[is_primary] into the 'Field' well. (3) Click the dropdown arrow (chevron) in the upper-right corner of the slicer visual and select 'Tile'. (4) Format visual > General > Title: toggle On, type 'Primary Coverage?', bold. (5) Format visual > Tiles > Selected state: set background to a contrasting color such as dark teal (#1A7A6E). (6) Format visual > Slicer settings > Selection: toggle 'Single select' Off so users can independently select True or False. (7) Go to View tab > Edit Interactions; confirm the slicer shows filter icons over both other visuals. (8) Position slicer top-right or in a side panel.

Visual 1 (8 pts): Award 3 pts for correct [Total Unique Patients] measure in field well; 2 pts for title font size 14 and bold; 2 pts for light blue background; 1 pt for correct positioning. Visual 2 (10 pts): Award 3 pts for insurer_id in Legend and count of patient_insurer_id in Values; 3 pts for data labels showing category and percent; 2 pts for inner radius at or near 50%; 2 pts for title. Visual 3 (10 pts): Award 3 pts for is_primary in field well and Tile style applied; 3 pts for contrasting highlight color on selected tile; 2 pts for clear title 'Primary Coverage?'; 2 pts for confirmed cross-filter interaction with other visuals. Award partial credit when the correct field is in the right well but formatting is incomplete.

Looking at the donut chart showing policy distribution by insurer — what business question might a clinic administrator ask that this chart alone cannot answer, and what additional data or visual would you add to the page to answer it?

• Student uses COUNT(patient_insurers[patient_id]) instead of DISTINCTCOUNT for the card: the result will be wrong if any patient has multiple policies. Deduct full measure points but award partial visual formatting points if everything else is correct.
• insurer_id treated as a text field by the student via Power Query type change: the donut chart will still work correctly — award full credit.
• Student creates a valid calculated column like is_primary_label = IF(patient_insurers[is_primary]=1,'Primary','Non-Primary') and uses that in the slicer instead of the raw is_primary column: accept this as full credit since it improves readability.
• Slicer is formatted correctly but Single Select is left On, restricting users to one tile at a time: deduct 1 formatting point but do not penalize heavily — this is a minor UX decision.

• Dragging the full coverage_start date column to the X-axis instead of expanding the hierarchy to the Month level, resulting in a chart that shows individual dates.
• Using coverage_start at the Year level for the line chart axis instead of Month, making the trend too coarse.
• Placing [Total Policies] instead of [New Policies Enrolled] on the line chart Y-axis, misunderstanding that the measure must respond to the date filter context.
• Not finding the Analytics pane to add the average reference line — looking in the Format pane instead.
• Configuring the reference line label as blank or forgetting to label it 'Avg Enrollment'.
• Placing is_primary in the X-axis instead of Legend on the clustered bar chart.
• Forgetting to sort bars by Total Policies descending, leaving them in default order.
• Not verifying the data model relationship before building the bar chart, leading to incorrect aggregations if an insurers table exists.
• Year slicer left as default list style instead of Dropdown.
• Slicer for Coverage Start Year uses a calculated column YEAR() but the column data type is left as text, causing sort order issues (sorting alphabetically as '2019','2020' etc. which may appear correct but breaks correctly for other scenarios).

• If a student is stuck on why the line chart shows a flat line or unexpected values: 'What measure is on your Y-axis right now? Does that measure change based on a date filter context, or does it always return the same number regardless of what period is on the X-axis?'
• If a student cannot find the average reference line: 'There are multiple panes available when a visual is selected in Power BI — Format, Fields, and one more. What is the third pane, and what kinds of things does it let you add to a chart?'
• If a student is stuck on the bar chart legend: 'Which field well controls the grouping that splits each bar into segments side by side? Is is_primary a measure or a category — and does that matter for which well it goes into?'
• If a student is stuck on sorting bars: 'Where in Power BI can you change the sort order of a chart? Is it in the Format pane, or is there another way to access sort options directly on the visual?'
• If a student is unsure about the Model view relationship: 'Before building a visual that combines fields from what might be multiple tables, what should you check in Power BI to make sure the data will join correctly?'
• If a student is stuck on the Dropdown slicer style: 'You changed the first page slicer to Tile style using a dropdown on the visual. Is the method for changing to Dropdown style the same, or different?'

SETUP — Calculated Column (if needed): If coverage_start does not auto-expand to a date hierarchy, in the Data view select patient_insurers table and create: Coverage Year = YEAR(patient_insurers[coverage_start]). Ensure coverage_start column data type is Date in Power Query (Home > Transform data; select column > Data Type > Date). VISUAL 1 — Coverage Start Year Slicer: (1) Insert Slicer. (2) Expand coverage_start hierarchy in Fields pane and drag 'Year' into Field well (or drag Coverage Year calculated column). (3) Click visual header dropdown arrow and select 'Dropdown'. (4) Format visual > Slicer header: toggle On, bold, font size 12. (5) View > Edit Interactions: confirm this slicer filters both the line chart and bar chart. VISUAL 2 — New Policies Enrolled Line Chart: (1) Insert Line chart. (2) Expand coverage_start hierarchy; drag 'Month' into X-axis well. If hierarchy is unavailable, create a calculated column Coverage Month = FORMAT(patient_insurers[coverage_start],'YYYY-MM') and use it. (3) Drag [New Policies Enrolled] into Y-axis. (4) Format visual > Lines: solid line; Markers: toggle On, size 4; Line > Line ends: Round. (5) Data labels: toggle On; configure to show only last data point (Apply settings to: Last data point). (6) Click Analytics pane icon (magnifying glass below Visualizations icons). Expand 'Average line' and click Add. Set label to 'Avg Enrollment', style to dashed, color to a contrasting gray. (7) General > Title: 'New Policies Enrolled Over Time'. (8) Set X-axis and Y-axis titles. VISUAL 3 — Total Policies by Insurer and Primary Status Clustered Bar Chart: (1) Go to Model view. Verify that if an insurers table exists, patient_insurers[insurer_id] has a many-to-one relationship to it. If missing and table exists, drag the key fields to connect them. Return to Report view. (2) Insert Clustered bar chart. (3) Drag patient_insurers[insurer_id] into Y-axis. Right-click in well and choose 'Don't summarize'. (4) Drag [Total Policies] into X-axis. (5) Drag patient_insurers[is_primary] into Legend. (6) Format visual > Bars: set distinct fill colors per legend value (e.g., teal for primary, orange for non-primary) under 'Colors'. (7) Data labels: toggle On, position 'Outside end'. (8) Click ellipsis (...) on visual > Sort axis > Total Policies > Sort descending. (9) General > Title: 'Total Policies by Insurer and Primary Status'.

Visual 1 (8 pts): 3 pts for correct Year field in slicer and Dropdown style; 2 pts for bold header; 3 pts for confirmed interaction with line and bar chart. Visual 2 (14 pts): 3 pts for Month-level date on X-axis; 3 pts for [New Policies Enrolled] measure on Y-axis; 3 pts for average reference line labeled 'Avg Enrollment'; 3 pts for markers, rounded line ends, and last-point data label; 2 pts for title and axis labels. Visual 3 (20 pts): 3 pts for correct field wells (insurer_id Y-axis, [Total Policies] X-axis, is_primary Legend); 4 pts for relationship verification noted or demonstrated; 4 pts for distinct colors per primary status; 4 pts for data labels and sort descending; 3 pts for title and formatting; 2 pts for axis titles. Award partial credit when the correct fields are in correct wells but sort or color formatting is missing.

The line chart shows monthly policy enrollments over time. If you noticed a sharp spike in enrollments during a particular month, what are two business explanations that could account for that spike, and how might you use Power BI to investigate whether your hypothesis is correct?

• Student uses [Total Policies] instead of [New Policies Enrolled] on the line chart: both measures use COUNTROWS and will produce identical results when filtered by date context from coverage_start — accept as equivalent and award full credit, but note in feedback that the naming distinction matters for report clarity.
• Student does not have an insurers table (dataset only contains patient_insurers): the relationship verification step is not applicable. Award the 4 relationship points if the student notes in their submission or during review that they checked Model view and confirmed no additional table exists.
• coverage_start hierarchy not available because of file format (e.g., CSV import with auto-detect off): accept a calculated Coverage Year and Coverage Month column approach for full credit.
• Bars sorted ascending instead of descending: deduct 2 pts but award remaining sort-related points if the sort was attempted.
• Average reference line added but unlabeled: deduct 1 pt from the reference line points.

• Configuring drill-through on the source page instead of the destination page — drill-through fields must be placed in the drill-through well on the destination (Page 3).
• Placing insurer_id in the table column well but NOT in the drill-through well, so the right-click drill-through option never appears on source pages.
• Back button added via a shape or text box instead of Insert > Buttons > Back, meaning it has no built-in navigation behavior.
• Conditional formatting applied to the wrong column or applied as font color instead of background color.
• Color scale direction reversed (green for short durations, red for long durations) rather than the specified red=short, green=long.
• Slicer style not changed to 'Search' (text search), remaining as a list of all policy numbers which is unusable.
• policy_number field type not recognized as text, so the Search slicer option does not appear.
• [Coverage Duration (Days)] measure not handling BLANK when coverage_start is missing, resulting in errors or 0 instead of BLANK in the table.
• Alternating row colors not enabled, leaving the table with a uniform background.
• Student does not test the drill-through from another page, so misconfiguration goes unnoticed.

• If a student is stuck on drill-through not working: 'Drill-through in Power BI is configured on a specific page — is it the page you are navigating FROM, or the page you are navigating TO? Where exactly in the Visualizations pane do you place the drill-through field?'
• If a student is stuck on the Back button not navigating: 'Power BI has a specific button type designed for navigation back to the previous page. Is the element you inserted that type of button, or is it something else like a shape or text box?'
• If a student cannot find the conditional formatting option: 'Conditional formatting is not in the main Format visual pane for a table. Where else in the Power BI interface might you find per-column formatting options for a table?'
• If a student is stuck on why Search style is not available for the slicer: 'The text search style only works with a specific type of data. What data type does policy_number need to be for the Search option to appear in the slicer style dropdown?'
• If a student is unsure about the BLANK handling in the DAX measure: 'What should the measure return when coverage_start has no value? How can you check for that condition in DAX before performing the date calculation?'
• If a student is confused about where drill-through fields go: 'Look at all the field wells available in the Visualizations pane when you have the table selected on Page 3. Do you see a section that is separate from Columns? What is it called?'

SETUP — Page Configuration: Right-click the page tab and rename to 'Drill-Through Patient Policy Detail'. In the Visualizations pane (with nothing selected), scroll to find the 'Drill through' section at the bottom. Drag patient_insurers[insurer_id] into the 'Add drill-through fields here' well. Power BI will auto-generate a Back button on the canvas; if it does not, go to Insert tab > Buttons > Navigator > Back. VISUAL 1 — Search by Policy Number Slicer: (1) Insert Slicer. (2) Drag patient_insurers[policy_number] into Field well. Confirm in Model view that policy_number is data type Text. (3) Click the visual header dropdown arrow and select 'Search'. (4) Format visual > General > Border: toggle On, set corner radius to 5. (5) General > Title: toggle On, type 'Search by Policy Number'. (6) View > Edit Interactions: confirm slicer filters the table visual below it. (7) Position above the detail table. VISUAL 2 — Patient Policy Detail Table: (1) Create the [Coverage Duration (Days)] measure first (see DAX section). (2) Insert Table visual. (3) Drag into Columns well in this order: patient_insurers[patient_id], patient_insurers[insurer_id], patient_insurers[policy_number], patient_insurers[coverage_start], patient_insurers[is_primary], [Coverage Duration (Days)]. (4) Format visual > Style presets: select 'Alternating rows'. If not available, go to Grid > Alternating row color: toggle On and pick a light gray. (5) Column headers: toggle Bold On; set header background color (e.g., dark blue with white text). (6) Adjust column widths: hover over column divider in the visual and drag to fit content. (7) Conditional formatting on Coverage Duration (Days): in the Columns field well, click the dropdown arrow next to [Coverage Duration (Days)] > Conditional formatting > Background color. In the dialog: Format style = Gradient; Minimum value = Lowest value, color = Red (#FF0000); Maximum value = Highest value, color = Green (#00B050). Click OK. (8) Back button: If not auto-generated, Insert tab > Buttons > Navigator > Back. Position upper-left. In Format > Action, confirm Type is 'Back'. (9) Test: Go to Page 2, right-click a bar segment for an insurer, hover over 'Drill through', and confirm 'Drill-Through Patient Policy Detail' appears as an option. Click it to verify navigation and that the Back button returns to Page 2.

Visual 1 (6 pts): 2 pts for policy_number in field well and Search style applied; 2 pts for rounded border formatting; 2 pts for clear title and confirmed filter interaction with table. Visual 2 (16 pts): 3 pts for all six correct columns in the Columns well in a logical order; 3 pts for drill-through correctly configured on this page with insurer_id in the drill-through well; 3 pts for conditional formatting on Coverage Duration (Days) with red-to-green gradient in correct direction; 3 pts for alternating row colors and bold column headers; 2 pts for functional Back button (Insert > Buttons > Back type, not a shape); 2 pts for confirmed drill-through test from Page 2. Award partial credit when drill-through is configured but Back button is wrong type (award 1 of 2 Back button points). Award partial credit when conditional formatting is applied but color direction is reversed (award 1 of 3 conditional formatting points).

Drill-through navigation is designed to take users from a summary view to a detail view. In the context of a physical therapy practice, who would be the likely end-user of this drill-through page, and what action might they take based on what they see in the Patient Policy Detail table?

• Student places insurer_name from an insurers lookup table in the drill-through well instead of insurer_id: this can work if the source pages also use insurer_name. Award full drill-through points if it functions correctly when tested, but note the dependency on the lookup table.
• Student has [Coverage Duration (Days)] returning 0 instead of BLANK for missing coverage_start due to omitting the ISBLANK check: the table will show 0 for null rows rather than blank. Deduct 2 pts from the DAX measure but do not re-penalize in the table visual grading.
• Auto-generated Back button was deleted and student re-created it using Insert > Buttons > Back correctly: award full Back button points.
• Student adds the drill-through field to the source page (Page 2) instead of the destination page (Page 3): drill-through will not work at all. Deduct all 3 drill-through configuration points but award other table formatting points if the table itself is built correctly.
• Conditional formatting gradient is applied but minimum/maximum are set to specific numbers rather than 'Lowest value'/'Highest value', causing some rows to not be colored if their values fall outside the hardcoded range: award 2 of 3 conditional formatting points.
• policy_number is imported as a number type due to leading zeros being stripped: student may need to fix in Power Query. If the student identifies this issue and attempts a fix (even partially), award full slicer Search style points.

• Using COUNT(patient_insurers[patient_id]) instead of DISTINCTCOUNT, which overcounts patients who have multiple insurance records.
• Using COUNTROWS(patient_insurers) which counts all rows, not unique patients.
• Referencing the wrong column, e.g., DISTINCTCOUNT(patient_insurers[patient_insurer_id]) which counts unique policy records, not patients.
• Creating the measure in the wrong table, causing context issues when used in cross-table visuals.

Total Unique Patients = DISTINCTCOUNT(patient_insurers[patient_id])

Award 3/3 for correct DISTINCTCOUNT on patient_id. Award 2/3 if the student uses COUNTROWS(DISTINCT(patient_insurers[patient_id])) or VALUES() approach that produces the same correct result. Award 1/3 if the syntax is correct DAX but references the wrong column (e.g., patient_insurer_id). Award 0/3 for COUNT or SUM without distinctness.

• Using COUNT(patient_insurers[patient_insurer_id]) instead of COUNTROWS — both produce the same result here but COUNTROWS is the idiomatic correct form per the formula hint.
• Using DISTINCTCOUNT(patient_insurers[patient_insurer_id]) which would be correct only if policy IDs were guaranteed non-unique — accept if result is the same.
• Using SUM on a non-numeric column causing an error.
• Adding unnecessary filters inside the measure that constrain the row count unintentionally.

Total Policies = COUNTROWS(patient_insurers)

Award 3/3 for COUNTROWS(patient_insurers). Award 3/3 for COUNT(patient_insurers[patient_insurer_id]) since the result is equivalent on this dataset. Award 2/3 for DISTINCTCOUNT(patient_insurers[patient_insurer_id]) — logically close but slightly different semantics. Award 0/3 if the measure errors or returns a constant that ignores filter context.

• Writing a measure identical to [Total Policies] without understanding that COUNTROWS already responds to filter context from the date axis — the measures are functionally equivalent, but students may not understand why.
• Attempting to use DATEADD or SAMEPERIODLASTYEAR unnecessarily, thinking time intelligence functions are required for monthly display.
• Adding an explicit FILTER on coverage_start inside the measure that hardcodes a date range, breaking the dynamic nature of the chart.
• Using CALCULATE with no filter argument, which is redundant and may confuse graders — still award credit if the result is correct.
• Placing the measure on a visual with no date axis and being confused when it returns a single total rather than a time series.

New Policies Enrolled = COUNTROWS(patient_insurers)

Award 4/4 for COUNTROWS(patient_insurers) — this is correct because the measure dynamically responds to the date filter context provided by the coverage_start Month axis on the line chart. Award 4/4 for COUNT(patient_insurers[patient_insurer_id]) for the same reason. Award 3/4 for CALCULATE(COUNTROWS(patient_insurers)) — technically works but the CALCULATE with no filter is unnecessary; accept with a note. Award 2/4 if the student writes a measure that returns correct totals when unfiltered but includes an internal FILTER that prevents it from working correctly on the line chart. Award 0/4 if the measure returns a constant regardless of filter context.

• Using DATEDIFF without handling BLANK, causing errors or returning 0 for rows where coverage_start is null.
• Reversing the DATEDIFF argument order (TODAY() as start, coverage_start as end), resulting in negative values.
• Using DAY instead of DAY as the interval identifier — note: the correct third argument in Power BI DAX is DAY (all caps), which is a constant.
• Using NOW() instead of TODAY(), which returns a datetime and may cause fractional day results.
• Using IF(coverage_start = BLANK(), BLANK(), ...) instead of ISBLANK(), which may not catch all null scenarios.
• Creating this as a calculated column rather than a measure, which means it calculates at row level and cannot respond to filter context the same way — however, for this use case (displaying in a table row by row), a calculated column would actually also work. Accept either but note the distinction.
• Referencing the wrong table or column name due to typos.

Coverage Duration (Days) = IF(ISBLANK(patient_insurers[coverage_start]), BLANK(), DATEDIFF(patient_insurers[coverage_start], TODAY(), DAY))

Award 8/8 for the correct formula with ISBLANK check, correct DATEDIFF argument order, TODAY(), and DAY interval. Award 7/8 if formula is correct but uses NOW() instead of TODAY() — results are nearly identical for day-level calculations, minor deduction. Award 6/8 if DATEDIFF is correct but ISBLANK check is missing — no blank handling. Award 5/8 if argument order in DATEDIFF is reversed (negative values output). Award 4/8 if the student uses a calculated column approach (correct logic but wrong object type) and it displays correctly in the table — note that as a measure it would show BLANK in the card context but work in the table. Award 2/8 if syntax is recognizable DAX but formula will error at evaluation. Award 0/8 if the formula is completely wrong or missing.