Knowledge + Practice

CCNA Model the data Questions

75 of 268 questions · Page 3/4 · Model the data · Answers revealed

Practice these questions Domain overview All questions

151

MCQeasy

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?

A.FullName = [FirstName] & " " & [LastName]

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

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

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

AnswerA

The ampersand (&) is the correct concatenation operator in DAX.

Why this answer

Option A is correct because 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.

Practice this question →

152

MCQhard

Refer to the exhibit. You are reviewing a DAX measure for month-over-month sales growth. The measure returns BLANK for all months. What is the likely issue?

A.The FILTER function removes all context from the Date table, causing PriorSales to be calculated over the entire table.

B.The Date table is not marked as a date table.

C.The variable PriorDate returns a blank because MAX is used.

D.The IF condition incorrectly treats zero as blank.

AnswerA

Using ALL('Date') removes the filter context, so PriorSales is not filtered by PriorDate.

Why this answer

Option A is correct because the FILTER function uses ALL('Date') which removes all filters, so the PriorDate filter is not applied correctly. Option B is wrong because the date table has a relationship. Option C is wrong because the variable is correctly defined.

Option D is wrong because the IF condition checks for zero, which may not be the issue.

Practice this question →

153

MCQeasy

You need to create a relationship between two tables in Power BI. Both tables contain a column named 'ProductID', but the values in one table are integers and in the other are text. What should you do first?

A.Merge the two tables into one in Power Query.

B.Ensure both columns have the same data type, either by changing the data type in Power Query or in the model view.

C.Create a new calculated column that converts the integer to text using FORMAT.

D.Set the relationship to 'Many-to-many' to bypass the type mismatch.

AnswerB

Matching data types is required before creating a relationship.

Why this answer

Option B is correct because Power BI requires matching data types to create a relationship. Option A is wrong because it is not necessary. Option C is wrong because changing the relationship type does not fix data type mismatch.

Option D is wrong because merging is not the first step.

Practice this question →

154

MCQeasy

You are designing a Power BI model for a multinational company. The fact table contains sales data in multiple currencies. You need to convert all amounts to a single reporting currency (USD) using exchange rates from a separate table. What is the recommended approach?

A.Add a calculated column to the fact table that multiplies by the exchange rate from a separate table

B.Create a relationship between the fact table and the exchange rate table on date and currency, then create a measure using SUMX with rate lookup

C.Hard-code the exchange rate in a measure

D.Use Power Query to merge the exchange rate table into the fact table during data load

AnswerB

Dynamic and accurate.

Why this answer

Create a relationship between the fact table and the exchange rate table using date and currency, then create a measure that multiplies the amount by the exchange rate. Option B is correct. Option A is wrong because it does not scale.

Option C is wrong because calculated columns are less flexible. Option D is wrong because Power Query conversion would be static.

Practice this question →

155

MCQmedium

You are building a star schema in Power BI. A fact table contains sales transactions with columns: OrderID, CustomerID, ProductID, Quantity, UnitPrice, Discount, and OrderDate. You need to create a dimension table for customers. Which columns should be included in the Customer dimension?

A.CustomerID, CustomerName, ProductID

B.CustomerID, CustomerName, OrderID

C.CustomerID, CustomerName, OrderDate

D.CustomerID, CustomerName, City, Region

AnswerD

These are descriptive customer attributes with a unique key.

Why this answer

Option B is correct because in a star schema, dimension tables should contain descriptive attributes (e.g., CustomerName, City, Region) and a unique key (CustomerID). Including foreign key columns like CustomerID from the fact table would violate normalization. Option A is wrong because including ProductID is irrelevant for customer dimension.

Option C is wrong because OrderDate belongs to a date dimension. Option D is wrong because OrderID is a fact grain identifier.

Practice this question →

156

MCQmedium

Refer to the exhibit. You apply the above RLS rule to a semantic model. The rule is intended to restrict sales data by the user's region, which is stored in the user's email domain (e.g., user@west.contoso.com). However, the rule does not filter any rows. What is the most likely issue?

A.The Sales table does not have a Region column.

B.USERPRINCIPALNAME() is not available in the current Power BI version.

C.The filter expression compares the full UPN to the Region column, which likely contains only the region name.

D.The RLS rule is not applied to the semantic model.

AnswerC

USERPRINCIPALNAME() returns 'user@west.contoso.com', not just 'West'.

Why this answer

Option B is correct because USERPRINCIPALNAME() returns the full UPN, which includes the domain. The filter expression compares the entire UPN to the Region column, which likely contains only the region part (e.g., 'West'). Option A is wrong because the rule is applied.

Option C is wrong because the table exists. Option D is wrong because USERPRINCIPALNAME() is valid.

Practice this question →

157

MCQmedium

A data modeler is creating a Power BI semantic model for a retail company. The model includes a 'Products' dimension table with columns ProductID, ProductName, Category, and Subcategory. The 'Sales' fact table has columns ProductID, Date, Quantity, and Revenue. The modeler wants to ensure that users can filter by Category and Subcategory. Which relationship type should be created between Products and Sales?

A.One-to-many (Products to Sales)

B.Many-to-one (Sales to Products)

C.Many-to-many

D.One-to-one

AnswerA

Each product can have many sales, so Products is the 'one' side.

Why this answer

In a Power BI semantic model, a one-to-many relationship from Products (the dimension table) to Sales (the fact table) is the standard star schema design. This allows users to filter Sales data by any attribute in Products, such as Category and Subcategory, because each ProductID in Products is unique (the 'one' side) and can be associated with many rows in Sales (the 'many' side). This relationship type ensures proper cross-filtering and aggregation behavior.

Exam trap

The trap here is that candidates might confuse the cardinality direction (one-to-many vs. many-to-one) as being technically different, but Power BI treats them identically; the key is recognizing that the dimension table (Products) should be on the 'one' side to maintain a proper star schema and avoid unnecessary complexity.

How to eliminate wrong answers

Option B is wrong because many-to-one (Sales to Products) is the same cardinality as one-to-many but with the direction reversed; Power BI treats them equivalently, but the question asks for the relationship type between Products and Sales, and the standard convention is to place the dimension on the 'one' side. Option C is wrong because many-to-many relationships are used when both tables have duplicate values on the key columns, which is not the case here (ProductID is unique in Products). Option D is wrong because one-to-one relationships are rare in star schemas and would require each ProductID in Sales to be unique, which contradicts the fact that a product can be sold multiple times.

Practice this question →

158

MCQhard

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?

A.Add an IF condition to check if prior year has any rows before computing growth

B.Use DIVIDE with an alternative result of 0

C.Use ISBLANK to check the current year value

D.Use COALESCE to replace blank with 0

AnswerA

Ensures blank when prior year data is missing.

Why this answer

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.

Practice this question →

159

MCQmedium

You have a Power BI data model with a table that contains duplicate rows. You want to remove duplicates in Power Query. Which transformation should you use?

A.Replace Values

B.Remove Duplicates

C.Filter Rows

D.Group By

AnswerB

Remove Duplicates eliminates duplicate rows.

Why this answer

Option C is correct because 'Remove Duplicates' removes duplicate rows based on selected columns. Option A filters rows. Option B groups rows.

Option D replaces values.

Practice this question →

160

MCQmedium

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?

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

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

C.One-to-many, both directions

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

AnswerB

Correct cardinality and filter direction ensures all dates are shown.

Why this answer

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.

Practice this question →

161

MCQhard

A Power BI data model includes a table 'Orders' with columns OrderID, CustomerID, OrderDate, SalesAmount. The model also has a 'Date' table and a 'Customer' table. The relationships are: Orders[CustomerID] -> Customer[CustomerID] (many-to-one, single direction) and Orders[OrderDate] -> Date[Date] (many-to-one, single direction). A user creates a measure that sums SalesAmount and then filters by a slicer on Customer[City]. The slicer works correctly. However, when the user adds another slicer on Date[Year], the measure does not respect both slicers simultaneously. What is the most likely cause?

A.The Customer and Date tables are not related to each other.

B.The relationship between Orders and Date is inactive.

C.The relationships are set to single direction, so filters from Date do not propagate to Orders.

D.The measure might be using ALL or ALLEXCEPT that removes the filter context from the Date table.

AnswerD

If the measure removes filters from Date, then the slicer on Date[Year] would be ignored.

Why this answer

Option D is correct because the measure likely uses ALL or ALLEXCEPT, which removes the filter context from the Date table. Even though the relationships are correctly configured and filters from the Date slicer propagate to Orders via the single-direction relationship, if the measure explicitly ignores those filters using a function like ALL(Date[Year]) or ALLEXCEPT(Orders, ...), the Date slicer will have no effect on the measure. This is a common DAX mistake where filter removal functions override slicer selections.

