Knowledge + Practice

CCNA Pl300 Model Data Questions

75 of 268 questions · Page 2/4 · Pl300 Model Data topic · Answers revealed

Practice these questions Exam hub All questions

76

MCQhard

Refer to the exhibit. The DAX measure 'Sales YTD' is defined as shown. The model has a Date table marked as a date table and a relationship to Sales[OrderDate]. However, the measure returns BLANK for all months except December. What is the most likely cause?

A.The measure uses 'Date'[Date] but the fact table uses 'Sales'[OrderDate].

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

C.The Date table's year-end date is December 31, so YTD only shows data for December.

D.The relationship between the Date table and Sales table is inactive.

AnswerC

DATESYTD uses the year-end date from the date table; if the fiscal year ends in December, YTD for other months returns BLANK.

Why this answer

Option D is correct because DATESYTD uses the date table's year-end date, which by default is December 31. If the Date table's year ends in December, the YTD calculation only returns values for that month. Option A is wrong because the relationship is correctly configured.

Option B is wrong because the measure uses the Date table column. Option C is wrong because the Date table is marked as a date table.

Practice this question →

77

MCQhard

You have a Power BI model with tables: Sales (DateKey, Amount) and Date (DateKey, Year, Month, Day). The measure above is intended to show sales for the current year ignoring any filters on date. However, when placed in a matrix with Month on rows, it returns the same value for every month. What is the most likely reason?

A.The CALCULATE function is misused and should be replaced with SUMX

B.The FILTER function removes month context from the Date table

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

D.The relationship between Sales and Date is inactive

AnswerB

ALL(Date) removes filters on all columns, including Month.

Why this answer

The FILTER function uses ALL(Date) which removes all filters from the Date table, including the month filter from the matrix row context. Therefore, the measure always returns the total sales for the entire current year regardless of the month. Option B is correct.

Option A is wrong because the expression is valid syntactically. Option C is wrong because there is no relationship ambiguity issue. Option D is wrong because the table name is used correctly.

Practice this question →

78

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

Option A and D are correct. Option A is correct because marking a date table enables time intelligence functions. Option D is correct because the date table must have a unique date column.

Option B is wrong because the date column must contain unique dates. Option C is wrong because marking a date table is not required for relationships, but it enables automatic time intelligence. Option E is wrong because marking a date table does not automatically add all date columns.

Practice this question →

79

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

Options A and B are correct. FILTER returns a table that can be used as a filter. CALCULATE modifies filter context.

Option C is wrong because SUM is an aggregation function. Option D is wrong because ALL removes filters. Option E is wrong because VALUES returns distinct values but does not filter by condition.

Practice this question →

80

MCQhard

Refer to the exhibit. You have the model shown. The active relationship is between OrderDate and Date. You create the measure TotalShipments. What does this measure calculate?

A.Sum of Amount where ShipDate is not blank

B.Sum of all Amount regardless of date

C.Sum of Amount filtered by OrderDate

D.Sum of Amount filtered by ShipDate

AnswerD

USERELATIONSHIP activates the ShipDate relationship.

Why this answer

The measure TotalShipments uses the active relationship between OrderDate and Date, but the CALCULATE function or filter context in the measure applies a filter on ShipDate. Because the active relationship is based on OrderDate, any filter on ShipDate requires the measure to use the inactive relationship (via USERELATIONSHIP or by default crossing the active relationship). The correct answer is D because the measure sums Amount filtered by ShipDate, leveraging the inactive relationship to evaluate shipments based on their actual ship date.

Exam trap

The trap here is that candidates assume the active relationship (OrderDate) is always used, but the measure explicitly filters by ShipDate, which requires using the inactive relationship, and many mistakenly think the measure sums all Amount or filters by OrderDate instead.

How to eliminate wrong answers

Option A is wrong because it describes a filter on ShipDate being not blank, but the measure does not explicitly check for non-blank ShipDate; it filters by ShipDate values directly. Option B is wrong because the measure does not ignore date filters; it applies a filter on ShipDate, so it does not sum all Amount regardless of date. Option C is wrong because the active relationship is between OrderDate and Date, but the measure filters by ShipDate, not OrderDate; filtering by OrderDate would use the active relationship directly, which is not what the measure does.

Practice this question →

81

MCQeasy

You have a table with columns 'Product', 'Category', and 'Sales'. You want to create a hierarchy that allows users to drill down from Category to Product. Which is the correct order to create the hierarchy?

A.Product -> Category -> Sales

B.Product -> Category

C.Category -> Product

D.Sales -> Product -> Category

AnswerC

Category is the higher level.

Why this answer

Option A is correct because hierarchies drill down from the highest level to the lowest. Option B is reverse. Option C is wrong because Sales is a measure, not a level.

Option D is wrong because Product should be below Category.

Practice this question →

82

MCQeasy

You are modeling a data source that includes a table with columns: EmployeeID, ManagerID. You need to create a hierarchy that shows the reporting structure. Which Power BI feature should you use?

A.Create a self-referencing relationship

B.Add a calculated column using IF statements

C.Use DAX PATH and PATHITEM functions to create a calculated hierarchy table

D.Use the 'Group' feature in Power Query

AnswerC

This is the standard approach for parent-child hierarchies.

Why this answer

Parent-child hierarchy functions, such as PATH, can be used to flatten a hierarchy. Option C is correct. Option A is wrong because relationships are for separate tables.

Option B is wrong because calculated columns alone cannot create a hierarchy. Option D is wrong because grouping is for binning values.

Practice this question →

83

Multi-Selectmedium

Which THREE considerations are important when designing a date table for time intelligence?

Select 3 answers

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

B.All date columns must be calculated columns.

C.The date table must have a relationship to the fact table's date column.

D.The date table must contain a contiguous range of dates.

E.The model must have exactly one date table.

AnswersA, C, D

This enables time intelligence functions.

Why this answer

Option A is correct because a continuous date range is required. Option B is correct because marking as a date table is required for time intelligence. Option D is correct because a date table should have a relationship to the fact table.

Option C is wrong because calculated columns are not a requirement. Option E is wrong because multiple date tables are not a requirement.

Practice this question →

84

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

An inactive relationship requires USERELATIONSHIP to be active.

Why this answer

Option D is correct because if the relationship is inactive, filters from the Date table won't propagate. Option A would cause a different error. Option B would give incorrect values.

Option C is not a typical issue.

Practice this question →

85

MCQeasy

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

A.Use DirectQuery on the Access database to avoid importing.

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

C.Migrate the Access database to SQL Server and then import.

D.Import both tables into Power BI and create a relationship between CustomerID columns.

AnswerD

This leverages Power BI's in-memory engine and star schema.

Why this answer

Option A is correct because importing both tables and creating a relationship allows for easy analysis. Option B is wrong because DirectQuery on Access is not recommended due to performance. Option C is wrong because a single table duplicates customer information.

Option D is wrong because an external database is unnecessary.

Practice this question →

86

MCQhard

Refer to the exhibit. What is the result of this DAX expression?

A.Sum of Amount for all regions except West

B.Sum of Amount only for the West region

