Courseiva

CCNA Model the data Questions

62 questions · Model the data · All types, answers revealed

Practice these questionsDomain overviewAll questions
1
MCQeasy

A company has a fact table with sales data and multiple dimension tables. They want to create a measure that calculates the total sales amount for the current year, but the measure returns incorrect results when used in a visual with a date hierarchy. What is the most likely cause?

A.The date table is not marked as a date table in Power BI.
B.The fact table is not in a star schema; it is snowflaked.
C.The relationship between the date table and the fact table is inactive.
D.The relationship between the date table and the fact table is set to bidirectional cross-filtering.
AnswerC

If the relationship between the date table and the fact table is inactive, the date table's columns will not automatically filter the fact table because an inactive relationship is ignored during normal filter propagation. A visual that uses a date hierarchy from the date table will still display dates, but the measure values will be evaluated without that date context—often showing the grand total or a blend of all dates—so the results appear correct in shape but are numerically wrong. The only way to apply the filter is to explicitly activate the relationship in DAX using USERELATIONSHIP, which the user has evidently not done.

Why this answer

If the relationship between the date table and the fact table is inactive, measures that rely on time intelligence functions (like TOTALYTD, SAMEPERIODLASTYEAR, or a simple SUM with date filtering) will not automatically propagate filters from the date hierarchy to the fact table. In Power BI, only one active relationship can exist between two tables; inactive relationships require explicit activation via USERELATIONSHIP in DAX. Without that, the measure ignores the date filter and returns incorrect or blank results.

Exam trap

The trap here is that candidates often assume any relationship between tables will automatically filter, but Power BI requires exactly one active relationship per pair of tables, and inactive relationships are ignored unless explicitly activated in DAX.

How to eliminate wrong answers

Option A is wrong because marking a table as a date table is not required for basic time intelligence; it only enables automatic date hierarchy creation and certain time intelligence functions to work correctly, but it does not cause incorrect results in a visual with a date hierarchy if the relationship is active. Option B is wrong because a snowflake schema does not inherently break time intelligence; it may affect performance or model complexity, but it does not cause a measure to return incorrect results due to filter propagation. Option D is wrong because bidirectional cross-filtering would actually strengthen filter propagation, not cause incorrect results; it might lead to ambiguity or unexpected filtering, but it would not cause the measure to ignore the date filter entirely.

Practice this question →
2
MCQmedium

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

A.Data type mismatch between the two columns
B.The columns are from different tables without a relationship
C.The relationship between tables is set to single direction
D.One of the columns contains only spaces
AnswerD

If one column contains only spaces (for example, a person's last name field holding `" "`), the concatenated result is a string composed entirely of whitespace. Because DAX does not automatically trim leading or trailing spaces in concatenation, the resulting column looks empty in visuals and may be treated as blank by later functions, even though it is not a true BLANK. This explains why the calculated column appears blank while actually holding a space-only value.

Why this answer

A column containing only spaces (e.g., ' ') is not null, but when concatenated with another name, the result may appear blank if the concatenation results in pure whitespace or if the column is trimmed before concatenation. Option A is incorrect because data type mismatch would typically cause an error, not a blank result. Option B is incorrect because the data source has no nulls, so missing rows are not the issue.

Option C is incorrect because relationship direction does not affect calculated columns within the same table.

Practice this question →
3
MCQmedium

You are designing a data model for a report that shows sales by region and product category. The source data includes a table 'Sales' with columns: Region, Category, SalesAmount. You also have separate tables 'Regions' and 'Categories' that contain additional attributes. You need to create a star schema. What should you do with the 'Region' and 'Category' columns in the 'Sales' table?

A.Remove them from the Sales table and use foreign keys to link to the dimension tables
B.Keep them in the Sales table as attributes for simplicity
C.Merge the Regions and Categories tables into the Sales table
D.Mark the Sales table as a date table
AnswerA

This is the correct star schema approach. Remove region and category descriptive text from the Sales table and replace them with foreign key columns (e.g., RegionID, CategoryID) that reference the primary keys of dedicated dimension tables. This normalizes the fact table, eliminates redundant string storage, and enables DAX filter and slicer operations to traverse relationships efficiently. It also simplifies future updates to dimension attributes without rewriting historical sales rows.

Why this answer

In a star schema, dimension tables (Regions, Categories) contain descriptive attributes, and the fact table (Sales) stores foreign keys referencing those dimensions. Removing the Region and Category columns from the Sales table and replacing them with foreign keys (e.g., RegionID, CategoryID) normalizes the model, reduces data redundancy, and enables efficient filtering and slicing by region and category attributes. This approach aligns with best practices for Power BI data modeling, ensuring optimal query performance and maintainability.

Exam trap

The trap here is that candidates often think keeping attributes in the fact table is simpler and faster, not realizing that a normalized star schema with foreign keys actually improves performance and scalability in Power BI.

How to eliminate wrong answers

Option B is wrong because keeping Region and Category as attributes in the Sales table violates star schema principles, leading to data duplication, larger table size, and inefficient filtering when dimension attributes change. Option C is wrong because merging the Regions and Categories tables into the Sales table creates a wide, denormalized flat table, which defeats the purpose of a star schema and increases storage and refresh overhead. Option D is wrong because marking the Sales table as a date table is irrelevant; date tables are used for time intelligence functions, and Sales is a fact table, not a date dimension.

Practice this question →
4
MCQhard

You have a Power BI model with a table 'Sales' that contains columns: Date, SalespersonID, and Amount. You have a 'Salespeople' table with columns: SalespersonID, Name, and Region. You need to create a measure that calculates the total sales amount for the current region, but only for salespeople who have made at least one sale in the current month. Which DAX expression achieves this?

A.CALCULATE(SUM(Sales[Amount]), Salespeople[Region] = SELECTEDVALUE(Salespeople[Region]))
B.SUMX(FILTER(Salespeople, CALCULATE(COUNTROWS(Sales), Sales[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)) > 0), CALCULATE(SUM(Sales[Amount]), Salespeople[Region] = SELECTEDVALUE(Salespeople[Region])))
C.SUMX(Salespeople, CALCULATE(SUM(Sales[Amount]), Salespeople[Region] = SELECTEDVALUE(Salespeople[Region]), Sales[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))
D.CALCULATE(SUM(Sales[Amount]), Salespeople[Region] = SELECTEDVALUE(Salespeople[Region]), Sales[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1))
AnswerB

Correctly iterates over salespeople who have at least one sale in the current month and sums their sales for the current region.

Why this answer

It first filters the Salespeople table to only those who have made at least one sale in the current month (using COUNTROWS > 0 with a date filter), then iterates over that filtered list with SUMX, and for each salesperson calculates the sum of Amount filtered to their region using CALCULATE. Option A is wrong because it sums all sales in the selected region without any filter on salespeople who have sales this month. Option C is wrong because it sums over all salespeople without first filtering to those with current month sales, so it would incorrectly include salespeople with no current month sales.

Option D is wrong because it sums sales only in the current month but does not restrict to salespeople with at least one sale in the current month; it would include all sales in that month, regardless of salesperson.

Practice this question →
5
MCQmedium

You are reviewing the partition configuration for a Power BI Import model as shown in the exhibit. The table Sales is partitioned by year. You need to modify the model to improve incremental refresh performance. What change should you make?

A.Increase the number of partitions to monthly
B.Configure incremental refresh policy
C.Remove all partitions and load data as a single table
D.Change the storage mode to DirectQuery
AnswerB

Configuring an incremental refresh policy is the correct approach because it automatically creates and manages partitions based on a date range, typically using RangeStart and RangeEnd parameters. During each refresh, only the data that has changed or is new within the sliding window is processed, while historical partitions remain untouched, significantly reducing refresh time and resource consumption. This also enables query pruning in the Power BI service, as only relevant partitions are scanned when building visuals, making it the most efficient way to optimize refresh performance for large fact tables.

Why this answer

Configuring an incremental refresh policy (Option B) is the correct approach because it automatically manages partition creation and refresh for the Sales table based on a date/time column. This improves performance by refreshing only the most recent data (e.g., last 5 years) while keeping historical partitions unchanged, reducing refresh time and resource consumption compared to manual yearly partitions.

Exam trap

The trap here is that candidates may think increasing partition count (Option A) always improves performance, but in Power BI, too many partitions increase metadata overhead and refresh orchestration time, making incremental refresh policies the correct solution for efficient, automated partition management.

How to eliminate wrong answers

Option A is wrong because increasing partitions to monthly would create more granular partitions, which can actually degrade refresh performance due to overhead from managing many small partitions, and it does not address the need for incremental refresh logic. Option C is wrong because removing all partitions and loading data as a single table would force a full refresh of the entire Sales table every time, eliminating any performance gains from partitioning and incremental refresh. Option D is wrong because changing the storage mode to DirectQuery would bypass the Import model entirely, which is not an incremental refresh improvement and could introduce query performance issues due to live querying of the source.

Practice this question →
6
Multi-Selecthard

Which THREE factors should you consider when designing a star schema for a Power BI semantic model? (Select three.)

Select 3 answers
A.Fact tables should contain measures and foreign keys to dimension tables.
B.Dimension tables should contain descriptive attributes and be denormalized.
C.Fact tables should be normalized to reduce data duplication.
D.Use calculated columns in dimension tables to derive new attributes.
E.Avoid creating many-to-many relationships between dimensions.
AnswersA, B, E

This is the core of star schema design.

Why this answer

The correct factors are: Fact tables should contain measures and foreign keys to dimension tables (A). Dimension tables should contain descriptive attributes and be denormalized (B). Avoid creating many-to-many relationships between dimensions; instead, use a bridge table when necessary (E).

Option C is incorrect because fact tables should not be normalized; they should be denormalized to improve query performance. Option D is incorrect because calculated columns in dimension tables can increase model size and processing time; they are better placed as measures or in the fact table.

Practice this question →
7
Multi-Selecthard

Which TWO of the following are true about the Power BI composite model?

Select 2 answers
A.Composite models do not support many-to-many relationships.
B.All tables in a composite model must use the same storage mode.
C.A composite model can combine DirectQuery and Import tables.
D.Relationships can be created between tables from different source groups.
E.Calculated tables are not supported in composite models.
AnswersC, D

This is a key feature of composite models.

Why this answer

A composite model in Power BI allows mixing DirectQuery and Import tables within the same data model. This enables you to leverage the performance of in-memory Import storage for some tables while using DirectQuery to access large or real-time data sources without duplicating data.

Exam trap

The trap here is that candidates often assume composite models require uniform storage modes or cannot handle many-to-many relationships, but Power BI's composite model is designed to flexibly mix storage modes and supports many-to-many relationships through proper configuration.

Practice this question →
8
MCQeasy

A data model has a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. There is a 'Customers' table with columns: CustomerID, CustomerName. To analyze orders by customer, what is the best practice for modeling the relationship?

A.Create a one-to-many relationship from Customers to Orders with single direction.
B.Create a one-to-one relationship between Customers and Orders based on CustomerID.
C.Create an inactive relationship and use USERELATIONSHIP in measures.
D.Create a many-to-one relationship from Orders to Customers with both directions.
AnswerA

This is the correct star schema design. The Customers table is a dimension with a unique CustomerID per row, while Orders is a fact table that can contain many rows per CustomerID. A one-to-many relationship from Customers to Orders lets filters applied to customers (e.g., region, segment) automatically propagate to their orders in visualizations and measures. The single cross-filter direction ensures one-way filtering from the dimension to the fact, which is the standard, repeatable pattern that avoids ambiguity and keeps DAX calculations predictable.

Why this answer

In a star schema, the Customers table (dimension) should have a one-to-many relationship to the Orders table (fact) filtered from the dimension side. This single-direction filter propagation ensures that when a customer is selected, only their orders are shown, while preventing unwanted cross-filtering from orders back to customers. This is the standard best practice for modeling dimension-to-fact relationships in Power BI.

Exam trap

The trap here is that candidates often confuse the direction of the relationship (thinking the fact table should be on the 'one' side) or overcomplicate the model by using inactive relationships or bidirectional filtering when a simple single-direction one-to-many is the correct and efficient choice.

How to eliminate wrong answers

Option B is wrong because a one-to-one relationship between Customers and Orders would require each CustomerID to appear only once in Orders, which is unrealistic for a transactional fact table where one customer can have many orders. Option C is wrong because an inactive relationship with USERELATIONSHIP is only used when you need multiple relationships between the same two tables (e.g., OrderDate and ShipDate), not for the primary dimension-to-fact relationship which should always be active. Option D is wrong because a many-to-one relationship from Orders to Customers with both directions would create ambiguous cross-filtering and potential performance issues; bidirectional filtering is reserved for specific scenarios like many-to-many relationships, not for standard star schema modeling.

Practice this question →
9
Multi-Selecteasy

You are creating a Power BI report that uses a table named Orders with columns: OrderID, OrderDate, ShipDate, and Status. You need to create a calculated table that contains one row per month with the total number of orders shipped in that month. Which TWO steps should you take?

Select 1 answer
A.Use CALENDARAUTO in a measure
B.Use the GROUPBY function
C.Create a date table using CALENDAR or CALENDARAUTO
D.Use SUMMARIZECOLUMNS with 'Date'[Month] and COUNTROWS of Orders
E.Use the VALUES function to get unique months
AnswersD

Using SUMMARIZECOLUMNS with 'Date'[Month] and COUNTROWS of Orders correctly groups orders by month and counts them. This is the direct method to achieve the desired calculated table.

Why this answer

To create a calculated table that shows total orders shipped per month, you can directly group by the month of ShipDate using SUMMARIZECOLUMNS. Since the Orders table already contains a ShipDate column, a separate date table is not required for this aggregation. Option C (creating a date table) is unnecessary here; you can simply extract the month from ShipDate within the grouping.

Option D correctly uses SUMMARIZECOLUMNS with 'Date'[Month] and COUNTROWS of Orders. Note: If the 'Date' table is not already present, you would need to create it, but the question asks for steps to create a calculated table that contains one row per month with totals. The most direct step is to use SUMMARIZECOLUMNS with the month column from the Orders table (or a date table if available).

However, among the given options, only D is a required step.

Exam trap

Candidates may think a separate date table is always required for time-based grouping in calculated tables. In this case, you can group directly by the month of ShipDate using functions like SUMMARIZECOLUMNS or GROUPBY without a date dimension.

Practice this question →
10
MCQmedium

You have a Power BI model with a table named 'Orders' that contains columns: OrderID, CustomerID, OrderDate, and TotalAmount. You need to create a measure that calculates the total sales amount for orders placed in the last 30 days, but only for customers who have placed more than 5 orders in total. What is the most efficient DAX measure?

A.TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), FILTER(Orders, Orders[OrderDate] > TODAY() - 30), FILTER(Orders, CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5))
B.TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), KEEPFILTERS(Orders[OrderDate] > TODAY() - 30), KEEPFILTERS(CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5))
C.TotalSalesLast30Days = SUMX(FILTER(Orders, Orders[OrderDate] > TODAY() - 30 && CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID])) > 5), Orders[TotalAmount])
D.TotalSalesLast30Days = CALCULATE(SUM(Orders[TotalAmount]), DATESINPERIOD(Orders[OrderDate], TODAY(), -30, DAY))
AnswerC

