Composite models in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Composite Models in Power BI

Which statement best describes a composite model in Power BI?

A A model that duplicates data across multiple tables to speed up queries.

B A model that only uses Import mode for all data sources to improve performance.

C A model that uses only DirectQuery mode to ensure real-time data access.

D A model that combines data from both DirectQuery and Import storage modes in a single report.

💡 Hint

Think about how Power BI can mix data storage modes.

Composite models allow combining DirectQuery and Import modes in one model, enabling flexibility and performance optimization.

❓ dax_lod_result

intermediate

DAX Measure with Composite Model Behavior

Given a composite model with a DirectQuery table 'Sales' and an Import table 'Products', what is the result of this DAX measure?

Measure = CALCULATE(SUM(Sales[Amount]), Products[Category] = "Electronics")

Assuming 'Products' is imported and 'Sales' is DirectQuery, what does this measure do?

A Calculates the sum of Sales Amount filtered by Electronics category from the imported Products table.

B Returns an error because you cannot filter DirectQuery data by an Import table column.

C Ignores the filter on Products and sums all Sales Amount.

D Calculates the sum of Sales Amount but only for categories present in the Sales table.

💡 Hint

Consider how relationships work between Import and DirectQuery tables in composite models.

In composite models, filters from Import tables can be applied to DirectQuery tables if relationships exist, so the measure sums Sales Amount filtered by Electronics category.

❓ visualization

advanced

Visualizing Performance Impact of Composite Models

You have a composite model mixing large DirectQuery sales data and small imported product data. Which visualization approach best helps identify performance bottlenecks?

A Add a card visual displaying total sales amount only.

B Create a report page with a table showing query durations and data source types for each visual.

C Build a line chart showing sales trends over time without any query diagnostics.

D Use a pie chart to show sales distribution by product category without performance metrics.

💡 Hint

Think about what helps diagnose performance issues in composite models.

A table showing query durations and data source types helps identify which visuals or queries cause slowdowns in composite models.

🔧 Formula Fix

advanced

Debugging Relationship Issues in Composite Models

You created a composite model with an Import table 'Customers' and a DirectQuery table 'Orders'. The relationship between them is inactive, causing incorrect sales totals. Which DAX expression activates the relationship temporarily to fix the measure?

Measure = ?

A SUMX(Orders, Orders[Amount]) + RELATED(Customers[CustomerID])

B CALCULATE(SUM(Orders[Amount]), ALL(Customers))

C CALCULATE(SUM(Orders[Amount]), USERELATIONSHIP(Customers[CustomerID], Orders[CustomerID]))

D SUM(Orders[Amount]) * COUNTROWS(Customers)

💡 Hint

Recall how to activate inactive relationships in DAX.

USERELATIONSHIP activates an inactive relationship temporarily within CALCULATE, fixing filtering issues in composite models.

🎯 Scenario

expert

Designing a Composite Model for Mixed Data Sources

Your company wants a Power BI report combining real-time sales data from a DirectQuery SQL database and historical sales data imported monthly. You also want to filter by product categories from an imported table. Which design approach best fits this scenario?

A Create a composite model with DirectQuery for real-time sales, Import for historical sales and products, and define relationships between tables.

B Import all data including real-time sales to ensure fast report performance.

C Use DirectQuery for all tables to keep data always current, ignoring import mode.

D Create separate reports for real-time and historical data without combining them.

💡 Hint

Consider how composite models allow mixing storage modes for best performance and freshness.

Composite models enable mixing DirectQuery for real-time data and Import for historical and dimension tables, allowing filtering and performance balance.