C.Sum of Amount for regions other than West

D.Sum of Amount for all regions

AnswerB

KEEPFILTERS ensures West filter is applied, ALL removes other filters.

Why this answer

The DAX expression uses the CALCULATE function to modify the filter context, applying a filter that restricts the calculation to only the 'West' region. The ALL function is not used to remove filters, so the expression returns the sum of Amount exclusively for the West region, not for all regions or other regions.

Exam trap

The trap here is that candidates often confuse a positive filter (Region = "West") with a negative filter (Region <> "West") or assume CALCULATE without ALL still sums all rows, leading them to pick options that describe exclusion or aggregation over all regions.

How to eliminate wrong answers

Option A is wrong because it suggests the sum excludes West, but the expression explicitly filters for West, not against it. Option C is wrong because it implies a negative filter (regions other than West), whereas the expression uses a positive filter for West only. Option D is wrong because it claims the sum is for all regions, but the CALCULATE function with a filter on Region = 'West' overrides any existing context to return only the West region's sum.

Practice this question →

87

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

Option B is correct because 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 →

88

MCQmedium

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

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

B.Use Power Query to clean and standardize region names before loading

C.Hide the region column and use only region ID in visuals

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

AnswerB

Ensures consistent data in the dimension table.

Why this answer

Option C is correct: Standardize region names using Power Query transformations before loading into the model. Option A is wrong because keeping inconsistent spellings causes incorrect grouping. Option B is wrong because many-to-many relationships should be avoided if possible.

Option D is wrong because hiding columns doesn't fix data quality.

Practice this question →

89

MCQmedium

A Power BI developer creates a star schema with a fact table Sales and dimension tables Customer, Product, Date. The relationship between Sales and Date is active. The developer wants to create a measure that calculates the total sales for the previous month relative to any selected month. Which DAX expression should the developer use?

A.CALCULATE(SUM(Sales[Amount]), DATESMTD(Date[Date]))

B.CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))

C.CALCULATE(SUM(Sales[Amount]), DATEADD(Date[Date], -1, MONTH))

D.CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Date[Date]))

AnswerD

PREVIOUSMONTH returns the set of dates in the previous month based on the current filter context.

Why this answer

Option D is correct because PREVIOUSMONTH(Date[Date]) returns a set of dates for the full previous month relative to the last date in the current filter context. When wrapped in CALCULATE, it shifts the filter on the Date dimension to the prior month, correctly computing total sales for the previous month regardless of the selected month.

Exam trap

The trap here is that candidates often confuse PREVIOUSMONTH (which returns the entire prior month) with DATEADD (which shifts dates individually and can produce incorrect totals when the current selection is not a full month).

How to eliminate wrong answers

Option A is wrong because DATESMTD returns the month-to-date period within the current month, not the entire previous month. Option B is wrong because SAMEPERIODLASTYEAR shifts the date range back one full year, not one month. Option C is wrong because DATEADD with -1 MONTH shifts each individual date back by one month, which can produce partial months or incorrect totals when the selected period is not a full month; it does not guarantee the contiguous previous month boundary like PREVIOUSMONTH does.

Practice this question →

90

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 removes filters on Category to get total.

Why this answer

Option B is correct because DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product[Category]))) calculates the percentage of total. Option A uses ALL(Product) which removes all filters on Product table. Option C uses ALLSELECTED which respects slicers.

Option D uses REMOVEFILTERS on a column.

Practice this question →

91

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 indicates that the measure is not a scalar, likely because it references multiple columns without aggregation.

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 →

92

MCQmedium

You are building a Power BI report for a logistics company. The data source is a SQL Server database with tables: 'Shipments' (columns: ShipmentID, OrderDate, ShipDate, CustomerID, Origin, Destination, Weight, Cost, Revenue), 'Customers' (CustomerID, CustomerName, Segment, Country), and 'Calendar' (Date, Year, Month, Quarter). You need to create a star schema in Power BI. You have already created the relationships. When you try to create a measure to calculate the average delivery time (ShipDate - OrderDate), you get an error because the data types are datetime. You want to create a calculated column in the Shipments table that shows the delivery time in days. Which DAX expression should you use?

A.DeliveryDays = Shipments[ShipDate] - Shipments[OrderDate]

B.DeliveryDays = CALCULATE(DATEDIFF(Shipments[OrderDate], Shipments[ShipDate], DAY))

C.DeliveryDays = FORMAT(Shipments[ShipDate] - Shipments[OrderDate], "d")

D.DeliveryDays = DATEDIFF(Shipments[OrderDate], Shipments[ShipDate], DAY)

AnswerD

DATEDIFF is the correct function to calculate date differences in days.

Why this answer

Option A is correct because DATEDIFF calculates the difference in days. Option B is wrong because subtracting datetimes directly may not work in all cases. Option C is wrong because DATEDIFF returns an integer, not a string.

Option D is wrong because CALCULATE is not needed.

Practice this question →

93

MCQeasy

You have a Power BI data model that contains a table 'Sales' with columns: Date, ProductID, and Revenue. You also have a 'Products' table with columns: ProductID, ProductName, and Category. You want to create a measure that calculates total revenue for the current year. Which DAX expression should you use?

A.CALCULATE(SUM(Sales[Revenue]), DATESYTD('Date'[Date]))

B.SUMX(Sales, Sales[Revenue] * (YEAR(Sales[Date]) = YEAR(TODAY())))

C.CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY())))

D.TOTALYTD(SUM(Sales[Revenue]), 'Date'[Date])

AnswerD

TOTALYTD calculates the year-to-date sum correctly.

Why this answer

Option A is correct because TOTALYTD is the appropriate time intelligence function for year-to-date calculations. Option B is wrong because DATESYTD returns a table, not a scalar. Option C is wrong because CALCULATE with DATESYTD would need to be wrapped properly.

Option D is wrong because SUM with FILTER on year is not as efficient and not standard for time intelligence.

Practice this question →

94

MCQhard

You are building a Power BI model for a logistics company. You have a table named 'Shipments' with columns: ShipmentID, OriginCity, DestinationCity, Weight, Cost, ShipDate, DeliveryDate. You also have a 'City' table with columns: CityName, State, Region. You create relationships: Shipments[OriginCity] to City[CityName] and Shipments[DestinationCity] to City[CityName]. Both relationships are active and many-to-one. You create a measure to calculate total cost: TotalCost = SUM(Shipments[Cost]) When you use a slicer on City[State], you expect to filter shipments where either the origin or destination city is in that state. However, the filter only applies to the origin city due to the active relationship. You need to modify the model so that a single slicer on City[State] filters shipments where either origin or destination is in the selected state. What is the best approach?

A.Create two separate dimension tables: 'Origin' and 'Destination', each containing a copy of the City data. Then create active relationships from Shipments to each. Finally, create a new calculated table 'AllCities' that unions the city names and use that for the slicer, with measures that use USERELATIONSHIP to switch between relationships.

B.Remove the relationship for DestinationCity and use a DAX measure with LOOKUPVALUE to get the destination state.

C.Create a new table 'Route' that contains all unique combinations of origin and destination cities, and use that as a filter table.

