Hospital Communications Dashboard

Transform 325,000+ rows of CMS patient survey data into a Power BI dashboard.

Introduction

30 Second Summary

Every hospital in the U.S. gets rated by real patients on how well their doctors and nurses communicate. These ratings are public, free to download, and reveal which hospitals are falling short.

In this project, you will transform 325,000+ rows of real government patient survey data into an interactive Power BI dashboard that visualizes hospital communication performance and reputation scores.

What You'll Build

Picture clicking a state in the slicer and watching every KPI card, bar chart, and scatter plot on the page instantly update to reveal how that state's hospitals compare on communication and reputation.

By the end of this project, you'll have:

KPI cards that show national averages for nurse communication, doctor communication, and overall hospital reputation at a glance.
Comparison and scatter plot visuals that reveal which communication areas hospitals struggle with most and how communication quality connects to overall reputation.
Interactive slicers and conditional formatting that let you filter the entire dashboard by state and hospital ownership type, with color-coded performance tiers highlighting top and bottom performers.
Secret Mission: Add a geographic map showing hospital reputation by state and a drill-through page for individual hospital detail.

Are there any prerequisites?

You need a Mac with at least 30 GB of free disk space. No prior Power BI experience is required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters to you.

Set Up Power BI Desktop on Your Mac

Power BI Desktop is a Windows-only application. To use it on your Mac, you need to run Windows inside a virtual machine. A virtual machine is a simulated computer that runs inside your real computer, letting you use an entirely different operating system in a window on your desktop.

You will use two free tools to make this happen: UTM (a macOS app that creates and runs virtual machines) and CrystalFetch (a companion utility that downloads the Windows 11 installer). Once Windows is running, you will install Power BI Desktop inside it.

Budget at least an hour for this step

Downloading the Windows 11 installer and completing the Windows installation involve long passive waits (30 to 60 minutes depending on your internet speed and Mac). You will only be actively clicking for about 15 minutes total. Start a podcast or grab a coffee while things download.

In this step, get ready to:

Install UTM and CrystalFetch on your Mac.
Create a Windows 11 virtual machine in UTM.
Install Power BI Desktop inside your Windows VM.

Install UTM and download a Windows 11 ISO

Before you can create a virtual machine, you need UTM (the app that runs it) and a Windows 11 installer file. CrystalFetch downloads the installer directly from Microsoft's servers.

Press Cmd+Space to open Spotlight.
Type App Store and press Enter to open the Mac App Store.
Search for UTM in the App Store search bar.
Click Get, then click Install to download UTM.
Once the install completes, click Open to launch UTM.

You should see an empty UTM window with no virtual machines listed. This confirms UTM is installed and ready.

Go back to the Mac App Store.
Search for CrystalFetch in the search bar.
Click Get, then click Install to download CrystalFetch.
Once the install completes, click Open to launch CrystalFetch.

CrystalFetch opens with dropdown menus for selecting which Windows version to download. You need to configure it for your Apple Silicon Mac.

Set Version to Windows 11.
Set Build to Latest.
Set Architecture to Apple Silicon.
Set Language to English (United States).
Set Edition to Windows 11.
Click Download ISO.
Choose your Downloads folder as the save location.

CrystalFetch begins downloading the Windows 11 ISO directly from Microsoft's servers. A progress bar appears in the CrystalFetch window. This download can take 30 minutes to over an hour depending on your internet connection.

What is an ISO file?

An ISO file is a digital copy of an installation disc. It contains everything needed to install an operating system. UTM will use this ISO to install Windows 11 in your virtual machine, just as you would use a physical DVD to install Windows on a real PC.

CrystalFetch showing an error or no architecture options?

If you see a conversion error or the Architecture dropdown is empty, your network connection may be blocking access to Microsoft's download servers. Try connecting to a different Wi-Fi network or using a VPN.

As an alternative, you can download a Windows 11 ARM ISO directly from Microsoft at microsoft.com/en-us/software-download/windows11arm64. Use this ISO in the next substep.

Can't download the Windows 11 ISO?

Create your Windows 11 virtual machine in UTM

With the ISO downloaded, you can now create a virtual machine in UTM that will run Windows 11. The UTM wizard walks you through allocating resources (RAM, CPU, disk space) from your Mac to the VM.

Switch back to UTM (the app you opened earlier).
Click the + button to open the VM creation wizard.
Select Virtualize.
Select Windows.
Check the box for Install Windows 10 or higher.
Check the box for Install drivers and SPICE tools.
Click Browse and navigate to your Downloads folder. Select the Windows 11 ISO file you downloaded with CrystalFetch.

What are SPICE tools?

