Courseiva

CCNA Model the data Questions

75 of 268 questions · Page 1/4 · Model the data · 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 is inactive, it must be activated using USERELATIONSHIP in the measure; otherwise, the measure will not filter correctly by date.

Why this answer

Option C is correct because 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
Multi-Selectmedium

Which TWO DAX functions can be used to create a calculated table in Power BI?

Select 2 answers
A.SELECTEDVALUE
B.FILTER
C.CALCULATE
D.SUMMARIZECOLUMNS
E.SUMX
AnswersB, D

FILTER returns a table.

Why this answer

Options A and C are correct. SUMMARIZECOLUMNS and FILTER return tables. Option B is wrong because SUMX returns a scalar.

Option D is wrong because SELECTEDVALUE returns a scalar. Option E is wrong because CALCULATE returns a scalar.

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

Spaces are not null, but concatenating with space results in blank if TRIM not used.

Why this answer

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

Option D is wrong because relationship direction doesn't affect calculated columns.

Practice this question →
4
Matchingmedium

Match each Power BI security feature to its purpose.

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

Concepts
Matches

Restrict data access at row level

Restrict access to specific tables or columns

Control permissions within a workspace

Grant access to individual reports or dashboards

Make report publicly accessible

Why these pairings

Security features control who can see what data.

Practice this question →
5
MCQhard

You are a data modeler for a logistics company. The company uses Power BI to analyze shipment data. The source system is a SQL Server database with tables: Shipments (ShipmentID, ShipDate, CustomerID, Weight, Cost, CarrierID), Customers (CustomerID, CustomerName, Region), Carriers (CarrierID, CarrierName, Mode). The database is updated in real-time. You need to build a semantic model that supports near real-time reporting with maximum data freshness while maintaining good query performance. The model should allow users to filter by customer region and carrier mode, and calculate total cost and weight per month. What is the best approach?

A.Import the three tables into Power BI and create a star schema with relationships.
B.Create a single view in SQL Server that joins all tables and use DirectQuery on that view.
C.Use DirectQuery on the three tables and create a date table in the model.
D.Use a composite model: import Customers and Carriers, and use DirectQuery on Shipments.
AnswerC

DirectQuery provides real-time data and the date table enables time intelligence.

Why this answer

Option C is correct because using DirectQuery (or dual storage) with a date table allows real-time data access and supports time intelligence. Option A is wrong because DirectQuery on a single flat table loses relationships. Option B is wrong because importing into a star schema does not support real-time.

Option D is wrong because composite model is not needed for this scenario.

Practice this question →
6
MCQmedium

You are a Power BI report creator for a university. You have a semantic model with a table named Enrollments with columns: EnrollmentID, StudentID, CourseID, EnrollmentDate, and Grade. You also have tables: Students (StudentID, StudentName, Major) and Courses (CourseID, CourseName, Department). You need to create a measure that counts the number of students who have enrolled in at least one course in the 'Science' department. The measure should be efficient and respect any filters on the report. Which DAX expression should you use?

A.Students in Science = CALCULATE(DISTINCTCOUNT(Enrollments[StudentID]), FILTER(Enrollments, RELATED(Courses[Department]) = "Science"))
B.Students in Science = CALCULATE(DISTINCTCOUNT(Enrollments[StudentID]), FILTER(Courses, Courses[Department] = "Science"))
C.Students in Science = DISTINCTCOUNT(Enrollments[StudentID])
D.Students in Science = COUNTROWS(Enrollments)
AnswerB

This counts distinct students enrolled in courses where the department is Science.

Why this answer

Option A is correct: CALCULATE with DISTINCTCOUNT and a FILTER on the related Courses table ensures only students with Science courses are counted. Option B counts enrollments, not students. Option C is incorrect because FILTER does not return a table of courses.

Option D is incorrect syntax.

Practice this question →
7
MCQhard

You are reviewing a Power Query M script used to create a table in Power BI. The script imports data from SQL Server, filters for orders in 2022, groups by ProductID to sum revenue, sorts descending, and takes the top 10. However, the table loads slowly. You need to improve performance. Which change should you make?

A.Add a Table.Buffer step before the filter to speed up subsequent operations.
B.Remove the sorting step because it is unnecessary for the final table.
C.Modify the script to use a native SQL query that performs the filtering and aggregation on the server side.
D.Combine the filter, group, and sort into a single step using Table.Buffer.
AnswerC

This enables query folding and reduces data transfer.

Why this answer

Option C is correct because pushing filtering, grouping, and aggregation to SQL Server via a native query reduces the volume of data transferred to Power BI and leverages the database engine's optimized execution. This minimizes memory and processing overhead in Power Query, directly addressing the slow load time caused by performing these operations on imported data.

Exam trap

The trap here is that candidates often assume buffering (Table.Buffer) or combining steps improves performance, when in reality the key performance gain comes from pushing transformations to the source database (query folding) to minimize data movement.

How to eliminate wrong answers

Option A is wrong because Table.Buffer only caches data in memory after it has already been loaded from SQL Server, which does not reduce data transfer or improve the initial load performance; it may even increase memory pressure. Option B is wrong because removing the sort step would change the result (top 10 requires sorted order), and sorting is not the primary cause of slowness; the bottleneck is the volume of data processed client-side. Option D is wrong because combining steps with Table.Buffer does not reduce the amount of data imported; it still requires all rows to be loaded into Power Query memory before any transformation, and buffering does not push computation to the server.

Practice this question →
8
MCQmedium

You are reviewing a Power BI model definition. The Sales table has a partition with an M expression. You need to ensure that the table can be refreshed incrementally. Which additional property must be set?

A.Add an annotation to enable incremental refresh
B.Change the data type of OrderDate to Date
C.Add more partitions to the table
D.Set the 'Mode' property of the table to 'Import' and define a range partition
AnswerD

Incremental refresh requires import mode and range partitions.

Why this answer

For incremental refresh, the table must have range partitions defined, and the partition source must be configured with a range start and end. Option B is correct. Option A is wrong because the table already has one partition.

Option C is wrong because data type conversion is not needed. Option D is wrong because annotations are optional.

Practice this question →
9
Multi-Selectmedium

Which TWO actions should you take to improve the performance of a DirectQuery model in Power BI? (Select two.)

Select 2 answers
A.Limit the columns selected to only those needed in the report.
B.Use calculated columns instead of measures to precompute values.
C.Push filters to the source database as much as possible.
D.Create aggregations on the imported tables.
E.Implement row-level security filters on the fact table.
AnswersA, C

Fewer columns mean less data transferred.

Why this answer

Options A and D are correct. Reducing the number of columns queried and applying filters early minimize data transfer. Option B is wrong because calculated columns in DirectQuery are pushed to the source and can degrade performance.

Option C is wrong because increasing row-level security complexity adds overhead. Option E is wrong because aggregations are not supported in DirectQuery mode.

Practice this question →
10
MCQmedium

You are designing a data model for a sales analysis report. The source data includes a table named 'Sales' with columns: OrderDate, CustomerID, ProductID, Quantity, and SalesAmount. You need to create a date table to support time intelligence calculations. Which approach should you use?

A.Create a date table using CALENDAR function with a continuous date range covering all possible dates, then use Mark as Date Table in the model view
B.Use the CALENDARAUTO function to automatically generate dates and rely on auto date/time
C.Use the DISTINCT function on Sales[OrderDate] to create a date table
D.Create a date table using CALENDAR function with start date = MIN(Sales[OrderDate]) and end date = MAX(Sales[OrderDate])
AnswerA