This is the correct answer because it uses a single SUMX iterator over a FILTERed table, where the filter expression evaluates both conditions in one row context. Inside the filter, `CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders, Orders[CustomerID]))` correctly counts all rows for the same customer, avoiding any interference from the date filter on the outer row, and the AND ensures only customers with more than five orders and a recent order date are included. SUMX then sums the TotalAmount across those qualifying rows, directly matching the requirement while remaining efficient and maintainable.

Why this answer

It iterates over filtered rows where both conditions are met using a single FILTER and SUMX, which is syntactically valid and more efficient than multiple FILTER iterators. Option B is invalid because KEEPFILTERS expects a filter expression, not a scalar boolean result from CALCULATE(...) > 5.

Exam trap

The trap here is that candidates often choose Option B because they think KEEPFILTERS can wrap a scalar boolean condition such as CALCULATE(COUNTROWS(...)) > 5. That is invalid; KEEPFILTERS expects a filter expression, not a boolean scalar. The correct approach uses SUMX with a single FILTER to apply both row-level conditions efficiently.

How to eliminate wrong answers

Option A is wrong because it uses two separate FILTER iterators over the Orders table, which forces a nested row context and can lead to incorrect results due to context transition; the second FILTER attempts to evaluate a CALCULATE with ALLEXCEPT inside a row context, which may not correctly count orders per customer. Option C is wrong because SUMX with a FILTER that includes a CALCULATE inside the logical expression causes context transition for each row, leading to poor performance and potentially incorrect customer-level aggregation; it also applies the date filter row-by-row rather than as a filter argument. Option D is wrong because it only filters by date using DATESINPERIOD and completely omits the customer condition (more than 5 orders), so it does not meet the requirement.

Practice this question →
11
MCQhard

You are a Power BI developer at a retail company. You have a data model with a 'Sales' fact table (10 million rows) and dimension tables: 'Date', 'Customer', 'Product', 'Store'. The 'Sales' table includes columns: SalesID, DateKey, CustomerID, ProductID, StoreID, Quantity, UnitPrice, Discount, TotalAmount. The 'Product' dimension has 5,000 rows and includes columns: ProductID, ProductName, Category, SubCategory, Brand, Price. The 'Store' dimension has 200 rows and includes columns: StoreID, StoreName, Region, City, Manager. The 'Customer' dimension has 100,000 rows. The report currently has a measure 'Total Sales' = SUM(Sales[TotalAmount]) and a measure 'Total Quantity' = SUM(Sales[Quantity]). Users complain that the report is slow when filtering by multiple categories and regions simultaneously. You need to improve performance without changing the data source. Which action should you take first?

A.Create an aggregation table for the Sales table
B.Disable the auto date/time feature in Power BI
C.Change the storage mode of the Sales table to Dual
D.Reduce the number of columns in the Customer dimension by removing unused columns
AnswerD

Removing unnecessary columns reduces model size and improves performance.

Why this answer

Reducing the number of columns in the Customer dimension removes unnecessary data, which decreases model size and improves query performance. Large dimensions like Customer (100,000 rows) benefit from column reduction. Option A (aggregation table) is a valid performance technique but requires more design effort and is not the simplest first step.

Option B (disabling auto date/time) can reduce model size slightly but is not as impactful as removing unused columns. Option C (Dual storage mode) may not improve performance for this scenario and could increase complexity.

Practice this question →
12
Multi-Selecteasy

Which TWO of the following are valid DAX functions for time intelligence? (Select two.)

Select 2 answers
A.RANKX
B.DATEADD
C.CONCATENATEX
D.MINX
E.TOTALYTD
AnswersB, E