SPICE tools are drivers that help the virtual machine communicate smoothly with your Mac. They enable features like automatic screen resizing, shared clipboard, and better mouse integration. Without them, the VM would feel clunky to use.

On the Hardware screen, set the RAM to 4096 MB. If your Mac has 16 GB of RAM or more, you can increase this to 8192 MB for better performance.
Set the CPU cores to 4.
Press Continue.
On the Storage screen, set the disk size to at least 30 GB.
Press Continue.
On the Shared Directory screen, press Continue to skip this for now.
Press Save to create the VM.

Your new Windows VM appears in the UTM sidebar. UTM automatically downloads guest tools in the background. Wait for that download to finish before continuing.

Once the guest tools download completes, press the play button (the large triangle) to start the VM.

A black screen appears with the message "Press any key to boot from CD or DVD". You need to act quickly.

Click inside the VM window and press any key on your keyboard to start the Windows installer.

Seeing a black screen that stays black?

Windows 11 24H2 has a known issue where the VM shows a black screen after starting. To fix this: stop the VM, eject the guest tools ISO from the drive menu in UTM, restart the VM, then re-mount and install the Windows Guest Tools after Windows finishes installing. Restart the VM one more time.

If the screen stayed black because you missed the "press any key" prompt, stop the VM and start it again. Press a key as soon as the boot message appears.

My Windows VM won't boot or shows a black screen.

The Windows Setup wizard appears. It will walk you through the installation. Most screens are straightforward, but a few choices are easy to miss.

On the language and region screen, leave the defaults and click Next.
Click Install now.
When asked for a product key, click I don't have a product key.
Select Windows 11 Home (or any edition) and click Next.
Accept the license terms and click Next.
Select Custom: Install Windows only (advanced).
Select the virtual drive (Drive 0) and click Next.

Windows begins installing. This takes 10 to 20 minutes. The VM will restart itself one or more times during installation. Do not press any key when you see the "Press any key to boot from CD or DVD" message again during restarts.

When the setup resumes, follow the on-screen prompts to select your region, keyboard layout, and create a user account.

After setup finishes, you see the Windows 11 desktop inside your UTM window. SPICE drivers install automatically in the background because you checked the drivers option earlier.

Do I need a Windows license?

Windows 11 can be used without activation for evaluation purposes. You may see a small watermark in the bottom-right corner reminding you to activate. This does not block any functionality you need for this project.

Mouse not working properly in the VM?

If your mouse cursor behaves strangely, press the mouse capture button in the UTM toolbar to send mouse input directly. Press Control+Option together to exit mouse capture mode. Toggling capture mode on and off once usually fixes cursor issues.

My mouse isn't working inside the Windows VM.

Install Power BI Desktop inside Windows

Now that Windows is running, you can install Power BI Desktop. It is a free application available from the Microsoft Store, which comes pre-installed on Windows 11.

Inside your Windows VM, click the Start button on the taskbar.
Type Microsoft Store and press Enter to open it.
In the Microsoft Store search bar, type Power BI Desktop and press Enter.
Click the Power BI Desktop result from Microsoft Corporation.
Click Get (or Install) and wait for the download to complete.

Why install from the Microsoft Store?

Power BI Desktop is free whether you install it from the Microsoft Store or as a standalone download. The Microsoft Store version stays updated automatically, which is more convenient inside a VM where you want to minimize maintenance.

Performance may feel a bit slow

Power BI Desktop runs through an emulation layer on ARM-based Windows VMs. It works correctly for this project's dataset of 325,000 rows, but you may notice it loads a few seconds slower than on a native Windows PC.

Before you open Power BI Desktop for the first time, what do you expect to see on the start screen?

Once the installation finishes, click Open in the Microsoft Store to launch Power BI Desktop.

Power BI Desktop opens to a start screen. You should see the Home ribbon at the top with a Get data button. This confirms your entire environment is working. UTM is running Windows 11, and Power BI Desktop is ready to import data.

Power BI Desktop not opening?

If Power BI Desktop hangs on a loading screen, give it a minute. The first launch is slower due to the emulation layer. If it fails to open entirely, try restarting the Windows VM from the Start menu, then launch Power BI Desktop again from the Start menu search.

Power BI Desktop won't open in my Windows VM.

Your development environment is set up. You have a Windows 11 VM running on your Mac with Power BI Desktop installed and ready. Next up, you will import real hospital patient survey data and see your first visual come to life.

Import the HCAHPS Patient Experience Data

Your Windows VM is running and Power BI Desktop is ready to go. Now you need real data to bring your dashboard to life.

The CMS HCAHPS dataset contains 325,000+ rows of real patient survey data covering communication scores, star ratings, and satisfaction metrics for hospitals across the U.S. In this step, you will import this data and build your first visual.

