Hands-On Case Study

Build a Complete Gym Performance Analytics Dashboard

A comprehensive, hands-on project that takes you through the entire Power BI workflow using real gym workout data.

Estimated Time: 4-6 hours

Scenario

You're a fitness analyst tasked with building a performance dashboard to track workout progress, identify trends, and provide insights for training optimization. You have workout data from multiple sources that needs to be consolidated, cleaned, modeled, and visualized.

Prerequisites

Power BI Desktop installed (latest version)
Basic understanding of fitness/gym terminology (sets, reps, exercises)
Access to download the provided data files (3 CSV files in ZIP format)

Download Data Files

Download all three data files in one convenient package. Extract the ZIP file and you'll have everything you need to complete the case study.

Download All Data Files (ZIP)

Included Files:

workout_sets.csv

Fact table with all workout sets (date, exercise, weight, reps, etc.)

dim_exercises.csv

Dimension table with exercise names and body part mappings

dim_body_parts.csv

Dimension table with muscle group reference data

Implementation Steps

Connect to CSV Data Source

Instructions

Import workout_sets.csv using the Text/CSV connector in Power BI Desktop. Configure data type detection and examine column quality.

Expected Outcome

You should see the workout_sets query in Power Query Editor with columns: id, date, exercise, weight, reps, set_number, timestamp, day_tag, is_unilateral, equipment.

Validation

Check the status bar - should show row count (likely 1000+). Verify 'date' is Date type and 'weight'/'reps' are Whole Number type.

Profile and Clean the CSV Data

Instructions

Enable Column quality, Column distribution, and Column profile (based on entire dataset). Remove any rows with errors or nulls in critical fields (date, exercise, weight, reps). Check for duplicate sets.

Expected Outcome

Data quality indicators show valid/error/empty percentages. Critical columns should show 100% valid. Column profile reveals data distributions.

Validation

View > Column quality/distribution/profile should all be enabled. Check the quality bar above each column - aim for no errors in date/exercise/weight/reps.

Transform and Enrich CSV Data

Instructions

Add custom columns: 'volume' (weight * reps), 'estimated_1rm' (weight * (1 + reps/30)). Extract 'year', 'month', 'day_of_week' from the date column. Set appropriate data types for new columns.

Expected Outcome

New calculated columns appear with correct formulas. Date-based columns show proper formatting.

Validation

Sort by volume descending - highest values should be heavy, high-rep sets. Check that day_of_week shows day names, not numbers.

Import Exercise Dimension Data

Instructions

Import dim_exercises.csv using the Text/CSV connector. This contains the exercise catalog with body part mappings. Review the data structure and column types.

Expected Outcome

dim_exercises query appears with columns: id, exercise_name, body_part_key, is_active.

Validation

Exercise names should match those in workout_sets. Body_part_key values should be text (e.g., 'chest', 'back', 'quads').

Import Body Parts Dimension Data

Instructions

Import dim_body_parts.csv using the Text/CSV connector. This is the reference table for muscle groups. Verify data quality and structure.

Expected Outcome

dim_body_parts query appears with columns: body_part_key, body_part_label.

Validation

Should see entries like 'chest', 'back', 'quads', 'hamstrings', etc. Keys should match those in dim_exercises.

Merge Queries - Add Body Part Context

Instructions

Merge workout_sets with dim_exercises on exercise name using a Left Outer join. Then merge the result with dim_body_parts on body_part_key. Expand both merges to include body_part_key and body_part_label.

Expected Outcome

workout_sets query now includes body_part_key and body_part_label columns showing which muscle group each exercise targets.

Validation

Exercises like 'Bench Press' should show 'chest', 'Squat' should show 'quads'. Check for null body parts - indicates unmatched exercises.

Create Date Table

Instructions

Create a new Date table using DAX (CALENDAR or CALENDARAUTO). Add calculated columns for Year, Quarter, Month, MonthName, WeekNum, DayOfWeek. Mark this as a Date table in Model view.

Expected Outcome

A Date dimension table with continuous dates covering your workout date range, plus all date hierarchy columns.

Validation

Table Tools > Mark as date table should succeed without errors. Date range should span from earliest to latest workout date.