DATEADD shifts dates by an interval.

Why this answer

DATEADD is a valid DAX time intelligence function that shifts dates forward or backward by a specified number of intervals (days, months, quarters, years). TOTALYTD is also a valid time intelligence function that calculates the year-to-date value of an expression. Both are part of the dedicated time intelligence function set in DAX, which requires a properly marked date table with continuous dates.

Exam trap

Microsoft often tests the distinction between iterator functions (like RANKX, MINX, CONCATENATEX) and dedicated time intelligence functions (like DATEADD, TOTALYTD), causing candidates to confuse functions that perform row-by-row operations with those that manipulate date ranges.

Practice this question →
13
MCQmedium

You are building a star schema model in Power BI. You have a fact table of sales transactions and dimension tables for Date, Customer, Product, and Store. The Date table contains a column 'FiscalYear' that you want to use for time intelligence calculations. What is the best practice for handling the Date relationship?

A.Create a separate fiscal date table and relate it to the fact table using the FiscalYear column.
B.Use the built-in DATESYTD function directly on the OrderDate column from the fact table.
C.Create a composite key using FiscalYear and Quarter columns in the Date table and relate to the fact table.
D.Mark the Date table as a date table using the Calendar icon in the Table tools ribbon and set a relationship on the Date column.
AnswerD

Marking the Date table as a date table using the Calendar icon in the Table tools ribbon is the correct approach because it explicitly identifies the Date column as the continuous set of dates that Power BI uses to enable time intelligence functions like DATESYTD, TOTALYTD, and SAMEPERIODLASTYEAR. Setting a relationship on the Date column—which is unique and contiguous—ensures proper filtering from the date dimension to the fact table, following the star schema design principle. This allows DAX calculations to correctly respect the user's selected date range and fiscal calendar, making it the only option that fully supports robust time-based reporting.

Why this answer

Marking the Date table as a date table (via the Calendar icon in Table tools) and creating a relationship on the Date column is the best practice for time intelligence in Power BI. This ensures that DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) work correctly by using a single, continuous date column that aligns with the fact table's date column. It also avoids the need for composite keys or separate fiscal tables, maintaining a clean star schema.

Exam trap

The trap here is that candidates often think they need a separate fiscal table or composite keys to handle fiscal years, but Power BI's date table marking feature inherently supports fiscal calendars through the 'Mark as Date Table' option and the 'Start of Fiscal Year' setting, making those workarounds unnecessary and incorrect.

How to eliminate wrong answers

Option A is wrong because creating a separate fiscal date table related via FiscalYear would break the star schema's simplicity and prevent proper time intelligence, as DAX functions require a continuous date column, not a fiscal year column. Option B is wrong because DATESYTD requires a date column from a properly marked date table, not a direct call on a fact table column, and it would ignore the fiscal year context. Option C is wrong because a composite key using FiscalYear and Quarter would not provide a continuous date range for time intelligence, and Power BI relationships should be on a single, unique column (typically the date) to avoid ambiguity and support proper filtering.

Practice this question →
14
MCQeasy

A company has a Power BI semantic model with a table named 'Sales' that contains columns: OrderDate, ShipDate, Quantity, and Revenue. The company wants to create a measure that calculates the total revenue for orders shipped within 7 days of the order date. Which DAX expression should be used?

A.CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7)
B.SUMX(FILTER(Sales, Sales[ShipDate] - Sales[OrderDate] <= 7), Sales[Revenue])
C.CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7)
D.CALCULATE(SUM(Sales[Revenue]), Sales[ShipDate] - Sales[OrderDate] <= 7)
AnswerA, C

This expression is syntactically and functionally identical to the other correct option, and its presence as a duplicate answer choice is a common exam design to test your ability to recognize a valid pattern when it appears more than once. The DATEDIFF function with DAY computes the number of day boundaries between the two dates, returning an integer that does not depend on any time portion, so the filter condition in CALCULATE correctly identifies all sales where the ship-date-to-order-date span is exactly seven days or less. Since CALCULATE applies its filter arguments as a row-level condition over the current filter context, the SUM of Revenue is computed only for that subset, yielding the desired total. Recognizing that this version is correct, despite being repeated, reinforces the key rule: use DATEDIFF for date-difference comparisons, not arithmetic subtraction on datetime columns.

Why this answer

Options A and C contain the same valid DAX expression: CALCULATE(SUM(Sales[Revenue]), DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY) <= 7). This expression correctly uses CALCULATE to modify the filter context, applying DATEDIFF to compute the day difference between OrderDate and ShipDate, and filtering for orders shipped within 7 days. Both are correct because they are identical.

Option B uses SUMX with FILTER, but direct date subtraction (Sales[ShipDate] - Sales[OrderDate]) in DAX treats dates as serial numbers with time components, which can lead to inaccurate day counts and is not recommended. Option D also uses direct date subtraction in a filter argument, which is similarly incorrect. Therefore, A and C are correct.

Exam trap

The trap here is that candidates often assume direct date subtraction works the same in DAX as in Excel or SQL, but DAX treats date subtraction as a datetime operation, not a simple day count, leading to incorrect results or errors.

How to eliminate wrong answers

Option B is wrong because it uses a direct subtraction `Sales[ShipDate] - Sales[OrderDate]`, which in DAX does not return a number of days but rather a date/time value (the difference in days as a decimal), leading to incorrect or unexpected results. Option C is wrong because it is syntactically identical to Option A but is listed as a separate answer; the question expects the correct expression, and Option C is a duplicate of A, not a distinct wrong answer. Option D is wrong because it uses direct subtraction `Sales[ShipDate] - Sales[OrderDate] <= 7`, which in DAX does not evaluate as a day count comparison; it compares a date/time value to the number 7, which is invalid and will cause an error or incorrect filtering.

Practice this question →
15
MCQeasy

A company has a Power BI dataset that contains a date table with columns: Date, Year, Month, Quarter, Day. The data model also includes a sales fact table with a SalesDate column. To enable time intelligence functions like TOTALYTD, what is the minimum requirement for the relationship between these tables?

A.Create a calculated column in the sales table to extract the date part and relate it to the date table.
B.Create a one-to-many relationship from the date table to the sales table and mark the date table as a date table.
C.Create a many-to-many relationship between the date table and the sales table.
D.Create a one-to-many relationship from the sales table to the date table with bidirectional cross-filtering.
AnswerB

This is the correct design: Power BI time intelligence functions (e.g., DATESYTD, DATEADD) rely on a date table that is explicitly marked with the Mark as Date Table option, and a one-to-many relationship from the date table to the sales table ensures each date filters its associated sales rows unambiguously. Marking the date table lets the engine identify the date column for time-based calculations, while the one-to-many cardinality matches the logical model where each calendar day can appear in many fact records. This star-schema pattern supports reliable, accurate time-series reporting.

Why this answer

Time intelligence functions like TOTALYTD require a properly configured date table marked as a date table, with a one-to-many relationship from the date table to the sales fact table. This ensures that the date table provides a continuous, unique set of dates that Power BI can use for time-based calculations, and marking it as a date table enables the engine to recognize it as the primary date dimension for time intelligence.

Exam trap

The trap here is that candidates often think any relationship between a date table and a fact table is sufficient, but they overlook the critical step of marking the date table as a date table, which is mandatory for time intelligence functions to work correctly.

How to eliminate wrong answers

Option A is wrong because creating a calculated column in the sales table to extract the date part is unnecessary and does not establish the required relationship; time intelligence functions rely on a dedicated date table with a marked date column, not on derived columns in the fact table. Option C is wrong because a many-to-many relationship between the date table and sales table would violate the requirement that the date table must have unique dates (one side) to support time intelligence, and it would introduce ambiguity in filter propagation. Option D is wrong because a one-to-many relationship from the sales table to the date table reverses the correct direction; the date table must be on the one side and the sales table on the many side, and bidirectional cross-filtering is not required for time intelligence functions.

Practice this question →
16
MCQmedium

A company has a fact table 'Sales' with a column 'SalesAmount' and a dimension table 'Date'. They want to create a measure that calculates the running total of sales over time. The Date table is marked as a date table. Which DAX expression is correct?

A.Running Total = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Date), Date[Date] <= EARLIER(Date[Date])))
B.Running Total = CALCULATE(SUM(Sales[SalesAmount]), DATESYTD(Date[Date]))
C.Running Total = TOTALYTD(SUM(Sales[SalesAmount]), Date[Date])
D.Running Total = SUMX(Sales, Sales[SalesAmount])
AnswerB

DATESYTD returns a set of dates from the start of the year to the current context date, which creates a running total within the year. For a full running total across years, one would use DATESBETWEEN or a filter.

Why this answer

DATESYTD returns a set of dates from the start of the year to the latest date in the current filter context, and when wrapped in CALCULATE, it correctly computes a year-to-date running total. Since the Date table is marked as a date table, DATESYTD works seamlessly with the time intelligence functions in DAX.

Exam trap

The trap here is that candidates often confuse DATESYTD with a general running total function, but DATESYTD only works within a single year, so the question's wording 'running total over time' might mislead test-takers into thinking any time intelligence function will work, when in fact a proper running total requires a FILTER with ALL or ALLSELECTED.

How to eliminate wrong answers

