Courseiva

CCNA Model the data Questions

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

Practice these questionsDomain overviewAll questions
226
MCQmedium

You have a Power BI data model with a table named Employees that includes columns: EmployeeID, ManagerID, and EmployeeName. You need to create a hierarchy that shows the reporting structure. Which type of relationship is required?

A.Many-to-many relationship
B.Bidirectional cross-filtering
C.A self-referencing relationship
D.One-to-many relationship to a separate table
AnswerC

A self-referencing relationship connects EmployeeID to ManagerID within the same table, enabling a hierarchy.

Why this answer

A self-referencing relationship (Option C) is required because the Employees table contains both EmployeeID and ManagerID, where ManagerID references EmployeeID within the same table. This allows Power BI to create a parent-child hierarchy that accurately represents the reporting structure, such as an org chart. In Power BI, this is implemented by creating a relationship from ManagerID to EmployeeID within the same table.

Exam trap

The trap here is that candidates confuse a self-referencing relationship with a one-to-many relationship to a separate table, thinking a manager table is required, when Power BI can handle parent-child hierarchies directly within a single table.

How to eliminate wrong answers

Option A is wrong because a many-to-many relationship would imply multiple managers per employee or multiple employees per manager in a non-hierarchical way, which does not model a standard reporting structure. Option B is wrong because bidirectional cross-filtering is a filter direction setting, not a relationship type; it controls how filters propagate across relationships but does not define the structure needed for a hierarchy. Option D is wrong because a one-to-many relationship to a separate table would require a distinct manager table, which is unnecessary and would break the self-referencing pattern needed for a single-table hierarchy.

Practice this question →
227
MCQmedium

You have a Power BI model with a 'Date' table marked as a date table. You need to create a measure that calculates the running total of sales over the last 12 months. Which DAX function should you use?

A.PREVIOUSYEAR
B.DATESYTD
C.DATESINPERIOD
D.DATEADD
AnswerC

DATESINPERIOD can return a set of dates going back 12 months from the current date.

Why this answer

Option C, DATESINPERIOD, is correct because it allows you to define a dynamic window of dates—specifically, the last 12 months ending with the latest date in the current filter context. When used with a measure like CALCULATE(SUM(Sales[Amount]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)), it shifts the date range backward by 12 months from the last visible date, making it ideal for a rolling 12-month total. The 'Date' table being marked as a date table ensures that time intelligence functions respect the continuous date range.

Exam trap

The trap here is that candidates often confuse DATESYTD (which is for year-to-date, not rolling) with a trailing 12-month calculation, or they mistakenly think PREVIOUSYEAR can handle a dynamic window, when in fact it only returns a fixed prior calendar year.

How to eliminate wrong answers

Option A is wrong because PREVIOUSYEAR returns the entire previous calendar year (e.g., all of 2023) relative to the current context, not a rolling 12-month window that moves with each period. Option B is wrong because DATESYTD calculates a year-to-date total from the start of the calendar year to the last date in context, which is a fixed annual accumulation, not a trailing 12-month period. Option D is wrong because DATEADD shifts a set of dates by a specified interval (e.g., -1 year) but returns a set of dates shifted from the original, not a contiguous 12-month window ending at the current context; it requires additional logic to create a rolling total.

Practice this question →
228
MCQhard

You are a data analyst for a financial services firm. You have a Power BI semantic model that uses DirectQuery mode against a SQL Server data warehouse. The model includes a large fact table named Transactions with billions of rows. Users are complaining that the report is slow when filtering by date range. You need to improve the performance of date-related queries without changing the data source structure. You cannot switch to Import mode due to data volume constraints. What should you do?

A.Switch to Import mode for the Transactions table and schedule frequent refreshes.
B.Create a date dimension table in the SQL Server data warehouse and use that table in the Power BI model for date filtering. Ensure the date column is indexed.
C.Add an index on the date column in the fact table and use the date column directly in visuals.
D.Create a calculated table in Power BI using DAX to generate a distinct list of dates from the Transactions table.
AnswerB

A date dimension table reduces the cardinality of date columns and improves query performance.

Why this answer

Option A is correct: creating a date dimension table in the data warehouse and using it for filtering reduces the number of rows scanned. Option B would increase model size and maintenance. Option C is not possible because calculated tables are not supported in DirectQuery.

Option D would not help performance.

Practice this question →
229
MCQhard

You are a data analyst for a retail company. You are building a Power BI model to analyze inventory levels across multiple warehouses. The source data is a SQL Server database with two tables: Inventory (WarehouseID, ProductID, StockOnHand, ReorderPoint) and Product (ProductID, ProductName, Category, UnitPrice). The Inventory table has 500,000 rows, and Product has 10,000 rows. You import both tables into Power BI. You need to create a measure that calculates the total value of inventory (StockOnHand * UnitPrice) for products that are below their reorder point. You create the following measure: TotalValueBelowReorder = CALCULATE( SUMX(Inventory, Inventory[StockOnHand] * RELATED(Product[UnitPrice])), Inventory[StockOnHand] < Inventory[ReorderPoint] ) However, the measure returns an incorrect total. You suspect an issue with the filter context. What is the most likely cause of the incorrect result?

A.SUMX cannot iterate over Inventory directly because it is a table reference; you need to use VALUES or a measure.
B.The filter condition is applied to the entire Inventory table, but the row context from SUMX is not being considered, causing the filter to apply incorrectly.
C.The measure needs to be written using SUM instead of SUMX because StockOnHand and UnitPrice are both numeric columns.
D.The RELATED function fails because the relationship between Inventory and Product is inactive.
AnswerB