D.Keep both relationships active and set the cross-filter direction to both.

AnswerA

This allows filtering by either origin or destination by using the AllCities table and DAX measures that activate the appropriate relationship.

Why this answer

Option A is correct because it resolves the ambiguity of having two active relationships from Shipments to City by creating separate dimension tables for origin and destination, each with an active relationship. A calculated 'AllCities' table unions all city names for the slicer, and measures use USERELATIONSHIP to activate the appropriate relationship context, enabling a single slicer to filter shipments where either origin or destination city matches the selected state.

Exam trap

The trap here is that candidates assume setting cross-filter direction to 'both' on both active relationships will solve the multi-role dimension filtering, but Power BI does not allow bidirectional filtering on multiple active relationships between the same two tables, leading to ambiguity or errors.

How to eliminate wrong answers

Option B is wrong because LOOKUPVALUE in a measure cannot dynamically filter rows based on slicer selections; it returns a scalar value and does not propagate filter context from the City table to the Shipments table for the destination city. Option C is wrong because a 'Route' table of city combinations would not allow a single slicer on City[State] to filter both origin and destination independently; it would require complex bidirectional filtering and does not solve the active relationship conflict. Option D is wrong because setting cross-filter direction to both on both active relationships would create ambiguous filter propagation and likely cause circular dependency errors or incorrect results, as Power BI does not support multiple active many-to-one relationships with bidirectional filtering between the same two tables.

Practice this question →

95

Multi-Selecteasy

Which TWO of the following are valid DAX functions for creating a calculated table?

Select 2 answers

A.SUM

B.COUNT

C.FILTER

D.CALENDAR

E.SELECTEDVALUE

AnswersC, D

FILTER returns a table.

Why this answer

Options B and D are correct. FILTER returns a table. CALENDAR returns a table.

Option A is wrong because SUM returns a scalar. Option C is wrong because COUNT returns a scalar. Option E is wrong because SELECTEDVALUE returns a scalar.

Practice this question →

96

MCQhard

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

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

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

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

D.SUMX('Orders', Orders[Quantity] * Orders[UnitPrice] * (1 - COALESCE(Orders[Discount], 0)))

AnswerD

Correctly treats null discount as 0.

Why this answer

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

Option D is wrong because it ignores discount column entirely.

Practice this question →

97

Drag & Dropmedium

Drag and drop the steps to create a quick measure in Power BI Desktop into the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

Why this order

Quick measures provide pre-built DAX calculations; you select the type and fields, and Power BI generates the measure.

Practice this question →

98

Multi-Selectmedium

Which THREE are best practices for designing a star schema in Power BI?

Select 3 answers

A.Create a separate date dimension table

B.Create multiple relationships between fact and dimension

C.Use surrogate keys for dimension tables

D.Store measures in fact tables at the lowest grain

E.Use composite keys in fact tables

AnswersA, C, D

Separate date dimension enables time intelligence.

Why this answer

Options A, C, and D are correct. Descriptive keys, separate date dimension, and aggregations at grain are best practices. Option B is wrong because natural keys are preferred when available.

Option E is wrong because multiple relationships complicate the model.

Practice this question →

99

MCQhard

You are designing a data model for a financial analysis report. The source data includes a 'Budget' table with columns: Department, Account, Month, and BudgetAmount. The 'Actuals' table has the same structure. You need to create a combined measure that shows the variance (Actual - Budget) for each Department and Account. What is the best approach?

A.Create separate dimension tables for Department and Account, and create fact tables for Budget and Actuals with relationships

B.Create a single table by merging Budget and Actuals on Department, Account, and Month

C.Create a calculated table using SUMMARIZE and then use DAX measures

D.Use Power Query to append Budget and Actuals with a 'Type' column

AnswerA

This star schema design allows easy comparison using measures across fact tables.

Why this answer

Option B is correct because using a star schema with separate dimension tables for Department and Account and a fact table with both Actual and Budget amounts (or two fact tables) allows easy comparison. Option A is wrong because merging into one table denormalizes. Option C is wrong because it does not use proper dimensions.

Option D is wrong because using only DAX without relationships is not scalable.

Practice this question →

100

MCQmedium

You are loading data into Power BI. The above Power Query script connects to a SQL Server database and loads aggregated sales by customer. However, the report requires the ability to drill down to individual transactions for a selected customer. What is the best approach to meet this requirement without losing the performance benefit of aggregation?

A.Remove the Grouped step and add a measure to aggregate at query time

B.Modify the filter date to include all years

C.Remove the Grouped step and import all transactions

D.Use a composite model with a DirectQuery connection to the source for detail data

AnswerD

Allows drill-through to details while keeping aggregation for performance.

Why this answer

Use a composite model: import the aggregated table for high-level queries and use DirectQuery for the full detail table when drilling down. Option D is correct. Option A is wrong because it loses performance.

Option B is wrong because the script filters out data before 2023. Option C is wrong because the script groups data, removing transaction details.

Practice this question →

101

MCQmedium

You have a Power BI semantic model that imports sales data from Azure SQL Database. The model includes a date table and a fact table with a column 'SalesAmount'. You need to create a measure that calculates the total sales amount only for the last 30 days relative to the report date. Which DAX expression should you use?

A.TOTALMTD(SUM(Sales[SalesAmount]), 'Date'[Date])

B.CALCULATE(SUM(Sales[SalesAmount]), DATESINPERIOD('Date'[Date], TODAY(), -30, DAY))

C.SUMX(FILTER(Sales, Sales[Date] >= TODAY()-30), Sales[SalesAmount])

D.CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN('Date'[Date], TODAY()-30, TODAY()))

AnswerD

Correctly filters the date range to the last 30 days.

Why this answer

Option B is correct because TOTALMTD is a time intelligence function that calculates month-to-date, not last 30 days. Option A uses DATESBETWEEN with a fixed range of 30 days from today, which matches the requirement. Option C returns the first 30 days of the current month.

Option D sums all sales in the last 30 days but does not filter by date.

Practice this question →

102

MCQmedium

You have a Power BI model that includes a table 'Orders' with columns: OrderID, CustomerID, OrderDate, and TotalAmount. You have a 'Customers' table with columns: CustomerID, CustomerName, and Country. You need to create a measure that calculates the total sales amount for customers who have placed orders in the last 30 days. Which DAX measure should you use?

A.SUMX(FILTER(Orders, Orders[OrderDate] >= TODAY() - 30), Orders[TotalAmount])

B.CALCULATE(SUM(Orders[TotalAmount]), Orders[OrderDate] >= TODAY() - 30)

C.CALCULATE(SUM(Orders[TotalAmount]), FILTER(Customers, Customers[CustomerID] IN VALUES(Orders[CustomerID]) && Orders[OrderDate] >= TODAY() - 30))

D.CALCULATE(SUM(Orders[TotalAmount]), ALL(Orders), Orders[OrderDate] >= TODAY() - 30)

AnswerB

Correctly filters orders within the last 30 days and sums the amount.

Why this answer

Option A is correct because it uses CALCULATE with a filter on Orders table for the last 30 days. Option B is wrong because it filters Customers table, not Orders. Option C is wrong because it uses SUMX unnecessarily.

