Practice Free Microsoft DP-500 Exam Questions Online, DP-500 Free Demo

Question 1 Selectable Answer

Your company is migrating its current, custom-built reporting solution to Power BI.
The Power BI tenant must support the following scenarios:
✑ 40 reports that will be embedded in external websites. The websites control their own security. The reports will be consumed by 50 users monthly.
✑ Forty-five users that require access to the workspaces and apps in the Power BI Admin portal. Ten of the users must publish and consume datasets that are larger than 1 GB.
✑ Ten developers that require Text Analytics transformations and paginated reports for datasets. An additional 15 users will consume the reports.
You need to recommend a licensing solution for the company. The solution must minimize costs.
Which two Power BI license options should you include in the recommendation? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

A. 70 Premium per user
B. one Premium
C. 70 Pro
D. one Embedded
E. 35 Pro
F. 35 Premium per user

Answer:
Explanation:
B:
Free - 40 reports that will be embedded in external websites. The websites control their
own security.
Free - The reports will be consumed by 50 users monthly.
Free + 1 Premium for the Worspace -Forty-five users that require access to the workspaces and apps in the Power BI Admin portal.
F: Ten of the users must publish and consume datasets that are larger than 1 GB.
Ten developers that require Text Analytics transformations and paginated reports for datasets. An additional 15 users will consume the reports.
Power BI Premium per user features and capabilities
* Pixel perfect paginated reports are available for operational reporting capabilities based on SSRS technology. Users can create highly formatted reports in various formats such as PDF and PPT, which are embeddable in applications and are designed to be printed or shared.
Note: There are three kinds of Power BI per-user licenses: Free, Pro, and Premium Per User.
Power BI (free): Access to content in My Workspace
Power BI (free) + Workspace is Premium: Consume content shared with them Power BI Pro: Publish content to other workspaces, share dashboards, subscribe to dashboards and reports, share with users who have a Pro license
Power BI Pro + Workspace is Premium: Distribute content to users who have free licenses
Power BI Premium Per User: Publish content to other workspaces, share dashboards, subscribe to dashboards and reports, share with users who have a Premium Per User license
Power BI Premium Per User + Workspace is Premium: Distribute content to users who have free and Pro licenses
Reference: https://docs.microsoft.com/en-us/power-bi/fundamentals/service-features-license-type

Question 2 Selectable Answer

You are using a Python notebook in an Apache Spark pool in Azure Synapse Analytics.
You need to present the data distribution statistics from a DataFrame in a tabular view.
Which method should you invoke on the DataFrame?

A. rollup
B. cov
C. explain
D. describe

Answer:
Explanation:
The aggregating statistic can be calculated for multiple columns at the same time with the describe function.
Example:
titanic[["Age", "Fare"]].describe()
Out[6]:
Age Fare
count 714.000000 891.000000
mean 29.699118 32.204208
std 14.526497 49.693429
min 0.420000 0.000000
25% 20.125000 7.910400
50% 28.000000 14.454200
75% 38.000000 31.000000
max 80.000000 512.329200
Reference: https://pandas.pydata.org/docs/getting_started/intro_tutorials/06_calculate_statistics.html

Question 3 Selectable Answer

You have a Power Bl report that contains the table shown in the following exhibit.

The table contains conditional formatting that shows which stores are above, near, or below the monthly quota for returns. You need to ensure that the table is accessible to consumers of reports who have color vision deficiency.
What should you do?

A. Add alt text to explain the information that each color conveys.
B. Move the conditional formatting icons to a tooltip report.
C. Change the icons to use a different shape for each color.
D. Remove the icons and use red, yellow, and green background colors instead.

Answer:
Explanation:
Report accessibility checklist, All Visuals.
* Ensure alt text is added to all non-decorative visuals on the page.
* Avoid using color as the only means of conveying information. Use text or icons to supplement or replace the color.
* Check that your report page works for users with color vision deficiency.
* Etc.
Reference: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-accessibility-creating-reports