In this step, get ready to:

Download the HCAHPS patient survey dataset from the CMS Provider Data Catalog.
Import the CSV data into Power BI Desktop.
Build a bar chart to explore patient survey star ratings by state.

Download the HCAHPS dataset from CMS

Every analysis starts with data. The HCAHPS patient survey is published by the Centers for Medicare & Medicaid Services (CMS) and is the standard for measuring how patients experience hospital care in the U.S.

What is HCAHPS?

HCAHPS stands for Hospital Consumer Assessment of Healthcare Providers and Systems. It is a national, standardized survey administered to a random sample of hospital patients about their experiences during a recent inpatient stay.

The survey covers communication with hospital staff, responsiveness, communication about medicines, discharge information, cleanliness, quietness, and transition of care. CMS publicly reports the results, making it an ideal dataset for communications analytics.

The simplest way to get the data is to download it directly inside your Windows VM. This avoids any file transfer steps.

Inside your Windows VM, click the Microsoft Edge icon on the taskbar to open the browser.
In the address bar, type data.cms.gov and press Enter.
In the search bar at the top of the page, type Patient survey HCAHPS Hospital and press Enter.
In the search results, click the dataset titled Patient survey (HCAHPS) - Hospital.
On the dataset page, click the Download full dataset (CSV) button.
Wait for the download to complete. The file saves to your Downloads folder inside the VM.

Downloading on your Mac instead?

If you prefer to download the file on your Mac, you can transfer it into the VM using UTM's shared folder feature. However, downloading directly inside the VM's browser is the fastest path.

Page not loading or search returning no results?

Make sure your VM has internet access. Try navigating to any website first to confirm connectivity. If the CMS site is slow, give it a moment. Government data portals can take longer to respond than typical websites.

If search returns too many results, look specifically for the one labeled Patient survey (HCAHPS) - Hospital under the Hospitals topic. You can also navigate directly to data.cms.gov/provider-data/dataset/dgck-syfz.

help me connect my UTM Windows VM to the internet so I can download the HCAHPS dataset

Import the data into Power BI Desktop

With the CSV file downloaded, you can now load it into Power BI Desktop. Power BI's Get Data feature connects to hundreds of data sources. For a local CSV file, you will use the Text/CSV connector.

Switch back to Power BI Desktop inside your VM.
On the Home ribbon at the top, click Get data.
In the Get Data dialog, select Text/CSV from the list, then click Connect.
A file browser opens. Navigate to your Downloads folder, select the HCAHPS CSV file you just downloaded, and click Open.
A preview of the data appears in the Navigator. You will see columns like Facility Name, State, HCAHPS Measure ID, and Patient Survey Star Rating.
Click Load to import the full dataset.

Why Load instead of Transform Data?

The Navigator gives you two options: Load imports the data directly into your report. Transform Data opens Power Query Editor for cleaning first. You are choosing Load now so you can see the raw data and understand what needs cleaning. You will open Power Query Editor in the next step.

The import takes a moment because you are loading 325,000+ rows. When it finishes, look at the Fields pane on the right side of the screen.

You should see a new table listed with all the dataset columns underneath it, including Facility Name, State, HCAHPS Question, Patient Survey Star Rating, and more.

Fields pane is empty or the import seems stuck?

If the import takes longer than expected, remember that Power BI is running through an emulation layer inside your VM. Give it up to a minute or two. If the Fields pane stays empty, try the import again by clicking Get data and selecting the file once more.

Make sure you selected Load, not Transform Data. If you accidentally opened Power Query Editor, close it by clicking Close & Apply on the Home ribbon to load the data into the report.

help me troubleshoot importing a CSV file into Power BI Desktop

Build your first bar chart

Your data is loaded. Time to create your first visual and see what 325,000+ rows of patient survey data actually look like when you chart it.

Make sure you are on the Report view (the chart icon on the left side of the Power BI Desktop window).
In the Visualizations pane on the right, click the Clustered bar chart icon. It looks like horizontal bars stacked side by side.
An empty chart placeholder appears on the canvas.
From the Fields pane, drag State to the Y-axis well in the Visualizations pane.
Drag Patient Survey Star Rating to the X-axis well.

Why does the outline say State on X-axis but you are using Y-axis?

In a Clustered Bar Chart, the categories (states) go on the Y-axis and the values (ratings) go on the X-axis. This is because bar charts display horizontal bars. If you used a Clustered Column Chart instead, the axes would be flipped. Either way, Power BI will place the fields correctly when you drag them into the wells.

Before you look closely at the chart, take a moment to predict. You have 325,000+ rows of raw survey data covering every HCAHPS measure for every hospital. Do you think the chart will come out clean and easy to read on the first try?

