Question No : 1
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

Show Answers

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 No : 2
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

Show Answers

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 No : 3
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.

Show Answers

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 No : 4
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.

Show Answers

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 No : 5
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.

Show Answers

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 No : 6
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]

Show Answers

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 No : 7
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.

Show Answers

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 No : 8
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.

Show Answers

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 No : 9
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.

Show Answers

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 No : 10
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.

Show Answers

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.