Question 4 Selectable Answer

Topic 2, Contoso, Ltd

Overview
Contoso, Ltd. is a company that sells enriched financial data to a variety of external customers.
Contoso has a main office in Los Angeles and two branch offices in New York and Seattle.

Data Infrastructure
Contoso has a 50-TB data warehouse that uses an instance of SQL Server on Azure Virtual Machines.
The data warehouse populates an Azure Synapse Analytics workspace that is accessed by the external customers. Currently, the customers can access alt the data.
Contoso has one Power Bl workspace named FinData that contains a single dataset. The dataset
contains financial data from around the world. The workspace is used by 10 internal users and one external customer. The dataset has the following two data sources: the data warehouse and the Synapse Analytics serverless SQL pool.
Users frequently query the Synapse Analytics workspace by using Transact-SQL.

User Problems
Contoso identifies the following user issues:
• Some users indicate that the visuals in Power Bl reports are slow to render when making filter selections.
• Users indicate that queries against the serverless SQL pool fail occasionally because the size of tempdb has been exceeded.
• Users indicate that the data in Power Bl reports is stale. You discover that the refresh process of the Power Bl model occasionally times out

Planned Changes
Contoso plans to implement the following changes:
• Into the existing Power Bl dataset, integrate an external data source that is accessible by using the REST API.
• Build a new dataset in the FinData workspace by using data from the Synapse Analytics dedicated SQL pool.
• Provide all the customers with their own Power Bl workspace to create their own reports. Each workspace will use the new dataset in the FinData workspace.
• Implement subscription levels for the customers. Each subscription level will provide access to specific rows of financial data.
• Deploy prebuilt datasets to Power Bl to simplify the query experience of the customers.
• Provide internal users with the ability to incorporate machine learning models loaded to the dedicated SQL pool.

You need to recommend a solution for the customer workspaces to support the planned
changes.
Which two configurations should you include in the recommendation? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

A. Set Use datasets across workspaces to Enabled
B. Publish the financial data to the web.
C. Grant the Build permission for the financial data to each customer.
D. Configure the FinData workspace to use a Power Bl Premium capacity.

Answer:
Explanation:
Build a new dataset in the FinData workspace by using data from the Synapse Analytics dedicated SQL pool.
Provide all the customers with their own Power BI workspace to create their own reports. Each workspace will use the new dataset in the FinData workspace
Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-admin-across-workspaces

Question 5 Written Answer

HOTSPOT
You are building a Power Bl dataset that contains a table named Calendar. Calendar contains the following calculated column.
pfflag = IF('Calendar'[Date] < TOOAYQ, "Past", "Future")
You need to create a measure that will perform a fiscal prior year-to-date calculation that meets the following requirements:
• Returns the fiscal prior year-to-date value for [sales Amount]
• Uses a fiscal year end of June 30
• Produces no result for dates in the future
How should you complete the DAX expression? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation:
Box 1: CALCULATETABLE
CALCULATETABLE evaluates a table expression in a modified filter context. Syntax: CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])
Incorrect:
* SUMMARIZECOLUMNS
SUMMARIZECOLUMNS returns a summary table over a set of groups.
Syntax: SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…)
* CROSSJOIN returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables.
Syntax: CROSSJOIN(<table>, <table>[, <table>]…)
* UNION creates a union (join) table from a pair of tables.
Syntax: UNION(<table_expression1>, <table_expression2> [,<table_expression>]…)
Box 2: SAMEPERIODLASTYEAR
SAMEPERIODLASTYEAR returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.
Syntax: SAMEPERIODLASTYEAR(<dates>)
The dates returned are the same as the dates returned by this equivalent formula:
DATEADD(dates, -1, year)
Example:
The following sample formula creates a measure that calculates the previous year sales of Reseller sales.
= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey))
Box 3: TODAY()
TODAY() returns the current date.
The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.
Example:
The following sample formula creates a measure that calculates the 'Running Total' for Internet sales.
= CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESYTD(DateTime[DateKey]))