Build the Data Model Relationships

Instructions

In Model view, create relationships: workout_sets[date] to Date[Date] (many-to-one), workout_sets[exercise] to dim_exercises[exercise_name] (many-to-one), dim_exercises[body_part_key] to dim_body_parts[body_part_key] (many-to-one). Set all to Single direction except where bi-directional is needed.

Expected Outcome

Star schema with workout_sets as fact table, Date, dim_exercises, and dim_body_parts as dimension tables. Relationship lines connect the tables.

Validation

All relationships should show '1' on dimension side and '*' on fact side. No broken (dotted) relationship lines.

Create DAX Measures for Key Metrics

Instructions

Create measures: Total Volume = SUM([volume]), Avg Weight = AVERAGE([weight]), Total Sets = COUNTROWS(workout_sets), Estimated Max 1RM = MAX([estimated_1rm]), Total Workouts = DISTINCTCOUNT([date]).

Expected Outcome

Five new measures appear in the Fields pane under workout_sets table with proper aggregation logic.

Validation

Create a card visual with each measure - values should be reasonable (Total Volume in thousands, Total Sets in hundreds, etc.).

Build Volume Trend Line Chart

Instructions

Create a Line chart with Date[Date] on X-axis and Total Volume on Y-axis. Add a trend line. Configure date hierarchy to show by Month or Week.

Expected Outcome

Line chart showing volume trends over time with clear upward/downward patterns.

Validation

Chart should show multiple data points across time. Trend line should be visible. Hovering over points shows date and volume values.

Build Exercise Performance Matrix

Instructions

Create a Matrix visual with dim_exercises[exercise_name] on Rows, Date[Year] and Date[Month] on Columns, and Total Volume in Values. Apply conditional formatting (Data bars or Color scale) to volume values.

Expected Outcome

Matrix showing exercises as rows, months as columns, with volume values color-coded by intensity.

Validation

Expand/collapse hierarchy works. Color gradient clearly shows high vs low volume periods. Right-click column headers to verify drill-down capability.

Create Body Part Distribution Donut Chart

Instructions

Create a Donut chart with dim_body_parts[body_part_label] as Legend and Total Sets as Values. Format with percentage labels and meaningful colors.

Expected Outcome

Donut chart showing proportion of training volume across different muscle groups.

Validation

All body parts should be represented. Percentages should sum to 100%. Chart legend is readable and colors are distinct.

Build Top Exercises Bar Chart with Dynamic TopN

Instructions

Create a Bar chart showing top 10 exercises by Total Volume. Add a slicer or parameter to allow users to change the TopN value (5, 10, 15, 20).

Expected Outcome

Horizontal bar chart with exercises ranked by volume. Dynamic filter allows changing how many exercises are displayed.

Validation

Adjusting the TopN slicer should change the number of bars shown. Bars should be sorted descending by volume.

Add Slicers for Interactive Filtering

Instructions

Add slicers for Date[Year], Date[Month], dim_body_parts[body_part_label], and workout_sets[day_tag]. Configure slicer styles (dropdown, list, or tile). Test cross-filtering behavior across all visuals.

Expected Outcome

Four slicers on the report page. Selecting any slicer value filters all visuals on the page.

Validation

Select a specific body part - all visuals should update to show only that muscle group's data. Clear filters should reset all visuals.

Configure Drillthrough Page

Instructions

Create a new report page named 'Exercise Detail'. Add exercise_name to the drillthrough filter well. Build detail visuals: volume by date (line chart), set-by-set breakdown (table), and personal records (cards).

Expected Outcome

Drillthrough page shows detailed analysis for a single exercise. Back button appears automatically.

Validation

From the main page, right-click any exercise in a visual > Drillthrough > Exercise Detail. Should navigate to detail page filtered to that exercise.

Create Bookmarks for View States

Instructions

Create bookmarks for different report states: 'All Data' (no filters), 'Upper Body Focus' (chest, back, shoulders), 'Lower Body Focus' (legs, glutes), 'Last 90 Days'. Add buttons to navigate between bookmarks.

Expected Outcome

Bookmark pane shows 4 saved states. Buttons on the report allow one-click switching between views.