Option D is wrong because it uses ALL, which removes filters incorrectly.

Practice this question →

103

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

Bidirectional filtering can lead to performance degradation and unintended filter propagation.

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 →

104

MCQhard

You are modeling data in Power BI that includes a table named SurveyResponses with columns: ResponseID, QuestionID, RespondentID, and AnswerText. Each respondent answers multiple questions. You need to create a measure that counts the number of unique respondents who answered a specific question. Which DAX measure should you use?

A.DISTINCTCOUNT(SurveyResponses[RespondentID])

B.COUNT(SurveyResponses[RespondentID])

C.COUNTROWS(SurveyResponses)

D.COUNTA(SurveyResponses[RespondentID])

AnswerA

DISTINCTCOUNT returns the count of unique values in the column.

Why this answer

Option A is correct because DISTINCTCOUNT(SurveyResponses[RespondentID]) counts the number of unique RespondentID values in the table, which directly gives the count of unique respondents who answered a specific question when used in a filter context (e.g., with a slicer or visual grouping by QuestionID). This is the standard DAX pattern for counting distinct entities in a column.

Exam trap

The trap here is that candidates often confuse COUNTROWS (which counts all rows) with DISTINCTCOUNT (which counts unique values), or they assume COUNT or COUNTA will automatically deduplicate, leading them to pick a wrong option that counts total responses instead of unique respondents.

How to eliminate wrong answers

Option B is wrong because COUNT(SurveyResponses[RespondentID]) counts only non-blank numeric values in the column; RespondentID is likely text or an ID, and COUNT ignores non-numeric values, returning 0 or an error. Option C is wrong because COUNTROWS(SurveyResponses) counts all rows in the table, including multiple responses per respondent, so it does not yield a unique respondent count. Option D is wrong because COUNTA(SurveyResponses[RespondentID]) counts all non-blank values in the column, including duplicates, so it counts each response row rather than unique respondents.

Practice this question →

105

MCQmedium

You have a DAX measure that sums the Amount column for rows where Quantity is greater than 10. The Sales table has 10 million rows. What is a potential performance issue with this measure?

A.The SUMX iterator can be slow on large tables; consider using CALCULATE instead

B.The FILTER function will cause a full table scan each time the measure is evaluated

C.The measure uses context transition which can cause performance issues

D.The measure uses a complex condition that cannot be optimized

AnswerA

CALCULATE with a filter is often more efficient than SUMX for simple aggregations.

Why this answer

Option D is correct because SUMX iterates over each row in the filtered table, which can be slow for large tables. It is generally more efficient to use CALCULATE with a filter argument. Option A is wrong because the FILTER function does not inherently cause table scans; it creates a filtered table.

Option B is wrong because the measure uses a simple condition, not complex. Option C is wrong because the measure is not context-based in a way that would cause issues.

Practice this question →

106

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

Power Query provides a built-in split function.

Why this answer

Option D is correct because 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 →

107

MCQeasy

You are modeling a fact table that contains sales transactions with columns: OrderDate, ShipDate, SalesAmount, CustomerKey. You need to create a relationship to a date table. The date table has a single Date column. Which relationship should you create for the most accurate time-based analysis?

A.Create a single relationship from OrderDate to Date and ignore ShipDate.

B.Create two active relationships: OrderDate to Date and ShipDate to Date.

C.Create an active relationship from OrderDate to Date and an inactive relationship from ShipDate to Date.

D.Create only one relationship from ShipDate to Date.

AnswerC

This allows using USERELATIONSHIP for ship date analysis.

Why this answer

Option C is correct because you should create an active relationship from OrderDate to Date for standard reporting, and an inactive relationship from ShipDate to Date that can be activated using USERELATIONSHIP. Option A would have multiple active relationships causing ambiguity. Option B is not recommended as it omits an important date.

Option D is incorrect because you can have multiple relationships, one active and others inactive.

Practice this question →

108

MCQhard

You have a Power BI data model that uses a DirectQuery connection to a SQL Server database. The model contains a fact table 'OrderDetails' with 50 million rows and a dimension table 'Products' with 10,000 rows. You need to create a measure that calculates the average order quantity per product. What is the best approach to optimize performance?

A.Use CALCULATE with a filter on the Products table

B.Create a calculated column in the Products table that sums the quantity

C.Use AVERAGEX over the Products table and sum the quantity for each product

D.Create a summarized table in the source database and import it

AnswerC

AVERAGEX iterates over products (10,000 rows) rather than order details (50 million rows).

Why this answer

Option C is correct because using AVERAGEX with the Products table reduces the number of rows processed; it calculates the average per product, which is more efficient than iterating over millions of rows. Option A is wrong because CALCULATE with FILTER on the fact table can be slow. Option B is wrong because calculated columns in DirectQuery are evaluated row by row and can degrade performance.

Option D is wrong because creating an aggregated table in the source is not always feasible and adds complexity.

Practice this question →

109

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 returns a set of dates in the previous year corresponding to the current selection.

Why this answer

Option B is correct because SAMEPERIODLASTYEAR returns a set of dates shifted back one year. Option A (DATEADD) can also be used but is more general. Option C (PREVIOUSYEAR) is not a valid DAX function.

Option D (PARALLELPERIOD) returns a parallel period but not necessarily the previous year.

Practice this question →

110

MCQhard

You have a star schema with a Sales fact table and a Date dimension. You need to calculate year-to-date sales that reset each fiscal year starting July 1. Which measure is correct?

A.TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

B.TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], "6-30")

C.TOTALYTD(SUM(Sales[Amount]), 'Date'[Date], ALL('Date'), "6-30")

D.CALCULATE(SUM(Sales[Amount]), DATESYTD('Date'[Date], "6-30"))

AnswerC

TOTALYTD with year-end date "6-30" correctly calculates fiscal YTD starting July 1. The ALL('Date') ensures proper context.

Why this answer

Option D is correct because TOTALYTD allows a custom year-end date. Option A uses standard calendar year. Option B has incorrect syntax.

Option C uses DATESYTD without specifying fiscal year-end.

Practice this question →

111

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

AnswerC

CustomerID is a foreign key to a dimension table; it should be stored in the fact table to link to the Customer dimension.

Why this answer

In a star schema, fact tables store quantitative, measurable data (measures) and foreign keys to dimension tables. CustomerID is a descriptive attribute that belongs in a Customer dimension table, not in the fact table. Including it in the fact table would violate normalization principles and reduce the model's flexibility for filtering and hierarchy navigation.

Exam trap

The trap here is that candidates often mistake any ID column as a fact table key, but CustomerID is a foreign key that should be moved to a dimension table, whereas OrderID is the fact table's grain key and is correctly placed.

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 →

112

MCQhard

After loading the data using the Power Query M code shown in the exhibit, the model contains a table with 10,000 rows. However, when users filter by OrderDate in a report, the filter does not affect the aggregated TotalDue values. What is the most likely reason?

A.The TotalDue column is not a numeric type.

B.The data is pre-aggregated in Power Query, so report-level filters cannot re-aggregate.

