Slicers (list, dropdown, between) in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants an interactive sales report where they can filter sales data by product category, region, and date range easily.

📊 Data: You have a sales dataset with columns: Date, Region, Product Category, Sales Amount.

🎯 Deliverable: Create a Power BI report with slicers: a list slicer for Product Category, a dropdown slicer for Region, and a between slicer for Date to filter the sales data dynamically.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	North	Electronics	1200
2024-01-10	South	Clothing	850
2024-01-15	East	Electronics	950
2024-01-20	West	Furniture	700
2024-02-05	North	Clothing	1100
2024-02-10	South	Furniture	650
2024-02-15	East	Clothing	900
2024-02-20	West	Electronics	1300
2024-03-05	North	Furniture	800
2024-03-10	South	Electronics	1400
2024-03-15	East	Furniture	750
2024-03-20	West	Clothing	900

1

Step 1: Load the sales data into Power BI Desktop by importing the table with columns Date, Region, Product Category, and Sales Amount.

Use 'Get Data' > 'Excel' or 'CSV' depending on your file format, then load the data.

Expected Result

Sales data is loaded and visible in the Fields pane.

2

Step 2: Create a list slicer for Product Category to allow selecting one or multiple categories.

Insert a slicer visual, drag 'Product Category' field to it, and set slicer type to 'List'.

Expected Result

A vertical list slicer showing all product categories: Electronics, Clothing, Furniture.

3

Step 3: Create a dropdown slicer for Region to save space and allow single selection.

Insert another slicer visual, drag 'Region' field to it, and set slicer type to 'Dropdown'.

Expected Result

A dropdown slicer with options: North, South, East, West.

4

Step 4: Create a between slicer for Date to filter sales by a date range.

Insert a slicer visual, drag 'Date' field to it, and set slicer type to 'Between'.

Expected Result

A date range slicer allowing selection of start and end dates.

5

Step 5: Add a card visual to show total sales amount that updates based on slicer selections.

Insert a Card visual, drag 'Sales Amount' field, and set aggregation to SUM.

Expected Result

Card shows total sales amount filtered by slicers.

6

Step 6: Test the slicers by selecting different product categories, regions, and date ranges to see the total sales update accordingly.

Interact with slicers on the report page.

Expected Result

Total sales amount changes dynamically based on slicer filters.

Final Result

↓

↓

↓

North

↓

2024-01-01

✓Slicers allow quick filtering of sales data by product category, region, and date range.

✓The total sales card updates instantly to reflect the selected filters.

✓Using different slicer types improves report usability and saves space.

Bonus Challenge

Add a slicer for Sales Amount using a numeric range slider to filter sales transactions by amount.

Show Hint

Insert a slicer, drag 'Sales Amount' field, and set slicer type to 'Between' to create a numeric range filter.

Practice

1.

What is the main purpose of a slicer in Power BI?

easy

A. To import data from external sources

B. To create calculated columns

C. To write DAX formulas

D. To filter report data visually and interactively

Solution

1. Step 1: Understand slicer functionality

   Slicers are visual tools that let users filter data on reports easily by selecting values.

2. Step 2: Compare options to slicer purpose

   Only To filter report data visually and interactively describes filtering data visually and interactively, which matches slicer use.

3. Final Answer:

   To filter report data visually and interactively -> Option D

4. Quick Check:

   Slicer = Visual filter [OK]

Hint: Slicers filter data visually, not for calculations [OK]

Common Mistakes:

• Confusing slicers with data import tools
• Thinking slicers create calculations
• Assuming slicers write DAX formulas

2.

Which slicer type should you use to let users select a range of dates?

easy

A. Between slicer

B. List slicer

C. Dropdown slicer

D. Checkbox slicer

Solution

1. Step 1: Identify slicer types for date ranges

   Between slicers allow users to pick a start and end value, ideal for date or numeric ranges.

2. Step 2: Eliminate other slicer types

   List and dropdown slicers select single or multiple discrete values, not ranges. Checkbox slicer is not a standard type.

3. Final Answer:

   Between slicer -> Option A

4. Quick Check:

   Date range = Between slicer [OK]

Hint: Use between slicer for numeric or date ranges [OK]

Common Mistakes:

• Choosing list or dropdown for ranges
• Confusing checkbox slicer with dropdown
• Not knowing between slicer exists

3.

Given a dropdown slicer on a "Category" column with values: "A", "B", "C", what happens if the user selects "B"?

medium

A. The report filters to show rows where Category is blank

B. The report shows all rows except those where Category = "B"

C. The report filters to show only rows where Category = "B"

D. The report shows an error because dropdown slicers can't filter text

Solution

1. Step 1: Understand dropdown slicer behavior

   Dropdown slicers filter the report to show only the selected value(s) from the column.

2. Step 2: Apply selection "B" to filter

   Selecting "B" filters the data to rows where Category equals "B" only.

3. Final Answer:

   The report filters to show only rows where Category = "B" -> Option C

4. Quick Check:

   Dropdown slicer selection = Filtered data [OK]

Hint: Dropdown slicer shows only selected value's data [OK]

Common Mistakes:

• Thinking it excludes selected value
• Assuming it filters blanks
• Believing dropdown slicers can't filter text

4.

You created a between slicer on a numeric "Sales" column but it shows no slider or range. What is the likely problem?

medium

A. Between slicers only work on date columns

B. The "Sales" column is stored as text, not numeric

C. The report page is filtered to zero rows

D. The slicer type is set to dropdown instead of between

Solution

1. Step 1: Check data type for between slicer

   Between slicers require numeric or date data types to show a range slider.

2. Step 2: Identify issue with "Sales" column

   If "Sales" is text, the slicer cannot create a numeric range slider, causing no slider to appear.

3. Final Answer:

   The "Sales" column is stored as text, not numeric -> Option B

4. Quick Check:

   Between slicer needs numeric/date type [OK]

Hint: Check column data type if between slicer shows no slider [OK]

Common Mistakes:

• Assuming between slicers only work on dates
• Not verifying slicer type setting
• Ignoring data type mismatch

5.

You want users to select multiple product categories but keep the slicer compact in the report. Which slicer type and setting should you choose?

hard

A. Dropdown slicer with multi-select enabled and single select off

B. Between slicer on the category column

C. List slicer with multi-select enabled and single select off

D. List slicer with single select enabled

Solution

1. Step 1: Understand slicer types for multi-select and compactness

   Dropdown slicers take less space than list slicers and support multi-select when single select is off.

2. Step 2: Evaluate options for multi-select and compactness

   List slicers with multi-select are larger; between slicers don't apply to categories; single select disables multi-select.

3. Final Answer:

   Dropdown slicer with multi-select enabled and single select off -> Option A

4. Quick Check:

   Compact multi-select = Dropdown slicer [OK]

Hint: Use dropdown slicer with multi-select enabled and single select off for compact multiple picks [OK]

Common Mistakes:

• Choosing list slicer for compactness
• Using between slicer on text categories
• Enabling single select disables multi-select