Look at your chart now. It is messy. You will notice entries labeled Not Available cluttering the results. The star ratings are aggregated across every survey measure, mixing nurse communication scores with doctor communication scores with overall ratings. The chart is hard to interpret because the raw data has not been filtered or cleaned.

This mess is the point

Raw data rarely tells a clear story on the first try. The HCAHPS dataset stores every survey question for every hospital in a single table, one row per measure per hospital. That means your chart is trying to show all of those measures at once.

In the next step, you will open Power Query Editor to remove the Not Available values and filter down to only the communication measures that matter for your dashboard. That is where real analytics work begins.

Your first chart is built, but it is far from ready. Next up, you will clean and transform this data so every visual tells a clear story.

Clean and Transform the Data with Power Query

In the last step, you imported over 325,000 rows of real hospital patient survey data into Power BI and built your first chart.

But that chart revealed a problem: the raw data is messy. "Not Available" entries are cluttering the visuals, and every survey question is mixed into a single table. Until you clean and filter the data, no chart will tell a clear story. This is where real analytics work begins.

In this step, get ready to:

Filter out "Not Available" values from key columns.
Keep only the communication-focused survey measures.
Remove unnecessary columns to streamline the dataset.

Filter out "Not Available" values

Right now, your HCAHPS data contains rows where hospitals reported "Not Available" instead of actual scores. These entries add noise to every visual you build. Power Query is the tool inside Power BI Desktop that lets you clean and reshape data before it reaches your report.

Open Power Query Editor by clicking Transform Data on the Home ribbon in Power BI Desktop.

A new window opens showing your data in a spreadsheet-like preview with a ribbon across the top.

What is Power Query?

Power Query is a data transformation engine built into Power BI Desktop. It records every change you make as a "step" in the Query Settings pane on the right side.

When you apply changes and return to your report, Power Query replays those steps automatically. Your original data source stays untouched.

Scroll right in the data preview until you find the Patient Survey Star Rating column.
Click the small dropdown arrow at the top of the Patient Survey Star Rating column header.
In the filter list, uncheck Not Available.
Click OK.

The data preview updates immediately. You should see that every value in the Patient Survey Star Rating column is now a number, with no "Not Available" entries.

Now scroll to find the HCAHPS Answer Percent column.
Click the dropdown arrow at the top of the HCAHPS Answer Percent column header.
Uncheck Not Available in the filter list.
Click OK.

Check the data preview again. Both columns should now show only numeric values. You can also see two new steps in the Applied Steps list on the right side of the Power Query Editor, one for each filter.

Don't see the column you need?

The HCAHPS dataset has many columns, and you may need to scroll quite far to the right. Use the horizontal scrollbar at the bottom of the data preview, or try pressing the right arrow key while clicking on a column header to move through columns faster.

help me find a specific column in Power Query Editor

Keep only communication-focused measures

The HCAHPS dataset contains dozens of different survey measures, from cleanliness to quietness to pain management. For a communications dashboard, you only need the measures that track how well hospitals communicate with patients.

Scroll to find the HCAHPS Measure ID column.
Click the dropdown arrow at the top of the HCAHPS Measure ID column header.
Uncheck (Select All) at the top of the list to clear all selections.

Now you need to find and check the specific measure IDs related to communication. The filter list has a search bar at the top that helps you locate values quickly.

Type H_COMP_1 in the search bar to find the Nurse Communication measures. Check every result that appears.
Clear the search bar, then type H_COMP_2 to find Doctor Communication measures. Check every result that appears.
Repeat this process for H_COMP_5 (Communication About Medicines), H_COMP_7 (Care Transition), H_HSP_RATING (Overall Rating), and H_RECMND (Willingness to Recommend).
After checking all six groups, click OK.

Use the search bar to stay organized

Each time you type a new value in the search bar, the filter list narrows to show only matching entries. Previously checked items remain checked even when they are not visible in the filtered list. This means you can search, check, clear, search again, check more, and all your selections accumulate.

Check the data preview. The row count in the bottom status bar should have dropped significantly now that you have filtered out all non-communication measures.

Why these six measure groups?

Each group captures a different dimension of hospital communication performance. H_COMP_1 and H_COMP_2 cover direct staff communication (nurses and doctors).

H_COMP_5 tracks how well hospitals explain medications. H_COMP_7 measures care transition communication. H_HSP_RATING and H_RECMND are the overall reputation scores that let you correlate communication quality with patient satisfaction.

Remove unnecessary columns and apply changes

The dataset still contains columns that add no value to your dashboard, like hospital addresses, phone numbers, and footnote metadata. Removing them keeps your data model lean and your Fields pane uncluttered.