C.The dataset is not refreshing properly.

D.The Date table has no relationship to the Sales table.

AnswerB

Pre-grouping in Power Query loses the detail rows; filters cannot re-evaluate aggregates without the underlying data.

Why this answer

Option C is correct because the GROUP BY and aggregation happen in Power Query before loading, so the data is pre-aggregated and static. Filters applied in the report cannot re-aggregate because the underlying detail is lost. Option A is wrong because the Date table relationship is not the issue; the data is already aggregated.

Option B is wrong because the data type is fine. Option D is wrong because the refresh is not related.

Practice this question →

113

MCQmedium

You are building a star schema in Power BI. Which table design best supports filtering a sales fact table by product category?

A.Use a single table containing all sales and product attributes.

B.Merge Sales and Product tables into one by appending rows.

C.Create a separate Product dimension table with category, related to Sales by ProductID.

D.Store product category in the Sales fact table.

AnswerC

Star schema design; enables filtering and reduces redundancy.

Why this answer

Option B is correct because in a star schema, dimension tables like Product are separate from fact tables. Relating Product to Sales by a common key (ProductID) enables filtering of sales by category. Option A is wrong because storing category directly in the fact table leads to redundancy and poor performance.

Option C is wrong because a single table with all data is a flat table, not a star schema. Option D is wrong because merging into one table removes the benefits of star schema design.

Practice this question →

114

MCQeasy

You are designing a data model that will support self-service analytics. You have a Sales table with over 100 million rows. Which of the following modeling approaches would provide the best query performance while maintaining a user-friendly experience?

A.Design a star schema with a fact table and dimension tables

B.Create a single flat table with all columns

C.Import all tables into a single table using Power Query merge

D.Use a snowflake schema with multiple levels of dimensions

AnswerA

Star schema is optimized for Power BI.

Why this answer

Star schema design separates facts and dimensions, optimizing query performance by reducing table scans and enabling efficient aggregations. Option C is correct. Option A is wrong because a single flat table with many columns can cause performance issues and is harder to maintain.

Option B is wrong because a snowflake schema can increase query complexity and reduce performance due to additional joins. Option D is wrong because importing all tables into a single table duplicates data and loses normalization benefits.

Practice this question →

115

Drag & Dropmedium

Drag and drop the steps to create a date table in Power BI Desktop using DAX into the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

Why this order

Creating a date table involves using the CALENDAR function to generate a continuous range of dates, then marking it as a date table for time intelligence.

Practice this question →

116

MCQmedium

You are modeling data for a retail company. The source data contains a table 'Transactions' with columns: TransactionID, StoreID, ProductID, Quantity, and SalesAmount. You need to create a star schema in Power BI. What should you do with the TransactionID column?

A.Create a separate dimension table for transactions

B.Keep TransactionID in the fact table

C.Move TransactionID to the Product dimension

D.Remove the TransactionID column to reduce model size

AnswerB

It is a natural key for the fact table and should remain there.

Why this answer

Option D is correct because TransactionID is a unique identifier for each transaction and should be kept in the fact table as a primary key. Option A is wrong because creating a separate dimension for TransactionID is unnecessary and would create a degenerate dimension. Option B is wrong because removing it loses granularity.

Option C is wrong because it is not a dimension attribute.

Practice this question →

117

Multi-Selectmedium

Which TWO of the following are best practices for designing a star schema in Power BI?

Select 2 answers

A.Dimension tables should have a primary key and descriptive columns.

B.Fact tables should contain calculated columns for business logic.

C.Fact tables should have foreign keys that relate to dimension tables.

D.Merge all tables into a single flat table for simplicity.

E.Use many-to-many relationships between fact and dimension tables.

AnswersA, C

This is a basic star schema design principle.

Why this answer

Option A is correct because dimension tables in a star schema are designed to store descriptive attributes (e.g., product name, category) and must have a primary key column that uniquely identifies each row. This primary key is referenced by foreign keys in fact tables, enabling efficient filtering and slicing in Power BI. Without a primary key, relationships cannot be properly enforced, leading to ambiguous or incorrect query results.

Exam trap

The trap here is that candidates often confuse calculated columns with measures, thinking that placing business logic in fact tables is acceptable, but Power BI best practices dictate that measures (calculated at query time) should be used instead to avoid inflating the model size and degrading performance.

Practice this question →

118

MCQeasy

You have a Power BI dataset that includes a table 'Orders' with columns: OrderDate, ShipDate, CustomerID, and SalesAmount. You need to create a calculated column that shows the number of days between OrderDate and ShipDate. Which DAX expression should you use?

A.YEAR(Orders[OrderDate])

B.DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY)

C.ENDOFMONTH(Orders[OrderDate])

D.DATEADD(Orders[OrderDate], 1, DAY)

AnswerB

Returns the number of days between the two dates.

Why this answer

Option A is correct. DATEDIFF computes the difference between two dates in the specified unit (DAYS). Option B returns a date, not a number.

Option C returns the end of the month, not a difference. Option D returns the year, not the difference.

Practice this question →

119

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

Option C is correct because product category is a descriptive attribute that should be in a product dimension. Option A is wrong because sales amount is a measure in the fact table. Option B is wrong because transaction date can be in a date dimension but is not strictly required to be in a dimension; however, the question asks for what should be stored in a dimension table, and product category is a clear dimension attribute.

Option D is wrong because customer ID is a foreign key that links to a dimension, but it is stored in the fact table as a key, not as a descriptive attribute.

Practice this question →

120

MCQeasy

You need to create a calculated column that categorizes products as 'Low', 'Medium', or 'High' based on the 'UnitPrice' column. Which DAX function should you use?

A.CALCULATE

B.SUM

C.IF

D.SWITCH

AnswerD

SWITCH can evaluate multiple conditions.

Why this answer

Option B is correct because SWITCH evaluates conditions sequentially. Option A is wrong because IF can only handle two outcomes. Option C is wrong because CALCULATE modifies filter context, not for categorization.

Option D is wrong because SUM is for aggregation.

Practice this question →

121

Multi-Selecteasy

Which TWO are valid reasons to use a star schema design in Power BI? (Select exactly 2.)

Select 2 answers

A.It improves query performance by reducing the number of joins needed

B.It allows many-to-many relationships between dimensions

C.It is required to use time intelligence functions

D.It reduces model size by storing dimension attributes in separate tables

E.It reduces the number of tables in the model

AnswersA, D

Star schema optimizes join paths and filter propagation.

Why this answer

Options B and D are correct. Option B: Star schema simplifies the model and improves query performance because of fewer joins and a clear structure. Option D: Star schema reduces model size by normalizing dimensions and avoiding duplication.

Option A is wrong because star schema does not inherently reduce the number of tables; it typically increases them compared to a flat table. Option C is wrong because star schema supports many-to-one relationships, not many-to-many directly. Option E is wrong because star schema is not specifically required for time intelligence; a date table is needed, but star schema is broader.

Practice this question →

122

MCQeasy

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

A.Create a separate table for price changes without any relationship

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

C.Create a separate dimension table for price with effective date ranges and relate it to the fact table

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

