Microsoft Fabric Analytics Engineer Associate

Question 1

Your organization requires that all data assets containing personally identifiable information (PII) carry a 'Highly Confidential' classification that automatically flows to any downstream artifacts and enforces encryption rights when data is exported to Excel or PDF. A data analyst has built a semantic model on top of a lakehouse table containing customer PII. Which action should you take to meet the requirement with the least ongoing manual effort?

Accepted Answer

Apply a Microsoft Purview Information Protection sensitivity label named 'Highly Confidential' to the semantic model so that it propagates to downstream reports and enforces the label's protection settings on exports.

Answer

Enable row-level security (RLS) roles on the semantic model to restrict which users can view the PII columns.

Answer

Endorse the semantic model as 'Certified' so consumers know it contains sensitive data.

Answer

Configure object-level security (OLS) to hide the PII columns from all report consumers.

Question 2

An analyst maintains a semantic model with 15 base measures. The business wants each measure to be viewable as Current, Year-to-Date, and Prior Year without creating 45 separate measures. Additionally, the YTD variant should display as currency while a new 'Percent of Total' variant must display as a percentage — all using the same underlying measures. Which combination of features should the analyst implement to meet these requirements with minimal measure duplication?

Accepted Answer

Create a calculation group with calculation items for each time-intelligence variant, and set a dynamic format string expression on the calculation items

Answer

Create 45 explicit measures and use the FORMAT function inside each measure to control display

Answer

Create a field parameter listing all 15 measures and apply a static format string to the visual

Answer

Enable large semantic model storage format and rely on Direct Lake to apply the formatting automatically

Question 3

An analytics engineer has built two calculation groups in a Fabric semantic model: one named 'Time Intelligence' (containing items like YTD, MTD) and another named 'Currency Conversion' (converting measures to EUR and USD). Users report that when they apply a YTD calculation item together with a EUR conversion item on the same visual, the results are inconsistent depending on which group's logic runs first. The engineer must ensure that time intelligence is always evaluated before currency conversion is applied. What should the engineer configure?

Accepted Answer

Set the Precedence property of the 'Time Intelligence' calculation group to a higher value than the 'Currency Conversion' calculation group

Answer

Set the Ordinal property on each calculation item in the 'Time Intelligence' group so YTD sorts first alphabetically

Answer

Merge both calculation groups into a single calculation group so the items evaluate in list order

Answer

Set the Precedence property of the 'Currency Conversion' calculation group to a higher value than the 'Time Intelligence' group

Question 4

A semantic model contains 40 base measures (Sales, Cost, Margin, Units, etc.). The business wants each measure to also be available as Year-to-Date, Prior Year, and Year-over-Year % variants. Rather than authoring 120 additional measures manually, you want a maintainable solution that applies these time-intelligence transformations to any measure the report author selects. Which feature should you implement?

Accepted Answer

Create a calculation group with calculation items for YTD, Prior Year, and YoY % that use SELECTEDMEASURE()

Answer

Create a field parameter that lists all 40 base measures so users can switch between them

Answer

Author 120 explicit measures but organize them into display folders for easier navigation

Answer

Enable large semantic model storage format so the additional measures compute faster

Question 5

A finance team needs a Fabric data store for their reporting layer. The requirements are: full multi-table transactional (ACID) writes using T-SQL INSERT/UPDATE/DELETE statements, no need to author Spark notebooks, and the team's developers are all experienced SQL professionals who prefer a purely T-SQL development experience. Which Fabric item should you recommend?

Accepted Answer

A Fabric Warehouse

Answer

A Lakehouse accessed through its SQL analytics endpoint

Answer

A KQL database in an Eventhouse

Answer

A Power BI datamart built on a Dataflow Gen2

Question 6

You are building a T-SQL view in a Fabric warehouse that calculates total compensation as BaseSalary + Bonus. During testing, several rows return NULL for TotalComp even though BaseSalary has a value, because the Bonus column contains NULLs for employees without bonuses. You must ensure that employees without a bonus show their BaseSalary as the total compensation instead of NULL. Which expression should you use in the view?

Accepted Answer

BaseSalary + COALESCE(Bonus, 0) AS TotalComp

Answer

BaseSalary + ISNULL(BaseSalary, Bonus) AS TotalComp

Answer

BaseSalary + NULLIF(Bonus, 0) AS TotalComp

Answer

COALESCE(BaseSalary + Bonus, Bonus) AS TotalComp

Question 7

You are building a customer dimension in a Fabric warehouse. The source table contains three phone columns: MobilePhone, HomePhone, and WorkPhone. Many rows have NULLs in one or more columns. You must produce a single PrimaryPhone column that returns the first non-null value in the priority order Mobile, then Home, then Work. Which T-SQL expression should you use?

Accepted Answer

COALESCE(MobilePhone, HomePhone, WorkPhone) AS PrimaryPhone

Answer

ISNULL(MobilePhone, HomePhone, WorkPhone) AS PrimaryPhone

Answer

NULLIF(MobilePhone, HomePhone) AS PrimaryPhone

Answer

CONCAT(MobilePhone, HomePhone, WorkPhone) AS PrimaryPhone

Question 8

You manage a Fabric warehouse that contains an Employees table with a Salary column. The HR analytics team must query all columns, but a group of operational reporting users must be able to query the Employees table for everything except the Salary column. You want to enforce this at the warehouse layer using T-SQL so the restriction applies regardless of which client tool connects. What should you implement?

Accepted Answer

Use column-level security by granting the operational group SELECT on all columns except Salary with a GRANT/DENY statement.

Answer

Apply a sensitivity label to the Salary column and rely on label-based protection to block access.

Answer

Create a row-level security policy with a predicate function that filters the Salary values.

Answer

Set the Salary column data type to a masked format using dynamic data masking for the operational group.

Question 9

You manage a composite semantic model that combines a DirectQuery connection to a multi-billion-row fact table in a Fabric warehouse with several imported dimension tables. Users report that high-level dashboard visuals showing yearly and monthly revenue totals are slow because every query hits the warehouse. Detailed drill-through visuals at the transaction level are used rarely but must remain fully accurate. What should you implement to improve the performance of the aggregated visuals while preserving detail-level accuracy?

