Report-level filters in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to retrieve the report-level filter on the 'Region' field.

Power BI

FILTERS('Sales'[[1]])

Drag options to blanks, or click blank then click option'

ADate

BRegion

CProduct

DCustomer

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a field that is not present in the report-level filter pane.

Using a field that filters only one page instead of the whole report.

The 'Region' field is used to filter the entire report at the report level.

2fill in blank

medium

Complete the DAX expression to check if the report-level filter on 'Year' is active.

Power BI

IF(HASONEVALUE('Date'[[1]]), "Filtered", "Not Filtered")

Drag options to blanks, or click blank then click option'

AQuarter

BMonth

CDay

DYear

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Month' or 'Day' which are more granular than the report-level filter intended.

Confusing 'Quarter' with 'Year'.

Checking 'Year' ensures the report-level filter on the year is active.

3fill in blank

hard

Fix the error in the DAX formula to correctly ignore the report-level filter on 'Category'.

Power BI

CALCULATE(SUM('Sales'[Amount]), ALL('Product'[[1]]))

Drag options to blanks, or click blank then click option'

ACategory

BSubcategory

CProductName

DBrand

Attempts:

3 left

💡 Hint

Common Mistakes

Using a more detailed field like 'ProductName' which does not match the report-level filter.

Using a field unrelated to the filter context.

Using ALL on 'Category' removes filters on that field, which is useful to ignore report-level filters on 'Category'.

4fill in blank

hard

Fill both blanks to create a measure that respects report-level filters on 'Region' and 'Year'.

Power BI

CALCULATE(SUM('Sales'[Amount]), [1]('Sales'[Region]), [2]('Date'[Year]))

Drag options to blanks, or click blank then click option'

AFILTER

BALL

CVALUES

DREMOVEFILTERS

Attempts:

3 left

💡 Hint

Common Mistakes

Using ALL or REMOVEFILTERS removes filters instead of respecting them.

Using FILTER requires a table expression, which is more complex here.

Using VALUES keeps the current filter context on 'Region' and 'Year' from report-level filters.

5fill in blank

hard

Fill all three blanks to create a measure that ignores report-level filters on 'Category' and 'Year' but respects 'Region'.

Power BI

CALCULATE(SUM('Sales'[Amount]), [1]('Product'[Category]), [2]('Date'[Year]), [3]('Sales'[Region]))

Drag options to blanks, or click blank then click option'

AREMOVEFILTERS

BALL

CVALUES

DFILTER

Attempts:

3 left

💡 Hint

Common Mistakes

Using VALUES for all blanks, which keeps all filters.

Using ALL for 'Region' which removes its filter.

ALL removes filters on 'Category' and 'Year', while VALUES keeps the filter on 'Region'.

Practice

1. What is the main purpose of a report-level filter in Power BI?

easy

A. To filter data only on a single visual

B. To filter data across all pages in the report

C. To filter data only on the current page

D. To create a new calculated column

Solution

1. Step 1: Understand filter scope

   Report-level filters apply to the entire report, affecting all pages.

2. Step 2: Compare filter types

   Page-level filters affect one page; visual-level filters affect one visual only.

3. Final Answer:

   To filter data across all pages in the report -> Option B

4. Quick Check:

   Report-level filter = all pages [OK]

Hint: Report-level filters affect all pages, not just one [OK]

Common Mistakes:

• Confusing report-level with page-level filters
• Thinking report-level filters apply only to visuals
• Assuming report-level filters create new columns

2. Which of the following is the correct way to set a report-level filter in Power BI?

easy

A. Drag a field to the 'Filters on all pages' pane

B. Drag a field to the 'Filters on this page' pane

C. Drag a field to the 'Filters on this visual' pane

D. Create a new measure with a filter condition

Solution

1. Step 1: Locate the Filters pane

   Power BI has separate filter areas: visual, page, and report level.

2. Step 2: Identify report-level filter area

   The 'Filters on all pages' section is where report-level filters are set.

3. Final Answer:

   Drag a field to the 'Filters on all pages' pane -> Option A

4. Quick Check:

   Report-level filter = 'Filters on all pages' [OK]

Hint: Report-level filters go under 'Filters on all pages' [OK]

Common Mistakes:

• Dragging fields to visual or page filter areas instead
• Trying to set report-level filters via measures
• Confusing filter panes with slicers

3. If you apply a report-level filter to show only sales from 2023, what will happen to the visuals on all pages?

medium

A. All visuals on all pages will show only 2023 sales data

B. Only visuals on the current page will show 2023 sales data

C. Visuals will show all sales data regardless of the filter

D. Only visuals with manual filters will show 2023 sales data

Solution

1. Step 1: Understand report-level filter effect

   Report-level filters apply to every page and visual in the report.

2. Step 2: Apply filter context

   Filtering sales to 2023 means all visuals only see 2023 data.

3. Final Answer:

   All visuals on all pages will show only 2023 sales data -> Option A

4. Quick Check:

   Report-level filter limits all pages [OK]

Hint: Report-level filters limit data everywhere in the report [OK]

Common Mistakes:

• Thinking filter affects only current page
• Assuming visuals ignore report-level filters
• Confusing report-level with visual-level filters

4. You set a report-level filter on 'Region' to only include 'East'. However, some visuals still show data from other regions. What is the likely cause?

medium

A. The data model has no 'Region' field

B. The visuals have their own page-level filters overriding the report-level filter

C. The report-level filter was set incorrectly in the 'Filters on this visual' pane

D. Report-level filters do not affect visuals

Solution

1. Step 1: Check filter placement

   Report-level filters must be set in 'Filters on all pages', not 'Filters on this visual'.

2. Step 2: Understand filter precedence

   Filters on visuals or pages cannot override report-level filters, but if report-level filter is misplaced, it won't apply globally.

3. Final Answer:

   The report-level filter was set incorrectly in the 'Filters on this visual' pane -> Option C

4. Quick Check:

   Report-level filter must be in 'Filters on all pages' [OK]

Hint: Set report-level filters only in 'Filters on all pages' pane [OK]

Common Mistakes:

• Setting report-level filters in visual or page filter areas
• Assuming report-level filters don't affect visuals
• Ignoring data model fields existence

5. You want to create a report that shows sales data only for products launched after 2020 across all pages. How should you apply this filter?

hard

A. Add a page-level filter on each page for 'Launch Year' after 2020

B. Create a calculated column filtering products launched after 2020

C. Use a slicer on one page and expect it to filter all pages automatically

D. Create a report-level filter on the 'Launch Year' field to include only years after 2020

Solution

1. Step 1: Identify filter scope needed

   The filter must apply to all pages, so report-level filter is best.

2. Step 2: Apply filter on 'Launch Year'

   Set report-level filter on 'Launch Year' to include only years after 2020.

3. Step 3: Evaluate other options

   Page-level filters require repeating on each page; slicers don't affect all pages by default; calculated columns don't filter data automatically.

4. Final Answer:

   Create a report-level filter on the 'Launch Year' field to include only years after 2020 -> Option D

5. Quick Check:

   Report-level filter = all pages filter [OK]

Hint: Use report-level filter for consistent filtering across all pages [OK]

Common Mistakes:

• Using page-level filters instead of report-level
• Expecting slicers to filter all pages automatically
• Trying to filter by calculated columns without measures