Reducing model size in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Column Data Types Impact on Model Size

Which of the following changes will most effectively reduce the size of a Power BI data model?

• A. Change columns storing whole numbers from Decimal Number to Whole Number data type.
• B. Convert text columns to Date/Time data type.
• C. Replace numeric columns with text columns.
• D. Use Fixed Decimal Number data type for all numeric columns.

A Replacing numeric columns with text columns reduces model size.

B Converting text columns to Date/Time always reduces model size.

C Using Fixed Decimal Number for all numeric columns reduces model size.

D Changing whole number columns from Decimal Number to Whole Number reduces storage size significantly.

💡 Hint

Think about how Power BI stores numbers internally and which data types use less space.

Whole Number data type uses less memory than Decimal Number because it stores integers more efficiently. Changing decimal columns that only contain whole numbers to Whole Number reduces model size.

❓ dax_lod_result

intermediate

Calculating Distinct Count with Filter to Reduce Model Size

Given a large sales table, you want to create a measure that counts distinct customers only for sales in the current year to reduce model size impact. Which DAX measure will produce the correct result?

Power BI

Measure = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), YEAR(Sales[Date]) = YEAR(TODAY()))

A Measure = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY())))

B Measure = DISTINCTCOUNT(FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY()))[CustomerID])

C Measure = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), Sales[Date] = TODAY())

D Measure = DISTINCTCOUNT(Sales[CustomerID])

💡 Hint

Remember that CALCULATE with FILTER applies row context correctly for filtering.

Option A uses CALCULATE with FILTER to apply a row context filter for the current year, correctly counting distinct customers only for that year. Option A is invalid because DISTINCTCOUNT cannot take a table as argument. Option A filters only today's date, not the whole year. Option A counts all customers without filtering.

❓ visualization

advanced

Designing a Dashboard to Highlight Model Size Reduction Impact

You want to create a Power BI dashboard that clearly shows the impact of reducing model size by removing unused columns and optimizing data types. Which visualization approach best communicates this?

A Use a table listing all columns with their data types.

B Use a stacked bar chart showing model size before and after optimization by category (e.g., columns removed, data type changes).

C Use a line chart showing sales over time before and after optimization.

D Use a pie chart showing percentage of total rows filtered out.

💡 Hint

Think about how to compare sizes visually and by category.

A stacked bar chart comparing model size before and after optimization by category clearly shows where size reductions happened. Pie charts and line charts do not effectively communicate size changes. A table listing columns is informative but less visual and harder to interpret quickly.

🔧 Formula Fix

advanced

Debugging a DAX Measure Causing Large Model Size

A DAX measure uses the following code to calculate total sales but causes the model size to grow unexpectedly. What is the main issue?

Power BI

Total Sales = SUMX(VALUES(Sales[OrderID]), Sales[Quantity] * Sales[Price])

A Using VALUES on Sales[OrderID] creates a large intermediate table increasing memory usage.

B SUMX cannot be used with VALUES function.

C The multiplication inside SUMX causes a syntax error.

D The measure should use CALCULATE instead of SUMX.

💡 Hint

Consider how VALUES works and what it returns.

VALUES(Sales[OrderID]) returns a distinct list of OrderIDs, which can be very large. SUMX iterates over this table, creating a large intermediate table in memory, increasing model size. Using SUM(Sales[Quantity] * Sales[Price]) or a simpler measure reduces memory usage.

🎯 Scenario

expert

Optimizing a Complex Model with Multiple Large Tables

Your Power BI model has multiple large tables with millions of rows. You want to reduce model size without losing critical analysis capabilities. Which combined approach is best?

A Remove all calculated columns, keep all data at the lowest granularity, and use DirectQuery mode.

B Keep all columns, convert all text columns to numeric codes, and disable auto date/time.

C Remove unused columns, convert numeric columns to Whole Number where possible, and aggregate data at a higher level before loading.

D Use only calculated tables, remove all relationships, and import all data as text.

💡 Hint

Think about practical ways to reduce size while keeping analysis possible.

Removing unused columns and converting numeric columns to Whole Number reduces size. Aggregating data before loading reduces row count. This combined approach balances size reduction and analysis needs. Other options either lose important data, increase size, or reduce performance.