Accepted Answer

Configure a user-defined aggregation table imported at the month/year grain and map it to the DirectQuery fact table so summary queries hit the cache and fall back to DirectQuery for detail.

Answer

Switch the entire model to Import mode by loading the full fact table into memory to eliminate all DirectQuery round trips.

Answer

Convert the dimension tables to DirectQuery so all tables share a single storage mode and the query engine can fold aggregations.

Answer

Enable automatic page refresh on the dashboard so cached results are served between refresh intervals.

Question 10

You are building a composite semantic model in Power BI Desktop. A large fact table uses DirectQuery against a Fabric warehouse. A shared Date dimension is used both to filter the DirectQuery fact table and to filter a smaller Import-mode fact table for a different report page. You want cross-highlighting and relationship filtering to work efficiently against both fact tables without generating unnecessary queries back to the source. Which storage mode should you set on the Date dimension table?

Accepted Answer

Dual

Answer

Import

Answer

DirectQuery

Answer

Direct Lake

Question 11

Your analytics team maintains several Power BI reports and semantic models in a Fabric workspace. Leadership wants full version history, the ability to review changes through pull requests, and text-based diffs of model and report definitions before changes are merged. You need to recommend how developers should structure and store their work to meet these requirements with the least ongoing manual effort. What should you do?

Accepted Answer

Save the work as Power BI Desktop projects (.pbip) and connect the Fabric workspace to a Git repository so item definitions sync as source files.

Answer

Have developers save reports as .pbix files to a shared network drive and manually upload new versions to the workspace after each change.

Answer

Distribute the semantic models as .pbit template files so each developer can regenerate the model from a fresh copy.

Answer

Use a Fabric deployment pipeline with Development, Test, and Production stages to promote content between environments.

Question 12

You are loading a fact table into a Fabric warehouse. The source column OrderTimestamp arrives as a VARCHAR value like '2024-03-15T09:42:11'. Analysts need a proper DATE column named OrderDate (no time component) so they can join to a date dimension and group by day. Which T-SQL expression correctly produces the OrderDate value?

Accepted Answer

CAST(OrderTimestamp AS DATE)

Answer

CONVERT(VARCHAR(10), OrderTimestamp)

Answer

TRY_CAST(OrderTimestamp AS TIME)

Answer

FORMAT(OrderTimestamp, 'yyyy-MM-dd')

Question 13

You are preparing data in a Fabric warehouse. A staging table named stg_Sales has a column UnitPrice defined as VARCHAR(20) because it was ingested from a CSV file. Some rows contain valid decimal values like '19.99', but a small number contain the literal text 'N/A'. You need to load a clean DECIMAL(10,2) column into the curated fact table and ensure the load does not fail when it encounters 'N/A' values, converting those to NULL instead. Which T-SQL expression should you use in the SELECT that populates the fact table?

Accepted Answer

TRY_CAST(UnitPrice AS DECIMAL(10,2))

Answer

CAST(UnitPrice AS DECIMAL(10,2))

Answer

CONVERT(DECIMAL(10,2), UnitPrice)

Answer

PARSE(UnitPrice AS DECIMAL(10,2))

Question 14

An analytics engineer is building a daily sales fact table in a Fabric warehouse. The source data only contains rows for dates on which at least one sale occurred, leaving gaps for days with zero sales. The reporting team needs a continuous row for every calendar day in the reporting period, with sales amounts of 0 for days that had no transactions. Which T-SQL approach best produces this continuous daily result?

Accepted Answer

LEFT JOIN a complete calendar date dimension to the sales data on the date key, then wrap the sales amount in ISNULL(SalesAmount, 0).

Answer

INNER JOIN the sales data to the calendar date dimension on the date key so only matching dates are returned.

Answer

Use SELECT DISTINCT on the sales table's date column to remove duplicate dates before aggregating.

Answer

GROUP BY the sales date and use COUNT(*) to identify which dates are missing from the result set.

Question 15

You are building a Dataflow Gen2 that ingests a 200-million-row transaction table from an on-premises SQL Server into a Fabric Lakehouse. The downstream report only needs transactions from the last 90 days. You want to minimize the volume of data transferred from the source system during refresh. In Power Query, which approach ensures the row filter is pushed to the source rather than applied after all data is loaded?

Accepted Answer

Apply a date filter step early in the query so that query folding pushes the WHERE clause to SQL Server

Answer

Load all rows into the Lakehouse first, then create a downstream view that filters to the last 90 days

Answer

Add a Table.Buffer step before the filter to cache the rows and speed up filtering

Answer

Use the Keep Bottom Rows transformation to retain only the most recent records

Question 16

You are preparing a customer dimension in a Dataflow Gen2. The source "Country" column contains inconsistent values such as "USA", "U.S.A.", "United States", and "us" that should all resolve to a single standardized value. You want to consolidate these variants to the canonical value "United States" with minimal manual mapping, tolerating minor spelling differences. Which Power Query transformation should you apply?

Accepted Answer

Use the Cluster values / fuzzy grouping option to group similar text values under a canonical representative

Answer

Use the Replace Values transformation once for each known variant of the country name

Answer

Use the Group By transformation on the Country column with a Count aggregation

Answer

Use the Detect Data Type transformation to convert the Country column to a categorical type

Question 17

A Fabric Analytics Engineer is building a Dataflow Gen2 to load customer data into a lakehouse. The source contains a single column named FullName with values formatted as "LastName, FirstName" (for example, "Smith, John"). The target table requires two separate columns, FirstName and LastName. Which transformation should the engineer apply in the Power Query editor to accomplish this most efficiently?

Accepted Answer

Use Split Column by Delimiter, choosing the comma as the delimiter and splitting into columns

Answer

Use Group By on the FullName column to create the two new columns

Answer

Use Merge Columns to combine FullName with an empty column

Answer

Use Fill Down to populate FirstName and LastName from FullName

Question 18