Question 6 Selectable Answer

You use the Vertipaq Analyzer to analyze tables in a dataset as shown in the Tables exhibit. (Click the Tables tab.)

The table relationships for the dataset are shown in the Relationships exhibit. (Click the Relationships tab.)

You need to reduce the model size by eliminating invalid relationships.
Which column should you remove?

A. Sales[Sales Amount]
B. Sales[RowlD]
C. Sales[Sales ID]
D. Plan[RowlD]

Answer:
Explanation:
Sales[Row ID] has 858,786 missing keys and 858,789 Max From Cardinality.
Note: The Max From Cardinality column defines the cost of the relationship which is the amount of time DAX needs to transfer the filters from the dimensions table to the fact table.
Reference: https://blog.enterprisedna.co/vertipaq-analyzer-tutorial-relationships-referential-integrity/

Question 7 Selectable Answer

You have a deployment pipeline for a Power BI workspace. The workspace contains two datasets that use import storage mode.
A database administrator reports a drastic increase in the number of queries sent from the Power BI service to an Azure SQL database since the creation of the deployment pipeline.
An investigation into the issue identifies the following:
✑ One of the datasets is larger than 1 GB and has a fact table that contains more than 500 million rows.
✑ When publishing dataset changes to development, test, or production pipelines, a refresh is triggered against the entire dataset.
You need to recommend a solution to reduce the size of the queries sent to the database when the dataset changes are published to development, test, or production.
What should you recommend?

A. From Capacity settings in the Power Bl Admin portal, reduce the Max Intermediate Row Set Count setting.
B. Configure the dataset to use a composite model that has a DirectQuery connection to the fact table.
C. Enable the large dataset storage format for workspace.
D. From Capacity settings in the Power Bl Admin portal, increase the Max Intermediate Row Set Count setting.

Answer:
Explanation:
A composite model in Power BI means part of your model can be a DirectQuery connection to a data source (for example, SQL Server database), and another part as Import Data (for example, an Excel file). Previously, when you used DirectQuery, you couldn’t even add another data source into the model.
DirectQuery and Import Data have different advantages.
Now the Composite Model combines the good things of both Import and DirectQuery into one model. Using the Composite Model, you can work with big data tables using DirectQuery, and still import smaller tables using Import Data.
Reference:
https://radacad.com/composite-model-directquery-and-import-data-combined-evolution-begins-in-power-bi
https://powerbi.microsoft.com/en-us/blog/five-new-power-bi-premium-capacity-settings-is-available-on-the-portal-preloaded-with-default-values-admin-can-review-and-override-the-defaults-with-their-preference-to-better-fence-their-capacity/

Question 8 Written Answer

HOTSPOT
You use Advanced Editor in Power Query Editor to edit a query that references two tables named Sales and Commission.
A sample of the data in the Sales table is shown in the following table.

A sample of the data in the Commission table is shown in the following table.

You need to merge the tables by using Power Query Editor without losing any rows in the Sales table.
How should you complete the query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation:
Box 1: Join
Box 2: LeftOuter
Left outer join
One of the join kinds available in the Merge dialog box in Power Query is a left outer join, which keeps all the rows from the left table and brings in any matching rows from the right table.

Diagram, table
Description automatically generated

Question 9 Selectable Answer

You have two Power BI reports named Report1 and Report2.
Report1 connects to a shared dataset named Dataset1.
Report2 connects to a local dataset that has the same structure as Dataset1. Report2 contains several calculated tables and parameters.
You need to prepare Report2 to use Dataset1.
Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

A. Remove the data source permissions.
B. Delete all the Power Query Editor objects.
C. Modify the source of each query.
D. Update all the parameter values.
E. Delete all the calculated tables.

