Table and matrix visuals in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ visualization

intermediate

Identify the correct way to display hierarchical data in a matrix visual

You have sales data by Region, Country, and City. You want to show this hierarchy in a matrix visual so users can expand and collapse levels.

Which option correctly configures the matrix visual to achieve this?

A Add Region, Country, and City fields to Rows area in order: Region, Country, City

B Add Region, Country, and City fields to Columns area in order: City, Country, Region

C Add City, Country, and Region fields to Values area

D Add Region to Rows, Country to Columns, City to Values

💡 Hint

Think about how to create drill-down levels in a matrix visual.

Adding the fields in the Rows area in hierarchical order allows users to expand and collapse levels in the matrix.

❓ dax_lod_result

intermediate

Calculate total sales per product category ignoring filters on subcategory

You have a table visual showing sales by Product Subcategory. You want to add a measure that shows total sales per Product Category ignoring any filters on Subcategory.

Which DAX measure will produce the correct result?

Power BI

Total Sales by Category = CALCULATE(SUM(Sales[Amount]), ALL(Product[Subcategory]))

A SUMX(VALUES(Product[Category]), SUM(Sales[Amount]))

B CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Product, Product[Category]))

C CALCULATE(SUM(Sales[Amount]), ALL(Product[Subcategory]))

D CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Product[Category]))

💡 Hint

Use a function that removes filters on Subcategory but keeps others.

ALL(Product[Subcategory]) removes filters on Subcategory, so total sales per Category is calculated ignoring Subcategory filters.

❓ data_modeling

advanced

Optimize a table visual with many columns for performance

You have a table visual with 20 columns from a large dataset. The report is slow to load and interact with.

Which approach will best improve performance without losing important data?

A Increase the number of rows shown per page to reduce pagination

B Add all columns to the visual and enable row-level security

C Use calculated columns for all fields instead of measures

D Remove unnecessary columns and use aggregations or measures instead of raw columns

💡 Hint

Think about reducing data volume and complexity in visuals.

Removing unused columns and using measures reduces data load and speeds up visuals.

🔧 Formula Fix

advanced

Identify the cause of incorrect subtotal values in a matrix visual

A matrix visual shows sales by Region and Product. The subtotal for Region shows a value smaller than the sum of products.

What is the most likely cause?

A The measure used applies a filter that removes some products at subtotal level

B The matrix visual is missing the Region field in Rows area

C The data model has no relationship between Region and Product tables

D The subtotal row is disabled in matrix visual settings

💡 Hint

Consider how measures behave differently at subtotal levels.

Measures with filters or conditions can return unexpected subtotal values because filter context changes.

🎯 Scenario

expert

Design a matrix visual to show monthly sales with dynamic conditional formatting

You want a matrix visual showing monthly sales by Product Category and Region. You want to highlight months where sales are below the average monthly sales for that category.

Which approach will achieve this dynamic conditional formatting?

A Manually set colors for each month column in the matrix visual

B Create a measure calculating average monthly sales per category, then use it in conditional formatting rules on the matrix values

C Use a calculated column to flag months below average and filter the matrix visual

D Apply conditional formatting based on total sales without considering category averages

💡 Hint

Think about how to use measures in conditional formatting.

Measures can dynamically calculate averages and be used in conditional formatting rules to highlight values below average.