PL-300 Model the data Practice Test 19 — 15 Questions

Question 1

You are creating a star schema in Power BI. Which TWO tables are typically dimension tables?

Accepted Answer

Product. In a star schema, dimension tables hold descriptive attributes used to slice and filter facts, so B (Product) is correct because it stores descriptive product attributes like name, category, and subcategory that relate to fact tables. C (Date) is also correct because a date/calendar table provides time attributes (year, quarter, month, day) for time intelligence and filtering, making it a classic dimension. By contrast, A (TransactionDetails), D (InventoryTransactions), and E (Sales) are transactional or event-level tables that store measures and foreign keys, so they are fact tables rather than dimensions.

Answer

TransactionDetails

Answer

InventoryTransactions

Answer

Sales

Question 2

You are a data analyst at a global retail company. You are building a Power BI semantic model to analyze sales performance across 50 countries. The data source is an Azure SQL Database with tables: Sales (SalesID, ProductID, StoreID, DateKey, Quantity, Amount), Products (ProductID, ProductName, CategoryID), Stores (StoreID, StoreName, CountryID), Countries (CountryID, CountryName), and Dates (DateKey, Date, Year, Month, Quarter). The model must support: 1) Hierarchical drill-down from Year to Quarter to Month. 2) Slicers for Country and Product Category. 3) Measures for Total Sales, Year-over-Year growth, and Moving Average (last 12 months). 4) The ability to filter by date range (e.g., last 3 months) while preserving the ability to show YoY growth for the selected period. The database contains 500 million rows in the Sales table. The company has strict performance requirements: report pages must load within 5 seconds. You need to design the model in Power BI Desktop. Which approach should you take?

Accepted Answer

Use Import storage mode for all tables with incremental refresh policy on the Sales table to load only the last 5 years of data.. Option C is correct because Import mode with incremental refresh on the 500-million-row Sales table delivers the sub-5-second report performance required, while the incremental refresh policy limits data loaded to the last 5 years and only refreshes changed partitions. Import mode also fully supports the required Year→Quarter→Month hierarchy, Country and Category slicers, and DAX time-intelligence measures like YoY growth and a 12-month moving average, provided a proper Dates table is related to Sales. Option A is wrong because DirectQuery for all tables pushes every visual query to Azure SQL, which will not reliably meet the 5-second page load requirement at this data volume. Option B is wrong because DirectQuery on the large Sales table still incurs slow remote queries for aggregations and time intelligence. Option D is wrong because using DateKey from Sales without a dedicated date table prevents correct time-intelligence functions such as SAMEPERIODLASTYEAR and DATESINPERIOD.

Answer

Use DirectQuery storage mode for all tables to ensure real-time data and aggregate queries at the source.

Answer

Use a composite model: Import for dimension tables and DirectQuery for Sales table to balance freshness and performance.

Answer

Use Import mode but do not create a date table; instead use the DateKey column from Sales for time intelligence.

Question 3

You are building a star schema in Power BI. The fact table contains sales transactions. Which of the following should be stored in a dimension table?

Accepted Answer

Product category. In a star schema, dimension tables store descriptive attributes used for filtering, grouping, and labeling fact data. Product category is a descriptive attribute of the product dimension, so it belongs in a dimension table. Fact tables store numeric, additive measures and foreign keys that reference dimensions.

Answer

Sales amount

Answer

Customer ID

Answer

Transaction date

Question 4

You need to create a calculated column in Power BI that shows the full name by combining 'FirstName' and 'LastName' columns with a space. Which DAX expression should you use?

Accepted Answer

FullName = [FirstName] & " " & [LastName]. The DAX concatenation operator is the ampersand (&). Option B is wrong because CONCATENATE function only takes two arguments. Option C is wrong because CONCATENATEX is for tables. Option D is wrong because the plus sign is for addition.

Answer

FullName = CONCATENATE([FirstName], " ", [LastName])

Answer

FullName = CONCATENATEX(Table, [FirstName] & " " & [LastName])

Answer

FullName = [FirstName] + " " + [LastName]

Question 5

You are modeling data from a SQL database that has a table with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, and Price. You want to create a star schema in Power BI. Which columns should you move to dimension tables?

Accepted Answer

CustomerID, ProductID, OrderDate. In a star schema these columns are foreign keys or attributes that belong in dimension tables: CustomerID links to a Customer dimension, ProductID links to a Product dimension, and OrderDate links to a Date dimension for time-based analysis. The fact table should retain the numeric measures and the order identifier, so Quantity, Price, and OrderID stay in the fact table. Option A incorrectly moves Quantity and Price, which are measures, into dimensions. Option B incorrectly moves Quantity and Price and keeps CustomerID/ProductID out of dimensions. Option D incorrectly moves Quantity and Price while leaving the dimension keys in the fact table.

