You have a Fabric warehouse that contains a table named Table1. Table1 contains three columns named SalesAmount, ProductCategory, and TransactionDate.
You need to create a Microsoft Power BI query that will calculate the total sales amount of each product category for transactions that occurred during the last quarter.
Which two actions should you perform in the visual query editor? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. For the TransactionDate column, select Remove duplicates.
B. For the TransactionDate column, select Keep top rows and set Number of rows to 90.
C. For the TransactionDate column, select Filter rows and set the value to last quarter.
D. For the ProductCategory column, select Group by and sum the SalesAmount column.
E. For the SalesAmount column, select Transformation number column - Standard, select Add, and then set the value to 90.
Show Answer
Correct Answer: C, D
Explanation: To calculate total sales by product category for the last quarter, you must first filter the data to only include transactions from the last quarter. This is done by filtering the TransactionDate column to last quarter. Then, you need to aggregate the filtered data by ProductCategory and sum the SalesAmount column. Other options either do not correctly filter by date or do not perform the required aggregation.
Question 12
HOTSPOT
-
You have a Fabric warehouse named Warehouse1 that contains a table named TaxiTrips.
TaxiTrips contains the following columns.
You need to create a query that shows the top three taxi companies based on the total miles traveled.
How should you complete the T-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Show Answer
Correct Answer: SELECT TOP (3)
company AS TaxiCompany,
sum(passengerCount) AS TotalPassengers,
sum(tripDistance) AS TotalDistance
FROM [Warehouse1].[dbo].[TaxiTrips]
GROUP BY company
ORDER BY sum(tripDistance) DESC;
Explanation: TOP (3) returns only the three rows with the highest values. GROUP BY company aggregates trips per taxi company, and ORDER BY sum(tripDistance) DESC sorts companies by total miles traveled from highest to lowest.
Question 13
DRAG DROP
-
You are implementing two dimension tables named Customers and Products in a Fabric warehouse.
You need to create two slowly changing dimensions that meet the requirements shown in the following table.
Which type of SCD should you use for each table? To answer, drag the appropriate SCD types to the correct tables. Each SCD type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Show Answer
Correct Answer: Customers: Type 2
Products: Type 1
Explanation: Type 2 SCD creates a new row for each change while preserving historical versions. Type 1 SCD overwrites the existing attribute value and does not retain history.
Question 14
You have a Fabric tenant that contains 30 CSV files in OneLake. The files are updated daily.
You create a Microsoft Power BI semantic model named Model1 that uses the CSV files as a data source. You configure incremental refresh for Model1 and publish the model to an F64 capacity in the Fabric tenant.
When you initiate a refresh of Model1, the refresh fails after running out of resources.
What is a possible cause of the failure?
A. XMLA Endpoint is set to Read Only.
B. Query folding is occurring.
C. The data type of the column used to partition the data has changed.
D. Only refresh complete days is selected.
E. Query folding is NOT occurring.
Show Answer
Correct Answer: E
Explanation: Incremental refresh depends on query folding so that date filters are applied at the source. If query folding does not occur (common with CSV files), the engine must load all data into memory and then filter it, which can exhaust capacity resources and cause the refresh to fail.
Question 15
You have a Fabric tenant that contains the workspaces shown in the following table.
You have a deployment pipeline named Pipeline1 that deploys items from Workspace_DEV to Workspace_TEST. In Pipeline1, all items that have matching names are paired.
You deploy the contents of Workspace_DEV to Workspace_TEST by using Pipeline1.
What will the contents of Workspace_TEST be once the deployment is complete?
A. Lakehouse2 - Notebook2 - SemanticModel1 -
B. Lakehouse1 - Notebook1 - Pipeline1 - SemanticModel1 -
D. Lakehouse2 - Notebook2 - Pipeline1 - SemanticModel1
Show Answer
Correct Answer: C
Explanation: In a Fabric deployment pipeline, items with matching names between source and target are paired and updated, while unpaired items in the target remain unless explicitly removed. When deploying from Workspace_DEV to Workspace_TEST, existing paired items are overwritten with the DEV versions, and additional items from DEV are added. Items already present in TEST that are not removed by the pipeline (including Pipeline artifacts and unpaired items) remain. Therefore, Workspace_TEST ends up containing all original items plus those deployed from DEV, resulting in Lakehouse1, Lakehouse2, Notebook1, Notebook2, Pipeline1, and SemanticModel1.
Question 16
DRAG DROP
-
You have a Fabric warehouse named Warehouse1 that contains a table named dbo.Product.
dbo.Product contains the following columns.
You need to use a T-SQL query to add a column named PriceRange to dbo.Product. The column must categorize each product based on UnitPrice. The solution must meet the following requirements:
• If UnitPrice is 0, PriceRange is "Not for resale".
• If UnitPrice is less than 50, PriceRange is "Under $50".
• If UnitPrice is between 50 and 250, PriceRange is "Under $250“.
• In all other instances, PriceRange is "$250+".
How should you complete the query? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Show Answer
Correct Answer: CASE
ELSE
END
Explanation: A CASE expression is required to evaluate UnitPrice conditions. ELSE handles all remaining values ("$250+"), and END closes the CASE statement.
Question 17
You have a Fabric workspace named Workspace1.
You need to create a semantic model named Model1 and publish Model1 to Workspace1. The solution must meet the following requirements:
• Can revert to previous versions of Model1 as required.
• Identifies differences between saved versions of Model1.
• Uses Microsoft Power Bl=I Desktop to publish to Workspace1.
• Can edit item definition files by using Microsoft Visual Studio Code.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. Enable Git integration for Workspace1.
B. Save Model1 in Power BI Desktop as a PBIT file.
C. Enable users to edit data models in the Power BI service.
D. Save Model1 in Power BI Desktop as a PBIP file.
Show Answer
Correct Answer: A, D
Explanation: Git integration in the Fabric workspace provides source control, enabling version history, rollback to previous versions, and diffing between versions of the semantic model. Saving the semantic model as a PBIP project allows publishing from Power BI Desktop, supports item definition files, and enables editing those files with Visual Studio Code. Together, these actions meet all stated requirements.
Question 18
HOTSPOT
-
You have a Fabric tenant that contains a workspace named Workspace1 and a user named DBUser. Workspace1 contains a lakehouse named Lakehouse1. DBUser does NOT have access to the tenant.
You grant DBUser access to Lakehouse1 as shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Show Answer
Correct Answer: OneLake endpoint
OneLake file explorer
Explanation: DBUser was granted access to the Lakehouse with Apache Spark read permissions only. This allows reading data through the OneLake storage endpoint and accessing files via OneLake File Explorer. SQL-based access (TDS/SSMS) or semantic model access isn’t available because SQL endpoint permissions were not granted.
Question 19
HOTSPOT
-
You have a Fabric workspace that contains a warehouse named Warehouse1. Warehouse1 contains the following data.
You need to create a T-SQL statement that will denormalize the tables and include the ContractType and StartDate attributes in the results. The solution must meet the following requirements:
• Include attributes from matching rows in the Contract table.
• Ensure that all the rows from the Employee table are preserved.
• Return the total number of employees per contract type for all the contract types that have more than two employees.
How should you complete the statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Show Answer
Correct Answer: LEFT OUTER JOIN
HAVING
Explanation: A LEFT OUTER JOIN preserves all rows from Employee while bringing matching Contract attributes. The HAVING clause filters grouped results to return only contract types with more than two distinct employees.
Question 20
You plan to use Fabric to store data.
You need to create a data store that supports the following:
• Writing data by using T-SQL
• Multi-table transactions
• Dynamic data masking
Which type of data store should you create?
A. KQL database
B. lakehouse
C. warehouse
D. semantic model
Show Answer
Correct Answer: C
Explanation: A Microsoft Fabric Warehouse supports writing data using T-SQL, provides full multi-table transaction support, and includes security features such as dynamic data masking. KQL databases focus on telemetry and analytics with KQL, lakehouses do not support full T-SQL transactional features or dynamic data masking, and semantic models are for analytics rather than data storage.
$19
Get all 189 questions with detailed answers and explanations
Instant download HTML + PDF delivered the moment payment clears.
Secure Stripe checkout we never see or store your card details.
7-day refund if files are defective see our refund policy.