The CALCULATE function modifies the filter context, but the filter is on the Inventory table without considering the current row from SUMX. The correct approach is to use FILTER inside CALCULATE to iterate over rows.

Why this answer

Option B is correct because the filter condition `Inventory[StockOnHand] < Inventory[ReorderPoint]` is applied as a table-level filter on the entire Inventory table, not within the row context of SUMX. SUMX iterates over each row of Inventory, but the CALCULATE filter overrides the row context, causing the filter to be evaluated against the whole table rather than per row. This leads to an incorrect total because the condition is not evaluated for each individual product as intended.

Exam trap

The trap here is that candidates mistakenly think the filter inside CALCULATE is evaluated row-by-row within SUMX, but in reality, CALCULATE's filter arguments are applied as table-level filters that override the row context, causing the condition to be evaluated incorrectly.

How to eliminate wrong answers

Option A is wrong because SUMX can iterate directly over a table reference like Inventory; it does not require VALUES or a measure to iterate. Option C is wrong because SUM cannot perform row-by-row multiplication of StockOnHand and UnitPrice across two tables; SUMX is necessary to iterate over Inventory rows and use RELATED to fetch UnitPrice. Option D is wrong because the relationship between Inventory and Product is assumed to be active (the default single-direction relationship from Product to Inventory), and RELATED works correctly in a row context; there is no indication of an inactive relationship.

Practice this question →
230
MCQmedium

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?

A.Readmission Rate = DIVIDE( COUNTROWS(Readmissions), COUNTROWS(Admissions) )
B.Readmission Rate = DIVIDE( COUNTROWS(Admissions), DISTINCTCOUNT(Admissions[PatientID]) )
C.Readmission Rate = VAR ReadmitPatients = CALCULATETABLE( VALUES(Readmissions[PatientID]), Readmissions[ReadmitDate] <= Admissions[DischargeDate] + 30 ) RETURN DIVIDE( COUNTROWS(ReadmitPatients), DISTINCTCOUNT(Admissions[AdmissionID]) )
D.Readmission Rate = DIVIDE( COUNT(Readmissions[ReadmissionID]), COUNT(Admissions[AdmissionID]) )
AnswerC

Correctly counts distinct patients readmitted within 30 days per admission.

Why this answer

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.

Practice this question →
231
MCQhard

You have the above measure in a Power BI model. The 'Date' table is marked as a date table and has a relationship to Sales[OrderDate]. The measure returns blank for all months. What is the most likely cause?

A.The Date table does not contain all dates in the continuous range
B.The relationship is set to filter in the wrong direction
C.The measure uses incorrect syntax for DATESYTD
D.The 'Date' table is not marked as a date table
AnswerA

DATESYTD requires a continuous date range; gaps cause blank results.

Why this answer

Option C is correct because DATESYTD requires a continuous date range. If the Date table does not contain all dates (e.g., missing weekends or holidays), DATESYTD may not work correctly. Option A is wrong because the relationship direction is fine.

Option B is wrong because the Date table is marked as date table, so date filter direction is not an issue. Option D is wrong because the measure syntax is correct.

Practice this question →
232
Multi-Selecthard

Which THREE of the following are valid considerations when using Power BI DirectQuery mode?

Select 3 answers
A.Data is imported into the Power BI dataset.
B.You cannot create relationships between tables.
C.Queries are sent to the underlying data source in real time.
D.Row-level security (RLS) can be applied.
E.Some DAX functions are not supported.
AnswersC, D, E

DirectQuery does not import data; it queries the source.

Why this answer

Option C is correct because in DirectQuery mode, Power BI does not import data; instead, it sends native queries to the underlying data source (e.g., SQL Server, Oracle) each time a visual is rendered or a filter is applied. This ensures that the data displayed is always current, as the query is executed in real time against the source system.

Exam trap

The trap here is that candidates often confuse DirectQuery with Import mode, assuming data is still cached locally, or mistakenly think relationships cannot be created because they are not enforced at the dataset level, when in fact they are supported but with limitations.

Practice this question →
233
MCQeasy

You are modeling data for a sales analysis. The Sales table includes a column 'ProductID' and a related Products table. You need to create a measure that calculates the total sales amount only for products in the 'Electronics' category. Which DAX measure should you use?

A.CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Products[Category] = "Electronics"))
B.CALCULATE(SUM(Sales[Amount]), ALL(Products[Category] = "Electronics"))
C.CALCULATE(SUM(Sales[Amount]), FILTER(Products, Products[Category] = "Electronics"))
D.SUMX(Products, IF(Products[Category]="Electronics", SUM(Sales[Amount])))
AnswerA

KEEPFILTERS adds the filter without removing existing filters.

Why this answer

CALCULATE with KEEPFILTERS ensures that the filter context from the Products table is preserved while adding the category filter. Option A is correct. Option B is wrong because FILTER would work but is less efficient and might not respect existing filters.

Option C is wrong because ALL removes filters. Option D is wrong because it uses a table name incorrectly.

Practice this question →
234
Multi-Selecthard

A company has a Power BI semantic model that uses DirectQuery to a SQL Server database. The model includes a large fact table with 100 million rows. Users are experiencing slow report performance. Which THREE actions should the developer take to improve query performance?

Select 3 answers
A.Configure incremental refresh to limit data retrieved per query.
B.Create indexes on columns used in filters and relationships.
C.Remove unused columns from the fact table.
D.Hide columns that are not needed in reports.
E.Add calculated columns to precompute aggregations.
AnswersA, B, C

