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

Question 1

You are building a star schema in Power BI. The Sales table contains sales amount, quantity, and discount. You need to create a relationship between Sales and a Date table. The Date table must have a one-to-many relationship with Sales, and all date filters must show sales even if no sales occurred on that date. Which relationship configuration should you use?

Accepted Answer

One-to-many, single direction (Date filters Sales). Option D is correct: Single direction filter from Date to Sales ensures all dates are shown when used as a filter, and one-to-many is the appropriate cardinality. Option A is wrong because bidirectional filter is unnecessary and can cause ambiguity. Option B is wrong because many-to-one is the reverse relationship. Option C is wrong because cross-filter direction both is not needed.

Answer

One-to-many, single direction (Sales filters Date)

Answer

One-to-many, both directions

Answer

Many-to-one, single direction (Date filters Sales)

Question 2

You have a Power BI semantic model with a fact table 'Orders' and dimension tables 'Customer', 'Product', and 'Date'. The model uses DirectQuery against Azure Synapse. Users report that a measure calculating Year-over-Year growth returns incorrect values for the current year when no data exists for the prior year. Which DAX modification ensures the measure returns blank instead of an incorrect value?

Accepted Answer

Add an IF condition to check if prior year has any rows before computing growth. Option B is correct: Using IF(COUNTROWS(...) > 0, ...) checks if prior year data exists. Option A is wrong because DIVIDE with 0 denominator returns blank, but does not handle missing prior year rows. Option C is wrong because ISBLANK on a scalar value doesn't check row existence. Option D is wrong because COALESCE returns the first non-blank, but the issue is incorrect CALCULATE results when prior year is missing.

Answer

Use DIVIDE with an alternative result of 0

Answer

Use ISBLANK to check the current year value

Answer

Use COALESCE to replace blank with 0

Question 3

You are designing a Power BI data model for a retail company. The model must include a table with product prices that change over time. Which table design should you use to support historical price analysis?

Accepted Answer

Create a separate dimension table for price with effective date ranges and relate it to the fact table. Option A is correct: A role-playing dimension table for price with date ranges supports slowly changing dimension Type 2. Option B is wrong because replacing prices in the fact table loses history. Option C is wrong because a single column in the product dimension only stores current price. Option D is wrong because a separate unrelated table cannot be used in relationships.

Answer

Create a separate table for price changes without any relationship

Answer

Add a price column to the product dimension and update it when price changes

Answer

Store the current price in the fact table and overwrite when price changes

Question 4

You are modeling data from multiple sales regions. Each region has a unique ID, but region names might be spelled inconsistently (e.g., 'North America' vs. 'N. America'). You need to create a single dimension table for regions. What is the best practice to handle this?

Accepted Answer

Use Power Query to clean and standardize region names before loading. Option C is correct: Standardize region names using Power Query transformations before loading into the model. Option A is wrong because keeping inconsistent spellings causes incorrect grouping. Option B is wrong because many-to-many relationships should be avoided if possible. Option D is wrong because hiding columns doesn't fix data quality.

Answer

Keep both spellings in the dimension and use a many-to-many relationship

Answer

Hide the region column and use only region ID in visuals

Answer

Use a bridge table to map both spellings to a single region ID

Question 5

You have a Power BI model with a measure that calculates total sales using SUMX over a filtered table. The measure performs slowly. Which change will most likely improve performance without altering business logic?

Accepted Answer

Rewrite the measure using CALCULATE with a simple SUM instead of SUMX. Option B is correct: Using a base measure for SUM of Sales Amount and then applying filter context often performs better than iterating with SUMX. Option A is wrong because removing filters may change logic. Option C is wrong because increasing storage mode changes data source behavior but may not be allowed. Option D is wrong because KEEPFILTERS can add overhead.

Answer

Add KEEPFILTERS to preserve existing filters

Answer

Change the storage mode of the table from Import to DirectQuery

Answer

Remove all filter functions from the measure

Question 6

You are designing a data model for a sales analysis. The 'Sales' fact table contains columns: OrderDate, ShipDate, Amount, Quantity. You need to analyze sales by both order date and ship date. What is the best practice to model the dates?

Accepted Answer

Create two date tables: one for OrderDate (active) and one for ShipDate (inactive), using USERELATIONSHIP as needed. Option A is correct: Use two date dimensions with active/inactive relationships to support both analyses. Option B is wrong because merging dates loses distinction. Option C is wrong because a single date table can only be used for one role. Option D is wrong because the fact table should not contain date dimension attributes.

Answer

Put the date keys in a separate table and relate to both

Answer

Create a single date table and merge both date columns into one

Answer

Use only one date table and create separate measures for each date

Question 7

Your Power BI model includes a calculated column that concatenates first and last name. Users report that the column shows blank for some rows. The data source has no nulls. What is the most likely cause?

Accepted Answer

One of the columns contains only spaces. Option C is correct: Leading or trailing spaces in name columns can cause concatenation to appear blank if one column is empty after trimming. Option A is wrong because data type mismatch would likely cause error, not blank. Option B is wrong because if source has no nulls, that's not the issue. Option D is wrong because relationship direction doesn't affect calculated columns.

Answer

Data type mismatch between the two columns

Answer

The columns are from different tables without a relationship

Answer

The relationship between tables is set to single direction

Question 8

You are modeling a fact table with granularity at the order line level. The table includes columns: OrderID, ProductID, Quantity, UnitPrice, Discount. You need to create a measure for total revenue considering discounts (Quantity * UnitPrice * (1 - Discount)). Some orders have no discount (null). What is the correct DAX for this measure?

Accepted Answer