Option A is wrong because EARLIER is used in calculated columns to reference an earlier row context, not in measures; in a measure, there is no row context, so EARLIER would cause an error or incorrect results. Option C is wrong because TOTALYTD is a time intelligence function that expects a date column reference as the second argument, but the syntax shown is correct; however, the question asks for a running total over time, not specifically year-to-date, and TOTALYTD would only compute YTD, not a general running total across all dates. Option D is wrong because SUMX(Sales, Sales[SalesAmount]) simply sums the SalesAmount column row by row, which is equivalent to SUM(Sales[SalesAmount]) and does not create any running total or time-based calculation.

Practice this question →
17
MCQmedium

You are modeling data from an Azure SQL Database into Power BI. The source table 'Sales' contains 10 million rows. You need to ensure that the data model supports fast query performance for a report that shows sales by month and product category. The report uses a slicer for year. What is the best practice for improving performance?

A.Disable the auto-date/time feature.
B.Increase the data load frequency to every 15 minutes.
C.Use DirectQuery mode to query the source database directly.
D.Create an aggregate table in Power BI that pre-aggregates sales by month and product category.
AnswerD

Creating an aggregate table in Power BI that pre-aggregates sales by month and product category is the correct approach because it reduces the fact table to a much coarser grain, shrinking the number of rows that report queries must scan. By configuring this aggregate table as an aggregation group in the model, Power BI can automatically route high-level visual queries to the small summary table while reserving the detailed fact table for drill-down operations. This leverages the storage engine's in-memory columnar compression and accelerates time-intelligence calculations such as year-over-year month comparisons, directly addressing the performance bottleneck caused by large transaction-level data.

Why this answer

Creating an aggregate table in Power BI that pre-aggregates sales by month and product category drastically reduces the number of rows the report must scan, from 10 million to a much smaller set of aggregated rows. This enables fast query performance for the slicer and visual-level filters, as Power BI can leverage the aggregate table via its aggregation feature, which automatically redirects queries to the pre-summarized data when possible.

Exam trap

The trap here is that candidates often confuse DirectQuery (option C) as a performance optimization for large data volumes, but in reality, DirectQuery offloads processing to the source and can be slower for aggregated reports, whereas pre-aggregating in Power BI (option D) is the correct approach for fast in-memory query performance.

How to eliminate wrong answers

Option A is wrong because disabling the auto-date/time feature reduces model size and improves load times, but it does not address the core performance bottleneck of scanning 10 million rows for every report interaction; it is a general best practice, not a solution for large-table aggregation. Option B is wrong because increasing data load frequency to every 15 minutes improves data freshness but has no impact on query performance against the existing 10 million rows; it may even degrade performance by causing more frequent refreshes. Option C is wrong because DirectQuery mode sends queries directly to the Azure SQL Database, which would still require scanning 10 million rows on each interaction, and it introduces network latency and dependency on source database performance, often resulting in slower report responsiveness compared to an in-memory aggregated model.

Practice this question →
18
Multi-Selectmedium

Which TWO of the following are true about using the 'Mark as Date Table' feature in Power BI?

Select 2 answers
A.It is required for any relationship involving a date column
B.The date column can contain duplicate dates
C.The date table must have a column with unique date values
D.It automatically creates all date hierarchy columns (Year, Quarter, Month, Day)
E.It enables time intelligence functions like TOTALYTD
AnswersC, E

The date column must be unique.

Why this answer

Options C and E are correct. Option C is correct because the 'Mark as Date Table' feature requires the designated date column to contain unique, non-duplicate date values to function properly. Option E is correct because marking a date table enables time intelligence functions like TOTALYTD, which rely on a properly marked date table for accurate calculations.

Option A is wrong because the feature is not required for relationships involving a date column; relationships can exist without marking a date table. Option B is wrong because the date column must have unique dates, not duplicate ones. Option D is wrong because marking a date table does not automatically create all date hierarchy columns; it only designates the table as the date table, and hierarchies must be created manually or through other means.

Practice this question →
19
Multi-Selecteasy

Which TWO DAX functions can be used to filter data in a measure?

Select 2 answers
A.VALUES
B.SUM
C.ALL
D.FILTER
E.CALCULATE
AnswersD, E

FILTER returns a table filtered by a condition.

Why this answer

(FILTER) is correct because it is a DAX function specifically designed to return a filtered subset of a table based on a logical condition, making it a primary tool for row-level filtering within measures. Option E (CALCULATE) is correct because it modifies the filter context of a measure by applying or removing filters, effectively filtering data through its filter arguments. Both functions directly control which rows are included in calculations.

Exam trap

The trap here is that candidates confuse functions that return a table (like VALUES or ALL) with functions that actively filter data, or they mistake aggregation functions (like SUM) for filtering functions, leading them to select options that do not actually modify the filter context.

Practice this question →
20
MCQmedium

You have a data model with a Sales table and a Date table. You create a measure: Total Sales = SUM(Sales[Amount]). When you add a slicer for Date[Year], the measure does not filter correctly. What is the most likely cause?

A.The relationship is inactive
B.The Date table is not marked as a date table
C.The Sales table has no relationship to the Date table
D.The measure uses SUM instead of SUMX
AnswerA

A relationship marked as inactive does not automatically propagate filters during evaluation. Even though a physical relationship exists between Sales and Date, it is bypassed unless the measure explicitly invokes USERELATIONSHIP (e.g., CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DateKey], Date[DateKey]))). Because that activation is missing, the slicer on Date does not filter the measure and every date shows the same grand-total value.

Why this answer

The most likely cause is that the relationship between the Sales table and the Date table is inactive. In Power BI, only one active relationship can exist between two tables; any additional relationships must be inactive. A slicer on Date[Year] will only filter through the active relationship.

If the active relationship is not the one intended for the measure, the slicer will not affect the measure's result. Using USERELATIONSHIP in the measure or setting the correct relationship as active would resolve this.

Exam trap

The trap here is that candidates often assume a slicer will automatically filter any measure referencing a related table, but they overlook that an inactive relationship requires explicit activation in the measure for filtering to work.

How to eliminate wrong answers

Option B is wrong because marking a table as a date table enables time intelligence functions and ensures continuous date ranges, but it does not affect whether a slicer filters a measure through a relationship. Option C is wrong because if there were no relationship between the Sales and Date tables, the slicer would have no effect at all, but the question states the measure does not filter correctly, implying some filtering occurs or a relationship exists. Option D is wrong because SUM and SUMX both aggregate values; the choice between them affects row context and calculation logic, not whether a slicer filters the measure through a relationship.

Practice this question →
21
MCQmedium

You are designing a Power BI data model that includes a table named Sales with 10 million rows. You need to create a relationship between Sales and a Product dimension table. The Product table has 10,000 rows. Which configuration will provide the best query performance?

A.Create a many-to-one relationship from Sales to Product
B.Create a one-to-many relationship from Product to Sales
C.Do not create a relationship; use LOOKUPVALUE in measures
D.Create a relationship with cross filter direction set to Both
AnswerB

This is the standard cardinality for dimension-to-fact relationships, optimized for performance.

Why this answer

A one-to-many relationship from Product (the dimension table with unique values) to Sales (the fact table with many rows) is the standard star schema design. This configuration allows Power BI to use the smaller Product table to filter the larger Sales table efficiently, leveraging in-memory columnar storage and automatic aggregations for optimal query performance.

Exam trap

The trap here is that candidates often confuse the direction of the relationship arrow, thinking the 'many' side should be the source, but Power BI requires the dimension table (unique values) to be on the 'one' side for correct filter propagation and optimal performance.

How to eliminate wrong answers

Option A is wrong because a many-to-one relationship from Sales to Product would imply that Sales is the 'one' side, which is incorrect for a fact-to-dimension relationship; it would confuse the filter propagation direction and degrade performance. Option C is wrong because not creating a relationship and using LOOKUPVALUE in measures forces row-by-row evaluation in the fact table, which is extremely slow with 10 million rows and bypasses Power BI's optimized storage engine and relationship-based filtering. Option D is wrong because setting cross filter direction to Both on a many-to-one relationship between a large fact table and a small dimension table can cause ambiguous filter propagation and performance degradation due to unnecessary bidirectional filtering, especially in large models.

Practice this question →
22
MCQmedium

You have a Power BI data model with a Sales table and a Product table. You want to create a measure that calculates the percentage of total sales for each product category. Which DAX pattern should you use?

A.SUM(Sales[Amount]) / CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales))
B.SUM(Sales[Amount]) / SUM(Sales[Amount])
C.DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLSELECTED()))
D.DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product[Category])))
AnswerD

ALL(Product[Category]) inside CALCULATE removes only the active filter on the Product[Category] column, while all other filters in the current context (such as date, region, or store) are preserved. This gives a denominator that represents total sales across all product categories within the same filtering context, which is exactly the correct baseline for computing a category's percentage. DIVIDE also safely handles division by zero by returning BLANK instead of an error, making this the most accurate and robust option.

Why this answer

It uses DIVIDE for safe division and CALCULATE with ALL(Product[Category]) to remove the filter context on the Product[Category] column, allowing the measure to compute the percentage of total sales for each product category. This pattern ensures that the denominator represents the total sales across all categories, while the numerator respects the current filter context for the specific category.

Exam trap

The trap here is that candidates often confuse ALL() with ALLSELECTED() or REMOVEFILTERS(), not realizing that ALLSELECTED() preserves external slicer filters while ALL() removes all filters on the specified column, which is essential for calculating a true percentage of total within the current filter context.

How to eliminate wrong answers