Exam trap

The trap here is that candidates often assume filter propagation direction is the problem, but the real issue is that DAX filter removal functions like ALL or ALLEXCEPT can silently override slicer filters, making it appear as though the relationship is broken.

How to eliminate wrong answers

Option A is wrong because the Customer and Date tables do not need to be directly related; filters propagate independently through their respective relationships to the Orders table. Option B is wrong because the relationship between Orders and Date is explicitly described as active (many-to-one, single direction), so it is not inactive. Option C is wrong because single-direction relationships do allow filters from the Date table to propagate to Orders; the issue is not with direction but with the measure overriding the filter context.

Practice this question →

162

MCQeasy

You have a date table with columns: Date, Year, Month, Quarter, Day. To enable time intelligence functions like TOTALYTD, what is the minimum requirement?

A.A separate date table with a continuous range of dates and a relationship to the fact table.

B.Mark the date table as a date table in Power BI.

C.Combine date and fact tables into one table.

D.A single date column in the fact table.

AnswerA

This is the standard requirement for time intelligence.

Why this answer

Option B is correct because time intelligence functions require a date table with continuous dates and a many-to-one relationship to the fact table's date column. Option A is wrong because a date column alone is not sufficient; the date table must be marked as a date table. Option C is wrong because marking the date table is required.

Option D is wrong because a single table is not required; a separate date table is preferred.

Practice this question →

163

Multi-Selectmedium

You are designing a Power BI data model for a sales analysis. The model includes a table named Sales with columns: OrderID, OrderDate, CustomerID, ProductID, Quantity, UnitPrice. You need to create a star schema. Which TWO tables should you create as dimension tables?

Select 2 answers

A.Calendar

B.Product

C.Sales

D.Orders

E.Customer

AnswersB, E

Product is a dimension containing descriptive attributes about products.

Why this answer

In a star schema, dimension tables contain descriptive attributes that provide context for the measures stored in the fact table. Product is a classic dimension because it holds product details (e.g., name, category) that describe the sales facts. Customer is also a dimension because it stores customer attributes (e.g., name, region) used for slicing and dicing sales data.

Exam trap

The trap here is that candidates often mistake the Sales table (which contains both measures and foreign keys) as a dimension table, or they incorrectly think Orders should be a dimension instead of recognizing it as part of the fact table's grain.

Practice this question →

164

Multi-Selectmedium

Which TWO of the following are valid reasons to use a calculated table in Power BI instead of a table from the source?

Select 2 answers

A.To create a bridge table for many-to-many relationships

B.To aggregate data from the source before loading

C.To create a date table for time intelligence

D.To enable incremental data refresh

E.To reduce the overall storage size of the model

AnswersA, C

Bridge tables are often created as calculated tables.

Why this answer

Options B and D are correct. Calculated tables can be used to create a date table or to create a bridge table for many-to-many relationships. Option A is wrong because calculated tables cannot be incremental refreshed.

Option C is wrong because calculated tables are stored in the model, not to reduce storage. Option E is wrong because calculated tables are not for aggregating data at source; they are created in the model.

Practice this question →

165

Multi-Selectmedium

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

Select 2 answers

A.Use a star schema to reduce model complexity.

B.Use surrogate keys in dimension tables to link to fact tables.

C.Store dimension attributes directly in fact tables for faster queries.

D.Enable bi-directional cross-filtering on all relationships.

E.Create calculated columns instead of Power Query transformations when possible.

AnswersA, B

Star schema is the recommended design.

Why this answer

Options A and D are correct. Option A: Using a star schema reduces complexity. Option D: Using surrogate keys improves performance and stability.

Option B is wrong because bi-directional cross-filtering can cause ambiguity. Option C is wrong because storing full names in the fact table increases size. Option E is wrong because calculated columns are less efficient than Power Query.

Practice this question →

166

MCQeasy

You need to create a hierarchy in Power BI that allows drilling down from Year to Quarter to Month. What is the correct approach?

A.Create separate measures for each level

B.Use the Drillthrough feature

C.Use the DATESYTD function

D.Create a hierarchy in the Date table using Year, Quarter, Month columns

AnswerD

Hierarchies enable drill-down in visuals.

Why this answer

Option C is correct because creating a hierarchy in the Date table using these columns enables drill-down. Option A does not create a hierarchy. Option B is a DAX function.

Option D is not a hierarchy.

Practice this question →

167

MCQmedium

Your organization uses Power BI with large datasets from Azure Synapse Analytics. You need to reduce model size and improve query performance. You decide to use aggregations. Which type of aggregation table is most appropriate for pre-aggregating data at the year and category level?

A.Calculated table

B.Table-based aggregation

C.Measure-based aggregation

D.In-memory aggregation

AnswerB

Table-based aggregations store pre-aggregated data.

Why this answer

Option B is correct because a table-based aggregation stores precomputed summaries in a separate table, commonly used for large datasets. Option A is not a standard aggregation type. Option C is incorrect because calculated tables are computed at refresh time and not pre-aggregated for performance.

Option D is incorrect because measures are not stored.

Practice this question →

168

Multi-Selectmedium

Which TWO of the following are best practices for modeling many-to-many relationships in Power BI?

Select 2 answers

A.Use a bridge table to resolve the many-to-many relationship

B.Set cross-filter direction to both for all relationships

C.Use a composite model with DirectQuery for one of the tables

D.Use a measure that iterates over the bridge table with SUMX

E.Create a calculated table that combines the two tables

AnswersA, D

A bridge table is the standard approach.

Why this answer

Option A and C are correct. Option A is correct because a bridge table normalizes many-to-many relationships. Option C is correct because using a measure with SUMX and a bridge table is a common pattern.

Option B is wrong because bidirectional filtering can cause ambiguity. Option D is wrong because composite models are not specifically for many-to-many. Option E is wrong because ignoring the relationship is not a solution.

Practice this question →

169

MCQhard

You are analyzing a DAX query. What is the purpose of the 'Filter Context' column in the result?

A.It returns the total sales for the current year, ignoring category.

B.It returns the total number of sales rows regardless of product category.

C.It returns the number of product categories in the current context.

D.It counts the number of distinct products in the current filter context.

AnswerB

ALL('Product') removes product filter, so count is overall.

Why this answer

Option D is correct. CALCULATE(COUNTROWS('Sales'), ALL('Product')) counts all sales rows ignoring any filter on the Product table. This is useful to compare category-level sales against total sales.

Option A is wrong because it does not count distinct products. Option B is wrong because it does not filter by current year. Option C is wrong because it counts sales rows, not product rows.

Practice this question →

170

MCQhard

You are designing a Power BI data model for a retail chain. You have three fact tables: Sales, Inventory, and Returns. Which modeling approach is best for cross-filtering between these facts?

A.Use calculated columns to link facts

B.Use role-playing dimensions

C.Merge all fact tables into one

D.Create separate dimensions for each fact table

AnswerB

Role-playing dimensions allow multiple facts to share the same dimension tables.

Why this answer

Option B is correct because role-playing dimensions allow sharing common dimensions like Date, Product, Store. Option A causes ambiguity. Option C is not a standard approach.

Option D is unnecessary if dimensions are shared.

Practice this question →

171

MCQmedium

You have a Power BI data model with a fact table and multiple dimension tables. You notice that many-to-many relationships cause ambiguous results. What is the best practice to resolve this?

A.Change the relationship to one-to-one

B.Use a bidirectional cross-filter direction

C.Create a calculated table to merge the dimensions

D.Add a bridge table with appropriate relationships

AnswerD

A bridge table normalizes the many-to-many relationship into two one-to-many relationships.

Why this answer

Option C is correct because introducing a bridge table with a many-to-one relationship to both sides resolves many-to-many ambiguity. Option A changes cardinality but loses data. Option B could cause ambiguity.

Option D is not applicable because a calculated table does not fix relationships.

Practice this question →

172

MCQmedium

You are modeling data from multiple sources: a SQL Server database for sales, an Excel file for budget, and a SharePoint list for product targets. You need to combine these into a single Power BI report. What is the recommended approach for handling data refresh?

A.Import each source into separate Power BI Desktop files and manually update.

B.Use Excel Online as the single source and import all data into it first.

C.Use Power Query to combine data from all sources into a single dataset, then schedule a daily refresh in the Power BI service.

D.Create separate datasets for each source and use composite models with DirectQuery.

AnswerC

Power Query can merge data from different sources, and a single refresh is efficient.

Why this answer