Answer

OrderDate, Quantity, Price

Answer

Quantity, Price, OrderID

Answer

OrderID, Quantity, Price

Question 6

You need to design a data model for a sales analysis that includes measures for total sales, sales by product, and sales by customer. The source data has a 'Transactions' table with columns: TransactionID, Date, CustomerID, ProductID, Quantity, Amount. What is the recommended star schema design?

Accepted Answer

Create a fact table and separate dimensions for Date, Customer, and Product. Option B is correct because a star schema centers on a single fact table (here, Transactions with Quantity and Amount as measures) surrounded by separate dimension tables for Date, Customer, and Product, which lets you aggregate total sales and slice by product or customer efficiently. Keeping each dimension distinct preserves clean grain, supports conformed attributes, and enables the required sales-by-product and sales-by-customer analysis. Option A is wrong because splitting into two fact tables fragments the same transaction grain and complicates cross-measure analysis. Option C is wrong because a single denormalized table is not a star schema and loses dimensional modeling benefits. Option D is wrong because combining Customer and Product into one dimension creates a snowflake-like or junk dimension that prevents independent analysis by each attribute.

Answer

Create two fact tables: one for sales and one for customers

Answer

Create a single table with all columns

Answer

Create a fact table and one dimension containing Customer and Product

Question 7

Which TWO of the following are best practices when designing a Power BI data model?

Accepted Answer

Use a star schema design. Option B is correct because a star schema — a central fact table surrounded by dimension tables — is the recommended Power BI model design: it produces simpler DAX, better compression, and faster query performance than snowflaked or flat models. Option E is correct because foreign key columns in dimension tables are implementation details used only for relationship joins; hiding them from report view keeps the field list clean and prevents report authors from accidentally grouping or filtering by meaningless surrogate keys. Option A is wrong because measures (evaluated at query time, not stored) are generally preferred over calculated columns, which consume memory and are computed during refresh. Option C is wrong because collapsing everything into one flat table causes massive redundancy, poor compression, and slow aggregations. Option D is wrong because bidirectional relationships can introduce ambiguity and unexpected filter propagation; they should be used sparingly and only when a specific cross-filtering requirement demands it, not as the default.

Answer

Use calculated columns instead of measures

Answer

Denormalize all tables into a single flat table

Answer

Use bidirectional relationships as default

Question 8

You are designing a Power BI semantic model for a retail company. The model includes a Sales table (50 million rows) and a Product table (10,000 rows). You need to create a measure that calculates the average sales amount per product category. The Product table has a column 'Category' with 20 distinct values. To optimize performance, what should you do?

Accepted Answer

Create a relationship between Sales and Product on ProductID, and use Product[Category] in the measure.. Option D is correct because creating a relationship between Sales and Product on ProductID and then using Product[Category] in the measure lets the VertiPaq engine aggregate the 50 million Sales rows through the dimension, which is the standard star-schema approach and performs best. The relationship filters Sales by category at query time, so no row-level lookups or duplicated category values are stored in the large fact table. Option A is wrong because a calculated column using RELATED materializes the category on all 50 million Sales rows, increasing model size and refresh time. Option B is wrong because summarizing Sales to category level in Power Query destroys the detail needed for other measures and prevents dynamic slicing. Option C is wrong because a separate calculated table would not be related to Sales on ProductID and would not correctly filter the fact table.

Answer

Add a calculated column to Sales that looks up the category using RELATED.

Answer

Summarize the Sales table to the category level in Power Query.

Answer

Create a separate calculated table for categories and link it to Sales.

Question 9

Which TWO actions should you take to improve the performance of a DirectQuery model in Power BI? (Select two.)

Accepted Answer

Limit the columns selected to only those needed in the report.. Option A is correct because a DirectQuery model translates each visual into a query against the source, so limiting the columns selected to only those needed in the report reduces the width of the generated SQL and the volume of data transferred, lowering query cost and latency. Option C is correct because pushing filters to the source database as much as possible lets the backend engine apply its own indexing and query optimization, so less data is returned to Power BI and processing is done where it is most efficient. Option B is wrong because calculated columns in a DirectQuery table are computed at query time and can force row-by-row evaluation or even prevent query folding, which typically hurts rather than helps performance. Option D is wrong because aggregations on imported tables apply to Import mode tables, not to a DirectQuery model's source tables. Option E is wrong because row-level security filters add predicates to every DirectQuery query and increase complexity and overhead rather than improving performance.