AnswerC

Enables historical analysis by joining on date.

Why this answer

Option A is correct: A role-playing dimension table for price with date ranges supports slowly changing dimension Type 2. Option B is wrong because replacing prices in the fact table loses history. Option C is wrong because a single column in the product dimension only stores current price.

Option D is wrong because a separate unrelated table cannot be used in relationships.

Practice this question →

123

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 a star schema, a dedicated date dimension table with a contiguous range of dates is essential for proper time intelligence. A single date column in the fact table is not sufficient because it lacks attributes like year, month, and quarter. Option C is correct.

Option A is wrong because multiple date columns in the fact table would violate normalization. Option B is wrong because a date table covering only sales dates would miss dates with no sales, causing incorrect YTD totals. Option D is wrong because the date table should have a unique date key.

Practice this question →

124

Multi-Selectmedium

Which TWO actions should you take when designing a data model that includes a fact table with a large number of rows and multiple dimension tables? (Select exactly 2.)

Select 2 answers

A.Use integer-based foreign keys in the fact table to link to dimension tables

B.Create calculated columns in the fact table for additional attributes

C.Enable the Auto Date/Time feature for date columns

D.Hide the fact table from the report view

E.Set default summarization to 'Sum' for all numeric columns in the fact table

AnswersA, D

Integer keys improve performance and reduce storage.

Why this answer

Options A and C are correct. Option A: Using integer foreign keys reduces storage and improves join performance. Option C: Hiding the fact table from report view prevents users from accidentally querying it directly, promoting use of measures.

Option B is wrong because creating calculated columns in the fact table can increase model size and degrade performance. Option D is wrong because default summarization is not recommended for fact tables; measures should be used instead. Option E is wrong because disabling auto date/time is a general best practice but not specific to large fact tables.

Practice this question →

125

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

Option B is correct because the role filter overrides the table-level filter for users in that role. Option A is wrong because the role filter takes precedence. Option C is wrong because the role filter applies only to the role.

Option D is wrong because RLS filters rows, not columns.

Practice this question →

126

Multi-Selectmedium

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

Select 2 answers

A.To create a summary table that is not present in the source.

B.To reduce model size by storing only aggregated data.

C.To create a date table with a continuous range of dates.

D.To create a column that depends on other columns in the same row.

AnswersA, C

Calculated tables can create new aggregations.

Why this answer

Options B and D are correct. Calculated tables are used to create new tables based on DAX expressions, useful for creating a date table (B) or a summary table (D). Calculated columns are row-level and cannot produce new tables.

Option A is not a reason; calculated columns can also use DAX. Option C is not a reason; both calculated tables and columns are recalculated on refresh.

Practice this question →

127

Multi-Selecteasy

Which THREE of the following are valid data types in Power BI?

Select 3 answers

A.Currency

B.Whole Number

C.Decimal Number

D.Time

E.Date/Time

AnswersB, C, E

Whole Number is a valid data type for integers.

Why this answer

Option A, B, and D are valid data types in Power BI. Option C is not a standard data type; 'Currency' is not a separate type (it's Decimal). Option E is not a standard type; 'Time' is not separate from DateTime.

Practice this question →

128

MCQmedium

Refer to the exhibit. A user is a member of both 'Manager' and 'Executive' roles. Which rows will the user see?

A.Only rows where Department is 'Sales'.

B.Rows where Department is 'Sales' or 'Marketing'.

C.All rows, because role membership overrides filters.

D.Rows where Department is 'Sales' only, but with both role permissions.

AnswerB

Combined roles result in union of filters.

Why this answer

Option B is correct because when a user belongs to multiple roles, the filters are combined with OR. So the user sees rows where Department is 'Sales' (from Manager) OR Department is 'Sales' or 'Marketing' (from Executive). The union is Sales and Marketing rows.

Option A is wrong because it ignores the Executive role. Option C is wrong because it shows only Sales. Option D is wrong because it shows all departments.

Practice this question →

129

Multi-Selecthard

Which THREE of the following are valid reasons to use a calculated column instead of a measure in Power BI?

Select 3 answers

A.The value is needed in a row-level security rule

B.The value is an aggregation (e.g., SUM) that changes with user interaction

C.The value must be used in a relationship between tables

D.The value is needed as a slicer or filter in a visual

E.The value is a time intelligence calculation that depends on the current filter context

AnswersA, C, D

RLS can use calculated columns.

Why this answer

Option A, B, and D are correct. Option A is correct because calculated columns are evaluated during data refresh and can be used as slicers. Option B is correct because calculated columns can be used in relationships.

Option D is correct because calculated columns are available in the data model for row-level security. Option C is wrong because measures are more efficient for dynamic aggregations. Option E is wrong because measures are better for time intelligence.

Practice this question →

130

MCQmedium

Refer to the exhibit. You write this Power Query M code to import data. What does the final step do?

A.Sorts rows by Amount descending

B.Groups rows by Amount

C.Removes the Amount column

D.Filters rows where Amount > 100

AnswerD

The condition filters rows.

Why this answer

The final step in the Power Query M code filters the table to keep only rows where the 'Amount' column value is greater than 100. This is achieved by the `Table.SelectRows` function with the condition `each [Amount] > 100`, which evaluates each row and retains those meeting the predicate. The result is a reduced dataset containing only transactions with an Amount exceeding 100.

Exam trap

The trap here is that candidates may confuse `Table.SelectRows` with sorting or grouping operations, or mistakenly think it removes a column, because the condition references a specific column name.

How to eliminate wrong answers

Option A is wrong because sorting rows by Amount descending would require `Table.Sort` with `Order.Descending`, not `Table.SelectRows`. Option B is wrong because grouping rows by Amount would use `Table.Group` with an aggregation, not a row-level filter. Option C is wrong because removing the Amount column would involve `Table.RemoveColumns`, which eliminates the column entirely rather than filtering rows based on its values.

Practice this question →

131

Multi-Selecthard

Which TWO actions are valid for improving query performance in DirectQuery mode?

Select 2 answers

A.Disable relationship enforcement to speed up joins.

B.Convert the model to Import mode.

C.Create aggregated tables to pre-summarize data.

D.Use calculated columns in the fact table.

E.Reduce the number of columns in the table.

AnswersC, E

Reduces the amount of data queried.

Why this answer

Option A is correct because reducing columns in the table reduces the data retrieved. Option C is correct because creating summarized tables can reduce data volume. Option B is wrong because disabling relationship enforcement can lead to incorrect results.

Option D is wrong because calculated columns often degrade performance in DirectQuery. Option E is wrong because Import mode is not available in DirectQuery.

Practice this question →

132

MCQhard

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

A.Create a date table using DAX with CALENDAR and mark it as a date table.

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

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

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

AnswerA

This creates a minimal, efficient date table that supports all time intelligence.

Why this answer

Option B is correct because creating a date table in DAX using CALENDAR and marking it as a date table satisfies all requirements: it minimizes model size (no extra columns), enables time intelligence, and provides a consistent dimension. Option A is wrong because Power BI's auto-generated date hierarchy cannot be marked as a date table for time intelligence. Option C is wrong because importing a full date table from SQL adds overhead without benefit.

Option D is wrong because using the Sales[OrderDate] column directly does not provide a separate date dimension and may cause issues with time intelligence.

Practice this question →

133

MCQeasy

You are building a Power BI report for a sales team. You have a table named 'Sales' with columns: SalesDate, ProductID, Quantity, UnitPrice, Discount. You also have a 'Date' table with continuous date range. You create a relationship from Sales[SalesDate] to Date[Date]. You need to calculate the total sales amount (Quantity * UnitPrice - Discount). You write the following measure: TotalSales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice] - Sales[Discount]) The measure returns the correct total. However, when you slice by month from the Date table, the total does not change. What is the most likely cause?