This ensures a continuous date range and proper recognition by time intelligence functions.

Why this answer

Option C is correct because creating a separate date table with a continuous date range and marking it as a date table using the Mark as Date Table feature allows DAX time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR to work correctly. Option A is wrong because generating dates from the earliest to latest order dates may miss dates with no sales, causing gaps. Option B is wrong because using only distinct order dates from Sales table does not create a continuous date range.

Option D is wrong because using DAX CALENDARAUTO without marking as date table may not be recognized by time intelligence functions.

Practice this question →
11
Multi-Selectmedium

Which TWO of the following are best practices when designing star schemas in Power BI? (Select two.)

Select 2 answers
A.Store numeric measures in fact tables.
B.Use calculated columns in fact tables for row-level security.
C.Place descriptive attributes in dimension tables.
D.Include many columns in fact tables for filtering.
E.Normalize dimension tables to reduce redundancy.
AnswersA, C

Fact tables contain the measurements.

Why this answer

Option A is correct because fact tables in a star schema are designed to store quantitative, numeric measures (e.g., sales amount, quantity) that can be aggregated. This aligns with the star schema principle of separating measures (facts) from descriptive context (dimensions), enabling efficient compression and fast aggregations in Power BI's VertiPaq engine.

Exam trap

The trap here is that candidates often confuse normalization (Option E) as a best practice from transactional databases, but Power BI star schemas require denormalized dimensions for optimal performance, and they may also mistakenly think calculated columns in fact tables (Option B) are acceptable for RLS, ignoring the performance and design implications.

Practice this question →
12
MCQmedium

You are designing a Power BI data model that includes a Sales table and a Product table. The Product table contains a column 'Category' with values like 'Electronics', 'Clothing', etc. You need to create a measure that calculates the total sales amount for the 'Electronics' category only. Which DAX expression should you use?

A.SUM(Sales[Amount])
B.SUMX(FILTER(Sales, RELATED(Product[Category]) = 'Electronics'), Sales[Amount])
C.CALCULATE(SUM(Sales[Amount]), FILTER(Product, Product[Category] = 'Electronics'))
D.CALCULATE(SUM(Sales[Amount]), Product[Category] = 'Electronics')
AnswerD

Direct filter in CALCULATE is efficient and correct.

Why this answer

Option D is correct. CALCULATE modifies filter context, and here it applies a filter on Product[Category] = 'Electronics'. Option A sums all sales without filter.

Option B uses SUMX with a filter but is less efficient. Option C uses CALCULATE with FILTER, which is redundant.

Practice this question →
13
MCQeasy

You need to create a relationship between two tables in Power BI. Table A has a column 'ProductID' with unique values. Table B has a column 'ProductID' with duplicate values. Which relationship cardinality should you choose?

A.Many-to-one (Table A to Table B)
B.One-to-many (Table A to Table B)
C.One-to-one
D.Many-to-many
AnswerB

Table A is the dimension, Table B is the fact.

Why this answer

Option A is correct. Since Table A has unique values and Table B has duplicates, the relationship is one-to-many (Table A to Table B). Option B is wrong because many-to-one would imply Table A has duplicates.

Option C is wrong because one-to-one requires uniqueness on both sides. Option D is wrong because many-to-many requires a junction table.

Practice this question →
14
MCQeasy

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

A.Import the CSV file only and use DirectQuery for the Excel file to save memory.
B.Import both tables but do not create relationships; use visual-level filters to synchronize selections.
C.Merge the two tables in Power Query into a single table to simplify the model.
D.Import both tables into Power BI, create a relationship between Enrollment[Program] and Programs[ProgramCode], use Star schema design.
AnswerD

Best practice for modeling; allows filtering by program attributes.

Why this answer

Option A is correct: Import both tables, create relationships, and use a star schema. Option B is wrong because DirectQuery on a CSV is not supported. Option C is wrong because having no relationships prevents filtering.

Option D is wrong because merging loses granularity and may cause duplication.

Practice this question →
15
MCQmedium

You are building a Power BI data model with a sales table containing millions of rows. You need to design a date dimension table to support time intelligence calculations. What is the best practice for creating the date table?

A.Use the date column from the sales table as the primary date dimension
B.Create a date table using DAX CALENDARAUTO function
C.Import a date table from an Excel file
D.Create a date table using Power Query M language
AnswerB

CALENDARAUTO automatically generates dates covering the entire model's date range.

Why this answer

Option C is correct because using DAX CALENDARAUTO ensures a contiguous set of dates covering all dates in the data model, which is essential for accurate time intelligence. Option A is wrong because importing from Excel may introduce gaps. Option B is wrong because Power Query M language can also create date tables, but DAX is typically more efficient for this purpose.

Option D is wrong because using a date column directly from the fact table without a separate date table limits time intelligence functions.

Practice this question →
16
MCQmedium

You have the above Power Query M script. What is the result of this transformation?

A.A table with one row per ProductID and columns: ProductID, Quantity
B.A table with all sales rows for 2023 and columns: ProductID, Quantity
C.A table with all sales rows for 2023 and columns: OrderDate, ProductID, Quantity, TotalQty
D.A table with one row per ProductID and columns: ProductID, TotalQty
AnswerD

Correct: grouped by ProductID, sum of Quantity as TotalQty.

Why this answer

Option D is correct because the script filters rows where OrderDate >= Jan 1 2023, removes the ProductDescription column, and groups by ProductID summing Quantity. The result is a table with one row per ProductID and a TotalQty column. Option A is wrong because it does not retain all columns.

Option B is wrong because it aggregates. Option C is wrong because it does not keep individual rows.

Practice this question →
17
MCQeasy

You have a table with columns 'Date', 'Sales', and 'Quantity'. You need to create a measure that calculates the average sales per transaction. Which DAX measure should you use?

A.AVERAGE(Sales[Quantity])
B.AVERAGE(Sales[Sales])
C.DIVIDE(SUM(Sales[Sales]), SUM(Sales[Quantity]))
D.SUM(Sales[Sales])
AnswerB

AVERAGE calculates the arithmetic mean of the Sales column.

Why this answer

Option B is correct because AVERAGE of Sales gives average per row. Option A totals sales, not average. Option C averages Quantity, not Sales.

Option D divides total sales by total quantity, which is not average per transaction.

Practice this question →
18
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 creates a proper star schema with normalized dimensions.

Why this answer

Option C is correct because in a star schema, dimension tables contain the attributes, and fact tables contain foreign keys. Removing the columns and using foreign keys normalizes the model. Option A is wrong because keeping them duplicates data and increases model size.

Option B is wrong because merging them is not necessary and can complicate the model. Option D is wrong because marking as date tables is irrelevant for region and category.

Practice this question →
19
MCQhard

You have the Power Query M code shown. What is the result of this query?

A.A table with total sales per product for years 2020 and later
B.A single row with the total sales for all products
C.A table of all sales for years 2020 and later
D.A table with total sales per year
AnswerA

Filter then group by ProductID with sum of Amount.

Why this answer

Option C is correct. The code filters rows where Year >= 2020, groups by ProductID, and sums Amount per product. Option A is not grouped.

Option B sums all rows into one. Option D is not grouped by product.

Practice this question →
20
MCQeasy