Incremental refresh reduces the amount of data queried at a time.

Why this answer

Option A is correct because incremental refresh limits the amount of data retrieved per query by partitioning the fact table into smaller, manageable date ranges. In a DirectQuery model, this reduces the volume of data scanned by SQL Server for each query, directly improving report performance by minimizing the data transfer and query execution time.

Exam trap

The trap here is that candidates often confuse hiding columns (a cosmetic change) with removing columns (a structural optimization), and they mistakenly believe calculated columns can improve query performance when in fact they add overhead in DirectQuery models.

Practice this question →
235
MCQeasy

You are designing a Power BI semantic model for an e-commerce company. You have a fact table with OrderID, CustomerID, OrderDate, and SalesAmount. You also have a Customers table with CustomerID, CustomerName, and CustomerSegment. You need to ensure that filters on CustomerSegment propagate to the SalesAmount measure. What should you do?

A.Use the LOOKUPVALUE function in a calculated column.
B.Create a one-to-many relationship from Customers[CustomerID] to Sales[CustomerID].
C.Merge the Customers and Sales tables into a single table in Power Query.
D.Create a snowflake schema by adding a separate Segment table.
AnswerB

This is the standard star schema design.

Why this answer

Option A is correct because creating a relationship between the fact and dimension table based on CustomerID allows filters on CustomerSegment to propagate to the fact table. Option B is incorrect because merging tables is not necessary and may cause data duplication. Option C is not a Power BI feature.

Option D is incorrect because a snowflake design is not required.

Practice this question →
236
Multi-Selecthard

Which THREE considerations are important when implementing row-level security (RLS) in Power BI? (Select exactly 3.)

Select 3 answers
A.Roles can use DAX expressions to define filters
B.RLS can filter data based on the user's identity
C.RLS is automatically applied when using Analyze in Excel
D.RLS in DirectQuery mode pushes filters to the source database
E.RLS can restrict access to specific measures
AnswersA, B, D

DAX is used to create filter conditions for roles.

Why this answer

Options A, B, and D are correct. Option A: RLS filters data at the row level based on the user's role. Option B: Roles can be defined using DAX expressions that filter tables.

Option D: RLS is enforced in DirectQuery mode, but performance depends on the source. Option C is wrong because RLS does not restrict access to measures; measures are still visible unless explicitly hidden. Option E is wrong because RLS is not applied when users view data in Power BI Desktop; it is only applied when published to the service.

Practice this question →
237
MCQeasy

You are building a Power BI semantic model that includes a Date table. Which of the following is a best practice for creating a Date table?

A.Mark the date table as a date table in Power BI Desktop.
B.Create relationships from the date table to every fact table column that contains dates.
C.Use the CALENDARAUTO function to automatically generate dates.
D.Use a date table that includes only dates that exist in the fact tables.
AnswerA

This enables time intelligence functions.

Why this answer

Option D is correct because marking a table as a date table enables time intelligence functions. Option A is incorrect because the date table should have a contiguous range of dates. Option B is incorrect because using CALENDARAUTO can produce unpredictable ranges.

Option C is incorrect because the date table should not have a relationship to every fact table; only one active relationship per fact table is needed.

Practice this question →
238
Multi-Selecteasy

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

Select 2 answers
A.Use calculated columns to perform all transformations.
B.Set all relationships to bidirectional cross-filtering to avoid ambiguity.
C.Use surrogate keys (integer IDs) for relationships instead of natural keys.
D.Reference other queries as a single source to avoid duplication.
E.Design the model with a star schema layout (fact and dimension tables).
AnswersC, E

Surrogate keys improve join performance and reduce model size.

Why this answer

Option C is correct because using surrogate keys (integer IDs) instead of natural keys improves relationship performance and reduces storage overhead. Integer joins are faster than string or composite key joins, and surrogate keys avoid issues with changing natural key values, ensuring referential integrity and stable model relationships.

Exam trap

The trap here is that candidates often confuse 'best practice' with 'always possible'—they may choose bidirectional filtering (B) because it seems flexible, or calculated columns (A) because they are easy, but the exam tests understanding of performance and maintainability trade-offs in large-scale models.

Practice this question →
239
MCQeasy

You have a Power BI model with a fact table and several dimension tables. You need to ensure that when a filter is applied to a dimension table, it also filters related rows in other dimension tables through the fact table. Which type of relationship direction should you configure?

A.Automatic
B.Both
C.None
D.Single
AnswerB

Both directions allow bidirectional filtering through the fact table.

Why this answer

Option B is correct because cross filter direction set to 'Both' allows filters to propagate in both directions, enabling dimension tables to filter each other through the fact table. Option A is wrong because 'Single' only filters from the one side to the many side. Option C is wrong because 'None' disables cross filtering.

Option D is wrong because 'Automatic' is not a valid setting in Power BI.

Practice this question →
240
MCQmedium

A Power BI developer has a fact table that contains sales data at the transaction level. The table includes columns: TransactionID, ProductID, CustomerID, DateKey, Quantity, UnitPrice, Discount, and SalesAmount. The developer wants to create a measure for total sales after discount. Which approach is best for performance and accuracy?

A.Create a measure: SUM(Sales[SalesAmount]) - SUM(Sales[Discount])
B.Add a calculated column in Power Query: NetAmount = Quantity * UnitPrice - Discount, then create a measure: SUM(Sales[NetAmount])
C.Create a measure: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice] - Sales[Discount])
D.Create a measure: SUM(Sales[Quantity] * Sales[UnitPrice]) - SUM(Sales[Discount])
AnswerB

