Do you want to level up your Business Intelligence (BI) skills? Whether you’re just starting out or looking to brush up on SQL, DAX, and KPIs, practice makes perfect. These 12 hands-on exercises are fun, simple, and super helpful. Plus, you’ll better understand real-world data problems. Ready? Let’s dive in!
1. Classic Inner Join – Find Common Ground
In this exercise, use SQL INNER JOIN to connect two tables: Orders and Customers. Each order has a customer ID.
Your task? Get a list of orders along with customer names.
- Start with
SELECTstatements - Use an
INNER JOINon CustomerID - Filter orders from the past year
This will prepare you for more complex joins later.
2. LEFT JOIN – No Customer? No Problem!
Now let’s look at what’s missing. Use SQL LEFT JOIN to find orders that don’t have a matching customer.
This could happen if there’s missing data. That’s common in the real world!
Your goal: List all orders and bring in customer info where available.
3. RIGHT JOIN Magic – Flip It Around
Are customers placing orders? Or are some sitting idle?
Use RIGHT JOIN to find all customers and check if they’ve ordered anything.
Bonus: Add a column to say “Yes” or “No” for active customers!
4. FULL OUTER JOIN – See the Whole Picture
Alright! Let’s combine everything we’ve learned.
With a FULL OUTER JOIN, you’ll catch all discrepancies between Orders and Customers.
This trick is great for audits and data quality checks.
5. Build a KPI Dashboard with DAX
Time to move into Power BI. Use DAX (Data Analysis Expressions) to build KPIs.
Let’s make a Card visual that shows:
- Total Sales
- Number of Orders
- Average Sales per Order
Start with basic DAX measures like:
Total Sales = SUM(Orders[Amount])
Order Count = COUNTROWS(Orders)
Average Sale = [Total Sales] / [Order Count]
Simple but super satisfying to see on your screen!
6. Compare This Year vs Last Year with DAX
This is a powerful one. Use SAMEPERIODLASTYEAR() to create year-over-year comparisons.
You’ll see if your numbers are growing or shrinking.
Try this DAX:
Last Year Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date]))
Super useful for business reviews!
7. Create a KPI Trend Line with Visualization
Use a Line chart to show sales over time.
Add a trend line for both actual and last year sales.
It tells a story. Everyone loves a good story!
Use some color magic here. Make last year’s line lighter. Keep this year bold and bright.
8. Highlight the Best (and Worst) Using Conditional Formatting
In Power BI tables, use Conditional Formatting to show impact.
Want to highlight top 5 customers? Use DAX ranking:
Sales Rank = RANKX(ALL(Customers), [Total Sales], , DESC)
Then color the top 5 in green. Bottom 5? Red!
9. Create a Dynamic Date Filter
Sometimes, you don’t want static visuals.
Add a slicer in Power BI for dates. Let users pick:
- Last 30 Days
- This Month
- Year to Date
Make sure your DAX measures respect the slicer – that’s key!
10. Join Products and Sales to Analyze Performance
Back to SQL! Time for some analysis.
Join Products with Sales table.
Use GROUP BY to get total sales for each product.
Ready for the twist?
Add a CASE statement to label products as:
- “Top Seller”
- “Moderate”
- “Needs Review”
Now you’re not just cleaning data. You’re giving it meaning!
11. Row-Level Security – Let’s Get Personal!
BI isn’t just about crunching numbers. It’s also about showing the right data to the right person.
Use DAX to apply Row-Level Security.
Create Roles like: Regional Manager. Filter data by region.
Set it in Power BI Desktop under the Modeling tab.
Now, when users log in, they only see their region’s data. Powerful stuff!
12. Final KPI Game: Traffic Lights System
Visuals are even better with symbols!
Create a KPI card with a colored circle:
- Green: On Target
- Yellow: Needs Attention
- Red: Bad Day!
Use DAX like this:
KPI Status = SWITCH(TRUE(),
[Total Sales] >= 100000, "Green",
[Total Sales] >= 50000, "Yellow",
"Red"
)
Then use an icon set to bring those colors to life.
Wrap Up
These 12 BI exercises cover all the essential skills:
- Joins in SQL to connect data
- DAX to calculate smart metrics
- KPIs to show critical results
With daily practice, you’ll get faster, smarter, and more confident.
You can even turn this into a mini portfolio!
Want extra credit? Challenge yourself:
- Use UNION and INTERSECT in SQL
- Make a rolling average in DAX
- Add bookmarks for interactive storytelling
BI is everywhere, and these skills are in high demand.
Practice a little every day and you’ll be a BI wizard in no time!
