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

Challenge - 5 Problems

❓ visualization

intermediate

Identify the correct layout for showing hierarchical data

You want to display sales data grouped by Region, then by Country, and finally by City. Which visual layout is best suited for this hierarchical display?

A A Pie chart showing sales distribution by City

B A Table visual with Region, Country, and City as separate columns

C A Card visual showing total sales for each City

D A Matrix visual with Region as rows, Country as sub-rows, and City as sub-sub-rows

💡 Hint

Think about which visual allows expanding and collapsing hierarchical levels.

A Matrix visual supports hierarchical rows and allows users to drill down or expand levels, making it ideal for showing Region > Country > City.

❓ dax_lod_result

intermediate

Calculate total sales ignoring filters on Product Category

Given a sales table with columns: SalesAmount, ProductCategory, and Date, which DAX measure returns total sales ignoring any filter on ProductCategory but respects other filters?

Power BI

Total Sales Ignore Category = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[ProductCategory]))

A CALCULATE(SUM(Sales[SalesAmount]), REMOVEFILTERS(Sales[ProductCategory]))

B CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[ProductCategory]))

C SUM(Sales[SalesAmount])

D CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

💡 Hint

Use a function that removes filters only on ProductCategory.

ALL(Sales[ProductCategory]) removes filters on ProductCategory only, so the measure sums all sales ignoring that filter but respects others.

❓ data_modeling

advanced

Optimize a matrix visual with many columns for performance

You have a matrix visual showing monthly sales for 50 products over 3 years. The visual is slow to load. What is the best way to improve performance?

A Use a calculated column to pre-calculate monthly sales per product

B Add all products as columns instead of rows to reduce visual complexity

C Use a measure with SUM and filter context instead of calculated columns

D Remove the date filter to reduce the number of months shown

💡 Hint

Measures are generally more efficient than calculated columns for aggregations.

Using measures leverages DAX engine optimizations and reduces data model size, improving matrix visual performance.

🔧 Formula Fix

advanced

Identify the error causing incorrect subtotal in matrix visual

A matrix visual shows sales by Region and Product. The subtotal for Region shows zero instead of the sum. Which DAX measure causes this issue?

Sales Amount = IF(HASONEVALUE(Product[ProductName]), SUM(Sales[Amount]), 0)

Power BI

Sales Amount = IF(HASONEVALUE(Product[ProductName]), SUM(Sales[Amount]), 0)

A The measure returns 0 for subtotals because HASONEVALUE is false for multiple products

B The measure syntax is invalid and causes a syntax error

C The measure sums sales incorrectly because it uses SUM instead of SUMX

D The measure ignores filters on Region causing zero subtotal

💡 Hint

Check what HASONEVALUE returns when multiple products are in context.

HASONEVALUE returns false for subtotal rows with multiple products, so the measure returns 0 instead of sum, causing zero subtotal.

🧠 Conceptual

expert

Best practice for accessibility in matrix visuals

Which practice improves accessibility for users navigating a matrix visual with keyboard and screen readers?

A Use clear and descriptive column and row headers with ARIA labels

B Disable keyboard navigation to simplify interaction

C Use color only to indicate important data differences

D Hide all subtotals to reduce visual clutter

💡 Hint

Think about how screen readers identify table structure.

Descriptive headers and ARIA labels help screen readers understand the matrix layout, improving accessibility.