You have a Power BI model with a table 'Sales' that contains a column 'OrderDate'. You need to create a calculated column that extracts the year from OrderDate. Which DAX expression should you use?

A.DATEPART("year", Sales[OrderDate])
B.YEAR(Sales[OrderDate])
C.CALENDAR(YEAR(Sales[OrderDate]), YEAR(Sales[OrderDate]))
D.FORMAT(Sales[OrderDate], "yyyy")
AnswerB

Returns the year as an integer.

Why this answer

Option B is correct because YEAR function returns the year from a date. Option A is wrong because FORMAT returns text. Option C is wrong because DATEPART is not a DAX function (it is SQL).

Option D is wrong because CALENDAR returns a table, not a scalar.

Practice this question →
21
MCQmedium

You are designing a star schema for a sales data model. Which table should be defined as a dimension table?

A.TransactionID
B.OrderQuantity
C.SalesAmount
D.Date
AnswerD

Date is a typical dimension for time analysis.

Why this answer

Option B is correct because Date is a classic dimension used for time-based analysis. Option A is wrong because SalesAmount is a measure. Option C is wrong because TransactionID is a fact table key.

Option D is wrong because OrderQuantity is a measure.

Practice this question →
22
MCQmedium

You have a Power BI semantic model that uses row-level security (RLS). The model contains a table 'Employees' with columns: EmployeeID, ManagerID, Region. You need to configure RLS so that a manager can see only the data for employees who report to them, including indirect reports (i.e., the entire hierarchy under the manager). What is the best approach?

A.Use RLS with a DAX filter that uses PATH and PATHCONTAINS to include all descendants.
B.Use RLS with a DAX filter like 'Employees[ManagerID] = USERPRINCIPALNAME()'.
C.Add a column to the Employees table that lists all employees under each manager, then filter on that column.
D.Create a separate role for each manager and assign users accordingly.
AnswerA

This dynamically filters based on the user's position in the hierarchy.

Why this answer

Option D is correct because RLS can only filter rows directly; to handle dynamic hierarchies, you need to use DAX with functions like PATH and PATHCONTAINS to flatten the hierarchy. Option A is wrong because adding a column for each manager is not scalable. Option B is wrong because RLS does not support dynamic hierarchy by itself.

Option C is wrong because a role per manager is not dynamic.

Practice this question →
23
MCQhard

Refer to the exhibit. You are managing a Power BI workspace and applying dataset permissions via the XMLA endpoint. The JSON policy shows permissions for two users on the 'Sales' dataset. user1 has Viewer role, user2 has Reshare role. The dataset has row-level security (RLS) defined. Which statement is true about the permissions?

A.user2 can share the dataset with others but cannot build reports.
B.user2 can add new measures to the dataset using the XMLA endpoint.
C.Both users will have RLS applied when querying the dataset.
D.user1 can modify the dataset structure through the XMLA endpoint.
AnswerC

RLS is enforced for all users regardless of permission role.

Why this answer

Option A is correct because RLS is enforced for both Viewer and Reshare roles. Option B is incorrect because Reshare allows sharing but not necessarily modifying reports. Option C is incorrect because Viewer cannot modify the dataset.

Option D is incorrect because Reshare does not allow editing the dataset.

Practice this question →
24
Multi-Selecteasy

Which TWO are valid reasons to use a date table in a Power BI semantic model? (Select two.)

Select 2 answers
A.To allow users to drill down into non-date hierarchies.
B.To automatically generate date hierarchies in visuals.
C.To enable time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR.
D.To improve the performance of relationships between tables.
E.To ensure consistent date filtering across multiple fact tables.
AnswersC, E

Time intelligence functions require a date table marked as such.

Why this answer

Options A and D are correct. A date table enables time intelligence functions and provides a consistent date dimension. Option B is wrong because date tables are not required for simple calculations.

Option C is wrong because date tables don't help with non-date hierarchies. Option E is wrong because relationships are not affected by date tables.

Practice this question →
25
MCQeasy

You have a Power BI semantic model with a date table that spans from January 1, 2020 to December 31, 2025. You want to calculate year-to-date (YTD) sales for the current year. Which DAX formula should you use?

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

TOTALYTD correctly computes year-to-date sum.

Why this answer

Option B is correct because TOTALYTD calculates year-to-date sales relative to the current context. Option A is wrong because it gives total sales without any time calculation. Option C is wrong because SAMEPERIODLASTYEAR compares to the prior year, not YTD.

Option D is wrong because CALCULATE with DATESYTD is redundant; TOTALYTD is the standard function.

Practice this question →
26
MCQeasy

You are creating a Power BI report that requires a many-to-many relationship between 'Product' and 'Customer' tables. What is the recommended way to implement this?

A.Combine Product and Customer into a single table
B.Create a bridge table with ProductID and CustomerID and create relationships to both tables
C.Create a direct many-to-many relationship using the 'many-to-many' cardinality option in Power BI
D.Use a calculated column to concatenate IDs
AnswerB

This resolves the many-to-many relationship correctly.

Why this answer

Option B is correct because in Power BI, a many-to-many relationship between two tables (e.g., Product and Customer) is best implemented using a bridge table that contains the foreign keys (ProductID and CustomerID) from both tables. This bridge table resolves the many-to-many cardinality by creating two one-to-many relationships: one from Product to the bridge table and one from Customer to the bridge table. This approach ensures proper filtering and avoids ambiguity in the data model, which is a standard best practice in dimensional modeling.

Exam trap

The trap here is that candidates often assume Power BI has a built-in 'many-to-many' cardinality option that can be applied directly between two tables, but in reality, Power BI requires an intermediary bridge table to properly model such relationships.

How to eliminate wrong answers

Option A is wrong because combining Product and Customer into a single table would create a denormalized structure that leads to data redundancy, violates normalization principles, and makes the model harder to maintain and query correctly. Option C is wrong because Power BI does not support a direct many-to-many relationship cardinality option between two tables; the 'many-to-many' cardinality in Power BI is only available when using a bridge table or when both tables are from the same source with a composite key, but it is not a direct relationship setting. Option D is wrong because using a calculated column to concatenate IDs does not create a proper relationship; it only creates a string value that cannot be used to establish a relational link between tables, and it would not support correct filtering or aggregation.

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

Option C is correct because it filters Salespeople to those who have sales in the current month, then uses SUMX to sum over that filtered table. Option A is wrong because it sums directly without checking sales. Option B is wrong because it sums all sales without the salesperson filter.

Option D is wrong because it uses an inappropriate filter.

Practice this question →
28
Multi-Selecthard

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

Select 3 answers
A.Use composite keys in relationships for better performance
B.Enable bidirectional cross-filtering by default
C.Implement business logic in measures rather than calculated columns
D.Use a star schema design with dimension and fact tables
E.Use surrogate keys for dimension tables
AnswersC, D, E

Measures are more efficient and flexible.

Why this answer

Options A, C, and E are correct. A star schema is recommended. Relationships should be based on surrogate keys.

Business logic should be in measures. Option B is wrong because composite keys are not recommended; use surrogate keys. Option D is wrong because bidirectional filtering is used sparingly, not by default.

Practice this question →
29
MCQeasy

You are modeling a many-to-many relationship between 'Student' and 'Class' tables. Which approach should you use in Power BI to handle this?

A.Merge both tables into one
B.Use bidirectional cross-filtering
C.Add a bridge table with composite keys
D.Create a single one-to-many relationship
AnswerC