Answer

Use calculated columns instead of measures to precompute values.

Answer

Create aggregations on the imported tables.

Answer

Implement row-level security filters on the fact table.

Question 10

Which TWO are valid reasons to use a date table in a Power BI semantic model? (Select two.)

Accepted Answer

To enable time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR.. Option C is correct because time intelligence DAX functions such as TOTALYTD, SAMEPERIODLASTYEAR, DATESYTD, and DATEADD require a contiguous, marked date table with a Date column at day granularity; without a proper date table these functions return incorrect or blank results. Option E is correct because a single shared date table can be related to multiple fact tables, letting one slicer or filter on the date dimension consistently filter all facts instead of relying on each fact's own date column. Options A, B, and D are not valid reasons: drill-down works with any hierarchy, not specifically a date table; Power BI can auto-generate date hierarchies from a date field without a dedicated date table; and relationship performance depends on cardinality, cross-filter direction, and model design rather than on the presence of a date table.

Answer

To allow users to drill down into non-date hierarchies.

Answer

To automatically generate date hierarchies in visuals.

Answer

To improve the performance of relationships between tables.

Question 11

You are a data analyst at a multinational retail company. The company uses Microsoft Power BI to analyze sales data from multiple regions. The source data is stored in Azure SQL Database and includes tables: Sales (OrderID, ProductID, Quantity, Amount, OrderDate, StoreID), Stores (StoreID, StoreName, Region, Country), Products (ProductID, ProductName, Category, Price). The model is imported daily. You need to design a semantic model that supports the following requirements: 1) Allow users to filter by year and month using a single slicer. 2) Ensure that time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) work correctly. 3) Minimize model size. 4) Provide a consistent date dimension for all fact tables. The Sales table has orders from 2018-01-01 to 2025-12-31. You decide to create a date table. Which of the following approaches should you take?

Accepted Answer

Create a date table using DAX with CALENDAR and mark it as a date table.. Option A is correct because creating a dedicated date table with DAX CALENDAR (e.g., CALENDAR(DATE(2018,1,1), DATE(2025,12,31))) and marking it as a date table gives a single, contiguous date dimension that supports one slicer for year/month, enables time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR, and can be kept narrow to minimize model size. Marking it as a date table ensures Power BI treats it as the official date table for the model, providing consistent filtering across fact tables. Option B is wrong because using Sales[OrderDate] directly does not create a shared date dimension and calculated year/month columns add size without proper date-table semantics. Option C is wrong because auto-generated date hierarchies create hidden tables per date column, increasing model size and not providing one consistent date dimension. Option D is not the best fit because importing a full date table with additional attributes can add unnecessary columns and size, whereas the requirement emphasizes minimizing model size and a purpose-built DAX date table is sufficient.

Answer

Use the Sales[OrderDate] column directly and create a calculated column for year and month.

Answer

Use Power BI's auto-generated date hierarchy for each date column.

Answer

Import a date table from the source database that includes all dates and additional attributes.

Question 12

You are a Power BI developer for a financial services company. The company has a large transactional database in Azure Synapse Analytics. The database contains a table 'Transactions' with 2 billion rows. The table includes columns: TransactionID, AccountID, TransactionDate, Amount, Type (Deposit/Withdrawal), Status (Pending/Completed). You need to build a Power BI semantic model that allows executives to analyze monthly trends of completed deposit amounts by account type (e.g., Savings, Checking). The account type is in a separate 'Accounts' table (1 million rows) with columns: AccountID, AccountType, CustomerID. The model must refresh within 2 hours. Due to the large data volume, you cannot import the entire Transactions table. What should you do?

Accepted Answer

Create an aggregated table in Synapse that pre-aggregates data at the month and account type level, then use DirectQuery on the aggregated table, and use a composite model with the detail table for drill-through.. Option D is correct because it pre-aggregates the 2 billion-row Transactions table in Synapse at the month and account type grain, which drastically reduces the data volume Power BI must query, and then uses DirectQuery on that small aggregated table so refreshes complete well within the 2-hour window; the composite model with the detail table preserves drill-through to transaction-level detail when needed. Option A is wrong because mixing incremental refresh (Import) with DirectQuery for older data still requires importing large volumes and does not aggregate the 2 billion rows, so it will not reliably meet the 2-hour refresh. Option B is wrong because Power BI aggregations still depend on importing or querying the underlying detail, and importing only necessary columns from 2 billion rows remains too large for the refresh SLA. Option C is wrong because DirectQuery over the full 2 billion-row Transactions table pushes all aggregation work to Synapse at query time, producing slow executive reports and no pre-aggregation benefit.