Option D is correct because using Power Query to combine data and scheduling a single refresh is efficient. Option A is wrong because live connections are not possible with all sources. Option B is wrong because it duplicates work.

Option C is wrong because Excel Online is not supported for direct refresh.

Practice this question →

173

MCQmedium

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?

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

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

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

D.Import both tables into Power BI, create a date table, and build measures using SUMX and time intelligence.

AnswerD

This leverages in-memory engine and efficient DAX.

Why this answer

Option C is correct because importing the CSV and CustomerRegion table into Power BI and creating a star schema with relationships allows efficient measure calculation. Option A is wrong because calculated columns in source increase storage. Option B is wrong because DirectQuery on CSV is not supported.

Option D is wrong because a flat table with appended customer region would duplicate data.

Practice this question →

174

Multi-Selecthard

Which THREE actions can improve query performance in a Power BI DirectQuery model?

Select 3 answers

A.Limit the columns retrieved from the source.

B.Create calculated columns in DAX rather than Power Query.

C.Use pre-aggregated tables in the source.

D.Reduce the number of rows returned by applying filters early.

E.Add more visuals to a single page to reduce page count.

AnswersA, C, D

Fewer columns means less data transferred.

Why this answer

Reducing columns and rows selected reduces data transfer. Increasing the number of visuals on a page can increase the number of queries, hurting performance. Using summarized tables can reduce the amount of data queried.

Creating calculated columns in Power Query (rather than DAX) improves performance because they are materialized at refresh.

Practice this question →

175

MCQhard

Refer to the exhibit. You have a Sales table. You create a measure: TotalSales = SUM(Sales[SalesAmount]). You then create another measure: SalesAboveAverage = CALCULATE([TotalSales], FILTER(Sales, Sales[SalesAmount] > AVERAGE(Sales[SalesAmount]))). What is the value of SalesAboveAverage?

A.1000

B.800

C.450

D.550

AnswerD

Sum of rows where SalesAmount > average (200): 300+250=550.

Why this answer

Option D is correct. The average of SalesAmount is (100+200+300+150+250)/5 = 200. The rows with SalesAmount > 200 are: 300 and 250.

Sum = 550. Option A is wrong because it includes all rows. Option B is wrong because it uses total sum.

Option C is wrong because it includes 200? No, 200 is not > 200. So correct is 550.

Practice this question →

176

Multi-Selecthard

Which THREE factors should you consider when designing a star schema in Power BI?

Select 3 answers

A.A separate date table should be created for time intelligence.

B.Use natural keys instead of surrogate keys in dimension tables.

C.Fact tables should contain only foreign keys and numeric measures.

D.Use a snowflake schema to reduce data redundancy.

E.Dimension tables should be denormalized.

AnswersA, C, E

A dedicated date table enables time-based calculations.

Why this answer

A separate date table is required for time intelligence functions in Power BI because DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) rely on a continuous, contiguous date range with no gaps. Power BI automatically marks a table as a date table only if it contains a complete set of dates from the earliest to the latest transaction, enabling functions like DATEADD and DATESBETWEEN to work correctly across all granularities.

Exam trap

The trap here is that candidates confuse the theoretical normalization benefits of a snowflake schema (reducing redundancy) with the practical performance requirements of Power BI, where denormalization and surrogate keys are essential for optimal query execution and time intelligence calculations.

Practice this question →

177

MCQeasy

You need to create a calculated column that categorizes sales amounts into 'Low', 'Medium', and 'High' based on thresholds. Which DAX function should you use?

A.LOOKUPVALUE

B.CALCULATE

C.SWITCH

D.IF

AnswerC

SWITCH allows multiple conditions in a clean syntax.

Why this answer

Option B is correct because SWITCH can evaluate multiple conditions. Option A (IF) can also work but becomes nested. Option C (LOOKUPVALUE) is for looking up values.

Option D (CALCULATE) modifies filter context.

Practice this question →

178

Multi-Selectmedium

Which THREE of the following are valid reasons to create a calculated table in Power BI?

Select 3 answers

A.To add a column that computes a value based on other columns in the same table.

B.To combine two tables by merging columns from one table into another.

C.To create a date table that is not available in the data source.

D.To create a disconnected table for use in what-if analysis (e.g., parameter slicers).

E.To create a summary table that pre-aggregates data for better performance.

AnswersC, D, E

CALENDAR or CALENDARAUTO can create a date table for time intelligence.

Why this answer

Option C is correct because calculated tables in Power BI are created using DAX and stored in memory, allowing you to generate a date table when no suitable date table exists in the data source. This is a common pattern to ensure a complete date range for time intelligence functions, as Power BI requires a continuous date table for functions like TOTALYTD or SAMEPERIODLASTYEAR.

Exam trap

The trap here is that candidates often confuse calculated tables with calculated columns or Power Query merges, thinking any table-like operation qualifies, but Power BI strictly distinguishes between row-level calculations (calculated columns) and table-level transformations (calculated tables).

Practice this question →

179

MCQmedium

You are building a Power BI model with a fact table and multiple dimension tables. You want to enforce a rule that each fact row must have a matching row in a related dimension. Which modeling technique should you use?

A.Set the relationship cardinality to many-to-many.

B.Set the cross-filter direction to both.

C.Use a calculated column to check for existence.

D.Assume referential integrity in the relationship settings.

AnswerD

Power BI can assume referential integrity to optimize queries, but enforcement is typically done at the data source.

Why this answer

Option C is correct because referential integrity ensures every fact foreign key matches a dimension primary key. Option A is wrong because cardinality does not enforce integrity. Option C is correct.

Option D is wrong because cross-filter direction affects filtering, not integrity.

Practice this question →

180

Multi-Selectmedium

Which THREE of the following are benefits of using a star schema in Power BI? (Select three.)

Select 3 answers

A.Simplified DAX formulas

B.Supports many-to-many relationships natively

C.Improved query performance

D.Increased data redundancy

E.Easier for business users to understand

AnswersA, C, E

Less complex relationships lead to simpler measures.

Why this answer

Option A is correct because star schemas reduce the number of table joins and filter context complexity, which simplifies DAX formulas. Measures and calculated columns in Power BI rely on unambiguous relationships; a star schema ensures each fact table connects to dimension tables via one-to-many relationships, allowing DAX to propagate filters automatically without complex cross-filtering logic.

Exam trap

The trap here is that candidates confuse star schemas with snowflake schemas or assume that many-to-many relationships are a native benefit, when in fact star schemas rely on one-to-many relationships for optimal performance and simplicity.

Practice this question →

181

MCQeasy

A company has a Power BI semantic model that uses Import mode. The model contains a table with 10 million rows. The data source is a SQL Server view that takes 5 minutes to execute. The scheduled refresh is set to every hour. What is the likely impact on refresh performance?

A.Refresh will fail due to timeout on the gateway.

B.The model will automatically use incremental refresh to split the load.

C.Refresh will complete in parallel with the view execution.

D.Refresh will take at least 5 minutes plus data loading time.

AnswerD

The view execution time adds to the overall refresh duration.

Why this answer

Option D is correct because the refresh process must first execute the SQL Server view to retrieve data, which takes at least 5 minutes, and then load that data into the Import mode model. The total refresh time is the sum of the query execution time and the data loading time, so it will be at least 5 minutes plus additional time for loading 10 million rows.

Exam trap

The trap here is that candidates may assume the gateway has a default 5-minute timeout, leading them to choose Option A, but the actual default timeout is 10 minutes, and the question does not specify any custom timeout settings.

How to eliminate wrong answers

Option A is wrong because the default gateway timeout for SQL Server is 10 minutes, which is longer than the 5-minute view execution time, so a timeout is unlikely unless other factors like network latency or resource contention exist. Option B is wrong because incremental refresh is not automatic; it must be manually configured by the model designer using Power Query date-range parameters and policy settings, and it does not automatically split the load for a view that takes 5 minutes. Option C is wrong because refresh is a sequential process: the view must finish executing before any data loading can begin; there is no parallel execution between the view query and the data load in Import mode.

Practice this question →

182

MCQhard

You are reviewing a Power BI model definition in TMSL. The model has incremental refresh configured with two partitions. When the model refreshes on July 1, 2024, which data will be refreshed in Partition1?

A.Only new rows added since last refresh.

B.No data, because the partition is in the past.

C.Only data modified since the last refresh.

D.All rows where OrderDate is between 2024-01-01 and 2024-04-01.

AnswerD

That's the partition's range; on July 1 it's past data and will be fully refreshed.

Why this answer

Option C is correct. Incremental refresh partitions use parameters like RangeStart and RangeEnd. In this example, the partitions are hardcoded dates.