Validation

Click each bookmark button - filters should apply/clear automatically. Bookmarks should remember slicer states and visual selections.

Apply Conditional Formatting

Instructions

Add conditional formatting to the matrix: background color gradient for volume values, data bars for the exercise performance table, and icons for trending indicators (up/down/flat).

Expected Outcome

Visuals now use color and symbols to highlight high/low values and trends at a glance.

Validation

High volume cells should be darker/more intense. Trend icons should appear next to values. Color scale should be intuitive (green=high, red=low or vice versa).

Design Custom Tooltip Page

Instructions

Create a tooltip report page (set page size to Tooltip). Add summary cards showing: Total Volume, Avg Weight, Total Sets, Estimated Max. Configure specific visuals to use this tooltip page.

Expected Outcome

Hovering over data points in designated visuals shows the custom tooltip with detailed metrics.

Validation

Hover over any bar/point in configured visuals - custom tooltip appears instead of default. Tooltip shows metrics relevant to the hovered data point.

Optimize Model Performance

Instructions

Review column data types - ensure no unnecessary Text columns (use Whole Number for numeric IDs). Remove unused columns from model. Check column cardinality and consider removing high-cardinality columns not used in visuals.

Expected Outcome

Model size decreases. Report loads faster. Unnecessary columns are hidden or removed.

Validation

Check Model view - hidden columns should be dimmed. File size (check File > Options) should be optimized. No errors in DAX measures.

Publish to Power BI Service

Instructions

Save the .pbix file. Publish to Power BI Service workspace. Verify the report renders correctly in the browser. Check that all interactions work in the service.

Expected Outcome

Report appears in Power BI Service. All visuals, slicers, bookmarks, and drillthrough work as expected online.

Validation

Open published report in browser - all pages load. Test interactions (slicers, bookmarks, drillthrough) in the service. No error messages.

Configure Row-Level Security (RLS)

Instructions

In Desktop, create an RLS role (e.g., 'UserFilter') with a DAX filter on a user-specific field (or simulate with date range). Test the role using 'View as'. Publish and assign users/groups in the service.

Expected Outcome

RLS role is created and tested. When viewing as the role, data is filtered appropriately.

Validation

In Desktop: Modeling tab > Manage roles > View as role - data should be filtered. In Service: dataset settings show role assignments.

Set Up Scheduled Refresh

Instructions

In Power BI Service, go to dataset Settings. Configure data source credentials (for API and any other sources). Set up a scheduled refresh (e.g., daily at 6 AM). Run a manual refresh to test.

Expected Outcome

Scheduled refresh is configured. Manual refresh completes successfully with no errors.

Validation

Dataset settings > Refresh history shows 'Completed' status. Schedule is active and shows next refresh time.

Create and Publish an App

Instructions

In the workspace, create an App. Configure app settings: name, description, theme color. Select which reports/dashboards to include. Define permissions and audience. Publish the app.

Expected Outcome

App is published and accessible to specified users. App provides a curated experience with selected content.

Validation

Install the app from 'Apps' section - content loads correctly. Users without direct workspace access can view the app.

Monitor with Usage Metrics

Instructions

Enable usage metrics for the report. Review the usage metrics report to see: view counts, unique viewers, most viewed pages, popular visuals. Identify engagement patterns.

Expected Outcome

Usage metrics report is available showing report consumption data and user engagement.

Validation

From report menu: Metrics > View usage metrics report. Should show views, viewers, and page-level metrics. Data updates as users interact with the report.

Implement Performance Tuning

Instructions

Use Performance Analyzer to identify slow visuals. Review DAX measures for optimization opportunities. Check query folding status in Power Query. Reduce visual count per page if needed.

Expected Outcome

Performance Analyzer shows query times for each visual. Bottlenecks are identified and addressed.

Validation

View > Performance Analyzer > Start recording > Refresh visuals. Review timings - most visuals should load under 1 second. Query folding indicators show steps that fold.

Ready to Get Started?

Download the data files, fire up Power BI Desktop, and work through each step. This hands-on experience will reinforce the concepts you've learned and prepare you for the real exam.

Download Data Files Back to Practice Questions