You are optimizing a DAX query that produces a table of product categories with total sales. A colleague wrote it using SUMMARIZE with the aggregation expression placed directly inside the SUMMARIZE function, and profiling shows slow performance and high CallbackDataID activity in the storage engine. You want to rewrite the query so that the grouping is done efficiently and the aggregation runs in a proper row context without the performance issues of computing extended columns inside SUMMARIZE. Which approach should you use?

Accepted Answer

Use ADDCOLUMNS over a SUMMARIZE that only lists the group-by column, placing the aggregation in ADDCOLUMNS

Answer

Use SUMMARIZECOLUMNS with the group-by column and the sales measure as arguments

Answer

Keep SUMMARIZE but wrap the aggregation expression in CALCULATE to force context transition

Answer

Use GROUPBY with CURRENTGROUP inside a SUMX to aggregate the sales column

Question 19

You are building a semantic model on a Fabric warehouse for a retail analytics team. A Sales fact table is joined to a Date dimension. You must create a measure that returns total sales for the same period one year earlier so the team can compute year-over-year growth in a matrix visual sliced by month. Which DAX expression should you use?

Accepted Answer

CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

Answer

SUM(Sales[Amount]) - SUM(Sales[Amount])

Answer

CALCULATE(SUM(Sales[Amount]), ALL('Date'))

Answer

SUMX(Sales, Sales[Amount] * RELATED('Date'[Year]))

Question 20

You are building a measure in a Power BI semantic model to display each product category's sales as a percentage of the grand total across all categories. The report page contains a matrix visual sliced by Product[Category]. Your base measure [Total Sales] = SUM(Sales[Amount]) works correctly per row. Which DAX expression correctly computes the percent-of-total for each category, regardless of which categories are visible in the current filter context?

Accepted Answer

DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product[Category])))

Answer

DIVIDE([Total Sales], CALCULATE([Total Sales], KEEPFILTERS(Product[Category])))

Answer

DIVIDE([Total Sales], SUMX(Product, [Total Sales]))

Answer

DIVIDE([Total Sales], CALCULATE([Total Sales], VALUES(Product[Category])))

Question 21

You are analyzing a Fabric semantic model using a DAX query in the Fabric portal's DAX query view. You need to return a single-column table containing the distinct list of product category names from the 'Product' table, but only for products where the 'Discontinued' column equals FALSE. Which DAX expression should you use?

Accepted Answer

EVALUATE CALCULATETABLE(VALUES('Product'[Category]), 'Product'[Discontinued] = FALSE)

Answer

EVALUATE FILTER(VALUES('Product'[Category]), 'Product'[Discontinued] = FALSE)

Answer

EVALUATE SUMMARIZE('Product', 'Product'[Discontinued])

Answer

EVALUATE DISTINCT('Product'[Discontinued])

Question 22

You built a calculation group named 'Time Intelligence' with items for MTD, QTD, and YTD in an enterprise semantic model. Business users report that when they apply the 'YTD' calculation item to a non-additive measure such as [Distinct Customers], the result is misleading because a year-to-date accumulation makes no logical sense for that metric. You want the calculation items to apply only to a defined set of additive measures and return the unmodified base measure for all others, without creating separate calculation groups. Which DAX approach inside each calculation item expression best achieves this?

Accepted Answer

Use SELECTEDMEASURENAME() in an IF condition to check whether the current measure is in an allowed list, and return SELECTEDMEASURE() unchanged when it is not, otherwise apply the time-intelligence CALCULATE.

Answer

Set the calculation group Precedence property to a higher value so non-additive measures are automatically excluded.

Answer

Add a dynamic format string to each calculation item so non-additive measures display a blank format.

Answer

Enable the 'Discourage implicit measures' property on the calculation group to block [Distinct Customers] from being affected.

Question 23

You build a calculation group named 'Time Intelligence' in a Fabric semantic model. It contains items like YTD, QTD, and PY. Users report that when they apply the YTD calculation item to a measure formatted as currency (for example, $#,##0), the resulting value loses its currency formatting and appears as a plain number. You want each calculation item to keep the formatting of the base measure it modifies. What should you do?

Accepted Answer

Set the dynamic format string expression for each calculation item to SELECTEDMEASUREFORMATSTRING()

Answer

Increase the calculation group precedence value so it evaluates before the measure's own format string

Answer

Convert the base measures to use a hard-coded FORMAT() function returning a text result

Answer

Disable the dynamic format string on the calculation group and rely on the model's default format

Question 24

You are building an enterprise semantic model with more than 40 base measures. Business users want to view every measure with three optional adjustments applied through a single slicer: 'Actual', 'Currency Converted' (multiply by a stored exchange rate), and 'Rounded to Thousands'. You want to avoid creating 120 duplicate measures. Which feature should you use, and what DAX construct is required inside each calculation item?

Accepted Answer

A calculation group, using SELECTEDMEASURE() inside each calculation item so the item's expression applies to whichever base measure is on the visual

Answer

A field parameter, using SELECTEDVALUE() inside each measure to detect which parameter option the user picked

Answer

A calculation group, using SUMMARIZECOLUMNS() inside each calculation item to re-aggregate the base measure at the visual grain

Answer

A set of role-based RLS filters, using USERNAME() to switch which measure version each user sees

Question 25

An analyst builds a semantic model with a Sales fact table and a Customer dimension. Management wants a measure that counts customers who purchased in the current filter context but excludes any customer whose total sales fell below a $500 threshold. The measure must respect all report slicers (region, year). Which DAX expression correctly returns this count?

Accepted Answer

COUNTROWS(FILTER(VALUES(Customer[CustomerKey]), [Total Sales] >= 500))

Answer

CALCULATE(DISTINCTCOUNT(Customer[CustomerKey]), Sales[Amount] >= 500)

Answer

COUNTROWS(FILTER(ALL(Customer[CustomerKey]), [Total Sales] >= 500))

Answer

SUMX(VALUES(Customer[CustomerKey]), IF([Total Sales] >= 500, [Total Sales], 0))

Question 26

You are building a semantic model with a Sales fact table and a Date dimension marked as the date table. Business users need a measure that returns the rolling 12-month average of total sales, recalculated for whatever month is in filter context. The measure must divide the sum of sales over the trailing 12 months by 12. Which DAX expression correctly implements this?

Accepted Answer

Rolling12MoAvg = CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)) / 12