On July 1, 2024, Partition1 covers data from Jan 1 to Apr 1, which is in the past, so it will be fully refreshed. Option A is wrong because only past data is refreshed. Option B is wrong because incremental refresh refreshes all data in the partition, not just changes.

Option D is wrong because the partition is not excluded.

Practice this question →

183

MCQeasy

A Power BI developer needs to model data from two sources: an on-premises SQL Server database and a cloud-based Salesforce instance. The developer wants to create a star schema in Power BI. Which approach should the developer use to combine the data?

A.Use DirectQuery for both sources and create relationships in the model.

B.Use Power Query in Power BI Desktop to import both sources and merge/append queries as needed.

C.Use Power BI dataflows to ingest both sources and then reference them in a dataset.

D.Create a composite model using DirectQuery for SQL Server and Import for Salesforce.

AnswerB

Power Query can import from multiple sources and shape data.

Why this answer

Option B is correct because Power Query in Power BI Desktop is the appropriate tool to import data from both an on-premises SQL Server database and a cloud-based Salesforce instance, allowing the developer to merge or append queries as needed to shape the data into a star schema. This approach supports combining disparate sources into a single import model, which is essential for creating a star schema with fact and dimension tables. Using Power Query ensures that all data is loaded into memory, enabling fast query performance and full modeling capabilities.

Exam trap

The trap here is that candidates may think a composite model (Option D) is the best approach for combining on-premises and cloud sources, but the question specifically asks for creating a star schema, which is most easily achieved by importing all data into a single in-memory model using Power Query, avoiding the limitations and complexity of mixed storage modes.

How to eliminate wrong answers

Option A is wrong because using DirectQuery for both sources would prevent the developer from merging or appending data at query time; DirectQuery sends queries directly to the source and does not support combining data from multiple sources in a single query unless a composite model is used, and it limits star schema design due to performance constraints. Option C is wrong because Power BI dataflows are used for data preparation and storage in the Power BI service, but they are not the primary tool for combining data within a single Power BI Desktop model; referencing dataflows in a dataset still requires import or DirectQuery, and the question asks for the approach to combine data in the model, not just ingest it. Option D is wrong because creating a composite model with DirectQuery for SQL Server and Import for Salesforce would allow combining data, but it introduces complexity with mixed storage modes, potential performance issues, and limitations on relationships (e.g., many-to-many relationships require specific configurations), and it is not the simplest or most straightforward approach for building a star schema; importing both sources is preferred for full control over data shaping.

Practice this question →

184

MCQhard

You are reviewing a Power BI model definition in JSON. The Sales table contains a ProductID column but the Product table does not have a ProductID column; instead it has a ProductID column with dataType string. What issue will occur when you try to create a relationship?

A.The relationship will fail because the data types are incompatible

B.The relationship will fail because the column names must be different

C.The relationship will create a cross filter direction of single

D.The relationship will work but be many-to-many due to different data types

AnswerA

Relationships require matching data types between the columns.

Why this answer

Option C is correct because the data types do not match: Sales.ProductID is int64, while Product.ProductID is string. Power BI relationships require matching data types. Option A is wrong because the column names match, so no rename is needed.

Option B is wrong because many-to-one is allowed, but the data type mismatch is the problem. Option D is wrong because the relationship direction is not the primary issue.

Practice this question →

185

Multi-Selectmedium

Which THREE are best practices for managing relationships in Power BI? (Select exactly 3.)

Select 3 answers

A.Use many-to-many relationships whenever possible to simplify the model

B.Hide foreign key columns in dimension tables to prevent misuse

C.Use single-direction cross-filtering unless bidirectional is required

D.Always set cross-filter direction to both to allow full interactivity

E.Ensure that the data types of related columns match

AnswersB, C, E

Foreign keys are not needed in report visuals and can confuse users.

Why this answer

Options A, C, and D are correct. Option A: Using single-direction cross-filtering is generally recommended to avoid ambiguity and performance issues. Option C: Hiding foreign key columns in dimension tables prevents confusion and accidental use in visuals.

Option D: Ensuring data types match between related columns is essential for relationship creation. Option B is wrong because many-to-many relationships should be avoided when possible; they can be complex and impact performance. Option E is wrong because bidirectional filtering is not typically recommended; it can cause ambiguous filtering and performance degradation.

Practice this question →

186

MCQmedium

You are designing a Power BI model that includes a fact table with sales data and a dimension table for customers. Each customer can have multiple addresses, but you only need the primary address for analysis. The source system has a 'CustomerAddress' table with a 'IsPrimary' flag. What is the best approach to bring this into the model?

A.Use a DAX measure to filter the address table dynamically.

B.Import the entire CustomerAddress table and create an active relationship on the CustomerID column.

C.In Power Query, filter the CustomerAddress table to only include rows where IsPrimary = True, then merge with Customer.

D.Create a calculated table using SUMMARIZE to get the primary address per customer.

AnswerC

This loads only the needed rows, reducing model size.

Why this answer

Option C is correct because it uses Power Query to filter the CustomerAddress table to only primary addresses before merging with the Customer dimension. This ensures that only the necessary rows are imported into the model, reducing data volume and avoiding complex DAX or relationship overhead. The result is a clean, single-row-per-customer dimension that directly supports analysis without runtime filtering.

Exam trap

The trap here is that candidates often choose Option B, thinking that importing the full table and using a relationship is simpler, but they overlook the need to enforce a single primary address per customer, which requires additional filtering logic that complicates the model and degrades performance.

How to eliminate wrong answers

Option A is wrong because a DAX measure cannot filter a table at the model level; it only applies dynamic filters at query time, which would not resolve the need for a single primary address per customer in the dimension table and would cause performance issues with repeated evaluation. Option B is wrong because importing the entire CustomerAddress table with an active relationship on CustomerID would create a one-to-many relationship from Customer to multiple addresses, requiring additional logic (e.g., a DAX filter or a calculated table) to isolate the primary address, which defeats the goal of a clean dimension. Option D is wrong because using SUMMARIZE to create a calculated table in DAX would work but is less efficient than Power Query filtering; it adds a calculated table to the model that is computed at refresh time and cannot leverage Power Query's native data transformation capabilities, and it may introduce subtle issues with blank rows or performance if the source table is large.

Practice this question →

187

MCQmedium

A company has a large fact table with sales data. The Sales table contains columns: OrderDate, ShipDate, DueDate, LineTotal, ProductKey, CustomerKey, TerritoryKey. The company needs to analyze sales by fiscal year (April 1 to March 31) and by calendar year. What is the recommended approach to model the date dimension?

A.Use a single date dimension table and keep only one active relationship (e.g., to OrderDate). For other dates, create measures that filter the date table using CALCULATE and FILTER.

B.Create a separate date dimension table for each date column in the Sales table and relate each to the fact table.

C.Use a single date dimension table and create multiple active relationships using bridging tables or by duplicating the dimension table for each role.

D.Use a single date dimension table and create inactive relationships for ShipDate and DueDate. Use USERELATIONSHIP in DAX measures.

AnswerA

This is the recommended approach: keep one active relationship (e.g., to OrderDate) and use USERELATIONSHIP in DAX to activate the inactive relationships for ShipDate and DueDate when needed.

Why this answer

Option A is correct because it uses a single date dimension table with one active relationship (typically to OrderDate) and leverages CALCULATE with FILTER or USERELATIONSHIP for other date roles. This is the recommended star schema design in Power BI, as it avoids unnecessary table duplication and maintains model simplicity while supporting fiscal year analysis via a calculated column in the date table.

Exam trap

The trap here is that candidates often assume multiple date columns require multiple date tables (Option B) or that inactive relationships alone solve all date role needs (Option D), forgetting that fiscal year logic must be explicitly modeled in the date table.

How to eliminate wrong answers

Option B is wrong because creating separate date dimension tables for each date column violates star schema principles, leads to model bloat, and complicates cross-date analysis without any performance benefit. Option C is wrong because using bridging tables or duplicating the dimension table for each role is unnecessary and introduces redundancy; Power BI supports role-playing dimensions through inactive relationships and USERELATIONSHIP, not by duplicating tables. Option D is wrong because while it correctly uses inactive relationships and USERELATIONSHIP, it does not address the fiscal year requirement—a date table must include a fiscal year column (e.g., April 1 start) to enable fiscal year analysis, which is not mentioned in this option.

Practice this question →

188

MCQhard