Calculated columns are computed at refresh time, improving query performance.

Why this answer

Option B is correct because it performs the net amount calculation at the row level in Power Query (M), which is computed during data refresh and stored in the table. This avoids runtime row-by-row iteration in DAX, making the measure SUM(Sales[NetAmount]) a simple, highly efficient aggregation. It ensures both performance and accuracy, as the discount is applied per transaction before aggregation.

Exam trap

The trap here is that candidates often assume a DAX measure using SUMX or a simple subtraction of aggregated columns is equivalent in performance, but the exam tests the understanding that pre-calculating row-level logic in Power Query (M) is the most performant approach for large fact tables, while also ensuring mathematical accuracy.

How to eliminate wrong answers

Option A is wrong because subtracting SUM(Discount) from SUM(SalesAmount) is mathematically incorrect when discounts are stored as absolute values per row; it would only work if Discount were a total discount amount per row, but here it is a per-row value that should be subtracted from the row’s net amount, not aggregated separately. Option C is wrong because SUMX iterates over the entire table row by row at query time, which is slower than a pre-calculated column, especially for large fact tables; it also forces the calculation engine to evaluate the expression for every row during measure execution. Option D is wrong because SUM(Sales[Quantity] * Sales[UnitPrice]) is invalid syntax in DAX—SUM expects a single column reference, not an expression; this would cause a syntax error or unexpected behavior, and even if corrected, it would still suffer from the same aggregation-order issue as Option A.

Practice this question →
241
MCQmedium

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

A.OrderDate, Quantity, Price
B.Quantity, Price, OrderID
C.CustomerID, ProductID, OrderDate
D.OrderID, Quantity, Price
AnswerC

These are foreign keys that should be replaced by dimension attributes.

Why this answer

Option B is correct because CustomerID, ProductID, and OrderDate are foreign keys that link to dimension tables. Option A is wrong because OrderID is the primary key of the fact table. Option C is wrong because Quantity and Price are measures.

Option D is wrong because Price is a measure.

Practice this question →
242
Multi-Selecteasy

Which TWO of the following are valid DAX functions for time intelligence?

Select 2 answers
A.TOTALYTD
B.RANKX
C.SUM
D.CALCULATE
E.SAMEPERIODLASTYEAR
AnswersA, E

Returns year-to-date total.

Why this answer

TOTALYTD is a valid DAX time intelligence function that calculates the year-to-date value of an expression, typically used with a date column to aggregate data from the start of the year to the current context. It requires a properly marked date table with continuous dates to function correctly.

Exam trap

Microsoft often tests the distinction between general DAX functions (like CALCULATE and SUM) and dedicated time intelligence functions, trapping candidates who assume any function that works with dates qualifies as time intelligence.

Practice this question →
243
MCQhard

A Power BI report uses a DirectQuery data source. The model includes a calculated column that uses the RELATED function to bring a value from another table. The report is performing slowly. What design change would most improve performance without compromising functionality?

A.Remove the RELATED function and merge tables in the source query.
B.Convert the model to Import mode.
C.Use a measure instead of a calculated column.
D.Replace the calculated column with a calculated table that includes the related column.
AnswerD

A calculated table is materialized in the model, reducing query-time computation and improving performance.

Why this answer

Option D is correct because replacing the calculated column with a calculated table that includes the related column moves the join logic to query time, reducing the per-row overhead of the RELATED function in DirectQuery mode. In DirectQuery, calculated columns are evaluated row-by-row for each query, which can cause significant performance degradation; a calculated table is materialized once during refresh (or in DirectQuery, it is resolved as a single query to the source), avoiding repeated row-level lookups. This change preserves the functionality of having the related value available in the table while improving query performance.

Exam trap

The trap here is that candidates often assume measures are always faster than calculated columns, but in DirectQuery, measures cannot replace columns needed for row-level operations, and the real performance gain comes from avoiding row-by-row evaluation by using a calculated table instead.

How to eliminate wrong answers

Option A is wrong because merging tables in the source query would require modifying the underlying data source, which may not be feasible or maintainable, and it does not address the performance issue caused by the calculated column's row-by-row evaluation in DirectQuery. Option B is wrong because converting to Import mode would change the data connectivity model entirely, potentially breaking real-time data requirements or exceeding memory limits, and it is not a targeted fix for the calculated column performance issue. Option C is wrong because a measure cannot replace a calculated column if the related value is needed for row-level filtering, grouping, or as a field in visuals; measures are evaluated in filter context and cannot be used as a column in slicers or as a row label in tables.

Practice this question →
244
MCQhard

You are modeling data for a retail company. The fact table contains sales transactions with columns: OrderDate, ProductID, CustomerID, Quantity, and SalesAmount. You also have dimension tables: Date, Product, Customer. The Date table has a relationship to the fact table on OrderDate, and the Customer table has a relationship to the fact table on CustomerID. However, you need to support analysis by both OrderDate and ShipDate. What is the recommended approach?

A.Create a calculated column that combines OrderDate and ShipDate into a single date column
B.Create a second Date table and add an inactive relationship from ShipDate to the new Date table, then use USERELATIONSHIP in measures
C.Add ShipDate as a column in the fact table and use it directly in calculations
D.Create two active relationships from the fact table to the Date table: one on OrderDate and one on ShipDate
AnswerB

This pattern allows analysis by both dates while maintaining model integrity.

Why this answer