Answer:
Explanation:
C: Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.
D: Common uses for parameters
Here are some of the most common ways to use parameters.
Control paginated report data
* Filter paginated report data at the data source by writing dataset queries that contain variables.
* Etc.
Reference:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-query-overview
https://docs.microsoft.com/en-us/learn/modules/dax-power-bi-add-calculated-tables/1-introduction

Question 10 Written Answer

HOTSPOT
You use Vertipaq Analyzer to analyze a model.
The Relationships tab contains the results 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.

Answer:

Explanation:
Box 1: Customer
There are 1804 invalid rows (records) in the Customer table.
Box 2: 22
There are 22 missing keys.
Note: VertiPaq Analyzer in DAX Studio is useful in identifying referential integrity violations which slow down your DAX codes. It helps you determine which table or column needs to be optimized and improved.

Question 11 Selectable Answer

You need to configure the Sales Analytics workspace to meet the ad hoc reporting requirements.
What should you do?

A. Grant the sales managers the Build permission for the existing Power Bl datasets.
B. Grant the sales managers admin access to the existing Power Bl workspace.
C. Create a deployment pipeline and grant the sales managers access to the pipeline.
D. Create a PBIT file and distribute the file to the sales managers.

Answer:
Explanation:
Allow sales managers to perform ad hoc sales reporting with minimal effort
Power BI report templates contain the following information from the report from which they were generated:
Report pages, visuals, and other visual elements
The data model definition, including the schema, relationships, measures, and other model definition items
All query definitions, such as queries, Query Parameters, and other query elements What is not included in templates is the report's data.
Report templates use the file extension .PBIT (compare to Power BI Desktop reports, which use the .PBIX extension).
Note: With Power BI Desktop, you can create compelling reports that share insights across your entire organization. With Power BI Desktop templates, you can streamline your work by creating a report template, based on an existing template, which you or other users in your organization can use as a starting point for a new report's layout, data model, and queries. Templates in Power BI Desktop help you jump-start and standardize report creation.
Reference: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-templates

Question 12 Written Answer

HOTSPOT
You have the following code in an Azure Synapse notebook.

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the code. NOTE: Each correct selection is worth one point.

Answer:

Explanation:
Box 1: three scatterplots
Compare Plots
Example, Draw two plots on the same figure:
import matplotlib.pyplot as plt
import numpy as np
#day one, the age and speed of 13 cars:
x = np.array([5,7,8,7,2,17,2,9,4,11,12,9,6])
y = np.array([99,86,87,88,111,86,103,87,94,78,77,85,86])
plt.scatter(x, y)
#day two, the age and speed of 15 cars:
x = np.array([2,2,8,1,15,8,12,9,7,3,11,4,7,14,12])
y = np.array([100,105,84,105,90,99,90,95,94,100,79,112,91,80,85])
plt.scatter(x, y)
plt.show()
Result:

Chart, scatter
chart
Description automatically generated
Box 2: three marker symbols
One for each scatterplot. One default, and two defined.
Default is point.
v is triangle down.
^ is triangle up.

Question 13 Selectable Answer

You are building a Power Bl dataset that will use two data sources.
The dataset has a query that uses a web data source. The web data source uses anonymous authentication.
You need to ensure that the query can be used by all the other queries in the dataset.
Which privacy level should you select for the data source?

A. Public
B. Organizational
C. Private
D. None

Answer:
Explanation:
A Public data source gives everyone visibility to the data contained in the data source. Only files, internet data sources, or workbook data can be marked Public. Data from a Public data source may be freely folded to other sources.
Reference: https://docs.microsoft.com/en-us/power-bi/enterprise/desktop-privacy-levels

Question 14 Written Answer

1. Topic 1, Litware, Inc. Overview

Litware, Inc. is a retail company that sells outdoor recreational goods and accessories. The company sells goods both online and at its stores located in six countries.