Answer

Rolling12MoAvg = AVERAGEX('Date', [Total Sales])

Answer

Rolling12MoAvg = CALCULATE([Total Sales], ALL('Date')) / 12

Answer

Rolling12MoAvg = CALCULATE([Total Sales], DATESYTD('Date'[Date])) / 12

Question 27

An analyst needs a measure that calculates the total profit margin across all order lines in a fact table. Each row has a Quantity, UnitPrice, and UnitCost column, but there is no pre-calculated line-level profit column in the model. The measure must compute profit per row (Quantity * (UnitPrice - UnitCost)) and then sum those row-level results correctly under any filter context. Which DAX approach produces the correct result?

Accepted Answer

SUMX(FactOrderLines, FactOrderLines[Quantity] * (FactOrderLines[UnitPrice] - FactOrderLines[UnitCost]))

Answer

SUM(FactOrderLines[Quantity]) * (SUM(FactOrderLines[UnitPrice]) - SUM(FactOrderLines[UnitCost]))

Answer

CALCULATE(SUM(FactOrderLines[Quantity]) * (FactOrderLines[UnitPrice] - FactOrderLines[UnitCost]))

Answer

AVERAGEX(FactOrderLines, FactOrderLines[UnitPrice] - FactOrderLines[UnitCost])

Question 28

An analyst builds a measure to count high-value orders (amount greater than 1000). When they place this measure in a matrix sliced by product category and also apply an external filter selecting only the 'Electronics' category, they notice that a standard CALCULATE with a boolean filter on order amount unexpectedly overrides part of the existing category filter behavior in a comparison measure. They want the amount condition applied as an ADDITIONAL constraint that intersects with — rather than replaces — any filters already coming from the same column context. Which DAX function should wrap the filter argument to preserve the existing filter context and intersect the new condition?

Accepted Answer

KEEPFILTERS

Answer

ALL

Answer

REMOVEFILTERS

Answer

ALLEXCEPT

Question 29

You are building a semantic model for a subscription business. A Customers table and a Products table have a many-to-many association because a single customer can subscribe to multiple products, and each product has many subscribers. You have a Subscriptions table that contains one row for every valid customer-product pairing. You need a measure that correctly counts distinct customers per product without inflating or losing rows, following recommended star-schema modeling practices. What should you do?

Accepted Answer

Use the Subscriptions table as a bridge table, create single-direction one-to-many relationships from Customers and Products into it, and enable cross-filtering as needed for the count measure.

Answer

Create a direct many-to-many relationship between Customers and Products and rely on Power BI to expand the cardinality automatically.

Answer

Merge Customers and Products into one flattened dimension table so that no relationship is required.

Answer

Create a calculated column in Products using RELATED to pull each customer name, then count the column.

Question 30

You are building a semantic model for a retail company. A report page must display each product's rank by total sales within its own product category, so the top-selling product in each category shows rank 1, regardless of how the visual is currently filtered by category. You create a measure called [Total Sales]. Which DAX measure correctly produces the per-category ranking?

Accepted Answer

Product Rank = RANKX(ALLEXCEPT('Product', 'Product'[Category]), [Total Sales], , DESC, DENSE)

Answer

Product Rank = RANKX(ALL('Product'[Product Name]), [Total Sales], , DESC, DENSE)

Answer

Product Rank = RANKX(ALLSELECTED('Product'), [Total Sales], , DESC)

Answer

Product Rank = RANKX(VALUES('Product'[Product Name]), [Total Sales], , ASC, DENSE)

Question 31

You are building a calculated column on the Sales fact table in a Fabric semantic model. Each row in Sales has a ProductKey that links to a Product dimension table via a single active one-to-many relationship (Product → Sales). You need the calculated column to return the CategoryName from the Product table for each sales row, so you can later group sales without physically merging the tables during transformation. Which DAX function should you use in the calculated column?

Accepted Answer

RELATED(Product[CategoryName])

Answer

RELATEDTABLE(Product)

Answer

LOOKUPVALUE(Product[CategoryName], Product[CategoryName], Sales[CategoryName])

Answer

USERELATIONSHIP(Sales[ProductKey], Product[ProductKey])

Question 32

You are an analytics engineer working in a Fabric semantic model. Business analysts need an ad hoc result set that returns total sales amount grouped by product category and calendar year, but only for rows where the sales amount is greater than zero. You want to write a single DAX query in the DAX query view of the model that produces this grouped, filtered aggregation efficiently. Which DAX function should form the basis of your query?

Accepted Answer

SUMMARIZECOLUMNS, using the category and year columns as groupings with a filter expression and a named measure for total sales

Answer

CALCULATETABLE wrapped around the entire fact table to pre-filter every row before display

Answer

ADDCOLUMNS over the full fact table to append a running total for each transaction

Answer

TOPN to return the highest-value sales rows without any grouping

Question 33

You are building a semantic model where users select a metric type from a disconnected slicer table named MetricSelector, which contains a single column [Metric] with three values: 'Revenue', 'Units', and 'Margin %'. You need a single measure named [Dynamic KPI] that returns the appropriate underlying measure based on the user's selection, and returns BLANK when no single value is selected. Which DAX expression should you use?

Accepted Answer

Dynamic KPI = SWITCH ( SELECTEDVALUE ( MetricSelector[Metric] ), "Revenue", [Total Revenue], "Units", [Total Units], "Margin %", [Margin Percent] )

Answer

Dynamic KPI = SWITCH ( VALUES ( MetricSelector[Metric] ), "Revenue", [Total Revenue], "Units", [Total Units], "Margin %", [Margin Percent] )

Answer

Dynamic KPI = IF ( HASONEVALUE ( MetricSelector[Metric] ), [Total Revenue] + [Total Units] + [Margin Percent] )

Answer

Dynamic KPI = CALCULATE ( [Total Revenue], MetricSelector[Metric] = SELECTEDVALUE ( MetricSelector[Metric] ) )

Question 34

An analyst is building a measure for a Power BI report on a Fabric semantic model. The requirement is: return the total sales contributed only by the top 5 products (by their own total sales) within the current filter context, and the measure must respond correctly when a category or year slicer is applied. Which DAX pattern produces the correct result?