Option D is correct because creating a second Date table with an inactive relationship for ShipDate allows you to use USERELATIONSHIP in measures to switch between active and inactive relationships as needed. Option A is wrong because multiple active relationships cause ambiguity and errors. Option B is wrong because merging ShipDate into a single column loses the original ship date information.

Option C is wrong because using the ShipDate directly without a date table limits time intelligence.

Practice this question →
245
MCQeasy

You have two tables: 'Orders' and 'Customers'. You want to create a relationship where each order is linked to one customer, but a customer can have many orders. Which cardinality should you choose?

A.Many-to-many (*:*)
B.Many-to-one (*:1) from Orders to Customers
C.One-to-many (1:*) from Orders to Customers
D.One-to-one (1:1)
AnswerB

Orders table has many rows per customer, so many-to-one from Orders to Customers.

Why this answer

Option A is correct because one-to-many from Customers to Orders is the natural direction. Option B is reverse. Option C is wrong because many-to-many is not appropriate.

Option D is wrong because one-to-one is too restrictive.

Practice this question →
246
MCQhard

You have a fact table 'Transactions' with 100 million rows. You need to reduce the model size without losing detail. Which action is most effective?

A.Remove columns that are not used in reports
B.Group rows by date and sum amounts
C.Use a composite model with DirectQuery
D.Create aggregations on the table
AnswerA

Removing unused columns reduces storage.

Why this answer

Option A is correct because removing unnecessary columns reduces storage. Option B is wrong because aggregations reduce detail. Option C is wrong because composite models still store data.

Option D is wrong because grouping is not the best for size reduction.

Practice this question →
247
MCQmedium

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

A.SWITCH(TRUE(), Sales[Amount] < 100, "Low", Sales[Amount] < 500, "Medium", "High")
B.IF(Sales[Amount] < 100, "Low", IF(Sales[Amount] < 500, "Medium", "High"))
C.SELECTEDVALUE(Sales[Amount]) & "Category"
D.SWITCH(Sales[Amount], 100, "Low", 500, "Medium", "High")
AnswerA

SWITCH with TRUE() is recommended for multiple conditions.

Why this answer

Option A is correct because it uses SWITCH(TRUE(), ...) to evaluate multiple conditions sequentially, returning the first matching result. This is the standard pattern for categorizing continuous values into discrete buckets in DAX, as SWITCH evaluates conditions in order and stops at the first TRUE condition.

Exam trap

Microsoft often tests the distinction between SWITCH with exact matching (Option D) and SWITCH with TRUE() for range-based logic, leading candidates to choose the simpler-looking SWITCH without TRUE() and miss the range evaluation.

How to eliminate wrong answers

Option B is wrong because while the nested IF logic works, it is less readable and less performant than SWITCH(TRUE()) for multiple conditions; however, it is not incorrect per se, but the question asks for the expression to use, and SWITCH is the recommended pattern in DAX for clarity and efficiency. Option C is wrong because SELECTEDVALUE returns a single value from a column when there is one distinct value in the current filter context, and concatenating it with 'Category' does not perform any threshold-based categorization. Option D is wrong because SWITCH without TRUE() performs exact match comparisons, so it would only return 'Low' when Amount equals exactly 100 and 'Medium' when Amount equals exactly 500, not for ranges.

Practice this question →
248
Multi-Selectmedium

Which TWO actions should you take to optimize a star schema in Power BI?

Select 2 answers
A.Denormalize dimension tables into the fact table.
B.Create a date table and mark it as a date table.
C.Remove unnecessary columns from dimension tables.
D.Use bidirectional cross-filtering for all relationships.
E.Add calculated columns to the fact table for filtering.
AnswersB, C

This enables time intelligence functions and improves performance.

Why this answer

Creating a date table and setting it as a date table is a best practice for time intelligence. Creating calculated columns in the fact table is generally discouraged because it increases storage and query time.

Practice this question →
249
MCQhard

You have a data model with two relationships between Sales and Date: one active (OrderDateKey) and one inactive (ShipDateKey). The DAX expression uses USERELATIONSHIP(Sales[DateKey], 'Date'[DateKey]). Which date will be used for filtering?

A.Ship dates
B.Both order and ship dates
C.Order dates
D.No relationship, all dates
AnswerA

The inactive relationship is activated by USERELATIONSHIP.

Why this answer

Option B is correct because USERELATIONSHIP activates the specified relationship. The relationship is defined by the columns Sales[DateKey] and Date[DateKey]; if this matches the ShipDateKey relationship, it uses ship dates. Option A is incorrect because USERELATIONSHIP overrides the active relationship.

Option C is incorrect because it activates the specified relationship. Option D is incorrect because the expression does not ignore filters.

Practice this question →
250
Multi-Selecthard

Which THREE of the following are valid reasons to use a composite model (mixed storage mode) in Power BI?

Select 3 answers
A.To enable real-time data from a DirectQuery source while using imported historical data.
B.To use aggregations on large fact tables while keeping other tables imported.
C.To improve relationship performance between tables.
D.To create calculated tables based on DirectQuery sources.
E.To combine data from a DirectQuery source with imported tables.
AnswersA, B, E

This hybrid approach is a common use case.

Why this answer

Options B, C, and E are correct. Option B: Composite models allow combining DirectQuery and Import. Option C: Aggregations can be used for large datasets.

Option E: Real-time data from DirectQuery with aggregated historical data. Option A is wrong because composite models do not automatically improve relationship performance. Option D is wrong because calculated tables are not supported in DirectQuery sources.

Practice this question →
251
Multi-Selecthard