Hold Ctrl and click the column headers for each of these columns to select them all: Address, Telephone Number, Patient Survey Star Rating Footnote, HCAHPS Answer Percent Footnote, Number of Completed Surveys Footnote, and Survey Response Rate Percent Footnote.
Right-click any of the selected column headers.
Select Remove Columns from the context menu.

The selected columns disappear from the data preview. A new step appears in the Applied Steps list on the right.

Why remove these columns?

Footnote columns contain metadata codes that the CMS uses for data quality notes. They are not useful for dashboard visuals. The address and phone number columns are hospital contact details that you do not need for aggregate analysis.

Removing unused columns early improves Power BI's performance and makes your Fields pane easier to navigate.

Before you apply your changes, what do you think will happen to the bar chart on your report canvas when you load this cleaned data back?

Click Close & Apply on the Home ribbon of the Power Query Editor.

Power BI loads the cleaned data back into your report. The Clustered Bar Chart from Step 2 automatically updates. The "Not Available" entries are gone, and the chart now shows clean star rating data.

Chart still looks the same?

Make sure you clicked Close & Apply (not just the X button) to save your Power Query transformations. If you closed the editor without applying, reopen it by clicking Transform Data on the Home ribbon and check that your filter steps are listed in the Applied Steps pane.

help me troubleshoot my Power Query transformations not appearing in my report

Your data is clean and focused on exactly the communication measures your dashboard needs. Next up, you will build KPI cards and comparison visuals that turn this clean data into real communications insights.

Build Communication Performance KPIs and Analysis

Your HCAHPS data is clean, filtered, and focused on the communication measures that matter. But clean data alone is not insight.

To turn numbers into a story, you need visuals that answer specific questions. In this step, you'll create KPI cards that show headline communication scores, a bar chart that reveals which communication dimension hospitals struggle with most, and a scatter plot that connects communication quality to overall hospital reputation.

In this step, get ready to:

Create three KPI Card visuals showing headline communication performance scores.
Build a comparison bar chart ranking communication dimensions nationally.
Add a scatter plot revealing whether communication quality connects to hospital reputation.

Create KPI cards for communication scores

A KPI card is a single-number visual that shows one headline metric at a glance. Executives read these first. You'll create three cards, each filtered to a different communication measure from your HCAHPS data.

Click an empty area of the report canvas to deselect any existing visuals.
In the Visualizations pane on the right, select the Build visual tab, then click the Card icon (hover over the icons to see their names).
From the Data pane, drag HCAHPS Answer Percent into the Values field well in the Visualizations pane.
The card displays a number. Click the dropdown arrow next to the field in the Values well and select Average to show the mean instead of a sum or count.
In the Filters pane, under Filters on this visual, drag HCAHPS Measure ID from the Data pane and check only H_COMP_1.
Drag HCAHPS Answer Description into Filters on this visual and check only Always.
Click the Format visual button (paint roller icon) in the Visualizations pane, expand General, then toggle Title on.
Replace the default title text with Nurse Communication - Always.

Your first card now shows the average percentage of patients who said nurses "always" communicated well.

What does this card show?

The HCAHPS Answer Percent field records what percentage of patients chose a specific answer at each hospital. By filtering to H_COMP_1 (Nurse Communication) and the answer Always, the card averages that percentage across all hospitals nationally.

A higher number means more patients felt nurses always communicated well. This is exactly the kind of headline metric a VP of Communications would want on a dashboard.

Click an empty area of the canvas, then click the Card icon in the Visualizations pane to create a second card.
Drag HCAHPS Answer Percent to the Values well and change the aggregation to Average.
In Filters on this visual, drag HCAHPS Measure ID and check only H_COMP_2.
Drag HCAHPS Answer Description to the filters and check only Always.
Set the title to Doctor Communication - Always.

You now have two cards side by side. The second shows the average percentage of patients who said doctors "always" communicated well.

Create a third Card visual on the canvas.
Drag HCAHPS Answer Percent to the Values well and set it to Average.
In Filters on this visual, drag HCAHPS Measure ID and check only H_HSP_RATING.
Drag HCAHPS Answer Description to the filters and check only 9 or 10.
Set the title to Overall Hospital Rating - 9 or 10.

Your canvas now shows three KPI cards. Each displays a different average communication or reputation score.

Card showing a count or a very large number?

If the card displays "Count of HCAHPS Answer Percent" or a number in the thousands, the field may have been imported as text. Click the dropdown arrow next to the field in the Values well and confirm Average is selected. If Average is not available, you may need to change the column's data type in Power Query.

To fix this, click Transform Data on the Home ribbon, select the HCAHPS Answer Percent column header, and change the data type to Decimal Number using the Data Type dropdown on the Transform ribbon. Click Close & Apply to reload.