You have a Power BI data model with a table named 'Sales' and a table named 'Targets'. The 'Sales' table contains daily sales data, and the 'Targets' table contains monthly sales targets for each product category. You need to create a measure that calculates the percentage of target achieved for the current month. The relationship between 'Sales' and 'Targets' is on CategoryID and Month. However, the 'Sales' table has a granularity of day, while 'Targets' has month. What issue might you encounter when creating this measure?

A.The relationship requires bidirectional cross-filtering to work correctly

B.The relationship may cause ambiguity because the granularity of the tables is different

C.The relationship will create a many-to-many cardinality that slows performance

D.The relationship will not work because the columns have different data types

AnswerB

Daily sales data aggregates to month level; the relationship may need special handling.

Why this answer

Option A is correct because the different granularities can cause ambiguity in the relationship if not properly managed. When 'Sales' is filtered by a specific day, the relationship to 'Targets' may not filter correctly because 'Targets' is at month level. Option B is wrong because data type mismatch is not mentioned.

Option C is wrong because many-to-many is not the issue; the relationship is likely many-to-one. Option D is wrong because bidirectional filtering is not necessary and could cause issues.

Practice this question →

189

MCQhard

You are building a data model for a retail company. The 'Sales' fact table has a column 'Discount' that is a percentage (0 to 1). You create a measure 'Total Discount Amount' = SUM(Sales[Discount]) * SUM(Sales[Quantity]) * SUM(Sales[UnitPrice]). However, the measure returns incorrect results when multiple discount percentages exist in the same filter context. What is the issue?

A.The measure contains a circular dependency.

B.The measure is performing aggregations at the wrong granularity; it should use SUMX to iterate over each row.

C.The measure is referencing columns from different tables without proper relationships.

D.The Discount column should be of type Decimal instead of Percentage.

AnswerB

SUMX ensures row-by-row calculation before summing.

Why this answer

The measure uses SUM on each column individually, which aggregates all values in the filter context before multiplying. When multiple discount percentages exist, this incorrectly multiplies the total of all discounts by the total of all quantities and total of all unit prices, rather than computing discount per row. The correct approach is to use SUMX to iterate over each row of the Sales table, calculating Discount * Quantity * UnitPrice per row and then summing those row-level results, ensuring accurate granularity.

Exam trap

The trap here is that candidates often assume SUM works correctly for all multiplicative measures, overlooking that SUM aggregates before multiplication, while SUMX is required for row-by-row calculations in DAX.

How to eliminate wrong answers

Option A is wrong because a circular dependency occurs when a measure or column references itself directly or indirectly, which is not the case here; the measure simply uses SUM on three columns. Option C is wrong because the measure references columns only from the Sales table, so no cross-table relationship issue exists. Option D is wrong because the data type of Discount (Percentage vs Decimal) does not affect the aggregation logic; the core problem is the aggregation granularity, not the column type.

Practice this question →

190

MCQeasy

You are building a Power BI report that uses a DirectQuery connection to a SQL Server database. You need to reduce the amount of data transferred to Power BI when users interact with visuals. What should you do?

A.Increase the query timeout setting

B.Apply filters in Power Query to reduce rows

C.Disable query reductions

D.Load all data into the Power BI model

AnswerB

Filters in Power Query are pushed down to the source, reducing data transfer.

Why this answer

Option B is correct because applying filters in Power Query reduces the number of rows retrieved from the SQL Server database before the data is transferred to Power BI. With a DirectQuery connection, queries are sent to the source each time a visual is interacted with, so reducing the row count at the query level minimizes network traffic and improves report performance.

Exam trap

The trap here is that candidates often confuse increasing query timeouts or disabling query reductions as performance improvements, when in fact they either do not address data transfer or actively worsen it.

How to eliminate wrong answers

Option A is wrong because increasing the query timeout setting does not reduce data transfer; it only allows longer-running queries to complete without timing out, which can actually increase the amount of data transferred if queries take longer. Option C is wrong because disabling query reductions would remove Power BI's built-in optimizations (like query folding and aggregations) that help minimize data transfer, making the problem worse. Option D is wrong because loading all data into the Power BI model would defeat the purpose of using DirectQuery, which is designed to keep data in the source; importing all data would increase memory usage and initial load time, not reduce data transfer during user interactions.

Practice this question →

191

MCQeasy

Refer to the exhibit. You apply the row-level security (RLS) policy shown to the Sales table. Which rows will be visible to users?

A.Rows where Amount is not blank

B.All rows in the Sales table

C.Only rows where Amount <= 1000

D.Only rows where Amount > 1000

AnswerD

The filter defines this condition.

Why this answer

Option B is correct because the filter only includes rows where Amount > 1000. Option A is wrong because it includes all rows. Option C is wrong because it includes less.

Option D is wrong because it excludes the condition.

Practice this question →

192

Multi-Selecteasy

Which THREE data types are supported in Power Query for Power BI?

Select 3 answers

A.Array

B.Decimal Number

C.Object

D.Text

E.Boolean

AnswersB, D, E

Decimal Number is supported for fractional numbers.

Why this answer

Power Query supports a specific set of primitive data types for columns in Power BI, including Decimal Number, Text, and Boolean. These types are used to define the structure and behavior of data during transformation and loading, ensuring compatibility with the Power BI engine.

Exam trap

The trap here is that candidates often confuse general programming data types (like Array or Object) with Power Query's specific type system, leading them to select options that are not valid in the M language or Power BI's data model.

Practice this question →

193

MCQeasy

You are creating a calculated table in Power BI that contains a list of unique products from the 'Sales' table. Which DAX function should you use?

A.SUMMARIZE(Sales, Sales[Product])

B.VALUES(Sales[Product])

C.DISTINCT(Sales[Product])

D.ALL(Sales[Product])

AnswerC

DISTINCT returns a single column of unique values, ignoring blanks.

Why this answer

Option C is correct because DISTINCT returns a single column of unique values. Option A is wrong because VALUES returns unique values but includes blank if present. Option B is wrong because SUMMARIZE creates a table with multiple columns.

Option D is wrong because ALL returns all rows, ignoring filters.

Practice this question →

194

MCQhard

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?

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

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

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

D.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.

AnswerD

This balances performance and granularity.

Why this answer

Option B is correct because creating an aggregate table in Synapse that pre-computes monthly completed deposit amounts by account type reduces the data volume significantly, and using DirectQuery on the aggregate with the ability to drill down to details via the original table (using composite model or dual storage) provides flexibility. Option A is wrong because DirectQuery on the full table would be slow. Option C is wrong because aggregations in Power BI still require importing the base data.

Option D is wrong because incremental refresh does not reduce the data volume for initial load.

Practice this question →

195

MCQeasy

You need to create a calculated column in Power BI that categorizes sales amounts as 'Low', 'Medium', or 'High' based on the value. The column should be evaluated row by row. Which DAX function should you use?

A.SWITCH

B.IF

C.CALCULATE

D.FORMAT

AnswerA

Designed for multiple condition evaluation.

Why this answer

Option B is correct. SWITCH is the appropriate DAX function for multiple conditions based on a single expression. Option A (IF) can be nested but is less efficient.

Option C (CALCULATE) modifies filter context, not suitable for row-by-row categorization. Option D (FORMAT) changes data type, not categorization.

Practice this question →

196

Multi-Selectmedium

You are designing a Power BI semantic model for a retail company. The model must support reporting on sales by product, store, and date. You need to decide on the modeling approach. Which TWO actions should you take? (Select exactly two.)

Select 2 answers

A.Create a separate date dimension table and mark it as a date table.

B.Connect the Sales table to Product and Store tables using relationships with many-to-one cardinality.

C.Add calculated columns for year, month, and quarter to the Sales table.

D.Create relationships between tables using the foreign key columns.

E.Combine all data into a single flat table to simplify the model.

AnswersA, B

A date table is required for proper time intelligence and filter context.

Why this answer

Option A is correct because creating a separate date dimension table and marking it as a date table enables time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) to work correctly in DAX. This also ensures that the date column is recognized as a continuous date hierarchy, allowing proper filtering and slicing by year, month, and quarter across all fact tables.

Exam trap

The trap here is that candidates often think adding calculated columns for date parts directly to the fact table is acceptable, but Microsoft explicitly tests the requirement for a separate, marked date table to enable time intelligence and maintain a proper star schema.

Practice this question →

197

MCQeasy

You have a fact table 'Orders' with columns: OrderID, CustomerID, OrderDate, SalesAmount. You also have a 'Calendar' table with Date, Year, Month, Quarter. The Calendar table is marked as a date table. You want to calculate total sales for the current month. Which DAX measure should you use?

A.TOTALYTD(SUM(Orders[SalesAmount]), Calendar[Date])

B.SUM(Orders[SalesAmount])