Option A is wrong because REMOVEFILTERS(Sales) removes all filters from the entire Sales table, which may include unrelated filters and does not specifically target the Product[Category] column, leading to an incorrect denominator. Option B is wrong because SUM(Sales[Amount]) / SUM(Sales[Amount]) always equals 1 (or 100%) for each row, as both numerator and denominator are the same value, failing to calculate a percentage of total. Option C is wrong because ALLSELECTED() respects slicers and external filters but does not remove the filter on Product[Category] within the visual, so the denominator would still be filtered by the current category, resulting in 100% for each category.

Practice this question →
23
MCQmedium

What is the most likely cause of the error in the DAX query shown in the exhibit?

A.The [Sales Amount] measure is returning multiple values or is not a valid scalar measure.
B.There is no relationship between Date and Product tables.
C.The SUMMARIZECOLUMNS function does not support multiple group-by columns.
D.The ORDER BY clause is not allowed in EVALUATE statements.
AnswerA

The error message indicates that the measure is not a valid scalar value. When a measure references a column without an aggregation function, such as Sales[SalesAmount] directly, or returns a table expression, SUMMARIZECOLUMNS fails because it expects a scalar for each column in its result set. Ensure the measure aggregates the underlying column using SUM, AVERAGE, or another scalar-returning function. Additionally, verify the measure is not using a calculated table or a row context that produces multiple results.

Why this answer

The error 'A single value for column 'Sales Amount' in table 'Sales' cannot be determined' occurs because the [Sales Amount] measure is being used in a context that expects a scalar value, but the measure is defined to return multiple values (e.g., using SUMX over a table that returns multiple rows without proper aggregation). In DAX, measures used in EVALUATE statements must return a single scalar value; if the measure is not properly aggregated or contains a many-to-many relationship, it can produce multiple values, causing this error.

Exam trap

The trap here is that candidates often misdiagnose the error as a missing relationship or syntax issue, when the root cause is a measure returning multiple values due to improper aggregation or context.

How to eliminate wrong answers

Option B is wrong because the error message specifically mentions a single value cannot be determined for a column, not a missing relationship; a missing relationship would cause a different error (e.g., 'No relationship found' or blank results). Option C is wrong because SUMMARIZECOLUMNS explicitly supports multiple group-by columns; it is designed to accept multiple columns in its group-by clause. Option D is wrong because ORDER BY is fully allowed in EVALUATE statements in DAX; it is a standard clause for sorting query results.

Practice this question →
24
MCQhard

You are reviewing the relationships in a Power BI data model as shown in the exhibit. The model has tables: Sales, Product, Customer, and Category. You need to evaluate the performance impact of the current configuration. Which relationship is most likely to cause performance issues?

A.All relationships are equally efficient
B.The relationship between Sales and Customer
C.The relationship between Product and Category
D.The relationship between Sales and Product
AnswerC

This is the correct answer because this relationship is the one configured with bidirectional filtering, a configuration known to degrade query performance. With bidirectional cross-filtering, a filter on Category flows to Product and then to Sales, but also filters on Product or Sales can propagate back to Category, causing extra dependency chains and potential ambiguity in filter context. This forces the query engine to evaluate additional row combinations and can make the model significantly less responsive. In contrast to unidirectional relationships, this bidirectional flow creates unnecessary complexity, so the Product–Category relationship is the least efficient.

Why this answer

The relationship between Product and Category is most likely to cause performance issues because it is a many-to-many relationship without a bridge table. In Power BI, many-to-many relationships require the engine to materialize cross-join-like intermediate tables in memory, increasing query complexity and reducing performance. This is especially problematic when filtering or aggregating across these tables, as the VertiPaq engine must resolve ambiguity by creating additional internal tables.

Exam trap

The trap here is that candidates often assume all relationships are equally performant if they are correctly defined, overlooking that many-to-many cardinality inherently requires more complex processing than one-to-many relationships.

How to eliminate wrong answers

Option A is wrong because not all relationships are equally efficient; many-to-many relationships are significantly more resource-intensive than one-to-many relationships. Option B is wrong because the relationship between Sales and Customer is typically a standard one-to-many relationship (many sales per customer), which is the most efficient cardinality for star schema design and does not cause inherent performance issues. Option D is wrong because the relationship between Sales and Product is also a standard one-to-many relationship (many sales per product), which is optimized by the VertiPaq engine and does not introduce the cross-join overhead seen in many-to-many relationships.

Practice this question →
25
MCQeasy

You are modeling data from a source that includes a column 'FullName' (e.g., 'John Doe'). You want to create separate 'FirstName' and 'LastName' columns for analysis. What is the most efficient way?

A.Create calculated columns using DAX functions LEFT, RIGHT, and FIND.
B.Use the 'Replace Values' feature to manually separate names.
C.Use Excel formulas in a source query.
D.In Power Query, split the column by delimiter (space) into two columns.
AnswerD

Splitting a column by the space delimiter in Power Query is a native M transformation that invokes the Splitter.SplitTextByDelimiter function behind the scenes, generating two separate columns for first and last names in a single step. This declarative approach is optimized for large volumes of rows, requires no manual mapping, and automatically applies to every row, making it the most efficient and maintainable solution among the choices.

Why this answer

Splitting a column by delimiter in Power Query is the most efficient, native method for transforming data at the query level. It leverages Power Query's M language to perform the split in a single step, which is optimized for performance and can be refreshed automatically. This approach avoids the overhead of DAX calculated columns, which are computed in the storage engine and can slow down report rendering.

Exam trap

The trap here is that candidates often choose DAX calculated columns (Option A) because they are familiar with Excel-like formulas, but they overlook that Power Query is the correct tool for data transformation in Power BI, and DAX should be reserved for measures and calculated columns that depend on the data model's context.

How to eliminate wrong answers

Option A is wrong because creating calculated columns with DAX functions like LEFT, RIGHT, and FIND is inefficient; DAX calculated columns are evaluated row-by-row in the VertiPaq engine, consuming memory and CPU, and they cannot be used to directly split a string by a delimiter without complex nested functions. Option B is wrong because 'Replace Values' is designed for substituting specific text, not for splitting a column into multiple columns; it would require multiple manual steps and cannot dynamically handle variable-length names. Option C is wrong because using Excel formulas in a source query ties the transformation to an external application, breaking the self-service, refreshable nature of Power BI; it also introduces dependency on Excel's calculation engine, which is not part of the Power Query or DAX ecosystem.

Practice this question →
26
MCQeasy

A data model contains a Date table and a Sales table. You need to create a measure that calculates total sales for the previous year. Which DAX function should you use?

A.SAMEPERIODLASTYEAR
B.DATEADD
C.PARALLELPERIOD
D.PREVIOUSYEAR
AnswerA

SAMEPERIODLASTYEAR is the correct time intelligence function for year-over-year comparisons. It takes the current filter selection — whether it is a single date, a month, a quarter, or a custom range — and returns the exact corresponding date range from the previous calendar year, preserving the number of days and the shape of the selection. This precision makes it the standard DAX approach for computing previous-year totals, especially when you need the prior year to match the current period on a day-for-day or period-for-period basis.

Why this answer

SAMEPERIODLASTYEAR is the correct DAX function for calculating total sales for the previous year because it returns a set of dates shifted back by exactly one year while preserving the current filter context (e.g., month, quarter). This function is specifically designed for year-over-year comparisons and works seamlessly with a Date table marked as a date table in the model. Option B: DATEADD can also shift dates by one year but requires specifying the interval and number of intervals, making it less direct for this specific requirement.

Option C: PARALLELPERIOD returns the entire parallel period (e.g., full year) regardless of current granularity, which does not preserve the same relative period. Option D: PREVIOUSYEAR returns the entire previous year, not the same period shifted back, so it does not maintain month-over-month or quarter-over-quarter comparisons.

Exam trap

The trap here is that candidates often confuse SAMEPERIODLASTYEAR with PREVIOUSYEAR, not realizing that PREVIOUSYEAR returns the entire previous year regardless of the current filter granularity, while SAMEPERIODLASTYEAR shifts the exact same period (e.g., month, quarter) back by one year.

How to eliminate wrong answers

Option B (DATEADD) is wrong because it shifts dates by a specified interval (e.g., -1 year) but requires an explicit interval parameter and can produce unexpected results if the Date table is not continuous or if the interval does not align with the current filter context. Option C (PARALLELPERIOD) is wrong because it returns a parallel period of a fixed length (e.g., full year) but does not respect the current granularity of the filter context (e.g., it returns the entire previous year even if the current filter is a single month). Option D (PREVIOUSYEAR) is wrong because it returns all dates in the previous year based on the current filter context, but it does not shift the entire period; it simply returns the set of dates for the previous year, which can cause incorrect totals when used with non-standard calendars or partial year filters.

Practice this question →
27
MCQmedium

A data analyst is designing a star schema in Power BI. The model includes a table named 'Orders' with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, and SalesAmount. Which column should NOT be included in the fact table to maintain a proper star schema?

A.Quantity
B.SalesAmount
C.CustomerID
D.OrderID
AnswerD

OrderID is a natural business key that identifies an order, not a numeric measure or a surrogate foreign key. In a star schema, natural keys and descriptive attributes should live in the appropriate dimension table, such as an Order dimension, so that the fact table contains only relationship keys and measures. Including OrderID directly in the fact table violates star schema normalization and reduces maintainability. Thus, OrderID is the item that should NOT be placed directly in the fact table.

Why this answer

In a proper star schema, fact tables should contain quantitative measures and foreign keys to dimension tables. Columns like CustomerID and ProductID serve as foreign keys linking to dimension tables, so they should remain in the fact table. OrderID is a natural key that typically belongs in an Order dimension table; the fact table should use a surrogate OrderKey instead.