Help me fix my Card visual. It's showing a count instead of an average.

Build a communication comparison bar chart

The KPI cards show headline numbers, but they don't tell you how communication dimensions rank against each other. A comparison bar chart lines up every dimension so you can instantly see which one is strongest and which one lags behind.

Click an empty area of the canvas to deselect any visuals.
In the Visualizations pane, click the Clustered bar chart icon.
From the Data pane, drag HCAHPS Question to the Y-axis field well.
Drag HCAHPS Answer Percent to the X-axis field well and change the aggregation to Average.
In the Filters pane, under Filters on this visual, drag HCAHPS Answer Description and check only Always and Definitely Yes.

The chart updates to show bars for each communication question, ranked by the average percentage of top-box responses.

What does this chart reveal?

Each bar represents a different communication dimension. The longer the bar, the higher the percentage of patients who gave the top response for that question.

By filtering to only "Always" and "Definitely Yes" answers, you isolate the strongest positive signal. If one bar is noticeably shorter than the rest, that is the communication dimension where hospitals struggle most nationally.

Chart showing too many bars or no bars?

If you see dozens of bars, the filter on HCAHPS Answer Description may not be applied. Open the Filters pane and confirm the filter is under Filters on this visual, not Filters on this page. A page-level filter would affect all your other visuals too.

If the chart is blank, double-check that you checked Always and Definitely Yes in the filter card. These values are case-sensitive.

Help me fix my communication comparison bar chart.

Uncover the communication-reputation connection

The cards and bar chart show how hospitals perform on individual communication dimensions. The next question is whether those scores actually connect to overall hospital reputation. A scatter plot is the right tool to reveal this kind of relationship.

Click an empty area of the canvas to deselect any visuals.
In the Visualizations pane, click the Scatter chart icon.
From the Data pane, drag HCAHPS Answer Percent to the X Axis field well and change the aggregation to Average.
Drag Patient Survey Star Rating to the Y Axis field well and change its aggregation to Average.
Drag State to the Legend field well so that each dot represents a state.
In the Filters pane, under Filters on this visual, drag HCAHPS Measure ID and check only H_COMP_1.
Drag HCAHPS Answer Description into the visual filter and check only Always.

Before you look at the result, take a moment to predict. Do you think states where more patients say nurses "always" communicated well will also have higher star ratings? Or is there no clear pattern?

Look at your scatter plot. Each dot is a state. The X-axis shows the average percentage of "Always" responses for nurse communication. The Y-axis shows the average patient survey star rating. You should see a general upward trend, meaning states with stronger communication scores tend to earn higher reputation ratings.

What does the scatter plot reveal?

This visual tests a hypothesis: does better communication lead to better reputation? The upward pattern suggests the answer is yes. States where hospitals score higher on nurse communication also tend to receive higher star ratings.

Any dots that sit far from the trend are worth investigating. A state with high communication scores but a low star rating, or vice versa, could point to factors beyond communication that drive reputation.

Scatter plot showing only one dot?

If you see a single aggregated data point instead of one dot per state, the Legend well may be empty. Confirm that State is in the Legend well. Without a categorical field there, Power BI aggregates all data into a single point.

If the Patient Survey Star Rating field does not appear as an option for the Y Axis, it may also need a data type change to Decimal Number in Power Query, just like you may have done for HCAHPS Answer Percent.

Help me fix my scatter plot. I only see one dot instead of a dot per state.

Take a final look at your report canvas. You should see three KPI cards with average scores, a clustered bar chart comparing communication dimensions, and a scatter plot with state-level dots showing the communication-reputation connection.

Your data now tells a story. Next up, you'll transform these individual visuals into a polished, interactive dashboard with slicers, conditional formatting, and a professional layout.

Design the Storytelling Dashboard

Your KPI cards, comparison chart, and scatter plot already tell a powerful story about hospital communication performance. Right now, though, they are just a collection of visuals sitting on a canvas.

A real dashboard needs interactive controls that let users explore the data on their own. It needs visual cues like color-coding to highlight performance gaps at a glance. In this step, you will add slicers, conditional formatting, and a polished layout to create something a VP of Communications would actually use.

In this step, get ready to:

Add interactive slicers so users can filter by state and hospital ownership type.
Apply conditional formatting to color-code KPI cards and chart bars by performance tier.
Add a dashboard title and arrange all visuals into a clean executive layout.

Add interactive slicers for state and hospital type

Slicers are interactive filter controls that sit directly on the report canvas. When a user selects a value in a slicer, every other visual on the page updates automatically to show only the filtered data.

Click on an empty area of the report canvas so no existing visual is selected.
In the Visualizations pane on the right, select the Slicer visual (the funnel-and-list icon).
From the Fields pane, drag State into the slicer's Field well.