Accepted Answer

CALCULATE(SUMX(TOPN(5, VALUES(Product[ProductKey]), [Total Sales], DESC), [Total Sales]))

Answer

CALCULATE([Total Sales], TOPN(5, ALL(Product), [Total Sales], DESC))

Answer

SUMX(TOPN(5, ALL(Product[ProductKey]), [Total Sales], DESC), [Total Sales])

Answer

TOPN(5, [Total Sales], Product[ProductKey])

Question 35

You are building a DAX query in a Fabric semantic model. The 'Budget' table stores a 'RegionName' column but has no physical relationship to the 'Sales' table, which also contains a 'RegionName' column. You need to write a measure that returns the sum of Sales amounts filtered by the RegionName values currently present in the Budget table's filter context, without creating a physical relationship. Which DAX function should you use to propagate the filter?

Accepted Answer

Use TREATAS to apply the Budget RegionName values as a filter onto the Sales RegionName column.

Answer

Use RELATED to pull the RegionName from the Budget table into the Sales table.

Answer

Use USERELATIONSHIP to activate an inactive relationship between Budget and Sales.

Answer

Use CROSSFILTER to change the filter direction between the two tables.

Question 36

An analytics engineer is optimizing a slow DAX measure in an enterprise semantic model. The measure computes a ratio and references the same base expression [Total Sales] four separate times: once in the numerator, once in the denominator, and twice inside conditional logic that returns BLANK when sales are zero or negative. Profiler traces show the storage engine re-scanning the fact table multiple times per visual. Which change will most reduce redundant evaluation while preserving the result?

Accepted Answer

Store [Total Sales] once in a VAR at the start of the measure and reference that variable in the numerator, denominator, and conditional checks

Answer

Wrap each reference to [Total Sales] in a separate CALCULATE with ALL() to force a single filter context

Answer

Convert the measure into a calculated column so the ratio is computed once at refresh time

Answer

Replace [Total Sales] references with SUMX iterating over the fact table in each location

Question 37

You are building a semantic model in Fabric with a fact table Sales and a marked Date table. Business users want a measure that compares the current visual's quarter total against the total from three quarters earlier, regardless of how the visual is sorted or which quarters are visible. You want to use the DAX window functions introduced for this purpose rather than classic time-intelligence functions. Which DAX function is the most appropriate core of this measure?

Accepted Answer

OFFSET, used within CALCULATE over a query-defined ordering to shift three rows back in the visual's row context

Answer

DATEADD, shifting the current date filter back by three quarters

Answer

EARLIER, referencing the outer row context of the quarter column

Answer

SAMEPERIODLASTYEAR, wrapped in CALCULATE to align quarters

Question 38

You are building a dimensional model in a Fabric warehouse. The source contains three normalized tables: Product, ProductSubcategory, and ProductCategory, linked by foreign keys in a snowflake pattern. Your Power BI report authors complain that navigating three related tables makes measures and filters confusing. You want to optimize the model for query performance and report usability while following star schema best practices. What should you do?

Accepted Answer

Denormalize the three tables into a single DimProduct dimension by joining them, so category and subcategory attributes are columns in one flat table.

Answer

Keep the three tables separate and create relationships between them so the snowflake structure is preserved in the semantic model.

Answer

Create a view that unions the three tables into a single result set containing all rows from each table.

Answer

Move ProductCategory and ProductSubcategory into the fact table as additional columns while leaving Product as a separate dimension.

Question 39

You maintain a large enterprise semantic model in a Fabric Premium workspace. A colleague made structural changes (new calculated columns and measures) directly in a development workspace model using an external tool. You now need to promote ONLY those metadata differences to the production model without overwriting production-only partitions or triggering a full data reload. Which approach best accomplishes this?

Accepted Answer

Use the ALM Toolkit connected to both models via the XMLA endpoint to compare schemas and selectively deploy only the changed metadata objects.

Answer

Download the development model as a .pbix from the service and re-publish it over the production model.

Answer

Create a deployment pipeline and click Deploy, which always copies only metadata and never affects data.

Answer

Export the development model as a .pbit template and import it into the production workspace.

Question 40

Your team has a Fabric workspace named 'Sales-Dev' that contains a lakehouse, a semantic model, and several reports. Leadership wants a formal promotion process across Development, Test, and Production environments using a Fabric deployment pipeline. You create a new deployment pipeline with three stages. What must you do next to begin promoting content through the pipeline?

Accepted Answer

Assign the 'Sales-Dev' workspace to the Development stage of the pipeline.

Answer

Export the semantic model as a .pbit template and import it into each stage.

Answer

Connect the 'Sales-Dev' workspace to a Git repository and commit all items.

Answer

Create three separate capacities, one for each deployment pipeline stage.

Question 41

Your team uses a three-stage Fabric deployment pipeline (Development, Test, Production). A semantic model in Development connects to a warehouse. When content is deployed from Test to Production, you have configured a data source rule on the Production stage that repoints the connection to the production warehouse. However, you also rely on Fabric's automatic re-binding, which connects deployed items to items already present in the target stage. During a deployment to Production, both the data source rule and auto-binding could apply to the same connection. What determines which connection the deployed semantic model uses in Production?

Accepted Answer

Deployment rules take precedence over automatic binding when a rule is defined for that connection.

Answer

Auto-binding always wins because it runs after deployment rules are evaluated.

Answer

The connection defined in the Development stage is preserved and overrides both mechanisms.

Answer

Fabric prompts the deploying user to manually choose the connection each time.

Question 42

Your team uses a Fabric deployment pipeline with Development, Test, and Production stages. A developer has updated three reports and one semantic model in the Development workspace, but only the semantic model change has been validated by QA. Before promoting to the Test stage, the lead analytics engineer wants to confirm exactly which items differ between Development and Test and then move only the approved semantic model. What should the engineer do?

Accepted Answer

Use the deployment pipeline compare view to identify changed items, then perform a selective deployment of only the semantic model to the Test stage.

Answer

Deploy all items from Development to Test, then manually delete the three unapproved reports from the Test workspace afterward.

