Medium DP-600 practice questions

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?