You have a Power BI data model with a table named Sales that includes columns: ProductID, SalesAmount, and SalesDate. You need to create a measure that calculates the total sales amount for the current quarter and compares it to the previous quarter. Which THREE DAX functions should you use?

Select 3 answers
A.PREVIOUSQUARTER
B.DATEADD
C.DATESQTD
D.CALCULATE
E.SUM
AnswersA, D, E

PREVIOUSQUARTER returns the dates for the previous quarter.

Why this answer

The correct answers are A, B, and D. CALCULATE (A) is used to modify filter context. SUM (B) to aggregate.

PREVIOUSQUARTER (D) for time intelligence. Option C (DATEADD) could also work, but PREVIOUSQUARTER is more specific. Option E (DATESQTD) is for quarter-to-date, not previous quarter.

Practice this question →
252
Multi-Selectmedium

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

Select 3 answers
A.Store calculated columns in the fact table for quick access.
B.Include as many columns as possible in fact tables for flexibility.
C.Disable the auto-date/time feature.
D.Use a star schema design.
E.Use integer keys for relationships instead of text.
AnswersC, D, E

Auto-date/time creates hidden tables that increase model size.

Why this answer

Option C is correct because disabling the auto-date/time feature prevents Power BI from automatically creating hidden date tables for each date column, which can significantly increase the model size and reduce performance in large datasets. This is a best practice for optimizing storage and refresh times when working with large data volumes.

Exam trap

The trap here is that candidates often think calculated columns in fact tables improve performance (Option A) or that more columns provide flexibility (Option B), but in reality both degrade performance and violate star schema best practices.

Practice this question →
253
Multi-Selecthard

A Power BI developer is designing a data model for sales analysis. The model includes a Sales fact table and dimension tables: Product, Customer, Date, and Store. Which TWO design considerations are best practices for optimizing query performance?

Select 2 answers
A.Configure bidirectional cross-filtering between all dimension tables and the fact table.
B.Use multiple inactive relationships between fact and dimension tables to support different analyses.
C.Create a separate date table and mark it as a date table to enable time intelligence functions.
D.Use many-to-many relationships between dimension tables and the fact table to simplify the model.
E.Reduce the cardinality of columns in dimension tables by using surrogate keys instead of natural keys.
AnswersC, E

A date table is essential for time-based calculations and filtering.

Why this answer

Option C is correct because creating a separate date table and marking it as a date table enables Power BI to use built-in time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) that rely on a continuous, contiguous date range. This design ensures optimal performance by allowing the engine to generate efficient DAX queries and leverage date-based relationships without ambiguity.

Exam trap

The trap here is that candidates often confuse 'optimizing query performance' with 'enabling more analysis features'—bidirectional filtering and multiple inactive relationships seem useful but actually harm performance or add complexity, while reducing cardinality with surrogate keys (Option E) is a genuine performance optimization that is easy to overlook.

Practice this question →
254
MCQhard

You are building a Power BI model that includes a table 'Orders' with columns: OrderID, CustomerID, OrderDate, and TotalAmount. You also have a table 'Customers' with columns: CustomerID, CustomerName, and Segment. You need to create a relationship between Orders and Customers on CustomerID. Which relationship configuration should you choose to ensure that filtering Customers by Segment correctly filters Orders?

A.One-to-many relationship from Customers to Orders
B.Many-to-one relationship from Orders to Customers
C.Many-to-many relationship with a bridge table
D.One-to-one relationship
AnswerA

This is the correct star schema design where the dimension table filters the fact table.

Why this answer

Option B is correct because a one-to-many relationship from Customers (one side) to Orders (many side) with Customers filtering Orders is the standard star schema design. Option A is wrong because many-to-one would reverse the direction. Option C is wrong because many-to-many is unnecessary and can cause ambiguity.

Option D is wrong because a one-to-one relationship is not appropriate as one customer can have many orders.

Practice this question →
255
MCQmedium

You have a Power BI model with a table 'Sales' and a separate 'Calendar' table. The relationship between Sales[OrderDate] and Calendar[Date] is active. You need to create a measure that calculates the total sales for the previous month relative to the current filter context. Which DAX expression should you use?

A.CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))
B.CALCULATE(SUM(Sales[Amount]), NEXTMONTH(Calendar[Date]))
C.CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Calendar[Date]))
D.CALCULATE(SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, MONTH))
AnswerD

Shifts dates back by one month.

Why this answer

Option C is correct. CALCULATE with DATEADD shifting by -1 month returns the same period last month. Option A uses PREVIOUSMONTH which is equivalent but requires a continuous date range; DATEADD is more flexible.

Option B returns dates in the next month. Option D returns dates in the same month of previous year.

Practice this question →
256
Multi-Selectmedium

Which THREE of the following are considerations when implementing row-level security (RLS) in Power BI? (Select three.)

Select 3 answers
A.RLS does not apply to data accessed via the XMLA endpoint unless using dynamic security.
B.RLS filters are applied at query time.
C.RLS can be defined using DAX filter expressions.
D.RLS is enforced only for users with the Viewer role.
E.RLS can be bypassed by using the 'Show all' option in visuals.
AnswersA, B, C

Static RLS is not enforced via XMLA; dynamic security is needed.

Why this answer

Options A, C, and E are correct. RLS is applied when the user queries the dataset (A). It can be defined using DAX filters (C).

It does not apply to the XMLA endpoint by default unless dynamic (E). Option B is incorrect because RLS filters apply to all visuals. Option D is incorrect because RLS is user-based, not role-based in terms of dynamic security.

Practice this question →
257
MCQhard