C.CALCULATE(SUM(Orders[SalesAmount]), DATESMTD(Calendar[Date]))

D.TOTALMTD(SUM(Orders[SalesAmount]), Calendar[Date])

AnswerD

TOTALMTD is a time intelligence function that uses the date table.

Why this answer

Option B is correct because TOTALMTD calculates month-to-date using the date context. Option A is wrong because it's not a function. Option C is wrong because it ignores the date table.

Option D is wrong because it's for year-to-date.

Practice this question →

198

MCQhard

You have a Power BI model with a fact table 'Sales' and dimensions 'Customer', 'Product', 'Date'. You need to enforce that every fact row has a valid CustomerKey. Which approach enforces referential integrity in Power BI?

A.Merge Sales and Customer tables into a single table

B.Hide CustomerKey columns in both tables

C.Assume all CustomerKey values are valid

D.Set the relationship between Sales and Customer to 'Enforce Referential Integrity'

AnswerD

This setting validates that every Sales.CustomerKey exists in Customer.

Why this answer

Option C is correct because marking the relationship as 'Enforce Referential Integrity' in Power BI ensures that only valid keys are used. Option A is wrong because combining tables changes the model structure. Option B is wrong because hiding columns does not enforce integrity.

Option D is wrong because the assumption is not enforced natively.

Practice this question →

199

MCQeasy

A company has a Power BI semantic model that uses DirectQuery to a SQL Server database. The model contains a large fact table with sales data. Users report that reports using this model are slow. Which design change would most improve query performance?

A.Remove all relationships between tables.

B.Switch the model to Import mode.

C.Remove unnecessary columns from the fact table.

D.Disable the 'Reduce queries' option in report settings.

AnswerC

Reducing columns minimizes data retrieved from source, improving query performance.

Why this answer

Option C is correct because removing unnecessary columns from the fact table reduces the amount of data that must be transferred from SQL Server to Power BI for each query. In DirectQuery mode, every report interaction sends a query to the source database, so fewer columns mean smaller result sets and faster query execution. This directly addresses the performance bottleneck caused by a large fact table without changing the underlying storage mode.

Exam trap

The trap here is that candidates often assume switching to Import mode is always the best performance fix, but the question specifically asks for a design change that improves query performance in DirectQuery mode, where reducing column count is a more targeted and less disruptive solution.

How to eliminate wrong answers

Option A is wrong because removing all relationships between tables would break the model's ability to filter and aggregate data across tables, making reports unusable and not improving query performance. Option B is wrong because switching to Import mode would require loading the entire large fact table into memory, which could cause memory pressure and long refresh times, and it does not address the root cause of slow queries in DirectQuery mode. Option D is wrong because disabling the 'Reduce queries' option in report settings would actually increase the number of queries sent to the source, making performance worse, not better.

Practice this question →

200

MCQmedium

A data model contains a table 'Sales' with columns: Date, ProductID, Quantity, Amount. There is a 'Products' table with columns: ProductID, ProductName, CategoryID. A measure 'Total Sales' = SUM(Sales[Amount]) returns correct values. However, when a user creates a visual with CategoryID from 'Products' and 'Total Sales', some categories show blank. What is the most likely cause?

A.There are ProductID values in Sales that do not exist in Products table.

B.The 'Total Sales' measure is not properly referencing the Sales table.

C.The relationship between Sales and Products is set to many-to-one, single direction.

D.The relationship is set to both directions (bidirectional).

AnswerA

This causes those rows to have no matching category, resulting in blank category in the visual.

Why this answer

Option A is correct because when ProductID values in the Sales table do not have matching entries in the Products table, the relationship between the two tables will result in blank CategoryID values for those unmatched rows. In Power BI, a many-to-one relationship (the default) filters from the 'one' side (Products) to the 'many' side (Sales), but if a Sales row has a ProductID not present in Products, it cannot be matched, and any column from Products (like CategoryID) will appear as blank in visuals. This is a classic data integrity issue where the fact table contains orphaned foreign keys.

Exam trap

The trap here is that candidates often assume the relationship direction or cross-filter setting is the culprit, but the real issue is data integrity—orphaned foreign keys in the fact table—which is a common data modeling pitfall tested in the PL-300 exam.

How to eliminate wrong answers

Option B is wrong because the measure 'Total Sales' = SUM(Sales[Amount]) explicitly references the Sales table, and the question states it returns correct values, so the measure definition is not the issue. Option C is wrong because a many-to-one, single-direction relationship is the default and correct configuration for this scenario; it does not cause blanks for unmatched rows—it simply means the filter context flows from Products to Sales, but orphaned Sales rows still produce blanks in Products columns. Option D is wrong because bidirectional cross-filtering would not fix the blank issue; it would allow filters to flow in both directions but still cannot match a Sales row with a ProductID that has no corresponding row in Products.

Practice this question →

201

MCQhard

You are creating a Power BI report that uses a composite model (DirectQuery for large tables and Import for small dimension tables). You want to ensure that measures referencing the DirectQuery tables are responsive. Which of the following design choices should you avoid?

A.Use complex time intelligence measures that iterate over the fact table

B.Use measures that aggregate columns rather than rows

C.Create a date dimension table imported from the source

D.Create user-defined aggregations for the DirectQuery table

AnswerA

Iteration over large DirectQuery tables can be slow.

Why this answer

Using many DAX functions that require iteration over large tables can cause performance degradation in DirectQuery mode. Option D is correct. Option A is acceptable because dimension tables are small.

Option B is a best practice. Option C is acceptable because aggregations can improve performance.

Practice this question →

202

MCQmedium

You have a Power BI report that shows sales by region. The data model contains a 'Sales' fact table and a 'Region' dimension table. When you filter by a specific region, the total sales for that region is correct, but the grand total shows all sales. What is the likely cause?

A.The relationship is set to single cross-filter direction

B.The measure is using SUM instead of SUMX

C.The Region table is not marked as a dimension table

D.The relationship between Sales and Region is inactive

AnswerD

An inactive relationship does not propagate filters, so grand total ignores region filter.

Why this answer

Option A is correct because if the relationship is inactive, filters won't propagate. Option B is wrong because measure calculation is not the issue. Option C is wrong because bidirectional would actually propagate.

Option D is wrong because cross-filter direction is relevant.

Practice this question →

203

Multi-Selecteasy

Which TWO of the following are valid methods to create a date table in Power BI?

Select 2 answers

A.Using the 'Create Date Table' button in the ribbon.

B.Using the 'Generate Date Table' option in Power Query.

C.Using the 'Mark as Date Table' feature on an existing table with dates.

D.Manually typing dates in Excel and importing.

E.Using DAX function CALENDARAUTO() in a calculated table.

AnswersC, E

This is a common method.

Why this answer

Option B is correct: using CALENDARAUTO auto-detects date range from model. Option D is correct: marking a table as date table is a standard method. Option A is incorrect because manual typing is not practical.

Option C is incorrect because there is no 'Create Date Table' button. Option E is incorrect because there is no 'Generate Date Table' in the modeling tab.

Practice this question →

204

MCQeasy

You have a Power BI data model with a 'Date' table that contains continuous dates from January 1, 2020, to December 31, 2025. The 'Sales' table has a relationship with the 'Date' table. You need to create a measure that calculates the total sales for the last 12 months from the current context. Which DAX function should you use?

A.PREVIOUSMONTH

B.DATEADD

C.DATESINPERIOD

D.SAMEPERIODLASTYEAR

AnswerC

DATESINPERIOD returns a set of dates in a period from a start date.

Why this answer

Option D is correct. DATESINPERIOD allows you to specify a range of dates (e.g., last 12 months) relative to a end date. Option A is incorrect because SAMEPERIODLASTYEAR returns the same period one year ago, not the last 12 months.

Option B is incorrect because DATEADD shifts dates by an interval. Option C is incorrect because PREVIOUSMONTH returns the previous month only.

Practice this question →

205

MCQhard

You have a Power BI model with a large fact table (100 million rows) and several dimension tables. You need to improve query performance for a report that uses a date slicer. The report currently takes 10 seconds to load. What is the most effective optimization?

A.Partition the fact table by date range.

B.Remove unused columns from the fact table.

C.Increase the Power BI service capacity.

D.Create an aggregated table that summarizes data at the month level.

AnswerA

Date partitioning allows incremental refresh and reduces query data.

Why this answer

Partitioning the fact table by date range allows Power BI to perform query folding and only scan the relevant partitions when a date slicer is applied. This reduces the amount of data loaded into memory and processed, directly addressing the 100-million-row fact table and the 10-second load time. Partitioning is a native optimization for large tables in DirectQuery or Import mode when using date-based filtering.