Bridge table breaks many-to-many into two one-to-many relationships.

Why this answer

Option C is correct because a bridge table resolves many-to-many relationships. Option A is wrong because a single relationship cannot handle many-to-many. Option B is wrong because bidirectional filtering is not sufficient.

Option D is wrong because merging creates a single table.

Practice this question →
30
Multi-Selectmedium

A Power BI developer is building a data model that includes a table 'Orders' with columns: OrderID, CustomerID, OrderDate, ShipDate, SalesAmount. The developer wants to analyze orders by both order date and ship date. Which THREE actions should the developer take to properly model this scenario?

Select 3 answers
A.Use the USERELATIONSHIP function in DAX measures to specify which relationship to use for a given calculation.
B.Create two separate date tables: one for order dates and one for ship dates.
C.Merge the OrderDate and ShipDate columns into a single column and use a flag to differentiate.
D.Create an active relationship between the Orders table and one date table, and an inactive relationship between Orders and the other date table.
E.Use a single date table with an active relationship to OrderDate and an inactive relationship to ShipDate.
AnswersA, B, D

USERELATIONSHIP activates the inactive relationship for that measure.

Why this answer

Option A is correct because the USERELATIONSHIP function in DAX allows the developer to temporarily activate an inactive relationship for a specific calculation. In this scenario, one relationship (e.g., between Orders[OrderDate] and a date table) is active, while the other (e.g., between Orders[ShipDate] and the same or another date table) is inactive. By using USERELATIONSHIP in measures, the developer can specify which relationship to use for filtering or aggregation, enabling analysis by both order date and ship date without altering the model's default behavior.

Exam trap

The trap here is that candidates often think a single date table can handle multiple date columns with only active relationships, but Power BI requires one active relationship per pair of tables, so they must create separate date tables or use USERELATIONSHIP to manage inactive relationships correctly.

Practice this question →
31
MCQeasy

You are a Power BI analyst at a marketing agency. You have a data model with a 'Campaigns' table (columns: CampaignID, CampaignName, StartDate, EndDate, Budget, Spent) and a 'Results' table (columns: ResultID, CampaignID, Date, Impressions, Clicks, Conversions). You need to model the data to analyze campaign performance over time. You create a relationship between Campaigns and Results on CampaignID (one-to-many). You also create a date dimension 'Calendar' and relate it to Results[Date]. However, when you create a measure to calculate the total budget by month, the budget for a campaign that spans multiple months is counted in each month, leading to double-counting. What should you do to accurately allocate the budget across months?

A.Create a calculated table using CROSSJOIN between Campaigns and Calendar, then use it as a bridge
B.Change the relationship between Campaigns and Results to bidirectional
C.Use the DAX function USERELATIONSHIP to activate an inactive relationship
D.Add a calculated column to Campaigns that divides the budget by the number of months
AnswerA

This creates a many-to-many relationship that allocates budget correctly.

Why this answer

Option D is correct because a many-to-many relationship between Campaigns and Calendar via a bridge table allows proper allocation. Option A is wrong because changing the relationship direction won't solve double-counting. Option B is wrong because a simple calculated column cannot allocate budget correctly.

Option C is wrong because a calculated table with CROSSJOIN is needed to create the bridge.

Practice this question →
32
MCQeasy

You are importing data from a SQL Server database into Power BI. The source table has a column 'OrderDate' of type DATETIME. You want to filter data based on the date only, ignoring time. What is the most efficient approach?

A.Change the data type of the OrderDate column to 'Date' in Power Query.
B.Create a calculated column in DAX using DATEVALUE(OrderDate).
C.Create a new column in Power Query using Date.From(OrderDate).
D.Use the 'Split Column' feature in Power Query to separate date and time.
AnswerB

This extracts the date part efficiently without altering the source column.

Why this answer

Option B is correct because DATEVALUE(OrderDate) in DAX converts a datetime value to a date-only value, which is the most efficient approach when you need to filter by date while keeping the original datetime column intact for other calculations. This avoids unnecessary data transformation in Power Query and leverages DAX's optimized date handling for filtering operations.

Exam trap

The trap here is that candidates often assume Power Query transformations are always more efficient than DAX calculated columns, but for simple date extraction, a DAX calculated column avoids data reloads and leverages the existing column's storage, making it the most efficient choice for filtering by date only.

How to eliminate wrong answers

Option A is wrong because changing the data type to 'Date' in Power Query modifies the source column permanently, which may lose time information needed for other analyses and requires reloading data if the original datetime is needed later. Option C is wrong because creating a new column in Power Query using Date.From(OrderDate) is functionally similar to Option A but adds an extra column, increasing data model size and processing overhead without performance benefit over a DAX calculated column. Option D is wrong because using 'Split Column' to separate date and time is inefficient and unnecessary, as it creates two columns and adds complexity, whereas a simple DAX calculated column achieves the same result with less overhead.

Practice this question →
33
MCQmedium

You are modeling data for a retail company. You have a 'Transactions' fact table with millions of rows. You need to improve query performance for a report that filters by 'Store' and 'Date'. Which action will have the most impact?

A.Merge the 'Store' and 'Transactions' tables into one.
B.Use a composite model with DirectQuery for the fact table.
C.Remove the date table and use the date column directly in the fact table.
D.Ensure the date table is marked as a date table and has a continuous date range.
AnswerD

Proper date table improves time intelligence and query performance.

Why this answer

Option C is correct because marking the date table improves time intelligence and performance. Option A is wrong because composite models add complexity. Option B is wrong because removing the date table would break time intelligence.

Option D is wrong because merging tables increases size.

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

Incremental refresh automatically creates and manages partitions based on a date range, optimizing refresh performance.

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 →
35
MCQmedium

You have a Power BI data model with a table named Products that contains columns ProductID, ProductName, and CategoryID. You also have a table named Categories with CategoryID and CategoryName. You want to ensure that when a user filters by a category, all products in that category are shown, even if the product has no sales. What type of relationship should you create between Products and Categories?

A.Many-to-many
B.Many-to-one from Products to Categories
C.One-to-one
D.One-to-many from Categories to Products
AnswerD

This ensures that filtering on Categories brings all related products, including those with no sales.

Why this answer

Option D is correct because a one-to-many relationship from Categories to Products ensures that each category can be associated with multiple products, and when a user filters by a category, all products in that category are displayed regardless of whether they have sales. This is the standard star schema design where the dimension table (Categories) filters the fact table (Products) via a one-to-many relationship, preserving referential integrity and showing all products in the filtered category.

Exam trap

The trap here is that candidates often confuse the direction of the relationship (many-to-one vs. one-to-many) and incorrectly think that filtering works the same in both directions, but Power BI's default cross-filter direction is single, so only a one-to-many from the dimension to the fact ensures all products in a category are shown.

How to eliminate wrong answers

Option A is wrong because a many-to-many relationship would imply that multiple categories can be associated with multiple products in a non-unique way, which is not the case here (each product belongs to exactly one category) and would introduce unnecessary complexity and potential ambiguity in filtering. Option B is wrong because a many-to-one relationship from Products to Categories would reverse the filter direction, meaning filtering by category would not automatically show all products in that category unless cross-filter direction is set to both, which is not the default and can lead to unexpected behavior. Option C is wrong because a one-to-one relationship would require each product to have exactly one category and each category to have exactly one product, which is incorrect since a category can contain many products.

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