Answer

Run an impact analysis on the semantic model in Development, which will automatically promote only that item to Test.

Answer

Assign the semantic model a Certified endorsement so the pipeline only deploys endorsed items to the next stage.

Question 43

Your team manages a Fabric deployment pipeline with Development, Test, and Production stages. During the last sprint, an analyst modified only one semantic model in the Development stage; all reports, lakehouses, and other semantic models remain identical to what is already in Test. You want to move just the updated semantic model into Test while minimizing the risk of accidentally overwriting unrelated Test items. Which action should you take in the deployment pipeline before deploying?

Accepted Answer

Use the compare view to identify items that differ between Development and Test, then select only the changed semantic model for a selective deployment.

Answer

Delete the Test stage workspace and redeploy everything from Development so the stages are guaranteed to match.

Answer

Deploy the entire Development stage to Test, relying on Fabric to skip items that have not changed.

Answer

Detach the deployment pipeline from Git and manually copy the semantic model .pbip files into the Test workspace.

Question 44

Your team maintains a Fabric workspace containing a semantic model and several reports. The team wants developers to work in isolated feature branches with pull-request reviews before merging, while release managers separately promote validated content from a Development workspace through Test to a Production workspace without touching source files. Which combination of Fabric lifecycle tools should you configure to meet both requirements?

Accepted Answer

Use Git integration for the Development workspace to enable branching and pull requests, and use a deployment pipeline to promote content from Development through Test to Production.

Answer

Use a deployment pipeline for all environments and rely on its built-in branch creation feature for developer isolation and pull-request reviews.

Answer

Use Git integration on all three workspaces (Development, Test, and Production) and merge branches directly into each workspace instead of using a deployment pipeline.

Answer

Export .pbip files to a Git repository for review, then manually import the reviewed files into the Test and Production workspaces for each release.

Question 45

You manage a Fabric workspace containing a shared semantic model that is used by 12 published reports across three separate workspaces. Before you deploy a schema change that renames several measures, your governance team requires you to identify every downstream report that could break, without opening each report manually. Which Fabric feature should you use to obtain this information?

Accepted Answer

The lineage view combined with the semantic model's impact analysis pane in the Fabric portal

Answer

The deployment pipeline compare view between the Development and Test stages

Answer

The XMLA endpoint connected through an external tool to script the model metadata

Answer

The Git integration commit history for the workspace

Question 46

Your team uses a Fabric deployment pipeline with Development, Test, and Production stages. A semantic model in the pipeline connects to a lakehouse. When you deploy from Development to Test, the semantic model in Test must automatically point to the Test workspace's lakehouse instead of the Development lakehouse, without manually editing the model after each deployment. What should you configure?

Accepted Answer

A deployment rule on the Test stage that maps the data source to the Test lakehouse connection

Answer

A sensitivity label on the semantic model so it inherits the correct connection per stage

Answer

An incremental refresh policy on the semantic model tables

Answer

A shared semantic model published to the Test workspace before deploying

Question 47

Your organization uses a Microsoft Fabric deployment pipeline with Development, Test, and Production stages. A junior analyst, Priya, has been assigned the Contributor role on the Development workspace and the Member role on the Test workspace. She is not assigned any role in Production and is not listed as a user of the deployment pipeline itself. When Priya opens the deployment pipeline, she reports that she cannot deploy content from Development to Test. What is the most likely reason she cannot perform the deployment?

Accepted Answer

She must be granted access to the deployment pipeline (added as a pipeline user) in addition to having appropriate workspace roles.

Answer

Contributor on the source workspace is insufficient; she must be an Admin on both the Development and Test workspaces to deploy between them.

Answer

Deployment across stages requires that she also hold at least a Viewer role in the Production workspace, since pipelines validate all stages.

Answer

The XMLA endpoint must be enabled in read/write mode for her to move content between deployment pipeline stages.

Question 48

Your team uses a Fabric deployment pipeline with Development, Test, and Production stages. A semantic model connects to a lakehouse SQL analytics endpoint. When you deploy from Test to Production, the semantic model must automatically point to the Production lakehouse instead of the Test one, without anyone manually editing the model after each deployment. What should you configure?

Accepted Answer

A deployment rule on the Production stage that sets the semantic model's data source connection to the Production endpoint

Answer

A sensitivity label on the semantic model so it inherits the correct connection per stage

Answer

An impact analysis report on the Test stage before each deployment

Answer

Git integration on the Production workspace to override the connection string on commit

Question 49

Your team has three developers who each need to work on separate features of the same Power BI project simultaneously without overwriting each other's changes. The workspace is already connected to a Git repository. During development, each developer needs an isolated environment to test their changes before merging them into the shared workspace. Which approach best supports parallel, isolated development in this scenario?

Accepted Answer

Have each developer create a separate feature branch in Git and use isolated developer workspaces synced to their branch, then merge back to the main branch via pull requests

Answer

Have all three developers commit directly to the main branch of the shared workspace and resolve conflicts as they occur

Answer

Create a deployment pipeline with Development, Test, and Production stages and assign one stage to each developer

Answer

Disable Git integration and have developers exchange .pbip files manually through a shared network folder

Question 50

You maintain a Direct Lake semantic model over a lakehouse that holds a 4-billion-row Sales fact table refreshed hourly. Business users also need to compare against a small, static PriorYearBudget table that is maintained manually in an Excel file and is not stored in the lakehouse. You must keep the large Sales table on Direct Lake for freshness and scale, while still bringing in the budget data without moving it into the lakehouse. Which approach meets these requirements?

Accepted Answer

Add the PriorYearBudget table as an Import table, creating a composite model that keeps Sales in Direct Lake mode.

Answer

Convert the entire model to Import mode and load both Sales and PriorYearBudget from their sources.

Answer

Create a shortcut to the Excel file in the lakehouse and add PriorYearBudget as a Direct Lake table.

Answer

Set the PriorYearBudget table to DirectQuery against the Excel file while leaving Sales in Direct Lake mode.

Question 51