Exam trap

The trap here is that candidates often choose aggregation (Option D) as a quick fix for performance, but they overlook that the report uses a date slicer requiring day-level granularity, making aggregation inappropriate unless the user is willing to lose detail; partitioning directly addresses the root cause of scanning too many rows.

How to eliminate wrong answers

Option B is wrong because removing unused columns reduces storage and refresh time but does not significantly improve query performance for a date-sliced report; the bottleneck is row scanning, not column width. Option C is wrong because increasing Power BI service capacity (premium SKU) improves concurrency and resource limits, not the per-query scan time for a single report; the issue is data model design, not capacity. Option D is wrong because creating an aggregated table at the month level can improve performance for month-level granularity, but the report uses a date slicer (likely day-level), so aggregation loses detail and may not satisfy the user's need for daily filtering; partitioning is more effective for the exact date range requested.

Practice this question →

206

MCQhard

You have a Power BI model with a table 'Sales' and a table 'Date'. The relationship between them is many-to-one (Sales[OrderDate] -> Date[Date]). You notice that when you filter by Date[Year], some sales rows are not included. What is the most likely cause?

A.The relationship has Referential Integrity enforced.

B.The relationship is inactive.

C.The cross filter direction is set to Single.

D.The Date table has blank rows.

AnswerB

Inactive relationships are not used in filter propagation.

Why this answer

Option C is correct. If the relationship is inactive (dashed line), it won't be used unless activated via USERELATIONSHIP. Option A is wrong because cross filter direction being single does not exclude rows; it just prevents bidirectional filtering.

Option B is wrong because blank rows in Date would not cause missing sales; they would cause sales to be unmatched. Option D is wrong because 'Referential Integrity' is a concept in data warehouse, not a setting in Power BI relationships.

Practice this question →

207

MCQmedium

You have a Power BI model where the Sales table is filtered by a Customer dimension. Users report that when they filter a measure from the Customer table (e.g., Customer Count), it does not affect the Sales visual. What is the most likely cause?

A.The relationship is inactive

B.The relationship cardinality is many-to-many

C.The cross-filtering direction is set to one direction (Customer -> Sales), but the filter is applied on Customer

D.The security filtering behavior is set to oneDirection

AnswerC

One-direction filtering from Customer to Sales means Sales filters Customer, not the other way.

Why this answer

The crossFilteringBehavior is set to 'oneDirection', which means filters flow only from Customer to Sales, not the other way. To allow Customer filters to affect Sales, it should be 'bothDirections' or use bidirectional cross-filtering. Option A is correct.

Option B is wrong because security filtering does not impact user filtering. Option C is wrong because the relationship is active. Option D is wrong because the cardinality is not specified but oneDirection is the key.

Practice this question →

208

MCQhard

You are examining a Power BI model definition. The Date table is generated via Power Query M code. The partition mode is 'import'. What will happen when the dataset refreshes after December 31, 2025?

A.The existing table will be appended with new rows.

B.The refresh will fail because EndDate is in the past.

C.The table will be refreshed with the same date range (2020-2025).

D.New dates after 2025-12-31 will be added incrementally.

AnswerC

The M code defines a static range; refresh reloads that range.

Why this answer

Option C is correct. Since the partition mode is 'import' (not incremental), the entire table is replaced on each refresh. The M code generates dates from 2020-01-01 to 2025-12-31, so after 2025, no new dates will be added unless the code is updated.

Option A is wrong because incremental refresh is not configured. Option B is wrong because the table is fully reloaded, not appended. Option D is wrong because the refresh will succeed, but no new dates beyond 2025 will appear.

Practice this question →

209

MCQhard

You run the above DAX query in DAX Studio. The result set shows a row for each Category and Year combination, but the Total Sales and Total Cost columns are both blank. What is the most likely reason?

A.The Sales[Amount] column contains only BLANK values

B.SUMMARIZECOLUMNS cannot compute two aggregate columns

C.The ORDER BY clause is causing the aggregation to fail

D.There is no active relationship between the Sales table and the Product and Date tables

AnswerD

Without relationships, the filter context does not propagate.

Why this answer

SUMMARIZECOLUMNS automatically adds a filter context. If there is no relationship between Sales and Product or Date, the columns will be blank. Option B is correct.

Option A is wrong because SUMMARIZECOLUMNS can compute multiple expressions. Option C is wrong because BLANK treatment does not affect sum. Option D is wrong because ordering does not affect calculation.

Practice this question →

210

MCQmedium

You have a Power BI semantic model with a fact table containing sales data at the transaction level. You need to create a measure that calculates the number of distinct customers who made a purchase in the current period and also made a purchase in the previous period. Which DAX pattern should you use?

A.Use CALCULATE with a filter that intersects customers in both periods.

B.Use the PREVIOUSCUSTOMER function.

C.Use DISTINCTCOUNT over a concatenated key.

D.Add a calculated column to identify returning customers.

AnswerA

This pattern counts customers who appear in both periods.

Why this answer

Option A is correct because the semi-additive pattern using CALCULATE and FILTER with an EARLIER-like approach is typical for retention. Option B is incorrect because DISTINCTCOUNT is not a standard function. Option C is incorrect because a calculated column is less flexible.

Option D is not a standard pattern.

Practice this question →

211

Multi-Selecthard

Which TWO actions can improve performance of a Power BI DirectQuery model?

Select 2 answers

A.Switch the model to Import mode

B.Enable bidirectional cross-filtering on all relationships

C.Ensure proper indexing in the source database

D.Use calculated columns with complex logic

E.Reduce the number of columns in the query

AnswersC, E

Indexing speeds up query execution.

Why this answer

Options A and D are correct. Reducing columns and increasing data source indexing improve query performance. Option B is wrong because bidirectional filtering often decreases performance.

Option C is wrong because complex calculated columns slow queries. Option E is wrong because Import mode is not DirectQuery.

Practice this question →

212

MCQmedium

You are a Power BI developer for an e-commerce company. You have a semantic model that contains a table named Orders with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, and SalesAmount. You also have a table named Products with columns: ProductID, ProductName, Category, and Price. You need to create a measure that calculates the percentage of total sales for each product category. The measure should be used in a matrix visual with Category on rows. Which DAX expression should you use?

A.% Total Sales = DIVIDE(SUM(Orders[SalesAmount]), CALCULATE(SUM(Orders[SalesAmount]), ALLSELECTED(Products[Category])))

B.% Total Sales = SUM(Orders[SalesAmount]) / CALCULATE(SUM(Orders[SalesAmount]), ALLSELECTED(Orders))

C.% Total Sales = DIVIDE(SUM(Orders[SalesAmount]), CALCULATE(SUM(Orders[SalesAmount]), ALLEXCEPT(Products, Products[Category])))

D.% Total Sales = DIVIDE(SUM(Orders[SalesAmount]), CALCULATE(SUM(Orders[SalesAmount]), ALL(Products)))

AnswerA

ALLSELECTED keeps the filter context from the visual's slicers but removes the category filter, giving the total for all categories.

Why this answer

Option A is correct: ALLSELECTED respects the current filter context for the denominator, giving the correct percentage. Option B uses ALL, which removes all filters, not just the category. Option C uses ALLEXCEPT, which removes filters from all columns except Category, which is not what is needed.

Option D is incorrect syntax.

Practice this question →

213

Multi-Selecthard

Which THREE factors can cause a DAX measure to return BLANK unexpectedly?

Select 3 answers

A.Division by zero.

B.Using SUM on a column with blanks.

C.Using FILTER with a false condition.

D.Referencing a column name that does not exist.

E.Using BLANK in arithmetic operations.

AnswersA, D, E

DAX returns BLANK for division by zero.

Why this answer

Option A is correct because division by zero returns BLANK. Option B is correct because referencing a column that doesn't exist causes BLANK. Option C is correct because BLANK propagation in arithmetic can cause BLANK.

Option D is wrong because SUM ignores blanks. Option E is wrong because filters do not return BLANK; they return a table.

Practice this question →

214

MCQmedium

You have a Power BI model with a date table. You need to ensure that time intelligence functions like TOTALYTD work correctly. What is the most important requirement for the date table?

A.The date table must be marked as a date table in Power BI

B.All date columns must be visible

C.The date table must have a single date column

D.The date table must contain continuous dates without gaps

AnswerA

Marking as date table enables time intelligence.

Why this answer

Option A is correct because Power BI requires that a date table be explicitly marked as a date table via the 'Mark as Date Table' option in the Table tools ribbon. This action tells Power BI that the table contains unique, non-blank date values that can be used by time intelligence functions like TOTALYTD, ensuring they reference the correct date column for calculations such as year-to-date aggregation.