Options A, C, and D are correct. Star schema design emphasizes dimension tables with descriptive attributes, fact tables with measures and foreign keys, and avoiding many-to-many relationships. Option B is wrong because normalization is for OLTP, not for analysis.

Option E is wrong because calculated columns are generally not recommended for dimension tables.

Practice this question →
37
Multi-Selecthard

Which TWO of the following are valid methods to handle slowly changing dimensions (SCD) Type 2 in Power Query? (Select two.)

Select 2 answers
A.Merge the dimension table with a fact table
B.Update the existing row with new attributes
C.Use a lookup table that tracks changes with start and end dates
D.Overwrite the existing attributes
E.Add a new row with a surrogate key and effective date range
AnswersC, E

A lookup table with date ranges can implement Type 2.

Why this answer

Options A and D are correct. Power Query can implement SCD Type 2 by adding a row for each change with effective dates, or by using a lookup approach with start and end dates. Option B is not a standard method.

Option C is SCD Type 1. Option E merges tables but not Type 2.

Practice this question →
38
MCQhard

Refer to the exhibit. A Power BI developer wrote the DAX measure above to calculate year-over-year sales growth. When tested in a matrix visual with Year on rows and Month on columns, the measure returns incorrect results for the month of February in leap years. What is the likely cause?

A.SAMEPERIODLASTYEAR does not handle leap years correctly for February 29.
B.The measure should be a calculated column, not a measure.
C.The variable CurrentSales is not needed; use SUM directly.
D.The DIVIDE function returns an error when PreviousSales is zero.
AnswerA

SAMEPERIODLASTYEAR shifts by 365 days, which may map to a different date in leap years.

Why this answer

Option B is correct because SAMEPERIODLASTYEAR shifts the date context by exactly 365 days, which can cause issues in leap years for dates after February 28. Option A is incorrect because DIVIDE handles division by zero. Option C is incorrect because the measure is correctly defined as a measure.

Option D is incorrect because the variable is fine.

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

Option B is correct: composite models allow mixing DirectQuery and Import. Option D is correct: relationships can be created between tables from different sources. Option A is incorrect because composite models can have multiple storage modes.

Option C is incorrect because calculated tables are limited. Option E is incorrect because composite models support many-to-many.

Practice this question →
40
MCQeasy

You have a Power BI model with a fact table 'Sales' and a dimension table 'Product'. The Product table contains columns: ProductID, ProductName, Category, and Subcategory. You want to create a hierarchy for drill-down in reports: Category > Subcategory > ProductName. What is the correct way to define this hierarchy?

A.In Model view, right-click 'Category' > 'Create hierarchy', then add Subcategory and ProductName as levels.
B.In Power Query, merge the Category, Subcategory, and ProductName columns into one.
C.Create a calculated column using CONCATENATE to combine the levels.
D.In Report view, add all three columns to a visual's Values well.
AnswerA

This creates a proper hierarchy for drill-down.

Why this answer

Option A is correct because Power BI's Model view allows you to create a hierarchy by right-clicking a column (e.g., Category) and selecting 'Create hierarchy', then adding Subcategory and ProductName as child levels. This defines a natural drill-down path for visuals, enabling users to navigate from Category to Subcategory to ProductName without modifying the data model or using workarounds.

Exam trap

The trap here is that candidates often confuse flattening data (merging or concatenating) with creating a true hierarchy, leading them to choose options B or C, which break drill-down functionality.

How to eliminate wrong answers

Option B is wrong because merging columns in Power Query creates a single text field, destroying the individual column granularity and preventing proper drill-down behavior in visuals. Option C is wrong because a calculated column using CONCATENATE also flattens the hierarchy into a single string, losing the ability to drill down stepwise through distinct levels. Option D is wrong because adding all three columns to a visual's Values well does not create a hierarchy; it treats each column as a separate measure or axis, not a structured drill-down path.

Practice this question →
41
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 standard star schema approach, allowing filters from Customers to propagate to Orders.

Why this answer

Option A is correct because 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 →
42
Multi-Selecthard

Which THREE of the following are best practices when designing a Power BI data model for performance?

Select 3 answers
A.Hide columns that are not needed in reports.
B.Avoid bi-directional cross-filtering unless necessary.
C.Use star schema design with dimension and fact tables.
D.Use many-to-many relationships directly without bridge tables.
E.Use calculated columns instead of measures for aggregations.
AnswersA, B, C

Reduces model size and complexity.

Why this answer

Options A, C, and D are correct. Star schema design (A) reduces complexity. Avoiding bi-directional cross-filtering (C) prevents performance degradation.

Hiding unnecessary columns (D) reduces model size and improves clarity. Option B is incorrect because calculated columns are generally slower than measures for dynamic calculations. Option E is incorrect because using many-to-many relationships without bridge tables can cause ambiguity and poor performance.

Practice this question →
43
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 2 answers
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
AnswersC, D

A date table is required for time intelligence functions.

Why this answer

The correct answers are A and D. First, create a date table (A) for time intelligence, then use SUMMARIZECOLUMNS (D) to group by month and count orders. Option B (CALENDARAUTO) is a function to create a date table, but the step is incomplete.

Option C (VALUES) returns distinct values, not aggregated. Option E (GROUPBY) is similar but less common.

Practice this question →
44
MCQeasy

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

A.One-to-one
B.Many-to-many
C.Many-to-one
D.One-to-many
AnswerC

Correct when the many side (fact) has duplicates and the one side (dimension) has unique keys.

Why this answer

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

Option D is wrong because one-to-one is for unique keys on both sides.

Practice this question →
45
MCQmedium

You have a Power BI model containing a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. You need to create a measure that calculates the total amount from the previous year. Which DAX function should be used?

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

Returns the previous calendar year.

Why this answer

The PREVIOUSYEAR function is specifically designed to return a table of dates for the previous year relative to the current context in the filter context. It is the most direct and appropriate DAX function for calculating total amount from the previous year when working with a standard calendar date column.

Exam trap

The trap here is that candidates often confuse PREVIOUSYEAR with SAMEPERIODLASTYEAR, not realizing that SAMEPERIODLASTYEAR shifts the exact date range (e.g., same month last year) rather than returning the entire previous year.

How to eliminate wrong answers

Option A is wrong because DATEADD shifts dates by a specified interval (e.g., -1 year) but requires a continuous date range and can produce unexpected results if the date column has gaps. Option B is wrong because PARALLELPERIOD returns a parallel period of the same length (e.g., full year) but shifts the entire period, which may not align with a simple 'previous year' calculation when the current context is a month or quarter. Option D is wrong because SAMEPERIODLASTYEAR returns the exact same dates one year back, which is ideal for same-period comparisons (e.g., same month last year) but not for aggregating the entire previous year.

Practice this question →
46
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))
AnswerB

This efficiently applies both filters using CALCULATE with KEEPFILTERS.

Why this answer

Option B is correct because it uses KEEPFILTERS to apply both the date filter and the customer-level condition within a single CALCULATE, ensuring that the row context for the customer filter is properly expanded via ALLEXCEPT without causing context transition issues. This approach is more efficient than using multiple FILTER iterators, as it leverages CALCULATE's filter modification capabilities to apply the conditions at the correct granularity.

Exam trap