Including OrderID directly would duplicate dimensional data and reduce modeling flexibility.

Exam trap

Candidates often assume that all ID columns belong in the fact table. However, natural keys (like OrderID) should reside in dimension tables; the fact table should contain a surrogate key to reference them. CustomerID, on the other hand, is a foreign key that is correctly placed in the fact table.

How to eliminate wrong answers

Option A is wrong because Quantity is a numeric, additive measure that is a classic fact column in a sales fact table, representing the number of units sold per transaction. Option B is wrong because SalesAmount is a monetary measure that is the core metric for analysis and belongs in the fact table. Option D is wrong because OrderID is the unique identifier for each transaction row and serves as the fact table's grain key, which is required for proper row-level identification and relationship creation.

Practice this question →
28
MCQmedium

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

A.Product category
B.Sales amount
C.Customer ID
D.Transaction date
AnswerA

Product category is a descriptive attribute that should reside in a product dimension table.

Why this answer

Product category is a descriptive attribute that belongs in a product dimension table. Option B is wrong because sales amount is a numeric measure that should be stored in the fact table. Option C is wrong because customer ID is a foreign key that links to a customer dimension but is stored in the fact table as a key, not as a descriptive attribute.

Option D is wrong because transaction date can be part of a date dimension, but the question asks for what should be stored in a dimension table, and product category is the clearest dimension attribute among the options.

Practice this question →
29
MCQmedium

You are building a star schema in Power BI. Your fact table contains sales data at the transaction level. Which of the following is the best practice for modeling the date dimension to support time intelligence functions like year-to-date (YTD) calculations?

A.Use the fact table's date column directly in measures without a date table
B.Create a date table with only the dates that appear in the fact table
C.Use multiple date columns (e.g., OrderDate, ShipDate) in the fact table
D.Create a date table with a contiguous range of dates and mark it as a date table
AnswerD

This enables time intelligence functions and proper filtering.

Why this answer

In Power BI, to enable time intelligence functions (like YTD, QTD, etc.), a dedicated date table with a contiguous range of dates is required. This table must be marked as a date table in the model. Option A is incorrect because using the fact table's date column directly lacks the necessary date hierarchy and attributes.

Option B is incorrect because a date table that only includes dates with transactions will have gaps, leading to incorrect time intelligence calculations. Option C is incorrect because having multiple date columns in the fact table is not a replacement for a proper date dimension; instead, role-playing dimensions should be used. Option D is correct as it describes the best practice: a contiguous date table marked as a date table.

Practice this question →
30
MCQhard

Refer to the exhibit. You are implementing row-level security (RLS) in Power BI. The JSON policy above is applied to the 'Sales' table. The user is in the 'SalesRegion' role. Which rows will the user see?

A.Rows where Region is 'North' and 'South'.
B.No rows, because there is a conflict.
C.Rows where Region is 'North' only.
D.Rows where Region is 'South' only.
AnswerC

The role filter '[Region] = "North"' applies, ignoring the table-level filter.

Why this answer

The JSON policy defines a filter that restricts the 'Sales' table to rows where the 'Region' column equals 'North'. The user is in the 'SalesRegion' role, which applies this filter. RLS in Power BI evaluates the filter expression for each row, returning only those that satisfy the condition, so only rows with Region = 'North' are visible.

Exam trap

The trap here is that candidates may assume a role named 'SalesRegion' implies access to multiple regions, but the actual filter logic is determined solely by the explicit condition in the JSON policy, not the role name.

How to eliminate wrong answers

Option A is wrong because the JSON policy uses a single equality condition ('Region' eq 'North'), not an OR or IN clause that would include both 'North' and 'South'. Option B is wrong because there is no conflict; a single filter is applied without contradictory conditions, so rows are returned normally. Option D is wrong because the filter explicitly specifies 'North', not 'South', so rows with Region = 'South' are excluded.

Practice this question →
31
MCQmedium

You are designing a data model in Power BI that includes a fact table called 'Sales' and dimension tables 'Customer', 'Product', and 'Date'. The 'Sales' table contains columns: 'SalesID', 'CustomerID', 'ProductID', 'DateKey', 'Quantity', and 'Amount'. You need to ensure that the model follows star schema best practices and that filters from the 'Customer' table propagate correctly to the 'Sales' table. What should you do?

A.Merge the Customer and Sales tables into a single flat table.
B.Set the cross-filter direction to Both on the relationship between Customer and Sales.
C.Create a many-to-many relationship between Customer and Sales using SalesID.
D.Create a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID.
AnswerD

Creating a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID is the correct star schema pattern. CustomerID is the unique primary key in the Customer dimension table, and it appears as a foreign key in the Sales fact table, allowing each customer to link to multiple sales transactions. This direction supports intuitive filter propagation from the dimension to the fact table, enabling reliable aggregations like total sales per customer while preserving the granularity of the fact table and maintaining a clean, normalized model.

Why this answer

In a star schema, the dimension table (Customer) should have a one-to-many relationship to the fact table (Sales) based on the common key (CustomerID). This ensures that filters applied to the Customer table propagate correctly to the Sales table, maintaining referential integrity and enabling efficient query performance.

Exam trap

The trap here is that candidates often think bidirectional cross-filtering (Option B) is needed for filter propagation, but in a star schema, unidirectional filtering from dimension to fact is the correct and efficient approach.

How to eliminate wrong answers

Option A is wrong because merging Customer and Sales into a single flat table violates star schema normalization, leading to data redundancy and poor performance. Option B is wrong because setting cross-filter direction to Both on the relationship between Customer and Sales is unnecessary and can cause ambiguous filter propagation and performance issues; a single-direction filter from dimension to fact is sufficient. Option C is wrong because creating a many-to-many relationship using SalesID is incorrect; SalesID is a unique identifier for each sale and should not be used as a bridge for many-to-many relationships, which would break the star schema and cause incorrect aggregations.

Practice this question →
32
MCQhard

You are analyzing a DAX query as shown in the exhibit. You need to determine the result set. The model contains tables: Date, Product, and Sales with relationships. Which statement accurately describes the output?

A.The query returns total sales per year and category for Amount > 100
B.The query returns total sales for each year, ignoring category
C.The query returns sales amounts only for products with Amount > 100
D.The query returns total sales for each category, ignoring year
AnswerA

Correct. SUMMARIZECOLUMNS groups the sales data by the Year and Category columns, and the filter condition Amount > 100 is applied to the base table before aggregation. Therefore, for every distinct Year-Category pair, the query returns the sum of the sales measure computed only from rows that satisfy Amount > 100. This exactly matches the described output of total sales per year and category under that filter.

Why this answer

The DAX query uses SUMMARIZECOLUMNS to group sales by 'Year' from the Date table and 'Category' from the Product table, then filters the Sales table to include only rows where Amount > 100. The result is a table of total sales (sum of Amount) for each combination of year and category that meets the filter condition.

Exam trap

The trap here is that candidates often misinterpret the SUMMARIZE function as returning individual rows rather than aggregated groups, or they overlook that the filter condition applies to the underlying Sales rows, not to the aggregated result.

How to eliminate wrong answers

Option B is wrong because the query includes 'Category' in the SUMMARIZE grouping columns, so it does not ignore category; it returns totals per year and category, not per year alone. Option C is wrong because the query returns total sales (sum of Amount) per group, not individual sales amounts for each product; it aggregates, not lists. Option D is wrong because the query includes 'Year' in the grouping, so it does not ignore year; it returns totals per year and category, not per category alone.

Practice this question →
33
MCQmedium

You are modeling a many-to-many relationship between 'Students' and 'Courses' via a junction table 'Enrollments'. You need to create a measure that counts the number of students enrolled in at least one course. The relationship between Students and Enrollments is one-to-many, and between Courses and Enrollments is one-to-many. What DAX measure should you use?

A.COUNTROWS(Enrollments)
B.COUNT(Courses[CourseID])
C.COUNTA(Students[StudentID])
D.DISTINCTCOUNT(Students[StudentID])
AnswerD

Counts unique students, works with many-to-many.

Why this answer

DISTINCTCOUNT(Students[StudentID]). This measure counts unique students, which correctly handles the many-to-many relationship because filters applied to 'Courses' propagate through 'Enrollments' to 'Students', ensuring only students with at least one enrollment are counted. Option A (COUNTROWS(Enrollments)) counts enrollment rows, potentially double-counting students with multiple courses.

Option B (COUNT(Courses[CourseID])) counts courses, not students. Option C (COUNTA(Students[StudentID])) counts all non-blank StudentIDs regardless of enrollment, missing the condition. DISTINCTCOUNT is the proper DAX function for distinct counts.

Practice this question →
34
MCQeasy

You have a Power BI model with a table named Sales that includes columns: OrderDate, Amount, and CustomerID. You need to create a measure that returns the total sales amount for the previous month based on the current filter context. Which DAX expression should you use?

A.CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD('Date'[Date], -1, MONTH))
B.CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date]))
C.CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, MONTH))
D.CALCULATE(SUM(Sales[Amount]), NEXTMONTH('Date'[Date]))
AnswerB