SUMX('Orders', Orders[Quantity] * Orders[UnitPrice] * (1 - COALESCE(Orders[Discount], 0))). Option B is correct: SUMX('Orders', Orders[Quantity] * Orders[UnitPrice] * (1 - COALESCE(Orders[Discount], 0))) handles null discounts. Option A is wrong because it does not handle null. Option C is wrong because DIVIDE is unnecessary. Option D is wrong because it ignores discount column entirely.

Answer

SUM('Orders'[Quantity] * 'Orders'[UnitPrice])

Answer

SUMX('Orders', Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[Discount]))

Answer

SUMX('Orders', DIVIDE(Orders[Quantity] * Orders[UnitPrice], 1 + Orders[Discount]))

Question 9

You need to create a relationship between two tables where one table has duplicate values on the key column. Which cardinality should you choose?

Accepted Answer

Many-to-one. Option B is correct: Many-to-one is used when the lookup table has unique values and the fact table has duplicates. Option A is wrong because one-to-many is the reverse. Option C is wrong because many-to-many is for when both sides have duplicates. Option D is wrong because one-to-one is for unique keys on both sides.

Answer

One-to-one

Answer

Many-to-many

Answer

One-to-many

Question 10

You are designing a Power BI data model for a manufacturing company. Which TWO practices help optimize performance when using DirectQuery?

Accepted Answer

Reduce the number of columns in the fact query to only those needed. Option A is correct: Reducing columns in the query reduces data transfer. Option C is correct: Using a single date table avoids multiple joins. Option B is wrong because calculated columns in DirectQuery are pushed to source but can degrade performance. Option D is wrong because disabling relationships is not a best practice. Option E is wrong because bidirectional filtering can cause performance issues.

Answer

Disable relationships between tables to reduce query complexity

Answer

Create calculated columns in Power Query instead of in DAX

Answer

Enable bidirectional cross-filtering for all relationships

Question 11

You are modeling data with a many-to-many relationship between 'Student' and 'Class' tables. Which THREE configurations can handle this scenario?

Accepted Answer

Use CROSSFILTER function to enable many-to-many filtering. Options A, B, and C are correct. Option A uses a bridge table which is the standard approach. Option B uses a calculated column concatenation for limited scenarios. Option C uses CROSSFILTER to enable many-to-many filtering. Option D is wrong because removing duplicates loses information. Option E is wrong because changing to one-to-many incorrectly assumes uniqueness.

Answer

Remove duplicate rows from both tables

Answer

Change relationship to one-to-many by creating a unique key

Question 12

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

Accepted Answer

Product. Option B is correct: Date table is a classic dimension. Option D is correct: Product table is a dimension. Option A is wrong because Sales is a fact table. Option C is wrong because TransactionDetails is typically a fact table. Option E is wrong because InventoryTransactions is a fact table.

Answer

TransactionDetails

Answer

InventoryTransactions

Answer

Sales

Question 13

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 B is correct: Using Import mode with incremental refresh reduces data volume and leverages Power BI's compression and in-memory engine for fast performance. Option A is wrong because DirectQuery over such a large table would be slow. Option C is wrong because Composite model adds complexity and may not meet performance targets. Option D is wrong because it lacks the necessary date hierarchy for drill-down.

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 14

You are a Power BI developer for a healthcare organization. You are building a semantic model to analyze patient readmission rates. The data warehouse contains: Admissions (AdmissionID, PatientID, AdmitDate, DischargeDate, DiagnosisCode, HospitalID) and Readmissions (ReadmissionID, PatientID, ReadmitDate, Reason). You need to create a measure that calculates the readmission rate within 30 days of discharge. The measure must consider only the first readmission per patient per admission. You also need to create a visual that shows the trend of readmission rates over time, allowing users to filter by diagnosis. The model will be used by 200 analysts and must refresh daily. Which DAX measure should you create?

Accepted Answer

Readmission Rate = VAR ReadmitPatients = CALCULATETABLE( VALUES(Readmissions[PatientID]), Readmissions[ReadmitDate] <= Admissions[DischargeDate] + 30 ) RETURN DIVIDE( COUNTROWS(ReadmitPatients), DISTINCTCOUNT(Admissions[AdmissionID]) ). Option C is correct: It calculates distinct count of patients readmitted within 30 days divided by distinct count of admissions, considering first readmission. Option A is wrong because COUNTROWS counts multiple readmissions per patient. Option B is wrong because it counts all admissions including those without readmission. Option D is wrong because it counts all readmissions, not first.

Answer

Readmission Rate = DIVIDE( COUNTROWS(Readmissions), COUNTROWS(Admissions) )

Answer

Readmission Rate = DIVIDE( COUNTROWS(Admissions), DISTINCTCOUNT(Admissions[PatientID]) )

Answer

Readmission Rate = DIVIDE( COUNT(Readmissions[ReadmissionID]), COUNT(Admissions[AdmissionID]) )

Question 15

You are a data analyst for a university. You need to build a Power BI report to analyze student enrollment by program and year. Data sources: a CSV file with student enrollment records (StudentID, Program, EnrollmentYear, Status) and an Excel file with program details (ProgramCode, ProgramName, Faculty). The CSV file contains 1 million rows. The report must allow users to filter by faculty and program, and see a bar chart of enrollment counts by year. You need to design the data model. What should you do?

Accepted Answer

Import both tables into Power BI, create a relationship between Enrollment[Program] and Programs[ProgramCode], use Star schema design.. Option A is correct: Import both tables, create relationships, and use a star schema. Option B is wrong because DirectQuery on a CSV is not supported. Option C is wrong because having no relationships prevents filtering. Option D is wrong because merging loses granularity and may cause duplication.

Answer

Import the CSV file only and use DirectQuery for the Excel file to save memory.

Answer

Import both tables but do not create relationships; use visual-level filters to synchronize selections.

Answer

Merge the two tables in Power Query into a single table to simplify the model.