Free Microsoft DP-600 Practice Exam with Questions & Answers | Set: 2

To create a calculation item that changes the context from the selected date to month-to-date (MTD), the appropriate DAX expression involves using the CALCULATE function to alter the filter context and the DATESMTD function to specify the month-to-date context.

The correct completion for the DAX expression would be:

In the first dropdown, select CALCULATE .

In the second dropdown, select SELECTEDMEASURE .

This would create a DAX expression in the form:

CALCULATE(

SELECTEDMEASURE(),

DATESMTD( ' Date ' [DateColumn])

)

To meet the technical requirement that data loading activities must ensure the raw and cleansed data is updated completely before populating the dimensional model, you would need a mechanism that allows for ordered execution. A pipeline in Microsoft Fabric with dependencies set between activities can ensure that activities are executed in a specific sequence. Once set up, the pipeline can be scheduled to run at the required intervals (hourly or daily depending on the data source).

Requirement

Fabric workspace: Workspace1

Contains: Warehouse1, Model1, Report1

Task: Allow User1 to access a single table in Warehouse1 .

Must follow principle of least privilege → grant only the exact permissions required.

Step 1: Evaluate Options

A. Assign object level permissions to User1 for Warehouse1.

In Fabric warehouses, you can grant permissions at the object level (table, schema, or column).

If the requirement is access to only one table , the correct approach is to grant SELECT permissions on that specific table .

This satisfies least privilege.

Correct. ✅

B. Assign the db_datareader role to User1 for Warehouse1.

db_datareader provides read access to all tables in the database/warehouse.

This violates least privilege.

Not correct.

C. Share Warehouse1 with User1.

Sharing grants access to the whole warehouse.

Too broad, not least privilege.

Not correct.

D. Assign the Viewer role to User1 for Workspace1.

Viewer role allows seeing all items in the workspace (warehouse, model, reports).

This would expose more than the single table.

Not correct.

Step 2: Correct Action

Use object-level permissions :

GRANT SELECT ON dbo.TableName TO [User1];

This ensures User1 can only query that specific table, nothing else.

References

Manage object-level security in Microsoft Fabric warehouses

Principle of least privilege in Fabric

When creating a shortcut to an Azure Data Lake Storage Gen2 account in a lakehouse, you should use the abfss (Azure Blob File System Secure) connection string and the dfs (Data Lake File System) endpoint. The abfss is used for secure access to Azure Data Lake Storage, and the dfs endpoint indicates that the Data Lake Storage Gen2 capabilities are to be used.

Step 1: Requirement

We need to calculate total sales amount by OrderDate .

Total Sales Amount = Quantity × UnitPrice per row.

Then SUM over all rows per OrderDate.

Only include rows where the OrderDate matches a public holiday in Australia .

Step 2: Calculation Logic

If we select s.Quantity * s.UnitPrice , it only computes row-level values.

To calculate the total sales amount per OrderDate , we need:

SUM (s.Quantity * s.UnitPrice) AS TotalSalesAmt

Thus, the correct calculation: SUM(s.Quantity * s.UnitPrice)

Step 3: Join Type

We want to return only those sales that occurred on public holidays in Australia .

If we used a LEFT JOIN , we’d include all sales regardless of holiday.

A FULL OUTER JOIN would include non-matching rows, not required.

A CROSS JOIN would duplicate results unnecessarily.

A RIGHT OUTER JOIN would include holidays with no sales, which is not needed.

The correct choice is an INNER JOIN on matching dates.

Step 4: Final Query

SELECT

s.OrderDate,

SUM (s.Quantity * s.UnitPrice) AS TotalSalesAmt

FROM dbo.sales AS s

INNER JOIN dbo.publicholidays AS ph

ON s.OrderDate = ph.Date

WHERE ph.countryOrRegion = ' Australia '

GROUP BY s.OrderDate;

Why This is Correct

SUM(s.Quantity * s.UnitPrice) → ensures sales are aggregated correctly.

INNER JOIN → ensures only rows where OrderDate matches a holiday are included.

WHERE ph.countryOrRegion = ' Australia ' → filters holidays to Australia only.

References

SUM (Transact-SQL)

JOIN operations in T-SQL

To validate the data seen by the HR manager, you should use the ' Test as role ' feature in Power BI service. This allows you to see the data exactly as it would appear for the HR role, considering the dynamic RLS setup. Here is how you would proceed:

Navigate to the Power BI service and locate Model1.

Access the dataset settings for Model1.

Find the security/RLS settings where you configured the roles.

Use the ' Test as role ' feature to simulate the report viewing experience as the HR role.

Review the data and the filters applied to ensure that the RLS is functioning correctly.

If discrepancies are found, adjust the RLS expressions or the role membership as needed.

The correct order for the DAX expression would be:

DEFINE VAR _SalesSince = DATE ( 2023, 12, 01 )

EVALUATE

FILTER (

SUMMARIZE ( Store, Store[Name], Store[OpenDate] ),

Store[OpenDate] > = _SalesSince )

In this DAX query, you ' re defining a variable _SalesSince to hold the date from which you want to filter the stores. EVALUATE starts the definition of the query. The FILTER function is used to return a table that filters another table or expression. SUMMARIZE creates a summary table for the stores, including the Store[Name] and Store[OpenDate] columns, and the filter expression Store[OpenDate] > = _SalesSince ensures only stores opened on or after December 1, 2023, are included in the results.

References =

DAX FILTER Function

DAX SUMMARIZE Function

To meet the requirements, developers should have Admin access to the deployment pipeline (B), Contributor access to the Development and Test workspaces (E), and Viewer access to the Production workspace (D). This setup ensures they can perform necessary actions in development and test environments without having the ability to affect production. References = The Power BI documentation on workspace access levels and deployment pipelines provides guidelines on assigning appropriate permissions.

To ensure that users assigned the SalesRegionManager role cannot see the Address column in the SalesAddress table, follow these steps in sequence:

Open the model in Tabular Editor.

Select the Address column in SalesAddress.

Set Object Level Security to None for SalesRegionManager.