PREVIOUSMONTH is the correct time-intelligence function because it returns a single-column table containing all dates from the calendar month immediately before the last date visible in the current filter context on the 'Date' table. When this table is used as a filter argument inside CALCULATE, it overrides the existing date filtering on the Sales table, so SUM(Sales[Amount]) is evaluated over exactly the prior month's dates. This is the idiomatic DAX pattern for a previous-month measure, and it avoids the shape-preserving ambiguity of PARALLELPERIOD and the forward-looking behavior of NEXTMONTH. No other period-shifting function gives as clean a one-month window as PREVIOUSMONTH in this scenario.

Why this answer

PREVIOUSMONTH returns a single month period shifted back by one month from the last date in the current filter context, which directly gives the total sales for the previous month. This measure respects the current filter context and works correctly when a proper date table is used.

Exam trap

The trap here is that candidates often confuse PREVIOUSMONTH with DATEADD or PARALLELPERIOD, not realizing that PREVIOUSMONTH is specifically designed to return a single full previous month based on the last date in context, while DATEADD shifts dates individually and PARALLELPERIOD can return multiple periods.

How to eliminate wrong answers

Option A is wrong because PARALLELPERIOD returns a set of parallel periods (e.g., entire months) but does not guarantee a single previous month; it can return multiple months if the current period spans multiple months, leading to incorrect totals. Option C is wrong because DATEADD with -1 month shifts each date by one month but does not restrict to a full previous month; it can return partial month data or overlapping periods depending on the granularity. Option D is wrong because NEXTMONTH returns the next month, not the previous month, which is the opposite of what is required.

Practice this question →
35
MCQhard

You are a Power BI administrator for a large enterprise. You have a Power BI semantic model that uses a single large fact table named Sales (100 million rows) and several dimension tables. The model is used by multiple departments, each with different row-level security (RLS) rules based on the SalesRegion column. You have implemented RLS using static roles. However, you notice that when users from different departments view the same report page, the query performance varies significantly. You suspect that the RLS filters are causing the performance difference. You need to investigate and optimize the RLS performance. What should you do first?

A.Increase the data model's memory limit in Premium capacity.
B.Use Power BI Performance Analyzer to capture query performance for each user role and analyze the generated DAX queries in DAX Studio.
C.Convert all RLS roles to use dynamic RLS with USERPRINCIPALNAME.
D.Remove all RLS roles and implement security at the report level using bookmarks.
AnswerB