Exam trap

The trap here is that candidates often confuse the requirement to mark a date table with the best practice of having continuous dates, leading them to select Option D, but Power BI does not enforce date continuity for time intelligence functions to work.

How to eliminate wrong answers

Option B is wrong because visibility of date columns is irrelevant; Power BI does not require all date columns to be visible for time intelligence functions to work correctly. Option C is wrong because a date table can have multiple date columns (e.g., Date, FiscalDate), but only one column is used as the primary date column when the table is marked; having a single date column is not a requirement. Option D is wrong because while continuous dates without gaps are a best practice for accurate time intelligence, Power BI does not enforce this as a requirement; the table can have gaps and still be marked as a date table, though results may be misleading.

Practice this question →

215

MCQmedium

You have a Power BI model with a table 'Sales' and a related 'Product' table. You want to count the number of distinct products sold. Which DAX expression should you use?

A.COUNTROWS(Sales)

B.COUNT(Sales[ProductID])

C.DISTINCTCOUNT(Sales[ProductID])

D.COUNTA(Sales[ProductID])

AnswerC

DISTINCTCOUNT counts unique values.

Why this answer

Option C is correct because DISTINCTCOUNT counts unique values. Option A is wrong because COUNT counts non-blank cells. Option B is wrong because COUNTA counts non-blank values.

Option D is wrong because COUNTROWS counts rows.

Practice this question →

216

MCQmedium

You are designing a data model for a report that shows sales by product category and by month. Which table configuration is most efficient?

A.Two fact tables: one for date and one for product.

B.A date dimension table, a product dimension table, and a sales fact table.

C.A single table with all columns.

D.One dimension table containing date and product attributes.

AnswerB

Star schema is efficient for this scenario.

Why this answer

Option D is correct because a star schema with separate tables for date and product is the most efficient for filtering and performance. Option A is wrong because combining date and product into one dimension is not needed. Option B is wrong because a single table is inefficient.

Option C is wrong because a bridge table is not needed for a simple star schema.

Practice this question →

217

Multi-Selecthard

Which TWO DAX functions can be used to implement row-level security (RLS) in Power BI?

Select 3 answers

A.FILTER

B.LOOKUPVALUE

C.USERNAME

D.CALCULATE

E.USERPRINCIPALNAME

AnswersA, C, E

FILTER is commonly used in RLS to restrict rows based on a condition.

Why this answer

LOOKUPVALUE is not used for RLS; it retrieves a single value. FILTER is used within RLS expressions to filter rows. USERNAME and USERPRINCIPALNAME are used to get the current user.

CALCULATE is not directly used in RLS filtering.

Practice this question →

218

Multi-Selectmedium

Which TWO actions are best practices for optimizing Power BI data models?

Select 2 answers

A.Replace text-based relationship columns with integer keys.

B.Use calculated columns instead of measures where possible.

C.Hide columns that are not used in reports.

D.Remove columns that are not used in reports.

E.Use many-to-many relationships instead of bridge tables.

AnswersA, D

Integer keys improve join performance.

Why this answer

Option A is correct because replacing text-based relationship columns with integer keys (surrogate keys) reduces storage size and improves join performance. Power BI's VertiPaq engine compresses integer columns far more efficiently than text columns, leading to faster query execution and smaller memory footprint.

Exam trap

The trap here is that candidates often confuse 'hiding' columns (which only affects report visibility) with 'removing' columns (which actually reduces model size and improves performance), leading them to select Option C instead of D.

Practice this question →

219

MCQmedium

Refer to the exhibit. You are using DAX Studio to profile a Power BI semantic model. The query above returns a table with sales totals by year and product category. However, you notice that the results do not include rows for years or categories with no sales. Which DAX function should you use to ensure all combinations of year and category appear, even with blank sales?

A.ADDCOLUMNS

B.CROSSJOIN

C.SUMMARIZE

D.EXPAND

AnswerB

CROSSJOIN creates all combinations of the distinct values from the columns.

Why this answer

Option D is correct because CROSSJOIN generates all combinations of the columns, then you can left join with the sales table. Option A is for cross-tabulation. Option B is an older function that may not handle blanks well.

Option C is not a valid DAX function.

Practice this question →

220

MCQhard

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?

A.Add KEEPFILTERS to preserve existing filters

B.Rewrite the measure using CALCULATE with a simple SUM instead of SUMX

C.Change the storage mode of the table from Import to DirectQuery

D.Remove all filter functions from the measure

AnswerB

CALCULATE is more efficient than iterating over rows.

Why this answer

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.

Practice this question →

221

Multi-Selectmedium

A Power BI developer is designing a semantic model that will be used by multiple departments. The developer wants to ensure that the model follows best practices for performance and usability. Which TWO actions should the developer take?

Select 2 answers

A.Use a star schema with dimension and fact tables.

B.Hide foreign key columns from report view.

C.Use calculated columns instead of measures for aggregations.

D.Combine all data into a single flat table to simplify the model.

E.Enable bidirectional cross-filtering between all tables.

AnswersA, B

Star schema optimizes query performance and simplifies user understanding.

Why this answer

A star schema organizes data into dimension and fact tables, reducing data redundancy and improving query performance by enabling efficient aggregations and filter propagation. This design aligns with Power BI best practices for semantic models, as it minimizes the number of tables and relationships, leading to faster DAX calculations and better compression.

Exam trap

The trap here is that candidates often confuse calculated columns with measures, thinking both are interchangeable for aggregations, or they assume a flat table simplifies the model, ignoring the severe performance penalties in Power BI's columnar storage engine.

Practice this question →

222

Multi-Selecteasy

Which TWO of the following are valid reasons to create a separate date table in a Power BI semantic model? (Select two.)

Select 2 answers

A.To calculate measures that require date arithmetic.

B.To enable time intelligence functions like TOTALYTD.

C.To provide a hierarchy of year, quarter, month, and day for filtering.

D.To create many-to-many relationships between fact tables.

E.To allow multiple active relationships from the same fact table to the date table.

AnswersB, C

Time intelligence functions require a marked date table.

Why this answer

Options A and C are correct. A date table is required for time intelligence functions (A) and for filtering by date attributes not present in the fact table (C). Option B is incorrect because relationships are one-to-many.

Option D is incorrect because multiple active relationships cause ambiguity. Option E is incorrect because measures can be calculated without a date table, but time intelligence functions need it.

Practice this question →

223

Multi-Selectmedium

Which TWO approaches can you use to implement row-level security (RLS) in Power BI?

Select 2 answers

A.Assign users to security groups in the dataset.

B.Use column-level security to restrict row visibility.

C.Use object-level security to restrict rows.

D.Create roles with static filters.

E.Use dynamic filters based on USERNAME().

AnswersD, E

Static RLS uses fixed filter expressions.

Why this answer

Option A is correct because static RLS uses roles with filters based on fixed values. Option D is correct because dynamic RLS uses the USERNAME or USERPRINCIPALNAME function to filter based on the current user. Option B is wrong because column-level security is not RLS; it hides columns.

Option C is wrong because object-level security restricts access to entire tables, not rows. Option E is wrong because Power BI does not support RLS based on groups directly; you need to use roles.

Practice this question →

224

MCQhard

You are designing a star schema for a sales analysis report. The source data includes Order Details, Products, Customers, and Dates. Which table should be the fact table?

A.Dates

B.Customers

C.Order Details

D.Products

AnswerC

Order Details has numeric measures and foreign keys to dimensions, making it the fact table.

Why this answer

Option A is correct because Order Details contains transactional measures (quantity, price) and foreign keys to dimensions. Option B (Products) is a dimension. Option C (Customers) is a dimension.

Option D (Dates) is a dimension.

Practice this question →

225

MCQeasy

You need to create a hierarchy in Power BI that allows users to drill down from Year to Quarter to Month. Which of the following actions is required?

A.Create a relationship between the Year, Quarter, and Month columns.

B.Create the hierarchy in the report view by grouping fields.

C.In the Fields pane, right-click a column and select 'Create hierarchy', then add child columns.

D.Use Power Query to merge the columns into one.

AnswerC

This is the standard method.

Why this answer

Option C is correct. A hierarchy is created by selecting multiple columns in the same table and defining a parent-child relationship. Option A is incorrect because relationships are between tables, not within a hierarchy.

Option B is incorrect because hierarchies can be created in the model view, not only in report view. Option D is incorrect because you cannot create a hierarchy in Power Query.

Practice this question →

← PreviousPage 3 of 4 · 268 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Model the data questions.

Start 20-question session