The trap here is that candidates often choose Option A or C because they think multiple FILTER functions or SUMX are necessary for row-level conditions, not realizing that CALCULATE with KEEPFILTERS and ALLEXCEPT can apply both filters efficiently without nested iteration.

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 →
47
MCQhard

Refer to the exhibit. You are configuring a Power BI data model for a workspace containing a semantic model with sensitive salary data. The JSON policy above is applied. A viewer reports that they can see the EmployeeSalaries table in the report. What is the most likely reason?

A.The EmployeeSalaries table is not in the semantic model.
B.The excludedTables property hides the table only in Power Query, not in reports.
C.The JSON policy is for row-level security (RLS), not object-level security (OLS).
D.The policy has not been applied to the semantic model.
AnswerC

RLS filters rows, not tables; OLS requires a different configuration.

Why this answer

Option C is correct because the JSON policy shown uses the `tables` array with `filter` expressions, which is the syntax for row-level security (RLS) policies in Power BI. RLS restricts data rows within a table but does not hide the table itself from the report view. Object-level security (OLS) requires a different policy structure using `excludedTables` or `setObjectLevelSecurity` in XMLA endpoints or Tabular Editor, not a JSON RLS policy.

Therefore, the viewer can still see the EmployeeSalaries table because OLS has not been configured.

Exam trap

The trap here is that candidates confuse row-level security (RLS) with object-level security (OLS), assuming any JSON policy that restricts data will also hide the table structure, but RLS only filters rows and does not prevent the table from appearing in the report.

How to eliminate wrong answers

Option A is wrong because if the EmployeeSalaries table were not in the semantic model, the viewer would not see it at all; the question states they can see it, so the table exists. Option B is wrong because the `excludedTables` property is not a Power Query concept; it is used in OLS policies to hide tables from reports, and the JSON shown does not contain `excludedTables`. Option D is wrong because the policy has been applied (the JSON is described as 'applied'), and the issue is not about application but about the type of security: RLS does not hide tables.

Practice this question →
48
MCQmedium

You are building a Power BI semantic model for a healthcare provider. The model must track patient visits and procedures. You have three source tables: Patients (PatientID, Name, DOB, Gender), Visits (VisitID, PatientID, VisitDate, DoctorID), Procedures (ProcedureID, VisitID, ProcedureCode, Cost). The model should allow users to analyze procedure costs by patient demographics (gender, age group) and by date (year, quarter, month). You need to design the star schema. Which of the following is the most appropriate design?

A.Use DirectQuery and create relationships between all three tables.
B.Create a separate model for visits and another for procedures, then combine them in a composite model.
C.Patients as a dimension, Visits as a fact table, Procedures as a fact table with a many-to-one relationship to Visits.
D.Merge all tables into a single flat table in Power Query.
AnswerC

This enables analysis of procedure costs by patient demographics and date.

Why this answer

Option A is correct: Patients as a dimension, Visits as a fact (or bridge), and Procedures as a fact. This allows cost analysis by patient attributes and date. Option B is wrong because merging all into one table leads to a wide table with redundancy.

Option C is wrong because creating separate models for visits and procedures prevents combined analysis. Option D is wrong because using DirectQuery for all tables can cause performance issues.

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

Option C is correct because reducing the cardinality of the Customer dimension by removing unnecessary columns (e.g., address details) can significantly reduce model size and improve performance. Option A is wrong because disabling auto date/time might help but is a minor improvement. Option B is wrong because aggregations can help but require additional setup and are not the first step.

Option D is wrong because changing to dual storage mode may not improve performance and could increase complexity.

Practice this question →
50
MCQeasy

You are modeling data for a sales analysis. You have a fact table 'Sales' and a dimension table 'Date'. You need to create a relationship between 'Sales[OrderDate]' and 'Date[Date]'. The 'Date' table contains dates from January 1, 2020 to December 31, 2025. The 'Sales' table contains orders from January 1, 2021 to June 30, 2024. Which relationship cardinality should you use?

A.Many-to-many (*:*)
B.One-to-many (1:*)
C.Many-to-one (*:1)
D.One-to-one (1:1)
AnswerC

Many sales rows for one date, and the Date table has unique dates.

Why this answer

The correct cardinality is Many-to-one (*:1) because the 'Sales' table contains many rows for each date (multiple orders can occur on the same date), while the 'Date' table has unique dates. This creates a classic star-schema relationship where the many side (Sales) points to the one side (Date). In Power BI, the cardinality is defined from the perspective of the filter propagation direction, so *:1 means many rows in Sales match one row in Date.

Exam trap

The trap here is that candidates often confuse the direction of the cardinality notation (1:* vs *:1) and incorrectly select One-to-many (1:*) because they think the 'one' side should be the dimension table, but Power BI's cardinality is defined from the filter context perspective, not the table roles.

How to eliminate wrong answers

Option A is wrong because Many-to-many (*:*) would imply that multiple rows in Sales can match multiple rows in Date and vice versa, which is not the case here since Date has unique dates and Sales has one OrderDate per row. Option B is wrong because One-to-many (1:*) would require the 'one' side to be Sales and the 'many' side to be Date, but Sales has many orders per date while Date has unique dates, so the cardinality must be reversed. Option D is wrong because One-to-one (1:1) would require each order date in Sales to correspond to exactly one unique date in Date and each date in Date to correspond to exactly one order in Sales, which is false since multiple orders can occur on the same date.

Practice this question →
51
Drag & Dropmedium

Drag and drop the steps to configure Row-Level Security (RLS) 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

RLS is configured in Power BI Desktop by creating roles with DAX filters, then tested locally before publishing and assigning users in the service.

Practice this question →
52
MCQeasy

You have a table with a column 'Date' and a measure 'Total Sales'. You want to calculate the cumulative total of sales over time. Which DAX function should you use?

A.SUM(Sales[Amount])
B.DATESYTD('Date'[Date])
C.CALCULATE(SUM(Sales[Amount]), ALL(Sales))
D.TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
AnswerD

Calculates year-to-date cumulative sum.

Why this answer

Option C is correct because TOTALYTD calculates year-to-date cumulative totals. Option A is wrong because SUM adds all values, not cumulative. Option B is wrong because CALCULATE modifies filter context but does not inherently provide cumulative.

Option D is wrong because DATESYTD returns a set of dates, not a cumulative sum.

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

You are building a Power BI report for a multinational corporation. The data model includes a fact table named Orders with columns: OrderID, CustomerID, OrderDate, ProductID, Quantity, UnitPrice, and Discount. The Customer dimension contains columns: CustomerID, CustomerName, Country, and Segment. The Product dimension contains: ProductID, ProductName, Category, Subcategory, and Price. You need to create a calculated column in the Orders table that calculates the net amount after discount for each order line (Quantity * UnitPrice * (1 - Discount)). You also need to ensure that the column is stored in the model for high-performance filtering. Which DAX expression should you use?

A.NetAmount = SUMX(Orders, Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[Discount]))
B.NetAmount = Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[Discount])
C.NetAmount = CALCULATE(SUM(Orders[Quantity]) * SUM(Orders[UnitPrice]) * (1 - SUM(Orders[Discount])))
D.NetAmount = Orders[Quantity] * Orders[UnitPrice] - Orders[Discount]
AnswerB

This is a valid calculated column expression that multiplies columns and creates a new column.

Why this answer

Option A uses the correct syntax and creates a calculated column that will be materialized in the model. Option B is a measure, not a calculated column. Option C is a measure.

Option D uses SUMX, which is for measures and would not create a stored column.