You publish a Direct Lake semantic model over a large Delta table in a Fabric lakehouse. During peak usage, users report that some visuals become noticeably slower and analysts observe that certain queries no longer benefit from in-memory VertiPaq column loading. In Performance Analyzer you notice these queries are being executed as DirectQuery against the SQL analytics endpoint. The model's DirectLakeBehavior property is left at its default value. What is the MOST likely cause of this behavior?

Accepted Answer

The Delta table exceeded a Direct Lake guardrail (such as the maximum rows per table or the row group / memory limit for the capacity SKU), triggering automatic fallback to DirectQuery.

Answer

Incremental refresh policies were applied to the model, which forces all queries to run in DirectQuery mode.

Answer

The semantic model was configured in Import storage mode, so queries are cached and periodically re-executed against the source.

Answer

Object-level security was enabled on the fact table, which disables VertiPaq column encoding and forces DirectQuery.

Question 52

You manage a Direct Lake semantic model on a Fabric F64 capacity. Business stakeholders complain that report performance is inconsistent: some visuals return instantly while others are noticeably slower, especially during peak hours. Investigation shows that when the model exceeds the maximum in-memory row guardrail, queries silently fall back to DirectQuery against the SQL analytics endpoint, causing the slowdowns. Leadership decides they would rather have a query fail with a clear error than silently degrade performance, so they can identify and fix the offending report. Which configuration change on the semantic model achieves this?

Accepted Answer

Set the DirectLakeBehavior property to 'DirectLakeOnly' so queries never fall back to DirectQuery and instead return an error when guardrails are exceeded

Answer

Set the DirectLakeBehavior property to 'DirectQueryOnly' to force all queries through the SQL analytics endpoint

Answer

Enable the Large semantic model storage format to raise the in-memory guardrails and prevent any fallback

Answer

Configure incremental refresh on the fact table to reduce the number of rows loaded into memory

Question 53

You maintain a Direct Lake semantic model over a large lakehouse fact table with 8 billion rows. Business users report that certain high-cardinality slicer visuals frequently trigger DirectQuery fallback because column memory guardrails are exceeded, causing slow performance. Your priority is to keep the model on Direct Lake for the bulk of queries while ensuring a small, frequently filtered dimension is always served from memory with no fallback. Which approach best meets this requirement?

Accepted Answer

Convert the model to a composite model, keeping the large fact table in Direct Lake and importing the small dimension table into Import storage mode.

Answer

Set the model's DirectLakeBehavior to DirectQueryOnly so all queries bypass the in-memory engine consistently.

Answer

Disable the SQL analytics endpoint on the lakehouse so Direct Lake can no longer fall back to DirectQuery.

Answer

Increase the capacity SKU so the guardrails are removed entirely and all columns stay in memory.

Question 54

You are building a Direct Lake semantic model on an F64 capacity over a lakehouse fact table that contains 4.2 billion rows. During validation you observe that queries against this table consistently fall back to DirectQuery, and performance is worse than expected. You confirm the SQL analytics endpoint is healthy and there is no memory eviction occurring. What is the most likely cause of the DirectQuery fallback?

Accepted Answer

The fact table exceeds the maximum row count per table guardrail for Direct Lake on the current capacity SKU, forcing DirectQuery.

Answer

Direct Lake does not support fact tables larger than 1 billion rows under any circumstance and must always use Import mode instead.

Answer

The lakehouse Delta table is missing a primary key, which Direct Lake requires to load columns into VertiPaq.

Answer

Direct Lake automatically reverts to DirectQuery whenever a table participates in a relationship, regardless of size.

Question 55

You maintain a Direct Lake semantic model on a Fabric F64 capacity. Business users report that a specific measure occasionally runs much slower than usual, and Performance Analyzer shows the underlying queries intermittently executing in DirectQuery mode against the SQL analytics endpoint. Investigation reveals that the fact table exceeds the maximum number of rows per table allowed for Direct Lake on F64 before falling back. Which single characteristic of Direct Lake behavior best explains why some queries succeed in Direct Lake mode while others fall back?

Accepted Answer

When a query would require exceeding a capacity guardrail (such as rows-per-table), Direct Lake transparently falls back to DirectQuery for that query while other queries within limits stay in Direct Lake mode.

Answer

Direct Lake caches the entire model in memory only during scheduled refresh windows, so queries outside those windows always use DirectQuery.

Answer

Direct Lake permanently switches the whole model to DirectQuery the first time any guardrail is exceeded, and it stays there until the model is reframed.

Answer

The SQL analytics endpoint randomly load-balances queries between import and DirectQuery to distribute capacity pressure.

Question 56

You maintain a Direct Lake semantic model built on a Fabric lakehouse. A large fact table is loaded daily via Spark notebooks that append new partitions to the underlying Delta table. Business users report that the model does not immediately reflect the newly appended data after each daily load, and you want the model to expose the latest data with minimal query latency. Which approach correctly ensures the Direct Lake model reflects the newly loaded data?

Accepted Answer

Trigger a semantic model refresh (reframing) after the notebook load so the model re-points to the current Delta table version.

Answer

Configure incremental refresh policies on the fact table so only new partitions are processed each day.

Answer

Switch the fact table's storage mode to Import and schedule a full refresh after each load.

Answer

Enable DirectQuery fallback so every query reads the latest files directly from the SQL analytics endpoint.

Question 57

You are building a Direct Lake semantic model over a Fabric lakehouse containing a fact table with over 5 billion rows. During testing, some queries unexpectedly fall back to DirectQuery and users report inconsistent performance. You want the model to keep as much data as possible resident in memory (VertiPaq) rather than falling back, and you need the model to support paging large column segments in and out of memory efficiently. Which configuration should you apply to the semantic model to best support this scale?

Accepted Answer

Enable the large semantic model storage format on the model so column segments can be paged and the model can exceed the default in-memory size limits

Answer

Switch the fact table's storage mode to Import so all rows are compressed and loaded at refresh time

Answer

Set the Direct Lake behavior to DirectQuery only so that no data is held in memory

Answer

Reduce the number of columns by hiding them in the model so VertiPaq loads fewer segments

Question 58