Azure Resources
Litware has the following Azure resources:
• An Azure Synapse Analytics workspace named synapseworkspace1
• An Azure Data Lake Storage Gen2 account named datalake1 that is associated with synapseworkspace1
• A Synapse Analytics dedicated SQL pool named SQLDW

Dedicated SQL Pool
SQLDW contains a dimensional model that contains the following table.

SQLDW contains the following additional tables.

SQLDW contains a view named dbo.CustomerPurchases that creates a distinct list of values from dbo.Customer [customeriD], dbo.Customer
[CustomerEmail], dbo.ProductfProductID] and dbo.Product[ProductName].
The sales data in SQLDW is updated every 30 minutes. Records in dbo.SalesTransactions are updated in SQLDW up to three days after being created. The records do NOT change after three days.

Power BI
Litware has a new Power Bl tenant that contains an empty workspace named Sales Analytics.
All users have Power B1 Premium per user licenses.
IT data analysts are workspace administrators. The IT data analysts will create datasets and reports.
A single imported dataset will be created to support the company's sales analytics goals. The dataset will be refreshed every 30 minutes.

Analytics Goals
Litware identifies the following analytics goals:
• Provide historical reporting of sales by product and channel over time.
• Allow sales managers to perform ad hoc sales reporting with minimal effort.
• Perform market basket analysis to understand which products are commonly purchased in the same transaction.
• Identify which customers should receive promotional emails based on their likelihood of purchasing promoted products.
Litware plans to monitor the adoption of Power Bl reports over time. The company wants custom Power Bl usage reporting that includes the percent change of users that view reports in the Sales Analytics workspace each month.

Security Requirements
Litware identifies the following security requirements for the analytics environment:
• All the users in the sales department and the marketing department must be able to see Power B1 reports that contain market basket analysis and data about which customers are likely to purchase a product.
• Customer contact data in SQLDW and the Power B1 dataset must be labeled as Sensitive. Records must be kept of any users that use the sensitive data.
• Sales associates must be prevented from seeing the CustomerEmail column in Power B1 reports.
• Sales managers must be prevented from modifying reports created by other users.

Development Process Requirements
Litware identifies the following development process requirements:
• SQLDW and datalake1 will act as the development environment. Once feature development is complete, all entities in synapseworkspace1 will be promoted to a test workspace, and then to a production workspace.
• Power Bl content must be deployed to test and production by using deployment pipelines.
• All SQL scripts must be stored in Azure Repos.
The IT data analysts prefer to build Power Bl reports in Synapse Studio.

HOTSPOT
You need to populate the CustomersWithProductScore table.
How should you complete the stored procedure? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation:
Box 1: FLOAT
Identify which customers should receive promotional emails based on their likelihood of purchasing promoted products.
FLOT is used in the last statement of the code: WITH (score FLOAT) as p;
From syntax: MODEL
The MODEL parameter is used to specify the model used for scoring or prediction. The model is specified as a variable or a literal or a scalar expression.
Box 2: dbo.CustomerWithProductScore
Identify which customers should receive promotional emails based on their likelihood of purchasing promoted products.
Only table CustomerWithProductScore has the required filed score.
From the syntax:
DATA
The DATA parameter is used to specify the data used for scoring or prediction. Data is specified in the form of a table source in the query. Table source can be a table, table alias, CTE alias, view, or table-valued function.

Question 15 Selectable Answer

You have a Power BI Premium capacity.
You need to increase the number of virtual cores associated to the capacity.
Which role do you need?

A. Power Bl workspace admin
B. capacity admin
C. Power Platform admin
D. Power Bl admin

Answer:
Explanation:
Change capacity size
Power BI admins and global administrators can change Power BI Premium capacity. Capacity admins who are not a Power BI admin or global administrator don't have this option.
Reference: https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-premium-manage

Test Online Free Microsoft DP-500 Exam Questions and Answers