A.The Discount column might have negative values causing the total to be incorrect.

B.The measure should use SUM instead of SUMX because there are no row-level calculations.

C.The Date table is not marked as a date table, and the relationship is not set to filter in both directions.

D.The measure does not use the Date table; it references only the Sales table, so it ignores filters from other tables.

AnswerC

If the Date table is not properly marked, the slicer may not filter the Sales table correctly. Also, the relationship direction should be single from Date to Sales.

Why this answer

Option C is correct because when a Date table is not marked as a date table, Power BI does not automatically enable bidirectional cross-filtering between the Date and Sales tables. Without this, slicers on the Date table (e.g., month) do not propagate filters to the Sales table, so the measure, which iterates over Sales rows, remains unaffected. Marking the Date table as a date table ensures that date hierarchies and slicers correctly filter related fact tables.

Exam trap

The trap here is that candidates often assume any relationship automatically allows filters to flow in both directions, but Power BI requires explicit configuration (marking as date table or setting bidirectional cross-filtering) for date-based slicers to affect measures that only reference the fact table.

How to eliminate wrong answers

Option A is wrong because negative discount values do not prevent the measure from responding to slicers; they would only affect the calculated total value, not filter propagation. Option B is wrong because SUMX is required here to perform row-by-row multiplication and subtraction; SUM cannot handle arithmetic across multiple columns. Option D is wrong because the measure does reference the Sales table, but the issue is not about the measure ignoring filters—it is about the Date table not being able to push filters to Sales due to missing bidirectional cross-filtering or date table marking.

Practice this question →

134

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

This is the standard star schema pattern.

Why this answer

Option B is correct because a star schema requires a one-to-many relationship from dimension tables to fact tables, with the dimension table on the one side. Option A is wrong because many-to-many relationships would break star schema simplicity. Option C is wrong because bidirectional filtering can cause ambiguity and is not recommended by default.

Option D is wrong because merging tables into a single flat table is the opposite of star schema.

Practice this question →

135

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

SUMMARIZECOLUMNS groups by Year and Category, and the CALCULATE with FILTER sums only where Amount > 100.

Why this answer

Option A is correct because the DAX query uses SUMMARIZE 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 →

136

Multi-Selectmedium

Which THREE of the following are benefits of using a calculated column over a measure in Power BI?

Select 3 answers

A.They can be referenced by other calculated columns.

B.They are computed once during data refresh and stored in memory.

C.They use less memory than measures.

D.They can be used in slicers and report filters.

E.They are more efficient for large datasets than measures.

AnswersA, B, D

Calculated columns can depend on each other.

Why this answer

Option A is correct: calculated columns are evaluated during data refresh. Option B is correct: they can be used as slicers. Option D is correct: they can be referenced in other calculated columns.

Option C is incorrect because measures are more efficient for aggregations. Option E is incorrect because measures are more memory efficient.

Practice this question →

137

MCQeasy

You are developing a Power BI report for a sales team. The data source is an Excel file with a single sheet containing columns: Date, Salesperson, Product, Units Sold, Revenue. The file is updated weekly. You need to model the data to allow analysis of revenue by salesperson and product over time. What is the simplest and most effective approach?

A.Load the data into a SQL Server database first, then import from there.

B.Import the Excel file into Power BI and create a date table for time intelligence.

C.Use DirectQuery on the Excel file to avoid importing.

D.Split the Excel data into separate dimension and fact tables in Power Query.

AnswerB

This is straightforward and enables time-based analysis.

Why this answer

Option A is correct because importing the Excel file and creating a date table allows for time-based analysis with minimal effort. Option B is wrong because using DirectQuery on an Excel file is not supported. Option C is wrong because splitting into multiple tables is unnecessary for a single sheet.

Option D is wrong because Power BI can handle the data without an external database.

Practice this question →

138

Multi-Selectmedium

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

Select 2 answers

A.Use calculated columns instead of measures

B.Use a star schema design

C.Denormalize all tables into a single flat table

D.Use bidirectional relationships as default

E.Hide foreign key columns from report view

AnswersB, E

Star schema is the recommended modeling approach.

Why this answer

Options A and D are correct. Option B is wrong because calculated columns should be avoided when possible; measures are preferred. Option C is wrong because bidirectional relationships can cause ambiguity and should be used sparingly.

Option E is wrong because star schema is the recommended approach.

Practice this question →

139

MCQhard

You have a Power BI data model with a table named 'Sales' that has a many-to-many relationship with 'Product' through a bridge table. When creating a measure to calculate total sales by product, you notice the totals are double-counting. What is the most likely cause?

A.The model lacks a date dimension

B.The Product table has incorrect data types

C.The bridge table has duplicate rows

D.The cross-filter direction is set to single instead of both

AnswerD

In many-to-many relationships, bidirectional cross-filtering ensures proper aggregation and prevents double-counting.

Why this answer

Option A is correct because many-to-many relationships often require a bidirectional cross-filter direction to avoid double-counting. Option B is wrong because the bridge table is necessary. Option C is wrong because a calendar table would not solve double-counting.

Option D is wrong because data types are not the issue.

Practice this question →

140

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

Option B is correct. DISTINCTCOUNT on Students[StudentID] counts unique students, which works correctly with many-to-many because the filter from Courses propagates to Enrollments and then to Students. Option A counts rows in Enrollments, which could be multiple per student.

Option C counts courses, not students. Option D counts students but only those with enrollments; however, the filter context may cause blanks. DISTINCTCOUNT is safer.

Practice this question →

141

MCQhard

You have a Power BI data model with a table 'Orders' containing columns: OrderID, CustomerID, OrderDate, Amount. You want to create a measure that calculates the running total of sales over time, ordered by OrderDate. Which DAX expression should you use?

A.CALCULATE(SUM(Orders[Amount]), FILTER(ALLSELECTED(Orders), Orders[OrderDate] <= MAX(Orders[OrderDate])))

B.SUMX(Orders, RANKX(ALL(Orders), Orders[OrderDate], , ASC) * Orders[Amount])

C.TOTALYTD(SUM(Orders[Amount]), Orders[OrderDate])

D.CALCULATE(SUM(Orders[Amount]), FILTER(ALL(Orders), Orders[OrderDate] <= EARLIER(Orders[OrderDate])))

AnswerD

EARLIER refers to the current row in the filter context, creating a running total.

Why this answer