You have a Power BI data model with a table 'Orders' related to 'Customers' (one-to-many). A measure calculates total sales per customer. Some customers have no orders, but they still appear in the report with blank sales. What should you do to hide these customers?

A.Remove customers with no orders from the Customers table using Power Query
B.Change the relationship cross-filter direction to both
C.Add a visual-level filter to exclude blanks
D.Mark the date table as a date table
AnswerA

This is the proper way to exclude customers without orders from the model.

Why this answer

Option D is correct because removing them from the Customers table ensures they don't appear. Option A is wrong because filtering the visual would be a workaround, not a model fix. Option B is wrong because changing relationship direction doesn't solve the issue.

Option C is wrong because marking as date table is irrelevant.

Practice this question →
258
MCQeasy

You have a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. You want to create a measure that shows the total number of orders. Which DAX function should you use?

A.DISTINCTCOUNT(Orders[OrderID])
B.SUM(Orders[OrderID])
C.COUNT(Orders[OrderID])
D.COUNTROWS(Orders)
AnswerD

Counts total rows in Orders table.

Why this answer

Option B is correct because COUNTROWS counts the number of rows in a table. Option A is wrong because COUNT counts numeric values in a column, not rows. Option C is wrong because DISTINCTCOUNT counts distinct values.

Option D is wrong because SUM adds values.

Practice this question →
259
MCQeasy

You want to create a measure that calculates the total sales for the current year. Which DAX expression should you use?

A.DATESYTD('Date'[Date])
B.TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
C.CALCULATE(SUM(Sales[Amount]), YEAR('Date'[Date]) = YEAR(TODAY()))
D.SAMEPERIODLASTYEAR(SUM(Sales[Amount]), 'Date'[Date])
AnswerB

TOTALYTD is the appropriate time intelligence function for year-to-date.

Why this answer

Option A is correct because TOTALYTD calculates year-to-date. Option B is wrong because DATESYTD returns a table. Option C is wrong because CALCULATE requires a filter.

Option D is wrong because SAMEPERIODLASTYEAR is for previous year.

Practice this question →
260
Multi-Selecthard

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

Select 3 answers
A.Replace incremental refresh
B.Create a summary table that aggregates data from another table
C.Modify the source data before loading
D.Create a crossjoin of two dimension tables
E.Create a date table that is not available in the source
AnswersB, D, E

Calculated tables can summarize data.

Why this answer

Options A, B, and D are correct. Option C is wrong because you cannot change the source table; calculated tables are derived. Option E is wrong because calculated tables are static and not a replacement for incremental refresh.

Practice this question →
261
MCQeasy

You have a Power BI model with a table named Orders that contains columns OrderDate, ShipDate, and CustomerID. You need to create a calculated column that computes the number of days between OrderDate and ShipDate. Which DAX expression should you use?

A.DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY)
B.DATEADD(Orders[OrderDate], 1, DAY)
C.DAY(Orders[ShipDate] - Orders[OrderDate])
D.NETWORKDAYS(Orders[OrderDate], Orders[ShipDate])
AnswerA

DATEDIFF returns the number of intervals between two dates; DAY specifies the interval.

Why this answer

Option A is correct because the DATEDIFF function in DAX calculates the interval between two dates in the specified unit (DAY). This directly computes the number of days between OrderDate and ShipDate, which is the required result for the calculated column.

Exam trap

The trap here is that candidates might confuse DATEDIFF with DATEADD (which shifts dates) or incorrectly use DAY() on a date difference, thinking it extracts the number of days, when DAY() actually returns the day of the month (1–31).

How to eliminate wrong answers

Option B is wrong because DATEADD shifts a date by a specified number of intervals (e.g., adds 1 day to OrderDate), not the difference between two dates. Option C is wrong because DAY extracts the day-of-month component from a date, not the interval between dates; subtracting two dates in DAX returns a decimal representing days, but wrapping it in DAY returns an incorrect integer (the day number of the difference). Option D is wrong because NETWORKDAYS calculates the number of whole working days between two dates, excluding weekends and optionally holidays, not the total calendar days.

Practice this question →
262
MCQmedium

You have the DAX expression shown. What does this expression return?

A.Total sales for the previous year
B.Total sales for the year of the latest date in the current filter context
C.Total sales for the maximum year in the Date table
D.Total sales for the current year
AnswerB

MAX('Date'[Year]) returns the maximum year in the current filter context.

Why this answer

The DAX expression uses CALCULATE to modify the filter context for 'Sales[Amount]' by applying a filter that selects only dates where the year equals the year of the latest date present in the current filter context. The MAX function returns the maximum date from the Date table within the current filter context, and YEAR extracts its year. This effectively returns total sales for the year containing the most recent date in the current filter context, not necessarily the previous year or the absolute maximum year in the Date table.

Exam trap

The trap here is that candidates often confuse 'maximum year in the Date table' (ignoring filter context) with 'year of the latest date in the current filter context', leading them to select Option C, or they mistakenly think the expression calculates the previous year (Option A) because they misread MAX as a time intelligence function.

How to eliminate wrong answers

Option A is wrong because the expression does not use DATEADD or SAMEPERIODLASTYEAR to shift dates back by one year; it filters to the year of the latest date in the current context, not the prior year. Option C is wrong because MAX(Date[Date]) returns the latest date within the current filter context, not the absolute maximum year in the entire Date table, so it respects any slicers or filters applied. Option D is wrong because 'current year' implies the calendar year of today's date, but the expression uses the year of the latest date in the current filter context, which may differ from the current calendar year if the data does not include today's date or if a filter restricts the date range.