What is a slicer?

A slicer is a standalone filter control that lives on the report page. Unlike the Filters pane (which is hidden by default), slicers are always visible. They show users exactly which filter is active and let them change it with a single click.

The slicer defaults to Dropdown style, which is exactly what you want for the State slicer. It saves space by collapsing the list until the user clicks it.

To confirm the style, select the State slicer.
In the Visualizations pane, select the Format visual icon (paint brush).
Expand Visual then Slicer settings then Options.
Confirm that Style is set to Dropdown. If it is not, select Dropdown from the list.
Position this slicer near the top-left of your report canvas.

Now add the second slicer for hospital ownership type.

Click on an empty area of the canvas again.
Select the Slicer visual from the Visualizations pane.
Drag Hospital Ownership from the Fields pane into the slicer's Field well.
With the Hospital Ownership slicer selected, open Format visual (paint brush icon).
Expand Visual then Slicer settings then Options.
Change Style to Vertical list.
Position this slicer below or beside the State slicer on the left side of your canvas.

Time to test your slicers. Click the State slicer dropdown and select any state. All the KPI cards, bar chart, and scatter plot should update to show only data for that state.

Click a hospital type in the Hospital Ownership slicer as well.
Confirm that the KPI values and chart data change to reflect the combined filter.
To clear the filters, click the eraser icon in the top-right corner of each slicer.

Slicers not filtering the other visuals?

Make sure all visuals are on the same report page. Slicers only filter visuals on the page where they live.
Check that the slicer has a field in its Field well. An empty slicer will not filter anything.
If a specific visual is not responding, select it and go to Format tab on the ribbon then Edit interactions. Make sure the filter icon (not the "none" icon) is active for that visual.

Still stuck? Help me figure out why my Power BI slicers are not filtering the other visuals on my page.

Apply conditional formatting to KPI cards and bar chart

Color-coding turns raw numbers into instant performance signals. A VP scanning this dashboard should be able to spot underperforming areas without reading a single number.

Select your first KPI Card visual (Nurse Communication).
In the Visualizations pane, select the Format visual icon (paint brush).
Expand the Visual tab, then expand Callout values.
Next to Color, click the fx button to open the conditional formatting dialog.

What is the fx button?

The fx button appears next to many formatting options in Power BI Desktop. It opens a dialog where you set rules that change the visual's appearance based on your data values. This is how you make colors respond to the data automatically.

In the conditional formatting dialog, set Format style to Rules.
Set What field should we base this on? to the field your card is using (average of HCAHPS Answer Percent).
Create three rules:
Click OK to apply the formatting.

Your Nurse Communication card value should now display in a color that reflects its performance tier.

Repeat the same conditional formatting steps for the Doctor Communication card and the Overall Hospital Rating card.
Use the same rules (green above 80, yellow 60 to 80, red below 60) so the color coding is consistent across all three cards.

Now add conditional formatting to the Clustered Bar Chart's data bars so the comparison chart carries the same color language.

Select the Clustered Bar Chart (the one comparing communication dimensions).
In the Format visual pane, expand Visual, then expand Bars (or Columns).
Next to Color, click the fx button.
Set Format style to Gradient or Rules (your choice). A gradient from red (lowest values) to green (highest values) works well here.
Click OK to apply.

The bar chart bars should now reflect performance through color. Bars representing stronger communication scores appear greener, and weaker scores appear redder.

Not seeing the fx button?

Make sure the visual is selected before opening the Format pane. The fx button only appears when a visual is actively selected.
For Card visuals, look under Callout values in the Visual tab of the Format pane. The fx button sits next to the Color option.
For bar charts, look under Bars (or Columns) in the Visual tab.

Still stuck? Help me find the conditional formatting fx button in Power BI Desktop for my Card and Bar Chart visuals.

Add a dashboard title and arrange the final layout

A clear title and a logical layout are what separate a collection of charts from a real dashboard. The title tells the viewer what they are looking at. The layout guides their eye from the headline numbers to the details.

On the Home tab in the ribbon, click Text box in the Insert section.
A text box appears on the canvas. Type the dashboard title: Hospital Reputation & Communications Intelligence Dashboard.
Increase the font size to 18 or 20 and set it to bold using the text formatting toolbar that appears above the text box.
Press Enter to start a new line inside the same text box. Type a subtitle such as: Source: CMS HCAHPS Patient Survey Data | Centers for Medicare & Medicaid Services.
Set the subtitle to a smaller font size (10 or 12) and a lighter color like grey.

Now arrange all your visuals into a clean executive layout.