CALCULATE with FILTER and EARLIER is a common pattern for running totals. Option D is correct. Option A is wrong because TOTALYTD is for year-to-date, not running total across all dates.

Option B is wrong because SUM with FILTER and ALLSELECTED may not preserve order correctly. Option C is wrong because RANKX is not needed.

Practice this question →

142

Multi-Selecthard

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

Select 3 answers

A.Use CROSSFILTER function to enable many-to-many filtering

B.Remove duplicate rows from both tables

C.Create a bridge table with StudentID and ClassID

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

E.Use a calculated column to concatenate Class names in the Student table

AnswersA, C, E

Allows filtering across both tables.

Why this answer

Options A, B, and C are correct. Option A uses a bridge table which is the standard approach. Option B uses a calculated column concatenation for limited scenarios.

Option C uses CROSSFILTER to enable many-to-many filtering. Option D is wrong because removing duplicates loses information. Option E is wrong because changing to one-to-many incorrectly assumes uniqueness.

Practice this question →

143

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 returns a set of dates for the previous month, and CALCULATE adjusts context.

Why this answer

Option B is correct because 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 →

144

MCQhard

You are a data analyst for a logistics company. You have a Power BI semantic model that contains a table named Shipments with columns: ShipmentID, OriginCity, DestinationCity, ShipDate, DeliveryDate, Weight, and Cost. The model also contains a table named Cities with columns: City, State, Region, and Population. You need to create a measure that calculates the average delivery time in days for shipments that originated from cities with a population greater than 1 million. The measure should be efficient. Which DAX expression should you use?

A.AVG Delivery Time = SUMX(FILTER(Shipments, RELATED(Cities[Population]) > 1000000), Shipments[DeliveryDate] - Shipments[ShipDate]) / COUNTROWS(FILTER(Shipments, RELATED(Cities[Population]) > 1000000))

B.AVG Delivery Time = CALCULATE(AVERAGE(Shipments[DeliveryDate] - Shipments[ShipDate]), FILTER(Cities, Cities[Population] > 1000000))

C.AVG Delivery Time = CALCULATE(AVERAGE(Shipments[DeliveryDate] - Shipments[ShipDate]), DISTINCT(Cities[Population] > 1000000))

D.AVG Delivery Time = AVERAGEX(Shipments, Shipments[DeliveryDate] - Shipments[ShipDate])

AnswerB

CALCULATE with FILTER applies the filter on Cities table and computes the average of the difference.

Why this answer

Option A uses CALCULATE with a FILTER on the related table, which is correct and efficient. Option B uses SUMX over the entire table, which can be slow. Option C uses an iterator incorrectly.

Option D uses DISTINCT unnecessarily.

Practice this question →

145

MCQhard

Refer to the exhibit. You have defined row-level security (RLS) in a Power BI semantic model using Role-level security. The JSON shows three rules for the 'Sales' and 'Targets' tables. The user 'john@contoso.com' belongs to this role. John's manager column in the Sales table has the value 'john@contoso.com'. What data will John see when he queries the model?

A.Sales rows where Region is 'West' and Manager is 'john@contoso.com'; Targets rows where Region is 'West'.

B.All Sales and Targets data because John is a manager.

C.Sales rows where Region is 'West' OR Manager is 'john@contoso.com'; Targets rows where Region is 'West'.

D.Only Sales rows where Manager is 'john@contoso.com'; no Targets restriction.

AnswerA

Both filters on Sales are applied with AND.

Why this answer

Option A is correct because RLS rules are combined with AND logic. John will see only Sales rows where Region is 'West' AND Manager is 'john@contoso.com'. For Targets, only rows with Region 'West'.

Option B is incorrect because OR is not used. Option C is incorrect because both rules on Sales apply. Option D is incorrect because all rules apply.

Practice this question →

146

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

This identifies the specific queries and their performance, helping pinpoint RLS impact.

Why this answer

Option A is correct: using Performance Analyzer and DAX Studio can identify which queries are slow and whether RLS is the cause. Option B would not help directly. Option C is a possible optimization but not the first step.

Option D is wrong because dynamic RLS is not necessarily faster.

Practice this question →

147

Multi-Selecteasy

Which TWO of the following are valid DAX functions for filtering data?

Select 2 answers

A.CALCULATE

B.FILTER

C.SUM

D.ALL

E.DISTINCT

AnswersB, D

FILTER is a filter function.

Why this answer

Options B and E are correct because FILTER and ALL are filter functions. Option A is wrong because SUM is an aggregation. Option C is wrong because CALCULATE is a function that modifies filter context but is not a filter function itself.

Option D is wrong because DISTINCT is a table function but not specifically a filter function.

Practice this question →

148

Matchingmedium

Match each Power BI component to its purpose.

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

Concepts

Matches

Transform and clean data

Create calculated columns and measures

Share and collaborate on reports

Create reports and data models

On-premises report hosting

Why these pairings

These are core components of the Power BI ecosystem.

Practice this question →

149

MCQhard

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

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

B.Create a fact table and separate dimensions for Date, Customer, and Product

C.Create a single table with all columns

D.Create a fact table and one dimension containing Customer and Product

AnswerB

This follows star schema best practices.

Why this answer

Option C is correct because a star schema has a fact table (Transactions) and dimension tables for Date, Customer, Product. Option A is wrong because a single flat table is not a star schema. Option B is wrong because placing Customer and Product in the same dimension violates star schema.

Option D is wrong because splitting into two fact tables is not necessary.

Practice this question →

150

MCQmedium

You are a Power BI developer for a healthcare organization. You have a semantic model that contains a table named PatientVisits with columns: VisitID, PatientID, VisitDate, DiagnosisCode, and Charges. You have a separate table named Diagnosis with columns: DiagnosisCode, DiagnosisDescription, and Category. The Diagnosis table contains duplicate rows for the same DiagnosisCode but with different descriptions due to data entry errors. You need to create a relationship between PatientVisits and Diagnosis on DiagnosisCode. However, the duplicate values in Diagnosis prevent creating a one-to-many relationship. You must ensure that the relationship can be created without removing any rows from the Diagnosis table. What should you do?

A.Create a calculated table in DAX using SUMMARIZE(Diagnosis, Diagnosis[DiagnosisCode], "Description", FIRSTNONBLANK(Diagnosis[DiagnosisDescription], 1)) and use that table for the relationship.

B.In Power Query, remove duplicate rows based on DiagnosisCode, keeping the first occurrence.

C.Create a many-to-many relationship between PatientVisits and Diagnosis.

D.Create a composite key in PatientVisits by concatenating VisitID and DiagnosisCode, and relate to Diagnosis on that key.

AnswerA

This creates a distinct DiagnosisCode table with one description, allowing a one-to-many relationship.

Why this answer

Option A is correct: creating a calculated table that groups by DiagnosisCode and takes the first description resolves duplicates. Option B is wrong because removing duplicates in Power Query would remove rows permanently. Option C is wrong because you cannot create a relationship on a dash.

Option D would create a many-to-many relationship, which is not ideal.

Practice this question →

← PreviousPage 2 of 4 · 268 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Pl300 Model Data questions.

Start 20-question session