Power BI Performance Analyzer captures per-visual query durations and the exact DAX produced, which you can then paste into DAX Studio for deep profiling. Running the same report as each RLS role (or using DAX Studio's 'Trace as Role'/'User' feature) lets you compare query plans and see how RLS filters are injected, exposing whether they cause excessive storage-engine scans or formula-engine bottlenecks. This combination is the standard way to pinpoint the exact query path responsible for role-specific slowness.

Why this answer

Using Power BI Performance Analyzer allows you to capture query performance for each user role, and analyzing the generated DAX queries in DAX Studio helps identify which queries are slow and whether RLS is the cause. This is the first logical step to diagnose the performance variation. Option A (increasing memory) may not address RLS-specific issues.

Option C (converting to dynamic RLS) is a potential optimization but not the initial diagnostic step. Option D (removing RLS and using bookmarks) is not a recommended security approach and would not investigate the root cause.

Practice this question →
36
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

Power BI relationships require that the key columns on both sides have identical data types; a mismatch between text and integer, for example, will prevent the relationship from being created. Changing the data type in Power Query is the preferred method because it transforms the data during load, while changing it in the Model view only alters the metadata and may not propagate back to the query. Ensuring the same data type is the foundational step before defining cardinality and cross-filter direction.

Why this answer

In Power BI, relationships require matching data types on both sides of the key columns. If one 'ProductID' column is integer and the other is text, the relationship engine cannot resolve the join because the data types are incompatible. Changing both columns to the same data type—either in Power Query (recommended for performance) or in the model view—resolves this mismatch and allows a valid relationship to be created.

Exam trap

The trap here is that candidates assume a many-to-many relationship can ignore data type mismatches, but Power BI still enforces type compatibility on the key columns used for the relationship.

How to eliminate wrong answers

Option A is wrong because merging tables in Power Query creates a single denormalized table, which is unnecessary and can lead to data duplication; the goal is to create a relationship, not to combine the tables. Option C is wrong because using FORMAT in a calculated column converts the integer to text, but this adds a redundant column and introduces performance overhead; it is better to change the data type of the column directly in Power Query or the model. Option D is wrong because a many-to-many relationship does not bypass data type mismatches; the relationship engine still requires compatible data types on both key columns, regardless of cardinality.

Practice this question →
37
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

A star schema organizes data into dimension and fact tables, reducing redundancy and simplifying queries. This design improves query performance by minimizing the number of table joins and enabling efficient aggregation, which is a core best practice in Power BI data modeling.

Exam trap

The trap here is that candidates often confuse 'faster queries' with storing attributes directly in fact tables (Option C), not realizing that this increases table size and degrades performance due to higher cardinality and reduced compression efficiency.

Practice this question →
38
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

Using a bridge table alone does not configure the many-to-many relationship; you typically need to adjust cross-filter direction or use many-to-many cardinality.

Why this answer

Both using a bridge table and using a measure that iterates over it with SUMX are best practices for modeling many-to-many relationships in Power BI. A bridge table, when properly configured with many-to-many cardinality or CROSSFILTER, enables correct filtering. A SUMX measure over the bridge table ensures accurate aggregations.

Setting cross-filter direction to both (Option B) can cause ambiguous filtering and performance issues. Composite models with DirectQuery (Option C) are not a specific best practice for this scenario. Creating a calculated table (Option E) does not properly resolve the relationship and often leads to data duplication.

Practice this question →
39
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 is the standard pattern for handling many-to-many relationships in Power BI: it holds unique combinations of the involved keys and connects to the fact table via one-to-many relationships to each dimension. This normalizes the originally ambiguous many-to-many relationship into two clear one-to-many paths, allowing filters from either dimension to propagate correctly without duplicating fact rows. It preserves each dimension's granularity and ensures that measures aggregate exactly once per relevant fact record, making it the correct modeling solution.

Why this answer

In Power BI, many-to-many relationships between fact and dimension tables can produce ambiguous results because the model cannot determine a unique filter propagation path. The best practice is to introduce a bridge table that resolves the many-to-many relationship into two one-to-many relationships, ensuring unambiguous filter context and correct aggregations.

Exam trap

The trap here is that candidates often confuse bidirectional cross-filter direction as a quick fix for many-to-many relationships, but Microsoft explicitly warns that bidirectional filtering can lead to ambiguous results and performance degradation, whereas a bridge table is the recommended pattern.

How to eliminate wrong answers

Option A is wrong because changing the relationship to one-to-one is rarely feasible in real-world data models where multiple facts naturally relate to multiple dimensions, and forcing a one-to-one would require data duplication or loss of granularity. Option B is wrong because bidirectional cross-filter direction can cause ambiguous filter propagation and performance issues, and it does not resolve the underlying logical many-to-many relationship; it often leads to circular dependencies or unexpected results. Option C is wrong because creating a calculated table to merge dimensions does not address the many-to-many relationship; it simply combines dimension attributes without resolving the cardinality mismatch, and it can introduce redundancy and maintenance challenges.

Practice this question →
40
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 (Get Data) provides native connectors and a rich transformation environment to merge, append, and shape datasets from disparate sources into a single, consistent model. Publishing that model once and configuring a daily scheduled refresh in the Power BI service centralizes maintenance and ensures all visuals receive updated data automatically. This approach aligns with best practices for scalable self-service BI because the refresh burden is handled by the service, not a user.

Why this answer

Power Query (Get Data) in Power BI Desktop is designed to connect to and combine data from multiple heterogeneous sources—SQL Server, Excel, and SharePoint—into a single dataset. After publishing to the Power BI service, you can configure a scheduled refresh (via an on-premises data gateway for on-premises sources) to keep the dataset up to date automatically, which is the recommended approach for recurring refreshes.

Exam trap

The trap here is that candidates often confuse composite models (DirectQuery) with import mode, thinking they can combine sources with DirectQuery and still schedule a refresh, but DirectQuery does not support scheduled refresh—it queries the source live, which is not the recommended approach for combining multiple sources into a single refreshable dataset.

How to eliminate wrong answers

Option A is wrong because manually updating separate Power BI Desktop files defeats the purpose of automation and introduces data inconsistency and extra overhead; Power BI is built for scheduled, centralized refresh. Option B is wrong because using Excel Online as an intermediary adds unnecessary complexity, potential data duplication, and a single point of failure; Power Query can directly ingest each source without an intermediate layer. Option D is wrong because composite models with DirectQuery are intended for real-time or large-scale scenarios where you need to keep data in the source, not for combining multiple sources into a single refreshable dataset; scheduled refresh is not supported with DirectQuery sources in the same way as import mode.

Practice this question →
41
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

Importing both tables into Power BI and creating a star schema with a central fact table (Orders) and dimension tables (CustomerRegion, Date) enables efficient measure calculation. The measures can use SUMX to compute sales amount (SUMX(Orders, Orders[Quantity] * Orders[UnitPrice] - Orders[Discount])) and average shipping cost per order filtered by region via relationships. A separate date table is required for time intelligence functions like SAMEPERIODLASTYEAR for year-over-year comparison.

Option A is suboptimal because adding calculated columns in Power Query increases storage and processing time; measures are preferable. Option B creates a flat denormalized table which duplicates customer region data, leading to larger model and slower performance. Option C is incorrect because DirectQuery is not supported on CSV files; Power BI requires data import or a connection to a database.

Therefore, Option D is the best approach.

Practice this question →
42
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 →
43
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

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 →
44
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 is a fixed prerequisite for the refresh: the Power BI engine must run the source query and wait for the full 5-minute execution to complete before it receives any data to load. Only after the view returns can the data loading phase, including any transformations, indexing, and storage into the in-memory columnstore, take place. Therefore, the absolute minimum refresh duration is 5 minutes, with the actual time being that 5 minutes plus the entire downstream loading and processing time.

Why this answer

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 →
45
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 in Power BI Desktop allows importing data from both on-premises SQL Server and cloud-based Salesforce, enabling merging/append operations to shape data into a star schema. This in-memory model supports all relationships and calculations needed.

Why this answer

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 →
46
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 B, C, and E are correct. Option B: Hiding foreign key columns in dimension tables prevents users from accidentally using them in visuals, which is a best practice. Option C: Single-direction cross-filtering is recommended to avoid ambiguity and performance issues; bidirectional should only be used when necessary.

Option E: Ensuring data types match between related columns is essential for creating valid relationships. Option A is incorrect because many-to-many relationships should be avoided when possible; they add complexity and can negatively impact performance. Option D is incorrect because setting cross-filter direction to both is not recommended by default—it can lead to ambiguous filtering and degraded performance.

Practice this question →
47
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

Filtering CustomerAddress to IsPrimary = True in Power Query before merging into Customer loads only one address per customer, eliminating fan-out and reducing model size. The merge creates a clean dimension table with the primary address attributes, allowing a single active relationship from the fact table to each customer's primary location. This ETL approach minimizes storage and prevents the need for complex DAX or inactive-relationship tricks.

Why this answer

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 →
48
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

SWITCH is the correct choice because it evaluates a single expression against a list of possible values and returns the corresponding result, making it ideal for creating multi-category calculated columns such as 'Low', 'Medium', or 'High'. Unlike nested IF functions, SWITCH keeps the logic linear and readable, and it can evaluate true/false conditions when used with a leading TRUE(), providing a clean row-level classification without context transition complexity.

Why this answer

SWITCH is the correct DAX function because it evaluates an expression (the sales amount) against a series of conditions and returns a corresponding result for each condition. In this scenario, you can use SWITCH(TRUE(), [Sales] < 1000, 'Low', [Sales] < 5000, 'Medium', 'High') to create a calculated column that categorizes values row by row. SWITCH is designed for multiple conditional branches, making it the most efficient and readable choice for this three-tier categorization.

Exam trap

The trap here is that candidates often choose IF because they are familiar with it from Excel, but SWITCH is the preferred DAX function for multiple conditions in Power BI, and the exam tests this distinction to see if you understand DAX-specific best practices.

How to eliminate wrong answers

Option B is wrong because IF is a nested function that becomes cumbersome and error-prone when handling more than two conditions; using IF for three categories would require nested IF statements, which is less efficient and harder to maintain than SWITCH. Option C is wrong because CALCULATE modifies filter context and evaluates an expression in a modified context, but it does not perform row-by-row conditional logic for categorizing values in a calculated column. Option D is wrong because FORMAT is used to convert a value to text based on a format string, not to evaluate logical conditions and return custom categories.

Practice this question →
49
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

Removing unnecessary columns from the fact table is correct because DirectQuery operates by pushing queries back to the source, and every extraneous column widens the SELECT statement, increasing network transfer and source-side processing. A narrower fact table means fewer columns are scanned and materialized for each visual interaction, which directly reduces query latency and memory overhead. This is a standard column-pruning practice for DirectQuery performance tuning.

Why this answer

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 →
50
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

In a one-to-many relationship between Products and Sales, every ProductID in Sales must have a matching ProductID in Products for the related attribute (e.g., Category) to be populated. If Sales contains ProductID values not present in Products, those rows participate in the relationship as unmatched orphans, so the lookup column from Products is blank. As a result, any visual that slices or groups by Category shows a blank bucket that aggregates the sales from those orphaned ProductID values.

Why this answer

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 →
51
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
AnswerA

The relationship is inactive, so no filtering occurs. This is the most likely cause.

Why this answer

When a relationship is inactive, filters do not propagate between tables by default. In this scenario, filtering a measure from the Customer table does not affect the Sales visual because the relationship is inactive. Active relationships are required for cross-filtering to work.

Options B and C would still allow filtering in specific directions, and D is not a standard setting.

Exam trap

Beware of inactive relationships—they are often the culprit when filters don't propagate.

Practice this question →
52
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

Proper indexing in the source database reduces the query execution time for DirectQuery models. DirectQuery sends queries to the source database in real-time, so efficient indexes on columns used in filters, joins, and aggregations minimize table scans and improve retrieval speed.

Exam trap

The trap here is that candidates often confuse performance improvements that apply to Import mode (like reducing columns) with those that are specific to DirectQuery, or they assume bidirectional filtering is always beneficial without considering its overhead on query generation.

Practice this question →
53
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

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 →
54
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 →
55
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 is a general-purpose time intelligence function that returns a table of dates from an initial start date and moves a specified number of intervals (such as -12 months) into the past or future. When used with a start date of the current context date and an interval of -12 MONTH, it dynamically constructs the exact trailing 12-month period, including all dates from the current date going back one year. This makes it the correct choice for a last-12-months measure because it directly defines the rolling window based on the current filter context.

Why this answer

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 →
56
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 TWO actions should the developer take to improve query performance?

Select 2 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.
AnswersB, C

In DirectQuery mode, Power BI sends every visual query directly to the underlying SQL Server, so query performance depends on the source engine's ability to return results quickly. Creating indexes on columns used in filters and relationship joins lets SQL Server use efficient lookup and merge operations instead of full table scans, dramatically reducing query latency. Without appropriate indexes, even simple filter operations can force the database to scan millions of rows, which directly degrades the Power BI report experience.

Why this answer

In DirectQuery mode, incremental refresh is not supported (option A is incorrect). Creating indexes on columns used in filters and relationships (option B) speeds up query execution on SQL Server. Removing unused columns from the fact table (option C) reduces the amount of data transferred per query.

Hiding columns (option D) does not affect the data retrieved by queries. Adding calculated columns (option E) increases query overhead and degrades performance.

Exam trap

Candidates often think hiding unused columns improves performance, but in DirectQuery it does not reduce query size. They also mistakenly believe calculated columns are beneficial, whereas they add overhead.

Practice this question →
57
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

Creating a calculated NetAmount column in Power Query (M) evaluates Quantity * UnitPrice - Discount once at refresh time, storing the result as a static column in the data model. The subsequent measure SUM(Sales[NetAmount]) simply aggregates those pre-computed values, avoiding row-by-row evaluation at report time and improving query performance for large fact tables. Because the calculation is pushed to the query engine instead of the DAX engine, it also keeps the code simpler and avoids iterator overhead during visual rendering.

Why this answer

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 →
58
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

TOTALYTD is a DAX time intelligence function that evaluates an expression over the year-to-date period based on a given date column. It returns a scalar value representing the cumulative total from the start of the year to the latest date in the current filter context. This function is specifically designed for time-based calculations, making it a valid answer.

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 →
59
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 canonical star-schema relationship. A single customer can appear in many orders, so Customers is the 'one' side and Orders is the 'many' side. By placing the relationship from Customers to Orders, customer attributes (region, segment, etc.) automatically filter and slice all related order rows, enabling correct aggregations in measures. This relationship uses the dimension table as the lookup table and the fact table as the data table, which is the preferred pattern for performance and intuitive filtering.

Why this answer

A one-to-many relationship from Customers (one side) to Orders (many side) with single-direction cross-filtering ensures that filtering Customers by Segment correctly filters Orders. Option B (many-to-one) would reverse the cardinality and, by default, would not allow filtering from Customers to Orders. Option C is unnecessary and can cause ambiguity.

Option D is inappropriate as one customer can have many orders.

Practice this question →
60
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, B, and C are correct. Row-level security (RLS) in Power BI ensures that users only see data they are authorized to view. Option A is correct because RLS does not apply to data accessed via the XMLA endpoint unless dynamic security is configured; static RLS filters are not applied through the endpoint.

Option B is correct because RLS filters are applied at query time, restricting data in all visuals. Option C is correct because RLS can be defined using DAX filter expressions in the Manage Roles feature. Option D is incorrect because RLS applies to all users regardless of their role (Viewer, Editor, etc.); it is not limited to the Viewer role.

Option E is incorrect because the "Show all" option in visuals does not bypass RLS; users cannot see data they are not permitted to see.

Practice this question →
61
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(Orders[OrderDate], Orders[ShipDate], DAY) is correct because the DATEDIFF function in DAX returns the number of interval boundaries crossed between a start date and an end date, with the third argument specifying the interval unit. Here, DAY asks for whole calendar days, so the result is an integer representing the total days from OrderDate to ShipDate. This is the intended calculation for order-to-ship time.

Why this answer

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 →
62
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

Reducing the number of columns in the fact query minimizes data transfer and improves DirectQuery performance. Option E is correct: Using a single date dimension table for all date columns simplifies relationships and reduces the number of joins required, enhancing query efficiency. Option A is wrong because disabling relationships would break the model integrity and force manual filtering, degrading performance.

Option B is wrong because calculated columns in DirectQuery are pushed to the source, but they can still increase query complexity and resource usage; it is better to use native columns or measures. Option D is wrong because bidirectional cross-filtering can lead to ambiguous relationships and additional query overhead, harming performance.

Practice this question →

Ready to test yourself?

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

Start 20-question session