Practice this question →
55
MCQmedium

Refer to the exhibit. You have a Power BI semantic model with a role 'Sales Manager'. The role is defined with read permission on the Sales table but only on the Amount column. When the user 'salesmgr@contoso.com' creates a report using this model, which of the following will they be able to see?

A.All columns in all tables in the model.
B.No data because the role is not assigned to the user.
C.Only the Amount column from the Sales table.
D.All columns in the Sales table.
AnswerC

The columnPermission explicitly allows only Amount.

Why this answer

Option A is correct because the role only grants read access to the Amount column. All other columns in the Sales table are not accessible. Option B is wrong because the role does not allow seeing the entire table.

Option C is wrong because the role does not include any other tables. Option D is wrong because the role does not block all data.

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

This enables time intelligence functions and ensures proper filtering.

Why this answer

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

You are building a Power BI semantic model that combines sales data from an on-premises SQL Server database and a cloud-based CRM system. The CRM system returns data in a de-normalized format with multiple fact tables. You need to design the model to minimize data duplication and improve query performance. What should you do?

A.Export the CRM data to Excel and import that Excel file along with the SQL Server data.
B.Create a SQL Server view that joins both sources and import the view.
C.Use DirectQuery for both sources to keep data in their original databases.
D.Import both sources into Power BI and create relationships.
AnswerD

Import mode stores data in-memory for fast queries and allows relationship creation.

Why this answer

Option B is correct because importing both sources into Power BI and creating relationships between tables allows the Power BI engine to optimize queries in-memory. Option A is wrong because DirectQuery would incur performance overhead with on-premises data. Option C is wrong because combining at the source doesn't reduce duplication and may be complex.

Option D is wrong because exporting the CRM data to a single Excel file introduces manual steps and data staleness.

Practice this question →
58
MCQhard

You are creating a star schema in Power BI. You have a fact table named Sales with columns: Date, ProductID, CustomerID, Quantity, and UnitPrice. You need to create a relationship between Sales and a Date dimension. Which column in the Date dimension should be used as the key to relate to the Date column in Sales?

A.DateID
B.Date
C.FullDate
D.DateKey
AnswerD

DateKey is the standard unique identifier for a date dimension, matching the granularity of the Sales date column without time.

Why this answer

The correct answer is A: DateKey. In a star schema, the dimension table should have a unique key that matches the fact table's foreign key. The DateKey column is typically an integer or date without time, ensuring a one-to-many relationship.

Option B (FullDate) might include time, causing granularity mismatch. Option C (Date) could work if unique, but DateKey is standard. Option D (DateID) is also plausible but less common; DateKey is the best practice.

Practice this question →
59
MCQhard

You are designing a Power BI semantic model for a retail company. The model includes a Sales table (50 million rows) and a Product table (10,000 rows). You need to create a measure that calculates the average sales amount per product category. The Product table has a column 'Category' with 20 distinct values. To optimize performance, what should you do?

A.Add a calculated column to Sales that looks up the category using RELATED.
B.Summarize the Sales table to the category level in Power Query.
C.Create a separate calculated table for categories and link it to Sales.
D.Create a relationship between Sales and Product on ProductID, and use Product[Category] in the measure.
AnswerD

This leverages the star schema and efficient query plans.

Why this answer

Option A is correct because marking the Product table as a dimension table with 'Category' as a column allows for efficient aggregations. Option B is wrong because a calculated column in Sales would increase model size. Option C is wrong because a calculated category table would be redundant.

Option D is wrong because summarizing the Sales table loses granularity.

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

This correctly uses CALCULATE with a filter that uses DATEDIFF to compare each row's dates.

Why this answer

Option A is correct because it uses CALCULATE to modify the filter context, applying a filter condition that uses DATEDIFF to compute the number of days between OrderDate and ShipDate. The condition `<= 7` correctly identifies orders shipped within 7 days, and SUM aggregates the Revenue column under that filtered context.

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 →
61
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 standard requirement for time intelligence.

Why this answer

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

When creating a many-to-many relationship between two tables, what is a common approach to model this in Power BI?

A.Use the CROSSJOIN DAX function in calculated tables.
B.Merge both tables into a single table.
C.Introduce a bridge table that contains the unique combinations.
D.Create a direct many-to-many relationship in the model.
AnswerC

Bridge table creates two one-to-many relationships.

Why this answer

Option B is correct because a bridge table resolves many-to-many relationships by creating two one-to-many relationships. Option A is wrong because a single many-to-many relationship without a bridge table is not supported directly. Option C is wrong because CROSSJOIN creates a Cartesian product, which is not correct.

Option D is wrong because merging tables into one removes the relationship structure.

Practice this question →
63
Multi-Selecthard

Which THREE of the following are valid ways to model a many-to-many relationship between tables in Power BI? (Select three.)

Select 3 answers
A.Use the LOOKUPVALUE function in a measure.
B.Add a calculated column to one table to create a unique key.
C.Create two one-to-many relationships and use USERELATIONSHIP in measures.
D.Use a bridge table with a many-to-many cardinality.
E.Set the relationship cross filter direction to Both with Many-to-Many cardinality.
AnswersC, D, E

USERELATIONSHIP activates an inactive relationship for specific calculations.

Why this answer

Options A, B, and D are valid techniques. Using a bridge table (A) is common. Using CROSSFILTER with Many-to-Many cardinality (B) is a built-in feature.

Using the USERELATIONSHIP function (D) can handle multiple relationships. Option C is incorrect because calculated columns do not create relationships. Option E is incorrect because lookup functions are for one-to-many.

Practice this question →
64
Multi-Selecteasy

Which TWO of the following are valid DAX functions for modifying filter context within a measure? (Select two.)

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

FILTER returns a table that can be used as a filter in CALCULATE.

Why this answer

Options A and D are correct. CALCULATE is the primary function to modify filter context (A). FILTER is used as a table function inside CALCULATE (D).

Option B is incorrect because SUM is an aggregation function, not filter modification. Option C is incorrect because ALL is a table function used to remove filters, but it is not a function that modifies filter context by itself; it is used inside CALCULATE. However, ALL is often used to clear filters, so it could be considered a filter modifier.

The question expects CALCULATE and FILTER as direct answers.

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

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

You have the above calculated column in a Power BI model. Some rows show blank values for Profit Margin even though Profit and SalesAmount are not blank. What is the most likely cause?

A.The calculated column syntax is incorrect
B.The columns are not numeric
C.The DIVIDE function cannot handle large numbers
D.SalesAmount is zero for those rows
AnswerD

DIVIDE returns blank when denominator is zero.

Why this answer

Option A is correct because DIVIDE returns blank if the denominator is zero or blank. If SalesAmount is zero, the division is undefined. Option B is wrong because DIVIDE handles division by zero gracefully.

Option C is wrong because data types are not an issue. Option D is wrong because the syntax is correct.

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

Aggregations improve query performance by reducing data volume.

Why this answer

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

You are reviewing a Power BI model definition with incremental refresh policy. The Sales table has an incremental refresh policy that refreshes the last 365 days. However, the report shows data for dates beyond 365 days ago. What is the most likely reason?

A.The incremental refresh policy is applied at the partition level instead of the table level
B.Incremental refresh only refreshes the data, it does not remove historical data
C.The incrementalPeriodsOffset should be set to a negative value to include historical data
D.The refresh policy automatically removes data older than the incremental period
AnswerB