Drag the title text box to the very top of the canvas, spanning the full width.
Position the three KPI cards in a row directly below the title.
Place the Clustered Bar Chart and Scatter Chart side by side below the KPI cards.
Move the State and Hospital Ownership slicers to the top-right or left side of the canvas, where they frame the dashboard without covering any visuals.
If the original bar chart from Step 2 (Patient Survey Star Rating by State) is still on the canvas, you can delete it by selecting it and pressing the Delete key. Your new visuals cover this analysis more effectively.

Align visuals precisely

Select multiple visuals by holding Ctrl and clicking each one. Then use the Format tab in the ribbon to access Align and Distribute options. This snaps your visuals into a precise grid without eyeballing it.

Save your report by pressing Ctrl+S. Choose a name like Hospital-Communications-Dashboard.pbix and save it inside the VM.

Before you test the final dashboard, take a moment to predict. When you select a state in the slicer, what do you think will happen to the KPI card colors? Will they change if that state's scores fall into a different performance tier?

Click the State slicer dropdown and select a state.
Confirm that every visual on the page updates to reflect the selected state's data.
Select a hospital type in the Hospital Ownership slicer.
Verify that the KPI card values change and the bar chart and scatter plot filter accordingly.
Clear both slicers using the eraser icon in each slicer's top-right corner to return to the national view.

Your dashboard should now tell a clear visual story: a title at the top, headline KPI numbers in a row, comparison and relationship charts side by side, and slicers that let any viewer drill into the data they care about.

KPI card colors not updating when you filter?

Conditional formatting on Card visuals recalculates based on the filtered data. If the filtered average stays in the same tier (for example, still above 80%), the color will not change.
Try filtering to a state with notably different performance to confirm the colors do shift between green, yellow, and red.
If colors never change regardless of filter, double-check your conditional formatting rules by selecting the card, opening Format visual, and clicking the fx button again to review the thresholds.

Need help? Help me debug why my Power BI card conditional formatting colors are not changing when I apply slicer filters.

Secret mission

Add a Geographic Map and Hospital Drill-Through Page

Your dashboard shows national and state-level trends, but it can't answer geographic questions at a glance or let you drill into individual hospitals. In this mission, you'll add a filled map that color-codes states by reputation score and a drill-through page that lists every hospital in a selected state.

Clean Up Your Resources

This project runs entirely on free tools with no cloud services or paid subscriptions. Decide whether to keep your resources running, pause them to come back later, or delete them to reclaim disk space.

Resources you used:

Windows 11 VM running in UTM (uses 30+ GB of disk space).
Power BI report (.pbix file) stored inside the VM.
HCAHPS CSV file stored inside the VM.
UTM app installed on your Mac.
CrystalFetch app installed on your Mac.

Keep everything running

No action needed. Choose this if you plan to keep building on your dashboard or want to explore Power BI further.

Your Windows VM stays running in UTM with Power BI Desktop and your report ready to go.
Reopen your .pbix file in Power BI Desktop anytime to iterate on your dashboard.
UTM and CrystalFetch remain on your Mac and are reusable for any future Power BI projects.

Pause - I'll come back to this later

Shut down the VM to free up memory while keeping all your files preserved on disk.

Save your Power BI report inside the VM before shutting down.
In UTM, click the stop button on your Windows 11 VM. You can also shut down Windows from the Start menu inside the VM.
Your VM files, Power BI report, and HCAHPS data are all preserved on disk.
To resume, open UTM and click the play button on your VM to restart it.

Delete - I don't want to use this again

Remove the VM and reclaim your disk space. This permanently deletes everything inside the VM, including your Power BI report and HCAHPS data.

If you want to keep your .pbix file, copy it from the VM to your Mac first using a shared folder or drag-and-drop before deleting.
In UTM, right-click the Windows 11 VM and select Delete. This removes the VM and frees up 30+ GB of disk space. Your HCAHPS CSV and Power BI report inside the VM are also removed.
To remove UTM from your Mac, open Finder, go to Applications, and drag UTM to the Trash.
To remove CrystalFetch, drag CrystalFetch from Applications to the Trash.

Nice Work!

You just built an executive-ready hospital communications dashboard powered by real U.S. government patient survey data in Power BI. This is the kind of analysis that communications and reputation measurement teams use to drive real decisions.

You've learned how to:

Set up a reusable Power BI Desktop development environment on your Mac using UTM and a Windows 11 VM.
Import, clean, and transform 325,000+ rows of real CMS patient survey data using Power Query to isolate hospital communication measures.
Design an interactive storytelling dashboard with KPI cards, comparison charts, a scatter plot, slicers, and conditional formatting that reveals which communication dimensions drive hospital reputation.
Secret Mission: Completed an optional challenge to push your Power BI skills further with advanced visualization and navigation features.

Ready to quiz yourself?