Answer

Use incremental refresh to import only recent data, and use DirectQuery for older data.

Answer

Import only the necessary columns and use Power BI aggregations to pre-aggregate.

Answer

Use DirectQuery on the full Transactions table and rely on the Synapse query optimizer.

Question 13

You are developing a Power BI semantic model for an e-commerce company. The source data comes from a CSV file containing order details: OrderID, OrderDate, CustomerID, ProductID, Quantity, UnitPrice, Discount, and ShippingCost. The file is updated daily. You need to model the data to support the following analyses: 1) Total sales amount (Quantity * UnitPrice - Discount) by product and month. 2) Average shipping cost per order by customer region (CustomerRegion is in a separate table). 3) Year-over-year comparison of sales. You need to create the measures and ensure optimal performance. What should you do?

Accepted Answer

Import both tables into Power BI, create a date table, and build measures using SUMX and time intelligence.. Importing both tables into Power BI and creating a star schema with a central fact table (Orders) and dimension tables (CustomerRegion, Date) enables efficient measure calculation. The measures can use SUMX to compute sales amount (SUMX(Orders, Orders[Quantity] * Orders[UnitPrice] - Orders[Discount])) and average shipping cost per order filtered by region via relationships. A separate date table is required for time intelligence functions like SAMEPERIODLASTYEAR for year-over-year comparison. Option A is suboptimal because adding calculated columns in Power Query increases storage and processing time; measures are preferable. Option B creates a flat denormalized table which duplicates customer region data, leading to larger model and slower performance. Option C is incorrect because DirectQuery is not supported on CSV files; Power BI requires data import or a connection to a database. Therefore, Option D is the best approach.

Answer

Use Power Query to add a calculated column for sales amount and shipping cost per order, then import.

Answer

Create a single table by appending the customer region to each row in the CSV using Power Query, then import.

Answer

Use DirectQuery on the CSV file to avoid storing data in Power BI.

Question 14

You are creating a Power BI report for a small business. The data source is a Microsoft Access database with tables: Customers (CustomerID, CompanyName, City), Orders (OrderID, CustomerID, OrderDate, Amount). You need to model the data to analyze total orders by customer and by month. What is the most efficient approach?

Accepted Answer

Import both tables into Power BI and create a relationship between CustomerID columns.. Importing both tables and creating a relationship between the CustomerID columns builds a proper star-schema-style model in Power BI, letting the Customers dimension filter the Orders fact table so totals by customer and by month can be computed efficiently with DAX. This approach leverages Power BI's in-memory VertiPaq engine and relationship-based filter propagation, which is the recommended modeling pattern for this kind of analysis. Option A does not fit because DirectQuery is not supported for Microsoft Access as a data source in Power BI, and even where DirectQuery applies it would not be more efficient here. Option B is less flexible because pre-joining the tables in Power Query flattens the model and complicates month-level aggregation and reuse. Option C is unnecessary overhead for a small business scenario and adds migration effort without modeling benefit.

Answer

Use DirectQuery on the Access database to avoid importing.

Answer

Create a single query in Power Query that joins the tables and import the result.

Answer

Migrate the Access database to SQL Server and then import.

Question 15

Which THREE considerations are important when designing a Power BI data model for large datasets?

Accepted Answer

Disable the auto-date/time feature.. Option C is correct because Power BI's Auto date/time feature creates a hidden calculated date table for every date column, which adds memory overhead and can bloat a large model, so disabling it (via Options or Tabular Editor) keeps the model lean. Option D is correct because a star schema with a central fact table surrounded by dimension tables produces efficient, simple relationships that the VertiPaq engine compresses and queries faster than snowflake or normalized designs. Option E is correct because integer keys consume less memory and compress better than text keys, and integer-to-integer relationships are processed more efficiently during query execution. Option A is not appropriate because calculated columns are stored and compressed in the model, consuming memory and increasing refresh time, so measures or calculated tables should be preferred where possible. Option B is not appropriate because adding unnecessary columns increases model size, hurts compression, and slows processing, so fact tables should contain only the columns needed for analysis.

Answer

Store calculated columns in the fact table for quick access.

Answer

Include as many columns as possible in fact tables for flexibility.