Historical data persists unless the policy includes filtering or the data source is filtered.

Why this answer

Option B is correct because incremental refresh policy only affects refresh operations; it does not delete historical data from the model. Data older than 365 days remains in the model unless explicitly removed. Option A is wrong because the policy is defined for the table, not partitions.

Option C is wrong because the policy is set to refresh the last 365 days, not filter. Option D is wrong because incremental refresh does not automatically remove old data; it only refreshes recent data.

Practice this question →
69
MCQmedium

You need to create a calculated column that categorizes products based on price: 'Low' (<$10), 'Medium' ($10-$50), 'High' (>$50). Which DAX expression should you use?

A.SWITCH(Product[Price], <10, "Low", <=50, "Medium", "High")
B.LOOKUPVALUE(Category, Product[Price], Product[Price])
C.SWITCH(TRUE(), Product[Price] < 10, "Low", Product[Price] <= 50, "Medium", "High")
D.IF(Product[Price] < 10, "Low", Product[Price] <= 50, "Medium", "High")
AnswerC

Correct use of SWITCH with TRUE() for multiple conditions.

Why this answer

Option A is correct because SWITCH evaluates conditions in order and returns the first match, which is appropriate for this scenario. Option B is wrong because IF cannot handle three categories efficiently. Option C is wrong because the syntax is incorrect (no condition after SWITCH).

Option D is wrong because LOOKUPVALUE is for looking up values in another table, not for conditional logic.

Practice this question →
70
Multi-Selecteasy

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

Select 2 answers
A.TransactionDetails
B.Product
C.Date
D.InventoryTransactions
E.Sales
AnswersB, C

Product is a dimension table.

Why this answer

Option B is correct: Date table is a classic dimension. Option D is correct: Product table is a dimension. Option A is wrong because Sales is a fact table.

Option C is wrong because TransactionDetails is typically a fact table. Option E is wrong because InventoryTransactions is a fact table.

Practice this question →
71
MCQeasy

You have a Power BI model with a table 'Employees' that includes columns: 'EmployeeID', 'ManagerID', 'Name'. You need to create a hierarchy that shows the reporting structure. What should you do first?

A.Create a self-join relationship from Employees[EmployeeID] to Employees[ManagerID].
B.Merge the Employees table with itself using Merge Queries.
C.Add a calculated column that concatenates EmployeeID and ManagerID.
D.Create a many-to-many relationship between Employees and itself.
AnswerA

Self-join enables parent-child hierarchy.

Why this answer

To create a hierarchy that shows the reporting structure in Power BI, you first need to establish a self-join relationship between the Employees table and itself, linking EmployeeID to ManagerID. This allows Power BI to traverse the parent-child relationship and build a natural hierarchy (e.g., using Parent-Child functions like PATH). Without this relationship, the model cannot understand how employees relate to their managers.

Exam trap

The trap here is that candidates often confuse a self-join relationship (which is a logical model relationship) with a Merge Queries operation (which physically combines tables), leading them to choose Option B instead of A.

How to eliminate wrong answers

Option B is wrong because merging the Employees table with itself using Merge Queries would create a flat, denormalized table, which breaks the recursive nature needed for a hierarchy and is not the standard approach for parent-child structures in Power BI. Option C is wrong because concatenating EmployeeID and ManagerID into a single column does not create a relationship or hierarchy; it merely creates a string that cannot be used for traversal. Option D is wrong because a many-to-many relationship between Employees and itself would imply multiple managers per employee or multiple employees per manager, which is not the typical reporting structure and would violate the intended one-to-many parent-child relationship.

Practice this question →
72
Multi-Selecteasy

Which TWO of the following are true about calculated columns in Power BI?

Select 2 answers
A.Calculated columns cannot be used in relationships.
B.Calculated columns are evaluated at query time.
C.Calculated columns are computed using DAX formulas.
D.Calculated columns consume memory because they are stored as part of the model.
E.Calculated columns are stored on disk and loaded on demand.
AnswersC, D

DAX is used for calculated columns.

Why this answer

Option C is correct because calculated columns in Power BI are defined using Data Analysis Expressions (DAX), a formula language specifically designed for data modeling and analysis. These DAX formulas are evaluated row by row during the data refresh process, not at query time, and the results are physically stored in the model.

Exam trap

The trap here is that candidates often confuse calculated columns with measures, mistakenly thinking calculated columns are evaluated at query time (Option B) or that they are not stored in memory (Option E), when in fact calculated columns are materialized during refresh and consume RAM.

Practice this question →
73
MCQeasy

You have a Power BI data model with a table 'Orders' that has columns: 'OrderID', 'OrderDate', 'CustomerName', 'Region', 'Product', 'Quantity', 'UnitPrice'. You want to create a measure that calculates total sales amount. Which DAX expression should you use?

A.Total Sales = COUNTROWS(Orders)
B.Total Sales = AVERAGE(Orders[Quantity]) * AVERAGE(Orders[UnitPrice])
C.Total Sales = SUM(Orders[Quantity] * Orders[UnitPrice])
D.Total Sales = SUMX(Orders, Orders[Quantity] * Orders[UnitPrice])
AnswerD

SUMX iterates over rows, performing a row-by-row calculation before summing.

Why this answer

Option A is correct. The SUMX function iterates over each row and multiplies Quantity by UnitPrice, then sums the result. Option B is incorrect because SUM only works on a single column.

Option C is incorrect because COUNTROWS counts rows, not amounts. Option D is incorrect because AVERAGE calculates average, not total.

Practice this question →
74
Multi-Selecthard

You have a Power BI data model with a table named Employees that contains: EmployeeID, ManagerID, Department, and Salary. You need to create a parent-child hierarchy for the reporting structure. Which THREE actions should you perform?

Select 3 answers
A.Enable the 'Group on' feature in the visual to expand/collapse
B.Create calculated columns for the hierarchy path (e.g., PATH, PATHITEM)
C.Create a relationship between Employees[EmployeeID] and Employees[ManagerID]
D.Create a separate table for managers
E.Set cross filter direction to Both on the relationship
AnswersA, B, C

The hierarchy can be used in visuals with expand/collapse functionality.

Why this answer

Option A is correct because enabling the 'Group on' feature in a visual (such as a matrix or table) allows users to expand and collapse parent-child hierarchies, making the reporting structure interactive. This feature works with the hierarchy path created by calculated columns (Option B) and the self-referencing relationship (Option C) to display the drill-down behavior.

Exam trap

The trap here is that candidates often think a separate manager table is required (Option D) or that bidirectional filtering is needed (Option E), but Power BI handles parent-child hierarchies entirely within the same table using a self-referencing relationship and calculated columns.

Practice this question →
75
MCQeasy

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

A.Create two date tables: one for OrderDate (active) and one for ShipDate (inactive), using USERELATIONSHIP as needed
B.Put the date keys in a separate table and relate to both
C.Create a single date table and merge both date columns into one
D.Use only one date table and create separate measures for each date
AnswerA

Allows analysis from both perspectives.

Why this answer

Option A is correct: Use two date dimensions with active/inactive relationships to support both analyses. Option B is wrong because merging dates loses distinction. Option C is wrong because a single date table can only be used for one role.

Option D is wrong because the fact table should not contain date dimension attributes.

Practice this question →
Page 1 of 4 · 268 questions totalNext →

Ready to test yourself?

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

Start 20-question session