Practice this question →
263
MCQmedium

You are a data analyst for a retail company. You have a Power BI semantic model that includes a fact table named Sales with columns: Date, ProductID, StoreID, Quantity, and Amount. You also have dimension tables: Product, Store, and Date. The Date table is marked as a date table. You need to create a measure that calculates the running total of sales amount over the last 12 months, including the current month. The measure should be dynamic based on the filter context. Which DAX expression should you use?

A.CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Date[Date], DATE(2024,1,1), MAX(Date[Date])))
B.CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD(Date[Date], -12, MONTH))
C.CALCULATE(SUM(Sales[Amount]), DATESINPERIOD(Date[Date], MAX(Date[Date]), -12, MONTH))
D.TOTALYTD(SUM(Sales[Amount]), Date[Date])
AnswerC

DATESINPERIOD shifts the date range back 12 months from the last date in context, creating a rolling 12-month total.

Why this answer

Option A uses DATESINPERIOD with a -12 month offset, which correctly calculates a rolling 12-month total. Option B uses PARALLELPERIOD for parallel period comparison, not rolling total. Option C uses DATESBETWEEN with a fixed start date, which is not dynamic.

Option D uses TOTALYTD which calculates year-to-date, not rolling 12 months.

Practice this question →
264
MCQhard

A Power BI developer is troubleshooting a report that uses a calculated table. The calculated table is defined as: 'Sales Summary = SUMMARIZE(Sales, Sales[ProductID], "Total Sales", SUM(Sales[Amount]))'. Users report that the 'Total Sales' column shows incorrect values when slicers are applied to the report. What is the most likely cause?

A.The calculated table lacks a relationship to the Sales table.
B.Calculated tables are static and do not respond to slicer selections.
C.The SUMMARIZE function syntax is incorrect.
D.The calculated table is not marked as a date table.
AnswerB

Calculated tables are evaluated once at refresh and are not dynamic with slicers.

Why this answer

Calculated tables in Power BI are evaluated at data refresh time and stored in the model as static data. They do not respond to slicer selections or any other report-level filters because they are not recalculated in the query context. Therefore, the 'Total Sales' column in the 'Sales Summary' table will always show the same aggregated values regardless of slicer interactions, which is why users see incorrect values when applying slicers.

Exam trap

The trap here is that candidates often confuse calculated tables with calculated columns or measures, assuming that all DAX expressions are dynamic and respond to slicers, but calculated tables are static and only evaluated at refresh time.

How to eliminate wrong answers

Option A is wrong because a calculated table defined with SUMMARIZE on the Sales table does not require a separate relationship to the Sales table; it inherits the data directly from the source table and any existing relationships in the model are irrelevant to the static nature of calculated tables. Option C is wrong because the SUMMARIZE function syntax is correct: it groups by Sales[ProductID] and creates a new column 'Total Sales' with the sum of Sales[Amount]; there is no syntax error. Option D is wrong because marking a table as a date table is only relevant for time intelligence functions and date-based filtering, not for the static behavior of calculated tables or their response to slicers.

Practice this question →
265
Multi-Selectmedium

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

Select 2 answers
A.Disable relationships between tables to reduce query complexity
B.Create calculated columns in Power Query instead of in DAX
C.Reduce the number of columns in the fact query to only those needed
D.Enable bidirectional cross-filtering for all relationships
E.Use a single date dimension table for all date columns
AnswersC, E

Minimizes data transfer from the source.

Why this answer

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.

Practice this question →
266
MCQhard

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?

A.Use DirectQuery storage mode for all tables to ensure real-time data and aggregate queries at the source.
B.Use a composite model: Import for dimension tables and DirectQuery for Sales table to balance freshness and performance.
C.Use Import storage mode for all tables with incremental refresh policy on the Sales table to load only the last 5 years of data.
D.Use Import mode but do not create a date table; instead use the DateKey column from Sales for time intelligence.
AnswerC

Reduces data volume, allows in-memory performance, and supports all requirements including date hierarchy.

Why this answer

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.

Practice this question →
267
Matchingmedium

Match each Power BI service feature to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Single page of visualizations from multiple reports

Collection of dashboards and reports for consumers

Container for dashboards, reports, and datasets

Cloud-based ETL for data preparation

Pixel-perfect report for printing

Why these pairings

These are key features available in the Power BI service.

Practice this question →
268
MCQmedium

You need to create a measure that calculates the average sales per transaction. The 'Sales' table has columns 'TransactionID' and 'Amount'. Which DAX expression is correct?

A.SUM(Sales[Amount]) / DISTINCTCOUNT(Sales[TransactionID])
B.DIVIDE(SUM(Sales[Amount]), COUNTROWS(Sales))
C.AVERAGEX(Sales, Sales[Amount])
D.AVERAGE(Sales[Amount])
AnswerB

Calculates total amount divided by number of transactions.

Why this answer

Option D is correct because DIVIDE(SUM(Sales[Amount]), COUNTROWS(Sales)) calculates total sales divided by number of transactions. Option A is wrong because AVERAGE(Sales[Amount]) averages the amounts, not per transaction. Option B is wrong because AVERAGEX iterates over each row, averaging amounts, which is not per transaction if multiple rows per transaction.

Option C is wrong because it sums amounts then divides by distinct transaction count, which would be correct if each transaction has one row, but not if multiple rows per transaction; however, DIVIDE is safer.

Practice this question →
← PreviousPage 4 of 4 · 268 questions total

Ready to test yourself?

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

Start 20-question session