Your team publishes a Direct Lake semantic model over a lakehouse. Security requirements state that row-level filtering must be enforced consistently whether users query through the semantic model, the SQL analytics endpoint, or Spark. The analytics engineer wants to define the security once at the data layer rather than duplicating logic in DAX RLS roles. Which approach on the newer Direct Lake on OneLake mode best meets this goal?

Accepted Answer

Define OneLake security roles (row/column-level security) at the lakehouse Delta table level so the rules apply across the semantic model, SQL endpoint, and Spark access.

Answer

Create DAX row-level security roles in the semantic model and rely on the SQL analytics endpoint to inherit them automatically.

Answer

Configure object-level security (OLS) in the semantic model to hide restricted rows from all query paths.

Answer

Apply a sensitivity label to the lakehouse and enable dynamic data masking on the SQL endpoint columns.

Question 59

You are building a Direct Lake semantic model over a Fabric lakehouse. The lakehouse contains several Delta tables written directly by a Spark notebook. Your team wants the semantic model to read table data with the least dependency on additional metadata-syncing layers, and you want to avoid the automatic framing delays that can occur when the SQL analytics endpoint has not yet caught up with newly written Delta files. Which approach should you choose when creating the model?

Accepted Answer

Create a Direct Lake on OneLake model that connects directly to the lakehouse Delta tables in OneLake

Answer

Create a Direct Lake on SQL analytics endpoint model built from the lakehouse SQL endpoint

Answer

Create an Import mode model and schedule refresh after each notebook run

Answer

Create a DirectQuery model against the SQL analytics endpoint

Question 60

A retail analyst maintains a Direct Lake semantic model built on a Fabric lakehouse Delta table. An overnight Spark job appends the previous day's transactions to the Delta table, writing new Parquet files and updating the Delta log. The next morning, users report that reports still show yesterday's totals and none of the newly appended rows appear. The model is not configured for scheduled refresh. What is the correct explanation and remedy?

Accepted Answer

Direct Lake requires the model to be reframed; configure a refresh (scheduled or via API) so the model picks up the latest committed Delta files, since reframing does not happen automatically after every write.

Answer

Direct Lake always reflects the latest Delta data in real time, so the missing rows indicate the Spark job failed to commit the transaction to the Delta log.

Answer

The model has fallen back to DirectQuery because the guardrail was exceeded; switch the fallback setting to Direct Lake only to restore current data.

Answer

Import mode is required to see appended rows; convert the model from Direct Lake to Import and schedule an incremental refresh.

Question 61

A retail company uses a Direct Lake semantic model built on a lakehouse Delta table. An overnight Spark job rewrites the sales Delta table with new Parquet files each night. Analysts report that reports opened first thing in the morning still show yesterday's numbers, even though the Delta table already contains the new data. You need the semantic model to reflect the latest data with minimal ongoing management. What should you do?

Accepted Answer

Schedule a refresh on the semantic model so that reframing occurs after the Spark job completes.

Answer

Switch the storage mode of the sales table to Import and configure incremental refresh.

Answer

Enable Large semantic model storage format on the model to force automatic data reload.

Answer

Disable DirectQuery fallback so the engine always reads the newest Parquet files.

Question 62

A retail company has a 400 million row sales fact table stored as Delta tables in a Fabric lakehouse. Analysts complain that the first report interaction each morning is noticeably slow, but subsequent queries are fast. The semantic model uses Direct Lake storage mode. Investigation shows no fallback to DirectQuery is occurring, and the model stays well within capacity guardrails. What is the MOST likely cause of the slow first query?

Accepted Answer

The required column segments must be transcoded and paged into memory from OneLake on the first access (cold cache), after which they remain resident for fast subsequent queries.

Answer

Direct Lake silently falls back to DirectQuery only for the first query of the day and then reverts to VertiPaq.

Answer

The Delta table needs to be re-framed on every first access, which fully reloads all 400 million rows into memory.

Answer

Incremental refresh policies process the historical partitions during the first user query, blocking the report.

Question 63

You manage a Direct Lake semantic model on an F64 capacity. Users report that certain visuals are intermittently slow during peak hours, but the same visuals are fast at other times. Direct Lake fallback to DirectQuery is NOT occurring. You suspect memory pressure is causing columns to be paged out. Which behavior of Direct Lake best explains the intermittent slowness, and what should you investigate first?

Accepted Answer

Direct Lake loads (transcodes) column data into memory on demand and evicts least-recently-used columns under memory pressure; investigate the model's column footprint and capacity memory limits to reduce cold-load reloading.

Answer

Direct Lake caches the entire Delta table in memory permanently after the first query; investigate whether the scheduled refresh is disabled during peak hours.

Answer

Direct Lake always queries the SQL analytics endpoint at runtime; investigate the endpoint's compute autoscale settings to add more replicas.

Answer

Direct Lake performance depends only on the number of active relationships; investigate whether bidirectional relationships are causing ambiguous filter paths.

Question 64

A Direct Lake semantic model built on a Fabric lakehouse powers an executive dashboard. Users report that certain visuals load slowly on the first interaction of the day, and Performance Analyzer shows most of the time is spent scanning a very wide fact Delta table (150+ columns) even though visuals reference only a handful of columns. You want to reduce the initial cold-query latency without switching away from Direct Lake. Which action most directly improves this?

Accepted Answer

Run the Delta table through OPTIMIZE and V-Order the Parquet files so columns load efficiently into VertiPaq on demand

Answer

Enable DirectQuery fallback set to 'DirectQuery only' so the SQL analytics endpoint always serves queries

Answer

Add all 150 columns to a single field parameter so Power BI can cache them together

Answer

Convert the model to Import mode and schedule an hourly refresh of the fact table

Exam domain	Exam weight	Practice
Maintain a data analytics solution	27%	Practice this topic
Prepare data	46%	Practice this topic
Implement and manage semantic models	27%	Practice this topic

Microsoft Fabric Analytics Engineer Associate (DP-600) practice exam & study guide

Start studying DP-600

All DP-600 study resources

DP-600 exam domains

Sample DP-600 questions

Key DP-600 terms

DP-600 frequently asked questions

Maintain a data analytics solution (27%)

Prepare data (46%)

Implement and manage semantic models (27%)