Data model best practices in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Star Schema Benefits

Which of the following is the main advantage of using a star schema in a Power BI data model?

A It duplicates data across multiple tables to increase redundancy.

B It stores all data in one large table to avoid joins.

C It simplifies queries and improves performance by having a central fact table connected to dimension tables.

D It uses many-to-many relationships extensively to connect tables.

💡 Hint

Think about how organizing data affects query speed and simplicity.

A star schema organizes data with a central fact table and related dimension tables, which makes queries simpler and faster. Other options either reduce performance or complicate the model.

❓ dax_lod_result

intermediate

Calculating Total Sales by Region with Filter

Given a Sales table with columns Region and SalesAmount, which DAX measure correctly calculates total sales only for the 'West' region?

A TotalSalesWest = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Region] = "West"))

B TotalSalesWest = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "West")

C TotalSalesWest = SUMX(FILTER(Sales, Sales[Region] = "West"), Sales[SalesAmount])

D TotalSalesWest = SUM(Sales[SalesAmount]) WHERE Sales[Region] = "West"

💡 Hint

Remember that CALCULATE needs a filter expression or a filter table.

Option A uses CALCULATE with FILTER to apply a row context filter correctly. Option A is invalid syntax because filters inside CALCULATE must be filter expressions or tables. Option A works but is less efficient. Option A is invalid DAX syntax.

❓ visualization

advanced

Best Visualization for Showing Sales Trends Over Time

You want to show how sales change month by month for the last year. Which visualization type is best suited for this purpose in Power BI?

A Line chart with months on the x-axis and sales on the y-axis.

B Stacked bar chart showing sales by product category.

C Pie chart showing sales distribution by region.

D Table listing sales amounts for each month.

💡 Hint

Think about which chart type best shows changes over time.

A line chart is ideal for showing trends over time because it clearly displays continuous data points along a timeline. Other options do not effectively show trends.

🔧 Formula Fix

advanced

Fixing a Relationship Cardinality Issue

You created a relationship between a Customers table and an Orders table in Power BI. The relationship is set as one-to-one, but you notice incorrect aggregation results. What is the most likely cause?

A The relationship should be inactive to avoid aggregation errors.

B The relationship should be one-to-many from Customers to Orders, not one-to-one.

C The relationship should be many-to-many to allow multiple customers per order.

D The relationship cardinality does not affect aggregation results.

💡 Hint

Think about how customers and orders relate in real life.

Typically, one customer can have many orders, so the relationship should be one-to-many. Setting it as one-to-one causes incorrect aggregations because it limits the data model's understanding of the data structure.

🎯 Scenario

expert

Optimizing a Large Data Model for Performance

Your Power BI report is slow because the data model has millions of rows and many calculated columns. Which approach will best improve performance without losing necessary detail?

A Import all data without filtering to keep the model complete.

B Add more calculated columns to pre-calculate values and speed up queries.

C Disable relationships to reduce model complexity.

D Replace calculated columns with measures where possible and use aggregations to reduce data volume.

💡 Hint

Think about how calculated columns and measures affect model size and query speed.

Measures are calculated on the fly and do not increase model size, unlike calculated columns. Using aggregations reduces data volume. Adding calculated columns or disabling relationships can harm performance or data integrity.