PL-300

Question: 1 CertyIQ
HOTSPOT -
You plan to create the Power BI model shown in the exhibit. (Click the Exhibit tab.)

The data has the following refresh requirements:

✑ Customer must be refreshed daily.
✑ Date must be refreshed once every three years.
✑ Sales must be refreshed in near real time.
✑ SalesAggregate must be refreshed once per week.
You need to select the storage modes for the tables. The solution must meet the following requirements:
✑ Minimize the load times of visuals.
✑ Ensure that the data is loaded to the model based on the refresh requirements.
Which storage mode should you select for each table? To answer, select the appropriate options in the answer
area.
NOTE: Each correct selection is worth one point.
Hot Area:
 A.nsweriA.re·a

Din?ct Qu,e1ry
1

Du.al
Import

!Date: I I_•
0··1rectQu ,e ·y
Dual
Import

Sales: I
I•
0·· 1re ·ctQu ,ery
Du.al
Import

I•
o·ire·ctQu,e y
Dual
Import

Answer:
Explanation:

Box 1: Dual -

Customer should use the dual storage mode.

Dual: Tables with this setting can act as either cached or not cached, depending on the context of the query that's
submitted to the Power BI dataset. In some cases, you fulfill queries from cached data. In other cases, you
fulfill queries by executing an on-demand query to the data source.

Note: You set the Storage mode property to one of these three values: Import, DirectQuery, and Dual.

Box 2: Dual -

You can set the dimension tables (Customer, Geography, and Date) to Dual to reduce the number of limited
relationships in the dataset, and improve performance.
 Box 3: DirectQuery -

Sales should use the DirectQuery storage mode.

DirectQuery: Tables with this setting aren't cached. Queries that you submit to the Power BI dataset"for
example, DAX queries"and that return data from

DirectQuery tables can be fulfilled only by executing on-demand queries to the data source. Queries that you
submit to the data source use the query language for that data source, for example, SQL.

Box 4: Import -

Import: Imported tables with this setting are cached. Queries submitted to the Power BI dataset that return
data from Import tables can be fulfilled only from cached data.

Note:-

Dual (Composite) Mode:

The dual storage mode is between Import and DirectQuery. it is a hybrid approach, Like importing data, the
dual storage mode caches the data in the table. However, it leaves it up to Power BI to determine the best way
to query the table depending on the query context.

1) Sales Must be Refreshed in Near real time so "Direct Query"

2) Sales Aggregate is once per week so "Import" (performance also required)

3) Both Date and Customer has relationship with both Sales and SalesAggregate tables so "Dual"

because to support performance for DirectQuery(Sales) and Import(SalesAggregate)

Reference:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode

Question: 2

Microsoft Teams Personal Analytics

SQL Server database

Answer: C
Explanation:

Data sources in Power BI Desktop.

Power BI dataflows -
 Common Data Service (Legacy)

Dataverse -

You can use the Microsoft Power BI template to import data into Power BI from Project for the web and
Project Online. When you're using the template, you're connected to your Microsoft Dataverse instance,
where your Microsoft Project web app data is stored.

https://support.microsoft.com/en-us/office/use-power-bi-desktop-to-connect-with-your-project-data-
df4ccca1-68e9-418c-9d0f-022ac05249a2

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-sources

Question: 3 CertyIQ
For the sales department at your company, you publish a Power BI report that imports data from a Microsoft Excel
file located in a Microsoft SharePoint folder.
The data model contains several measures.
You need to create a Power BI report from the existing data. The solution must minimize development effort.
Which type of data source should you use?

A. Power BI dataset
B. a SharePoint folder
C. Power BI dataflows
D. an Excel workbook

Answer: A
Explanation:

Power BI dataset

because the case states there is already a report published and the datamodel contains measures. therefore
and to be able to use the measures in the datamodel you should connect to the existing dataset (which was
created when you plublished the report) instead of starting from scratch with the files in the SharePoint
folder.

Question: 4
You import two Microsoft Excel tables named Customer and Address into Power Query. Customer contains the

✑ Customer ID
✑ Customer Name

✑ Address ID
Address contains the following columns:
✑ Address ID
✑ Address Line 1
✑ Address Line 2
✑ City
✑ State/Region
✑ Country
✑ Postal Code
Each Customer ID represents a unique customer in the Customer table. Each Address ID represents a unique
address in the Address table.
You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country
for each customer.
What should you do?

A. Merge the Customer and Address tables.

B. Group the Customer and Address tables by the Address ID column.
C. Transpose the Customer and Address tables.
D. Append the Customer and Address tables.

Answer: A
Explanation:

Remember Merge is JOIN, APPEND is UNION

A merge queries operation joins two existing tables together based on matching values from one or multiple
columns. You can choose to use different types of joins, depending on the output you want.

Reference:

https://docs.microsoft.com/en-us/power-query/merge-queries-overview

Question: 5
HOTSPOT -

You need to combine the Customer tables into a single table. The solution must minimize the size of the data model

What should you do? To answer, select the appropriate options in the answer area.
 Answer:

Explanation:
Box 1: Append Queries as New -
When you have additional rows of data that you'd like to add to an existing query, you append the query.
There are two append options:
* Append queries as new displays the Append dialog box to create a new query by appending multiple tables.
* Append queries displays the Append dialog box to add additional tables to the current query.
Incorrect: When you have one or more columns that you'd like to add to another query, you merge the queries.
Box 2: Disable loading the query to the data model
By default, all queries from Query Editor will be loaded into the memory of Power BI Model. You can disable
the load for some queries, especially queries that used as intermediate transformation to produce the final
query for the model.
Disabling Load doesn't mean the query won't be refreshed, it only means the query won't be loaded into the
memory. When you click on Refresh model in Power
BI, or when a scheduled refresh happens even queries marked as Disable Load will be refreshed, but their
data will be used as intermediate source for other queries instead of loading directly into the model. This is a
very basic performance tuning tip, but very important when your Power BI model grows bigger and bigger.

Reference:
https://docs.microsoft.com/en-us/power-query/append-queries
https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up

Question: 6
DRAG DROP -
In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product.
Every Product has a ProductSubCategory.
Not every ProductsubCategory has a parent ProductCategory.
You need to merge the three queries into a single query. The solution must ensure the best performance in Power
Query.
How should you merge the tables? To answer, drag the appropriate merge types to the correct queries. Each
merge 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.
Select and Place:

Answer:

Explanation:
Box 1: Inner -
Every Product has a ProductSubCategory.
A standard join is needed.
One of the join kinds available in the Merge dialog box in Power Query is an inner join, which brings in only
matching rows from both the left and right tables.

Box 2: Left outer -

 Not every ProductsubCategory has a parent ProductCategory.

https://docs.microsoft.com/en-us/power-query/merge-queries-inner https://docs.microsoft.com/en-us/power-
query/merge-queries-left-outer

Question: 7 CertyIQ
You are building a Power BI report that uses data from an Azure SQL database named erp1.
You import the following tables.

You need to perform the following analyses:

✑ Orders sold over time that include a measure of the total order value
Orders by attributes of products sold
The solution must minimize update times when interacting with visuals in the report.
What should you do first?

A. From Power Query, merge the Order Line Items query and the Products query.
B. Create a calculated column that adds a list of product categories to the Orders table by using a DAX
function.
C. Calculate the count of orders per product by using a DAX function.
D. From Power Query, merge the Orders query and the Order Line Items query.

Answer: D
Explanation:

D. It's the Header/Detail Schema, and the most optimal way is to flatten the header into the detail table.

Source:

https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/

GPT: Merging the Orders query and the Order Line Items query in Power Query will allow you to create a
single query that combines the necessary data from the different tables. This will make it easier and more
efficient to perform the required analyses, as you will have all the information you need in one place.

--- PBI will do the best aggregation base on Star Schema model, we now have 1 Fact table (Order Line Items)
and 2 Dim tables (Products, Orders). Orders has common field with Products (ProductID), and pretty sure time
series field (OrderDate); Orders Line Items has Price and Quanity.

--- We need summarize some values like "price" and "quantity" over-time by attributes product. But we only
have common field in Dim table (Orders) so we need to merge Dim (Orders) and Fact (Order Line Items) to new
single Fact table to design the right Star Schema model.

=> So that D is correct

Question: 8 CertyIQ
You have a Microsoft SharePoint Online site that contains several document libraries.
One of the document libraries contains manufacturing reports saved as Microsoft Excel files. All the
manufacturing reports have the same data structure.
You need to use Power BI Desktop to load only the manufacturing reports to a table for analysis.
What should you do?

A. Get data from a SharePoint folder and enter the site URL Select Transform, then filter by the folder path to
the manufacturing reports library.
B. Get data from a SharePoint list and enter the site URL. Select Combine & Transform, then filter by the folder
path to the manufacturing reports library.
C. Get data from a SharePoint folder, enter the site URL, and then select Combine & Load.
D. Get data from a SharePoint list, enter the site URL, and then select Combine & Load.

Answer: A
Explanation:

Get Data from SharePoint folder + select Combine & Load to load the data from all of the files in the
SharePoint folder directly into your app.

Note: Connect to a SharePoint folder from Power Query Desktop

To connect to a SharePoint folder:

1. From Get Data, select SharePoint folder.

2. Paste the SharePoint site URL you copied in Determine the site URL to the Site URL text box in the
SharePoint folder dialog box. In this example, the site URL is https://contoso.sharepoint.com/marketing/data.
If the site URL you enter is invalid, a warning icon. warning icon will appear next to the URL text box.

SharePoint folder selection.

3. Select OK to continue.

4. If this is the first time you've visited this site address, select the appropriate authentication method. Enter
your credentials and choose which level to apply these settings to. Then select Connect.

5. When you select the SharePoint folder you want to use, the file information about all of the files in that
SharePoint folder are displayed. In addition, file information about any files in any subfolders is also displayed.

6. Select Combine & Transform Data to combine the data in the files of the selected SharePoint folder and
load the data into the Power Query Editor for editing. Or select Combine & Load to load the data from all of
the files in the SharePoint folder directly into your app.

Reference:

https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder

Question: 9
DRAG DROP -
You have a Microsoft Excel workbook that contains two sheets named Sheet1 and Sheet2.
Sheet2 contains the following table named Table2.

You need to use Power Query Editor to combine the products from Table1 and Table2 into the following table that
has one column containing no duplicate values.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct order.
Select and Place:
 Answer:

Explanation:

Remove Duplicates from the table appended to (Table1)

Reference:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data

Question: 10 CertyIQ
You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the
date and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.
You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.
What should you do?

A. Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the
new column to Date.
B. Change the data type of the Logged column to Date.
C. Split the Logged column by using at as the delimiter.
D. Apply a transformation to extract the first 11 characters of the Logged column.

Answer: C
Explanation:

You should split the Logged column by using "at" as the delimiter. This will allow you to separate the date and
time into separate columns, which will enable you to analyze the complaints by date and use a built-in date
hierarchy. Alternatively, you could also use a transformation to extract the date and time from the Logged
column and set the data type of the new columns to Date and Time, respectively. Option A is incorrect
because it only extracts the last 11 characters of the Logged column, which would not include the date. Option
B is incorrect because the data in the Logged column is in a non-standard date format and cannot be directly
converted to the Date data type. Option D is incorrect because it only extracts the first 11 characters of the
 Logged column, which would not include the time.

Question: 11
You have a Microsoft Excel file in a Microsoft OneDrive folder.

Which two connectors can you use to connect to the file? Each correct answer presents a complete solution.

SharePoint

Answer: DE

Explanation:

A, B, C: wrong! Would work technically, but the connection will be only to the local copy of the file, no refresh

E: correct, this is the best option to import from OneDrive

Question: 12
HOTSPOT -

You have a table named Reports that contains a column named State. The distribution and quality data metrics for

Use the drop-down menus to select the answer choice that completes each statement based on the information
Answer:

Explanation:
Box 1: 69 -
69 distinct/different values.
Note: Column Distribution allows you to get a sense for the overall distribution of values within a column in
your data previews, including the count of distinct values (total number of different values found in a given column)
and unique values (total number of values that only appear once in a given column).

Box 2: 4 -

Reference:
https://systemmanagement.ro/2018/10/16/power-bi-data-profiling-distinct-vs-unique/
Question: 13 CertyIQ
HOTSPOT -
You have two CSV files named Products and Categories.
The Products file contains the following columns:
✑ ProductID
✑ ProductName
✑ SupplierID
✑ CategoryID
The Categories file contains the following columns:
✑ CategoryID
✑ CategoryName
✑ CategoryDescription
From Power BI Desktop, you import the files into Power Query Editor.
You need to create a Power BI dataset that will contain a single table named Product. The Product will table
includes the following columns:
✑ ProductID
✑ ProductName
✑ SupplierID
✑ CategoryID
✑ CategoryName
✑ CategoryDescription
How should you combine the queries, and what should you do on the Categories query? To answer, select the
appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:

Explanation:
Box 1: Merge -
There are two primary ways of combining queries: merging and appending.
* When you have one or more columns that you'd like to add to another query, you merge the queries.
 * When you have additional rows of data that you'd like to add to an existing query, you append the query.

Box 2: Disable the query load -

Managing loading of queries -

In many situations, it makes sense to break down your data transformations in multiple queries. One popular
example is merging where you merge two queries into one to essentially do a join. In this type of situations,
some queries are not relevant to load into Desktop as they are intermediate steps, while they are still required
for your data transformations to work correctly. For these queries, you can make sure they are not loaded in
Desktop by un-checking 'Enable load' in the context menu of the query in Desktop or in the Properties screen:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data https://docs.micr

Question: 14 CertyIQ
You have an Azure SQL database that contains sales transactions. The database is updated frequently.
You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five
minutes of an update.
How should you configure the data connection?

A. Add a SQL statement.

B. Set the Command timeout in minutes setting.
C. Set Data Connectivity mode to Import.
D. Set Data Connectivity mode to DirectQuery.

Answer: D

Explanation:
DirectQuery: No data is imported or copied into Power BI Desktop. For relational sources, the selected tables
and columns appear in the Fields list. For multi- dimensional sources like SAP Business Warehouse, the
dimensions and measures of the selected cube appear in the Fields list. As you create or interact with a
visualization, Power BI Desktop queries the underlying data source, so you're always viewing current data.

Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

Question: 15
DRAG DROP -

Which three actions should you perform in sequence. To answer, move the appropriate actions from the list of
 Answer:

Explanation:

1. Get data and select folder

2. Remove the content column

3. Expand the attributes column

You'll have only metadata of the files remaining.

Question: 16 CertyIQ
A business intelligence (BI) developer creates a dataflow in Power BI that uses DirectQuery to access tables from
an on-premises Microsoft SQL server. The
Enhanced Dataflows Compute Engine is turned on for the dataflow.
You need to use the dataflow in a report. The solution must meet the following requirements:
✑ Minimize online processing operations.
✑ Minimize calculation times and render times for visuals.
✑ Include data from the current year, up to and including the previous day.
What should you do?

A. Create a dataflows connection that has DirectQuery mode selected.

B. Create a dataflows connection that has DirectQuery mode selected and configure a gateway connection for
the dataset.
C. Create a dataflows connection that has Import mode selected and schedule a daily refresh.
D. Create a dataflows connection that has Import mode selected and create a Microsoft Power Automate
solution to refresh the data hourly.

Answer: C
Explanation:
 https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-local-drive

Question: 17 CertyIQ
DRAG DROP
-

You publish a dataset that contains data from an on-premises Microsoft SQL Server database.

The dataset must be refreshed daily.

You need to ensure that the Power BI service can connect to the database and refresh the dataset.

Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct order.

Answer:
Question: 18
You attempt to connect Power BI Desktop to a Cassandra database.

From the Get Data connector list, you discover that there is no specific connector for the Cassandra database.

Which type of connector should you choose?

ODBC
OLE DB

Answer: B
Explanation:

B is Correct because, B´cause it allows you to connect to data sources that aren't identified in the Get Data

List details a few examples of data sources to which Power BI Desktop can connect by using the generic

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-connect-using-generic-interfaces
Question: 19 CertyIQ
DRAG DROP
-

You receive annual sales data that must be included in Power BI reports.

From Power Query Editor, you connect to the Microsoft Excel source shown in the following exhibit.

You need to create a report that meets the following requirements:

• Visualizes the Sales value over a period of years and months

• Adds a slicer for the month
• Adds a slicer for the year

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct order.

Answer:
 Sefeot the Month and Ir· onthNumber
corumns_

Selled Unpivot.other column ·_

ar·•• the Value oolurnnas Sales.

Question: 20 CertyIQ
HOTSPOT
-

You are using Power BI Desktop to connect to an Azure SQL database.

The connection is configured 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 solution is worth one point.

Answer:
 Explanation:

The defaut time out is 10 minutes, but if it takes more than it you can enter another value in minutes to keep
the connection open longer.

minutes

https://learn.microsoft.com/en-us/power-query/connectors/azuresqldatabase

Question: 21 CertyIQ
HOTSPOT

You plan to build a single PBIX file to meet the following requirements:

Data must be consumed from the database that corresponds to each stage of the development lifecycle.

The solution must minimize administrative effort.

What should you do? To answer, select the appropriate options in the answer area.
 Answer Area

Create: T

01ne param1ete,r
1

Two parameters
Three 1para11meters

Parameter type: T

Te,xt
r·ru1e/False
Dec:imal 1num11ber

Answer:

@n,e·IP,ara,meter]
Two,ip,aram,eter-s
Thr,e,e para1met,ers

Para1met,er type:
l[Te·xt]
I True/FaIIs,e
Decimal numlber

Explanation:

To meet the requirements specified, we can use a single parameter in the PBIX file that controls which
database is used for data consumption based on the stage of the development lifecycle.
 We can use a Text parameter type in Power BI to achieve this. The parameter can be used to switch between
the different database connections when a user interacts with the report. The text parameter could include
values such as "Development", "Staging", and "Production", which correspond to the different databases
shown in the table.

the development lifecycle. By using a single parameter, we can minimize administrative effort and ensure that

Question: 22 CertyIQ
You are creating a query to be used as a Country dimension in a star schema.

A snapshot of the source data is shown in the following table.

You need to create the dimension. The dimension must contain a list of unique countries.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

A. Delete the Country column.

B. Remove duplicates from the table.
C. Remove duplicates from the City column.
D. Delete the City column.
E. Remove duplicates from the Country column.

Answer: DE

Question: 23
DRAG DROP

You use Power Query Editor to preview the data shown in the following exhibit.
You need to clean and transform the query so that all the rows of data are maintained, and error values in the
discount column are replaced with a discount of 0.05. The solution must minimize administrative effort.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct order.

Answer:
Question: 24 CertyIQ
HOTSPOT
-

You attempt to use Power Query Editor to create a custom column and receive the error message 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:

Question: 25

You load a data extract that includes a free text field named coll.
You need to analyze the frequency distribution of the string lengths in col1. The solution must not affect the size of
the model.
What should you do?

A. In the report, add a DAX calculated column that calculates the length of col1
B. In the report, add a DAX function that calculates the average length of col1
C. From Power Query Editor, add a column that calculates the length of col1
D. From Power Query Editor, change the distribution for the Column profile to group by length for col1

Answer: D

Explanation:

A will affect the size of the model as would C.

B doesn't give you enough information about the distribution (just the average)

D is the right answer.

1. Power Query Editor -> View -> Enable Column Profile

2. Select three dots (top left corner) in the profile pane appear at the bottom of the Query Editor window.

3. Group By -> Text length

Question: 26 CertyIQ
You have a collection of reports for the HR department of your company. The datasets use row-level security
(RLS). The company has multiple sales regions.
Each sales region has an HR manager.
You need to ensure that the HR managers can interact with the data from their region only. The HR managers must
be prevented from changing the layout of the reports.
How should you provision access to the reports for the HR managers?

A. Publish the reports in an app and grant the HR managers access permission.
B. Create a new workspace, copy the datasets and reports, and add the HR managers as members of the
workspace.
C. Publish the reports to a different workspace other than the one hosting the datasets.
D. Add the HR managers as members of the existing workspace that hosts the reports and the datasets.

Answer: A
Explanation:

correct ans looks as A because in the Power BI service, members of a workspace have access to datasets in
the workspace. RLS doesn't restrict this data access. and RLS is used to restrict access to data not to layout
of the report. Members are allowed to change the report layout.

Reference:

https://kunaltripathy.com/2021/10/06/bring-your-power-bi-to-power-apps-portal-part-ii/

Question: 27
You need to provide a user with the ability to add members to a workspace. The solution must use the principle of
Which role should you assign to the user?

Answer: D
Explanation:
Member role allows adding members or other with lower permissions to the workspace.

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-roles-new-workspaces

Question: 28
You have a Power BI query named Sales that imports the columns shown in the following table.

Users only use the date part of the Sales_Date field. Only rows with a Status of Finished are used in analysis.

Remove the rows in which Sales[Status] has a value of Canceled.

Remove Sales[Sales_Date].
Change the data type of Sale[Delivery_Time] to Integer.
Split Sales[Sale_Date] into separate date and time columns.
 E. Remove Sales[Canceled Date].

Answer: AD
Explanation:

D: Splitting the Sales_Date column will make comparisons on the Sales date faster.

Question: 29 CertyIQ
You build a report to analyze customer transactions from a database that contains the tables shown in the
following table.

You import the tables.

Which relationship should you use to link the tables?

A. one-to-many from Transaction to Customer

B. one-to-one between Customer and Transaction
C. many-to-many between Customer and Transaction
D. one-to-many from Customer to Transaction

Answer: D
Explanation:
One on the primary Key side (customer table), many on the foreign key side (Transaction table) of the relation.
Question: 30 CertyIQ
You have a custom connector that returns ID, From, To, Subject, Body, and Has Attachments for every email sent
during the past year. More than 10 million records are returned.
You build a report analyzing the internal networks of employees based on whom they send emails to.
You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size.
What should you do?

A. From Model view, set the Subject and Body columns to Hidden.
B. Remove the Subject and Body columns during the import.
C. Implement row-level security (RLS) so that the report recipients can only see results based on the emails
they sent.

Answer: B
Explanation:

"prevent report recipients from reading the analyzed emails"

The Subject and the Body are not needed in the report. Dropping them resolves the security problem and
minimizes the model.

Question: 31 CertyIQ
HOTSPOT -
You create a Power BI dataset that contains the table shown in the following exhibit.

You need to make the table available as an organizational data type in Microsoft Excel.
How should you configure the properties of the table? To answer, select the appropriate options in the answer
area.
NOTE: Each correct selection is worth one point.
Hot Area:
 I,.-
Cost C e,1nt,er
1

Headc 0 n 1 1

ID
Name

Co st C · ·,1nter
1 1
I
•
Headc 0,unt 1

ID
Name

Is featured tailble:
No
•
Yes

Answer:
Explanation:

Box 1:Row label: Name

See: https://www.myonlinetraininghub.com/power-bi-organizational-data-types-in-
excel#:~:text=Power%20BI%20Organizational%20Data%20Types%20in%20Excel%20allow%20you%20to,compa

Box 2: ID -

The Key column field value provides the unique ID for the row. This value enables Excel to link a cell to a
specific row in the table.

Box 3: Yes -

In the Data Types Gallery in Excel, your users can find data from featured tables in your Power BI datasets.

Reference:

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-create-excel-featured-tables
Question: 32 CertyIQ
You have the Power BI model shown in the following exhibit.

A manager can represent only a single country.

You need to use row-level security (RLS) to meet the following requirements:
✑ The managers must only see the data of their respective country.
✑ The number of RLS roles must be minimized.
Which two actions should you perform? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. Create a single role that filters Country[Manager_Email] by using the USERNAME DAX function.
B. Create a single role that filters Country[Manager_Email] by using the USEROBJECTID DAX function.
C. For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions.
D. Create one role for each country.
E. For the relationship between Purchase and Purchase Detail, change the Cross filter direction to Single.

Answer: AC
Explanation:

A: You can take advantage of the DAX functions username() or userprincipalname() within your dataset. You
can use them within expressions in Power BI

Desktop. When you publish your model, it will be used within the Power BI service.

Note: To define security roles, follow these steps.

Import data into your Power BI Desktop report, or configure a DirectQuery connection.

1. From the Modeling tab, select Manage Roles.

2. From the Manage roles window, select Create.

3. Under Roles, provide a name for the role.

4. Under Tables, select the table to which you want to apply a DAX rule.

5. In the Table filter DAX expression box, enter the DAX expressions. This expression returns a value of true or
false. For example: [Entity ID] = Value.

6. After you've created the DAX expression, select the checkmark above the expression box to validate the
expression.

Note: You can use username() within this expression.

 https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

Question: 33 CertyIQ
HOTSPOT -
You have a Power BI imported dataset that contains the data model 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.
Hot Area:
Answer:

Explanation:

Box 1: cross filter direction -

As the answer correctly states "Assume Referential Integrity" only works for direct query connections.

Box 2: Star schema -

Star schema is a mature modeling approach widely adopted by relational data warehouses. It requires
modelers to classify their model tables as either dimension or fact.

Generally, dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can
contain a very large number of rows and continue to grow over time.

Example:
 https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-assume-referential-integrity
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Question: 34
HOTSPOT -

You need to create a measure that calculates the total sales from the equivalent month of the previous year.
How should you complete the calculation? To answer, select the appropriate options in the answer area.
Sale Prev·o 1s Yea
._,
•

E,VA 1· ATE
SUM
SUMX
[To·, .a Sales],

AMEPERIODL 1ASTYEAR

T
[Dal e]
·,Date'· Dat • e]
'Da ·e ' Month]
• 1 .,1

)
)

Answer:
 Sale, P· ev • o,us Yea

E,VALUAT1•
SUM
•. OMX

T
I-
P 'ARJ.\LLELPERI
[SAMEPERIODLASTYEAR

)
)
Explanation:

CALCULATE

SAMEPERIODLASTYEAR

'DATE'[DATE]

Box 1: CALCULATE -

Box 2: SAMEPERIODLASTYEAR

accepts a data column, Month will usually be either text (Jan) or Integer (1). so: CALCULATE([Total Sales],
 SAMEPERIODLASTYEAR('Date'[Date]))

Box 3: 'DATE' [DATE]

https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax https://docs.microsoft.com/en-
us/dax/sameperiodlastyear-function-dax

Question: 35 CertyIQ
DRAG DROP -
You plan to create a report that will display sales data from the last year for multiple regions.
You need to restrict access to individual rows of the data on a per region-basis by using roles.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct order.
Select and Place:

Answer:

Explanation:

With respect, you can not assign users to a role until AFTER the report has been published to the Power BI
Service. Those posting that you create the role and then assign users to the role BEFORE publishing are
incorrect. Roles are created in Power BI Desktop. Desktop does not have any way to assign users to the roles.
They are empty when created. Role assignment happens in the service.

Publish the report to the Power BI service. Go to your Workspace, using the Dataset, select the More Options
menu(...) and click Security. This is where the Roles are populated.

1) Import your data into Power BI Desktop

2) Create the role definition (on the Modeling tab)

3) Publish the report to the Power BI service

4) Assign users to the role

Question: 36
DRAG DROP -
You create a data model in Power BI.
Report developers and users provide feedback that the data model is too complex.
The model contains the following tables.

The model has the following relationships:

✑ There is a one-to-one relationship between Sales_Region and Region_Manager.
✑ There are more records in Manager than in Region_Manager, but every record in Region_Manager has a
corresponding record in Manager.
✑ There are more records in Sales_Manager than in Sales_Region, but every record in Sales_Region has a
corresponding record in Sales_Manager.
You need to denormalize the model into a single table. Only managers who are associated to a sales region must
be included in the reports.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you
select.
Select and Place:

Answer:
 Explanation:

1.Merge [Region_Manager] and [Manager] by using an inner join.

3.Merge [Sales_Region] and [Sales_Manager] by using an inner join.

6.Merge [Sales_Region] and [Region_Manager] by using an inner join.

Question: 37 CertyIQ
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App
workspace in shared capacity of powerbi.com.
The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The
dataset is scheduled to refresh twice a day at 08:00 and 17:00.
The report is a single page that contains 15 AppSource visuals and 10 default visuals.
Users say that the report is slow to load the visuals when they access and interact with the report.
You need to recommend a solution to improve the performance of the report.
What should you recommend?

A. Change any DAX measures to use iterator functions.

B. Enable visual interactions.
C. Replace the default visuals with AppSource visuals.
D. Split the visuals onto multiple pages.

Answer: D
Explanation:
One page with many visuals may also make your report loading slow. Please appropriately reduce the number
of visualizations on one page.

Reference:
https://community.powerbi.com/t5/Desktop/Visuals-are-loading-extremely-slow/td-p/1565668

Question: 38
HOTSPOT -
You are creating a Microsoft Power BI imported data model to perform basket analysis. The goal of the analysis is
to identify which products are usually bought together in the same transaction across and within sales territories.
You import a fact table named Sales as shown in the exhibit. (Click the Exhibit tab.)

The related dimension tables are imported into the model.

You are evaluating how to optimize the model.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:
 Explanation:
Box 1: Yes -
Those two columns not need in the analysis.

Box 2: No -

Tax charged not relevant for the analysis.

Question: 39 CertyIQ
You have a Microsoft Power BI data model that contains three tables named Orders, Date, and City. There is a one-
to-many relationship between Date and
Orders and between City and Orders.
The model contains two row-level security (RLS) roles named Role1 and Role2. Role1 contains the following filter.
City[State Province] = "Kentucky"
Role2 contains the following filter.

Date[Calendar Year] = 2020 -

If a user is a member of both Role1 and Role2, what data will they see in a report that uses the model?

A. The user will see data for which the State Province value is Kentucky or where the Calendar Year is 2020.
B. The user will receive an error and will not be able to see the data in the report.
C. The user will only see data for which the State Province value is Kentucky.
D. The user will only see data for which the State Province value is Kentucky and the Calendar Year is 2020.

Answer: A
Explanation:

A, from the Microsoft documentation (https://docs.microsoft.com/en-us/power-bi/guidance/rls-guidance):

"When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see
table rows that represent the union of those filters."

This means that you would see all data where either Role1 OR Role2 applies, so the answer is A not D.

Example from MS Learn linked below:

https://learn.microsoft.com/en-us/power-bi/guidance/rls-guidance

"Consider a model with two roles: The first role, named Workers, restricts access to all Payroll table rows by
using the following rule expression:

DAX:

FALSE()

A rule will return no table rows when its expression evaluates to false.

Yet, a second role, named Managers, allows access to all Payroll table rows by using the following rule
expression:

DAX:

TRUE()
 Take care: Should a report user map to both roles, they'll see all Payroll table rows."

Question: 40 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table
named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: From Power Query Editor, you import the table and then add a filter step to the query. Does
this meet the goal?

A. Yes
B. No

Answer: B
Explanation:
This would load the entire table in the first step.
Instead: You add a WHERE clause to the SQL statement.

Reference:
https://docs.microsoft.com/en-us/power-query/native-database-query

Question: 41 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table
named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: You write a DAX expression that uses the FILTER function.
Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:

Instead: You add a WHERE clause to the SQL statement.

Note: DAX is not a language designed to fetch the data like SQL rather than used for data analysis purposes.
It is always a better and recommended approach to transform the data as close to the data source itself. For
example, your data source is a relational database; then, it's better to go with T -SQL.
 https://www.learndax.com/dax-vs-sql-when-to-use-dax-over-sql/

Question: 42 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question i n the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table
named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: You add a WHERE clause to the SQL statement.
Does this meet the goal?

A. Yes
B. No

Answer: A
Explanation:
Power Query enables you to specify your native database query in a text box under Advanced options when
connecting to a database. In the example below, you'll import data from a SQL Server database using a native
database query entered in the SQL statement text box.
1. Connect to a SQL Server database using Power Query. Select the SQL Server database option in the
connector selection.
2. In the SQL Server database popup window:
3. Specify the Server and Database where you want to import data from using native database query.
4. Under Advanced options, select the SQL statement field and paste or enter your native database query,
then select OK.
 https://docs.microsoft.com/en-us/power-query/native-database-query

Question: 43
DRAG DROP -

You need to prepare the data to support the following:

✑
✑
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of
 Answer:

Explanation:

1. Use first row as header

2. Unpivot all columns other than "Measure"

3. Rename "Attribute" to "Year"

4. Change data type of "Year" to date (Date > Year)

Reference:

https://docs.microsoft.com/en-us/power-query/unpivot-column

Question: 44 CertyIQ
HOTSPOT -
You are creating an analytics report that will consume data from the tables shown in the following table.
There is a relationship between the tables.
There are no reporting requirements on employee_id and employee_photo.
You need to optimize the data model.
What should you configure for employee_id and employee_photo? To answer, select the appropriate options in the
answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Answer:

Explanation:
Box 1: Hide -
Need in the relation, so cannot delete it.

Box 2: Delete -

Reference:
https://community.powerbi.com/t5/Desktop/How-to-Hide-a-Column-in-power-Bi/m-p/414470

Question: 45
HOTSPOT -

View1 contains the columns shown in the following table.

View2 contains the columns shown in the following table.

The views can be related based on the Class ID column.
Class ID is the unique identifier for the specified class, period, teacher, and school year. For example, the same
class can be taught by the same teacher during two different periods, but the class will have a different class ID.
You need to design a star schema data model by using the data in both views. The solution must facilitate the
following analysis:
✑ The count of classes that occur by period
✑ The count of students in attendance by period by day
✑ The average number of students attending a class each month
In which table should you include the Teacher First Name and Period Number fields? To answer, select the
appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Answer:

Explanation:

Box 1: Teacher Dimension-

Box 2: Class Dimension-

teacher's dim and class dim because teacher name and period number are static information that are directly
related to the keys (teacher ID and class ID) so they belong in the relevant dimension tables. Since the "Class
ID is unique for the class, period, teacher and school year" this information should be included in the class
dimension table and not repeated for each student's attendance to keep your model as small as possible and
to avoid mistakes.

Reference:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Question: 46
There are four departments in the Departments table.
You need to ensure that users can see the data of their respective department only.
What should you do?

A. Create a slicer that filters Departments based on DepartmentID.

B. Create a row-level security (RLS) role for each department, and then define the membership of the role.
C. Create a DepartmentID parameter to filter the Departments table.
D. To the ConfidentialData table, add a calculated measure that uses the CURRENTGROUP DAX function.

Answer: B
Explanation:

Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data
access at the row level, and you can define filters within roles.

Reference:

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

Question: 47 CertyIQ
In Power BI Desktop, you are building a sales report that contains two tables. Both tables have row-level security
(RLS) configured.
You need to create a relationship between the tables. The solution must ensure that bidirectional cross-filtering
honors the RLS settings.
What should you do?

A. Create an inactive relationship between the tables and select Apply security filter in both directions.
B. Create an active relationship between the tables and select Apply security filter in both directions.
C. Create an inactive relationship between the tables and select Assume referential integrity.
D. Create an active relationship between the tables and select Assume referential integrity.

Answer: B
Explanation:
By default, row-level security filtering uses single-directional filters, whether the relationships are set to
single direction or bi-directional. You can manually enable bi-directional cross-filtering with row-level security
by selecting the relationship and checking the Apply security filter in both directions checkbox. Select this
 option when you've also implemented dynamic row-level security at the server level, where row-level security

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

Question: 48 CertyIQ
HOTSPOT -
You have a column named UnitsInStock as shown in the following exhibit.
UnitsInStock has 75 non-null values, of which 51 are unique.
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.
Hot Area:
Answer:

Explanation:

Box 1: 75 rows -

Is nullable allows NULL values in the column.

Box 2: reduce -

We're not dealing with a matric here, we're dealing with a simple table. In simple tables values that occur
more than once won't be shown in the rows multiple times. Since you're they tell you you have 51 unique
values (and the other ones aren't null values) you can be sure it's more than 51. Since you'll already have 51
rows of unique values.

So the first is answer is 75.

Furthermore, when you add another table, change the sign to summarize, you will add up all the values of the
51 unique values and all the rest. Which means you will get one single row, displaying the sum of all these
values.

Therefore, the second answer is reduce.

Reference:

https://blog.crossjoin.co.uk/2019/01/20/is-nullable-column-property-power-bi/
Question: 49 CertyIQ
HOTSPOT -
You have a Power BI report.
You have the following tables.

You have the following DAX measure.

Accounts :=
CALCULATE (
DISTINCTCOUNT (Balances[AccountID]),
LASTDATE ('Date'[Date])
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:

Explanation:
Box 1: No -
It will show the total number of accounts that were live at the last day of the year only.
Note:
DISTINCTCOUNT counts the number of distinct values in a column.
LASTDATE returns the last date in the current context for the specified column of dates.
 Box 2: No -

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax https://docs.microsoft.com/en-us/dax/lastd
ate-function-dax

Question: 50 CertyIQ
You have the tables shown in the following table.

The Impressions table contains approximately 30 million records per month.

You need to create an ad analytics system to meet the following requirements:
✑ Present ad impression counts for the day, campaign, and site_name. The analytics for the last year are required.
Minimize the data model size.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Create one-to-many relationships between the tables.

B. Group the Impressions query in Power Query by Ad_id, Site_name, and Impression_date. Aggregate by using
the CountRows function.
C. Create a calculated table that contains Ad_id, Site_name, and Impression_date.
D. Create a calculated measure that aggregates by using the COUNTROWS function.

Answer: AB
Explanation:
Incorrect:
Not C: A calculated table would increase the data model size.
Not D: Need Impression_date etc.
Question: 51 CertyIQ
HOTSPOT -
You are creating a Microsoft Power BI data model that has the tables shown in the following table.

The Products table is related to the ProductCategory table through the ProductCategoryID column. Each product
has one product category.
You need to ensure that you can analyze sales by product category.
How should you configure the relationship from ProductCategory to Products? To answer, select the appropriate
options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:
 Explanation:

One-to-many because several products have the same product category. Single because the performance is
much better and the assignment states only that you need to be able to analyze sales by product category.

Box 1: One-to-many -

The one-to-many and many-to-one cardinality options are essentially the same, and they're also the most
common cardinality types.

Incorrect: A many-to-many relationship means both columns can contain duplicate values. This cardinality
type is infrequently used. It's typically useful when designing complex model requirements. You can use it to
relate many-to-many facts or to relate higher grain facts. For example, when sales target facts are stored at
product category level and the product dimension table is stored at product level.

Box 2: Single -

Incorrect:

Bear in mind that bi-directional relationships can impact negatively on performance. Further, attempting to
configure a bi-directional relationship could result in ambiguous filter propagation paths. In this case, Power
BI Desktop may fail to commit the relationship change and will alert you with an error message.

Reference:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

Question: 52
You import a Power BI dataset that contains the following tables:
Date
✑ Product
✑ Product Inventory
The Product Inventory table contains 25 million rows. A sample of the data is shown in the following table.

The Product Inventory table relates to the Date table by using the DateKey column. The Product Inventory table
relates to the Product table by using the
ProductKey column.
You need to reduce the size of the data model without losing information.
What should you do?

A. Change Summarization for DateKey to Don't Summarize.

B. Remove the relationship between Date and Product Inventory
C. Change the data type of UnitCost to Integer.
D. Remove MovementDate.

Answer: D
Explanation:

The DateKey and MovementDate columns have the same information. Movementdate can be removed.

D, because the best way to reduce the data model size is to remove the unnecessary column.

Incorrect:

Not C: Integer data type would lose data.

Question: 53
HOTSPOT -
You are enhancing a Power BI model that has DAX calculations.
You need to create a measure that returns the year-to-date total sales from the same date of the previous

Which DAX functions should you use? To answer, select the appropriate options in the answer area.
 AnswerA, a

Saile:s PYTD "'

VAR star-tyear- •

STAR:TOFYi R ( PRWIOU iA ( 'C I ndar [D c] ) )

VAR e:nddaite •

L.ASTDATE ( sa1-s[Da e] ) - 365

RETURN

CALCULATE{
DATES'BETWEEN(
SAMEPERIODLASlYEAR {

( Sales[sales] )
CAlCULATE
DATES'BETWEEN
S.AJMEPERIDDLASTYEAR
SUM
( 'Celendar'[Di,te], !itartyear, enddlate)
CAlCULATE
DATES'BETWEEN
SAMEPERIODLASTYEAR
SUM

Answer:
Explanation:

Box 1: CALCULATE -

Example:

Total sales on the last selected date =

CALCULATE (

SUM ( Sales[Sales Amount] ),

'Sales'[OrderDateKey] = MAX ( 'Sales'[OrderDateKey] )

Box 2: SUM -

Box 3: DatesBetween

This is due to the expected parameters. DatesBetween expects two parameters as per the exhibit,
SamePeriodLastYear expects one parameter (but two are used in the exhibit)

Reference:

https://docs.microsoft.com/en-us/dax/calculate-function-dax

https://dax.guide/sameperiodlastyear/
Question: 54 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table
named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: You add a report-level filter that filters based on the order date.
Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:

You want the raw data, not a report with the data.

Instead add a WHERE clause to the SQL statement.

Reference:

https://docs.microsoft.com/en-us/power-query/native-database-query

Question: 55 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source.
The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: For each date foreign key, you add inactive relationships between the sales table and the date table.
Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:

Instead: Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to
create Order Date and Delivery Date as calculated tables.

You can reference an inactive relationship whit DAX function USERELATIONSHIP(), but using DAX is not
mentioned here.

So follow this refactory methodology:

 Create a copy of the role-playing table, providing it with a name that reflects its role. If it's an Import table, we
recommend defining a calculated table. If it's a DirectQuery table, you can duplicate the Power Query query.

Source: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Reference:

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Question: 56 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source.
The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query
twice to make the queries for Order Date and
Delivery Date.
Does this meet the goal?

A. Yes
B. No

Answer: A

Explanation:
1. It's not going to be great solution from the performance side...but that's not part of the requirements
2. Answer is YES.That's not the best solution regarding the performance but it's not the subject.

Question: 57 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source.
The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order
Date and Delivery Date as calculated tables.
Does this meet the goal?

A. Yes
B. No

Answer: A
 Explanation:
Refactoring methodology -
Here's a methodology to refactor a model from a single role-playing dimension-type table, to a design with
one table per role.
1. Remove any inactive relationships.
2. Consider renaming the role-playing dimension-type table to better describe its role. In the example (not
present here), the Airport table is related to the
ArrivalAirport column of the Flight table, so it's renamed as Arrival Airport.
3. Create a copy of the role-playing table, providing it with a name that reflects its role. If it's an Import table,
we recommend defining a calculated table. If it's a
DirectQuery table, you can duplicate the Power Query query.
In the example, the Departure Airport table was created by using the following calculated table definition.
Departure Airport = 'Arrival Airport'
Create an active relationship to relate the new table.
4. Consider renaming the columns in the tables so they accurately reflect their role. In the example, all
columns are prefixed with the word Departure or Arrival.
These names ensure report visuals, by default, will have self-describing and non-ambiguous labels. It also
improves the Q&A experience, allowing users to easily write their questions.
5. Consider adding descriptions to role-playing tables. (In the Fields pane, a description appears in a tooltip
when a report author hovers their cursor over the table.) This way, you can communicate any additional filter
propagation details to your report authors.

Reference:
https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Question: 58 CertyIQ
DRAG DROP -
You receive revenue data that must be included in Microsoft Power BI reports.

You plan to create several visuals from the data, including a visual that shows revenue split by year and product.
You need to transform the data to ensure that you can build the visuals. The solution must ensure that the columns

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of
 Answer:

Explanation:

Correct Sequence = 2>3>4

Select Department and Product and Unpivot Other Column

Rename the Attribute column to YEAR and the Value column to REVENUE

Question: 59 CertyIQ
HOTSPOT -
You have a Power BI report named Orders that supports the following analysis:
✑ Total sales over time
✑ The count of orders over time
✑ New and repeat customer counts
The data model size is nearing the limit for a dataset in shared capacity.
The model view for the dataset is shown in the following exhibit.

The data view for the Orders table is shown in the following exhibit.

The Orders table relates to the Customers table by using the CustomerID column.
The Orders table relates to the Date table by using the OrderDate column.
For each of the following statements, select Yes if the statement is true, Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:
 Answer:

Explanation:
Box 1: No -
Would not support total sales over time.

Box 2: No -
Would not support new and repeat customer counts
Box 3: Yes

Question: 60
HOTSPOT -

You have a table named financials that contains a column named Date and a column named Sales.
You need to create a measure that calculates the relative change in sales as compared to the previous quarter.
How should you complete the measure? To answer, select the appropriate options in the answer area.
Answer Area

IF(

1Sfl!LTEAED( 'fi'nandal.s' (Date]),

1:RROR("Uh oh.• )J

V R PREV_QUARTER .,

CALCULATE
CALCULATETABLE
DATEADD
OVIDE
FILTER
FIIND
(SU l('finan ials'[Sal@s]),

CALCULATE
CALCULATETABLE
DATEADD
DIVIDE
I LHR
RifltJR

(SUH('financi.als. (Sal,es J) - PREV_QUARTER, PREV_QUAR:TER)

CALCULATE
CALCULATETABLE
DATEADD
DIVIDE
FILTER
FIIND

Answer:
 Explanation:
Box 1: CALCULATE -
Calculate the sum.

Box 2: DATEADD -
DATEADD -1 QUARTER will give the previous month.

Box 3: DIVIDE -
Use DIVIDE to get the relative change.

Question: 61
DRAG DROP -

Color
Category

You need to create a calculated table that shows only the top eight products based on the highest value in Total

How should you complete the DAX expression? 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.
Select and Place:

Answer:

Explanation:
Box 1: TOPN -
TOPN returns the top N rows of the specified table.
Syntax: TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]�])

Box 2: DESC -
Descending order to get the highest values first.

Reference:
https://docs.microsoft.com/en-us/dax/topn-function-dax

Question: 62
You are creating a sales report in Power BI for the NorthWest region sales territory of your company. Data will
come from a view in a Microsoft SQL Server database. A sample of the data is shown in the following table:

✑
✑ The count of customers who placed an order
✑ The average quantity per order
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Set the data type for SalesOrderNumber to Decimal Number.

Remove the CustomerKey and ProductKey columns.
Remove the TaxAmt and Freight columns.
Filter the data to only the NorthWest region sales territory.

Answer: CD
Explanation:

Not B: Need CustomerKey to count of customers who placed an order

Question: 63 CertyIQ
You are creating a Power BI model that contains a table named Store. Store contains the following fields.

You plan to create a map visual that will show store locations and provide the ability to drill down from Country to
State/Province to City.
What should you do to ensure that the locations are mapped properly?

A. Change the data type of City, State/Province, and Country.

B. Set Summarization for City, State/Province, and Country to Don't summarize.
C. Set the data category of City, State/Province, and Country.
D. Create a calculated column that concatenates the values in City, State/Province, and Country.

Answer: C
Explanation:
A hierarchy is a set of fields categorized in a hierarchical way that one level is the parent of another level.
Values of the parent level can be drilled down to the lower level.

Create Hierarchy -
Right-click on the field you want to set as level 1 of the hierarchy in the fields list, and then select Create
Hierarchy.
 After that, you will see a new hierarchy created named your field name Category plus the word Hierarchy. This would
have a hierarchy icon beside it and also an option to expand to the fields of the hierarchy. If you expand, you will
see a copy of the Category field in there too.

Etc.

Reference:
https://radacad.com/what-a-power-bi-hierarchy-is-and-how-to-use-it

Question: 64
You are building a data model for a Power BI report.
You have data formatted as shown in the following table.
You need to create a clustered bar chart as shown in the following exhibit.

What should you do?

A. From Power Query Editor, split the Machine-User column by using a delimiter.
B. From Power Query Editor, create a column that contains the last three digits of the Machine-User column.
C. In a DAX function, create two calculated columns named Machine and User by using the SUBSTITUTE
function.
D. In a DAX function, create two measures named Machine and User by using the SUBSTITUTE function.

Answer: A

Explanation:
Split a column of text (Power Query)
You can split a column with a text data type into two or more columns by using a common delimiter character.
For example, a Name column that contains values written as <LastName>, <FirstName> can be split into two
columns using the comma (,) character.
Note: Power Query is an Extract Transform Load (ETL) tool. It allows us to
Download and fetch data from different sources. We call this data ingestion
Combine, clean, and model this data. We call this data wrangling

Reference:
https://support.microsoft.com/en-us/office/split-a-column-of-text-power-query-5282d425-6dd0-46ca-95bf-
8e0da9539662
Question: 65 CertyIQ
DRAG DROP -
You need create a date table in Power BI that must contain 10 full calendar years, including the current year.
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.
Select and Place:

Answer:

Explanation:
Box 1: YEAR -
Get the current year.

Box 2: TODAY -
TODAY returns the current date.

Box 3: CALENDAR -
CALENDAR returns a table with a single column named Date containing a contiguous set of dates. The range
of dates is from the specified start date to the specified end date, inclusive of those two dates.
The following formula returns a table with dates between January 1st, 2005 and December 31st, 2015.
CALENDAR (
 DATE ( 2005, 1, 1 ),
DATE ( 2015, 12, 31 )

Question: 66 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source.
The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: You create measures that use the USERELATIONSHIP DAX function to filter sales on the active
relationship between the sales table and the date table.
Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:

You can't use USERELATIONSHIP() to filter on an active relationship, but need additional innactive
relationships

Instead: Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to
create Order Date and Delivery Date as calculated tables.

Reference:

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Question: 67 CertyIQ
HOTSPOT -

How should you complete the DAX expression? To answer, select the appropriate options in the answer area.
 Answer Area

Answer:
 Explanation:
Box 1: TOTALYTD -
TOTALYTD evaluates the specified expression over the interval which begins on the first day of the year and
ends with the last date in the specified date column after applying specified filters.
Syntax: TOTALYTD (
<Expression>,
<Dates>
[, <Filter>]
[, <YearEndDate>]
Expression - The expression to be evaluated.
Dates - The name of a column containing dates or a one column table containing dates.
Example:
TOTALYTD ( -- 2007-01-01 : 2007-05-12
[Sales Amount],
'Date'[Date]
Box 2: 'Date'[Date]

Reference:
https://dax.guide/totalytd/

Question: 68
DRAG DROP -
You are modifying a Power BI model by using Power BI Desktop.
You have a table named Sales that contains the following fields.
You have a table named Transaction Size that contains the following data.

You need to create a calculated column to classify each transaction as small, medium, or large based on the value
in Sales Amount.
How should you complete the code? 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.
Select and Place:

Answer:
 Explanation:

Box 1: FILTER

Box 2: AND

Box 3: CALCULATE

FILTER needs to followed by table reference ,

AND is needed to check the limits , and

CALCULATE because needs to be followed by expression such as distinct in this case

Reference:

https://docs.microsoft.com/en-us/dax/calculate-function-dax

https://docs.microsoft.com/en-us/dax/filter-function-dax

Question: 69
You have a Power BI report for the procurement department. The report contains data from the following tables.
There is a one-to-many relationship from Suppliers to LineItems that uses the ID and Supplier ID columns.
The report contains the visuals shown in the following table.

You need to minimize the size of the dataset without affecting the visuals.
What should you do?

A. Merge Suppliers and LineItems.

B. Remove the LineItems[Description] column.
C. Remove the rows from LineItems where LineItems[Invoice Date] is before the beginning of last month.
D. Group LineItems by LineItems[Invoice ID] and LineItems[Invoice Date] with a sum of LineItems[Price].

Answer: B
Explanation:

Remove a column that is not used in the visuals reduces the size of the dataset.
Question: 70 CertyIQ
You have a Power BI report for the marketing department. The report reports on web traffic to a blog and contains
data from the following tables.

There is a one-to-many relationship from Posts to Traffic that uses the URL and URL Visited columns.
The report contains the visuals shown in the following table.

The dataset takes a long time to refresh.

You need to modify Posts and Traffic queries to reduce load times.
Which two actions will reduce the load times? Each correct answer presents part of the solution.
NOTE:
Each correct selection is worth one point.

A. Remove the rows in Posts in which Posts[Publish Date] is in the last seven days.
B. Remove the rows in Traffic in which Traffic[URL Visited] does not contain blog.
C. Remove Traffic[IP Address], Traffic[Browser Agent], and Traffic[Referring URL].
 D. Remove Posts[Full Text] and Posts[Summary].

Answer: BD
Explanation:
B: Only blog posts rows are useful for the visuals.

Question: 71 CertyIQ
HOTSPOT
-

You are creating a quick measure as shown in the following exhibit.

You need to create a monthly rolling average measure for Sales over time.
How should you configure the quick measure calculation? To answer, select the appropriate options in the answer
area.

NOTE: Each correct selection is worth one point.

Answer:
 Answer Area

Baseva ue:
Month
Total C.ost
Tota Orde,r Qty
Tota Sa es
Vear

Date:
Date
Month
Tota Sa es
Ye,ar

Period:

. s
Years

Explanation:

1. Total Sales;

2. Date;

3. Months

Question: 72
The Sales table contains records of sales by day from the last five years up until today’s date.

You plan to create a measure to return the total sales of March 2021 when March 2022 is selected.

Which DAX expression should you use?

A. Calculate (Sum(Sales[Sales]), PREVIOUSYEAR( dimDate[Date])

B. TOTALYTD (SUM(Sales[Sales]), dimDate[Date] )
C. Calculate (SUM(Sales[Sales]), SAMEPERIODLASTYEAR(dimDate[Date] ))
D. SUM(Sales[Sales])

Answer: C

Question: 73

While waiting for the data to load, you receive the following error.

You need to resolve the error.

What are two ways to achieve the goal? Each correct answer presents a complete solution.

Split log running queries into subsets of columns and use Power Query to merge the queries.
 Answer: AB

Question: 74 CertyIQ
Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.

From Power Query Editor, you profile the data shown in the following exhibit.

The IoT GUID and IoT ID columns are unique to each row in the query.

You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.

Solution: You split the IoT DateTime column into a column named Date and a column named Time.

Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:

Dataset point of view splitting the IoT Date Time column into a column named Date and a column named Time.
will not improve the performance. It will unnecessary create two different columns to store it. Instead we can
use time and date function extract whenever required.

Hence Ans No.

Question: 75
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.

From Power Query Editor, you profile the data shown in the following exhibit.

The IoT GUID and IoT ID columns are unique to each row in the query.

You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.

Solution: You remove the IoT GUID column and retain the IoT ID column.

Does this meet the goal?

A. Yes
B. No

Answer: A

Question: 76

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not

From Power Query Editor, you profile the data shown in the following exhibit.
The IoT GUID and IoT ID columns are unique to each row in the query.

You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.

Solution: You change the IoT DateTime column to the Date data type.

Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:

B is correct because changing the IoT DateTime column to the Date data type alone will not meet the goal of
analyzing IoT events by the hour and day of the year in power query.

Question: 77 CertyIQ
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App
workspace in shared capacity of powerbi.com.

The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The
dataset is scheduled to refresh twice a day at 08:00 and 17:00.

The report is a single page that contains 15 AppSource visuals and 10 default visuals.

Users say that the report is slow to load the visuals when they access and interact with the report.

You need to recommend a solution to improve the performance of the report.

What should you recommend?

A. Change any DAX measures to use iterator functions.

B. Remove unused columns from tables in the data model.
C. Replace the default visuals with AppSource visuals.
D. Increase the number of times that the dataset is refreshed.

Answer: B
 Explanation:

B is correct. from performance point of view its always good to drop unwanted columns. Avoid complicated
DAX and iterator functions as much as possible

Question: 78 CertyIQ
DRAG DROP
-

You have a Power BI data model that contains two tables named Products and Sales.

A one-to-many relationship exists between the tables.

You have a report that contains a report-level filter for Products.

You need to create a measure that will return the percent of total sales for each product. The measure must
respect the report-level filter when calculating the total.

How should you complete the DAX measure? To answer, drag the appropriate DAX functions to the correct
targets. Each function 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.

Answer:

Explanation:

1. Calculate

2. ALLSELECTED.

ALLSELECTED Removes only the filter on the expression visual but respect all external filters.
Question: 79 CertyIQ
You have a Power BI data model that analyzes product sales over time. The data model contains the following
tables.

A one-to-many relationship exists between the tables.

The auto date/time option for the data model is enabled.

You need to reduce the size of the data model while maintaining the ability to analyze product sales by month and
quarter.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct answer is worth one point.

A. Create a relationship between the Date table and the Sales table.
B. Disable the auto date/time option.
C. Create a Date table and select Mark as Date Table.
D. Disable the load on the Date table.
E. Remove the relationship between the Product table and the Sales table.

Answer: AC
Explanation:

1. AC is the correct answer. B is not needed as: It's important to note that when you specify your own date
table, Power BI Desktop does not auto-create the hierarchies that it would otherwise build into your model on
your behalf. If you later deselect your date table (and no longer have a manually set date table), Power BI
Desktop recreates the automatically created built-in date tables for you, for the date columns in the table.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables
Question: 80 CertyIQ
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App
workspace in shared capacity of powerbi.com.

The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The
dataset is scheduled to refresh twice a day at 08:00 and 17:00.

The report is a single page that contains 15 AppSource visuals and 10 default visuals.

Users say that the report is slow to load the visuals when they access and interact with the report.

You need to recommend a solution to improve the performance of the report.

What should you recommend?

A. Implement row-level security (RLS).

B. Remove unused columns from tables in the data model.
C. Replace the default visuals with AppSource visuals.
D. Enable visual interactions.

Answer: B

Question: 81 CertyIQ
HOTSPOT
-

You have a Power BI data model that contains a table named Stores. The table has the following columns:

• Store Name
• Open Date
• Status
• State
• City

You need to create a calculated column named Active Store Name that meets the following requirements:

• When the value of the Status column is “A”, the value in the Store Name column must be returned.
• When the value of the Status column is NOT “A”, the value in the Store Name column that is prefixed with
"Inactive - " must be returned.

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:

&& Is the same for AND function in DAX, nd & is the same for concatenate on Excel.

Question: 82 CertyIQ
You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the
date and time each complaint occurred. The data in Logged is in the following format: 2018 -12-31 at 08:59.

You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.

What should you do?

A. Apply a transformation to extract the first 11 characters of the logged column.

B. Add a conditional column that outputs 2018 if the Logged column starts with 2018 and set the data type of
the new column to Whole Number.
C. Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date.
D. Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the
new column to Date.

Answer: C
Explanation:

Answer is C and not B because the conditional column would be year and not the logged date, also the data
type should be date not whole number as specified in B.

Question: 83

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not

From Power Query Editor, you profile the data shown in the following exhibit.
The IoT GUID and IoT ID columns are unique to each row in the query.

You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.

Solution: You create a custom column that concatenates the IoT GUID column and the IoT ID column and then
delete the IoT GUID and IoT ID columns.

Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:

Answer is NO.

IoT GUID & IOT ID both are unique key columns. so we can delete any one among them. From performance
point of view its good to delete text ID column i.e IOT GUID and keep IOT ID. concatenation is not required

Both are unique columns, but by concatenating them you will end up with a Unique Key with data type Text.
This raises performance issues since Unique keys should be preferably integers for performance reasons.
Also, since IoT GUID is not required might as well remove it.

Question: 84 CertyIQ
You have a Power BI model that contains a table named Employee. The table contains the following data.
Each employee has one manager as shown in the ParentEmployeeID column.

All reporting paths lead to the CEO at the top of the organizational hierarchy.

You need to create a calculated column that returns the count of levels from each employee to the CEO.

Which DAX expression should you use?

A. PATHLENGTH(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]))
B. PATHITEM(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]),1,INTEGER)
C. PATHCONTAINS(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]),1)
D. PATH(Employee[EmployeeID],Employee[ParentEmployeeID])

Answer: A
Explanation:

The Answer is A because the question instructs that we count the different levels of each employee. The
PathLength gives the result. For more information see the link https://learn.microsoft.com/en-
us/dax/pathlength-function-dax

Although for CEO it returns 1 - so I personally would substract 1 from this PATHLENGTH when creating the
report, as I think numbers of levels from CEO to CEO is 0, formanagaers directly under CEO it is 1 etc

Answer D is wrong because it only returns the items related to the current row value and does not give the
count.

Question: 85

Users say that the report is slow to load the visuals when they access and interact with the report.
 Change the imported dataset to DirectQuery
Increase the number of times that the dataset is refreshed.

Answer: B
Explanation:

Question: 86 CertyIQ
You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the
date and time each complaint occurred. The data in Logged is in the following format: 2018 -12-31 at 08:59.

You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.

What should you do?

A. Change the data type of the Logged column to Date.

B. Split the Logged column by using at as the delimiter.
C. Add a conditional column that outputs 2018 if the Logged column starts with 2018 and set the data type of
the new column to Whole Number.
D. Apply the Parse function from the Date transformations options to the Logged column.

Answer: B

Explanation:

C refers to a whole number as data type which can't be used as a date hierarchy, so B is the only right answer.

Question: 87
HOTSPOT
You need to create a measure to count the number of product categories that had products sold during a selected
period.

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:

Distinctcount('Product'[product category],

'sales'

We have to count the distinct number of categories in the product table and then use the filter 'sales' so it will
return only those product categories with products sold.

Question: 88
workspace in shared capacity of powerbi.com.

The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The
dataset is scheduled to refresh twice a day at 08:00 and 17:00.

The report is a single page that contains 15 AppSource visuals and 10 default visuals.

Users say that the report is slow to load the visuals when they access and interact with the report.

You need to recommend a solution to improve the performance of the report.

What should you recommend?

A. Enable visual interactions.

B. Change any DAX measures to use iterator functions.
C. Implement row-level security (RLS).
D. Remove unused columns from tables in the data model.

Answer: D

Question: 89 CertyIQ
HOTSPOT
-

You have the Power BI data model shown in the following exhibit.

The Sales table has the following columns.

The data model must support the following analysis:

• Total sales by product by month in which the order was placed

• Quantities sold by product by day on which the order was placed
• Number of sales transactions by quarter in which the order was placed

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

Answer:

Question: 90

You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.
 Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date
Create a column by example that starts with 2018-12-31
Apply a transformation to extract the last 11 characters of the Logged column
Add a conditional column that outputs 2018 if the Logged column starts with 2018 and set the data type of
the new column to Whole Number

Answer: A

Question: 91
You have a Power BI data model that contains a table named Employees. The table has the following columns:

Employee Name
Email Address

Job Title

You are implementing dynamic row-level security (RLS).

You need to create a table filter to meet the following requirements:

A.[Email Address] - USERNAME()

B.[Employee Name] - USERPRINCIPALNAME()
C.[Employee Name] = USERNAME()
D.[Email Address] = USERPRINCIPALNAME()

Answer: D

Question: 92
DRAG DROP
The Country table contains the following data.

You create two row-level security (RLS) roles named Manager and CFO.

You plan to publish the dataset to the Power BI service.

You need to create DAX expressions for the RLS filters. The solution must meet the following requirements:

• Each manager must see only the data in the Sales and Human Resources tables for their own country.
• The CFO must be prevented from seeing the data in the Human Resources table.
• The CFO must see the sales data of all countries.

How should you complete the DAX expressions to meet the requirements? To answer, drag the appropriate
expressions to the correct targets. Each expression 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.

 Answer:

Question: 93 CertyIQ
You have a Power BI data model that imports data from a Microsoft Excel spreadsheet.

You use Power Query to load a query that contains both renamed and custom columns.

Later, you attempt to reload the query and receive the following error message.

Expression.Error: The column 'Category' of the table wasn't found.

What are two possible causes of the error? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.

A. The column was removed from the source file.

B. The column was renamed in the source file.
C. The file is no longer in the specified location.
D. The data type of the column was changed.

Answer: AB

Question: 94
You have a Power BI model that contains a table named Sales. The Sales table contains the following columns:
You need to write a DAX measure that counts the number of orders.

Which formula should you use?

B.CountA('Sales' [Order ID])

D.DistinctCount('Sales' [Order ID])

Answer: D

Question: 95 CertyIQ
HOTSPOT
-

You are creating a Power BI model in Power BI Desktop.

You need to create a calculated table named Numbers that will contain all the integers from -100 to 100.

How should you complete the DAX calculation? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Answer:
Question: 96
You have a Power BI data model that contains a table named Employees. The table has the following columns:

Employee Name
Email Address

Job Title

You are implementing dynamic row-level security (RLS).

You need to create a table filter to meet the following requirements:

A.[Employee Name] = USERPRINCIPALNAME()

B.[Email Address] = USERNAME()
C.[Employee Name] = USERNAME()
D.[Email Address] = USERPRINCIPALNAME()

Answer: D

Question: 97
DRAG DROP -

You plan to build a data model for a Power BI report.

You need to prepare the data so that it is available to the model in the format shown in the following table.

Which three actions should you perform in sequence in Power Query Editor? To answer, move the appropriate
 Answer:

Explanation:
Step 1: Select the [Department] and [Stage] columns and unpivot the other columns.
We unpivot the School1, School2, School3, and the School4 columns.
You might want to unpivot data, sometimes called flattening the data, to put it in a matrix format so that all
similar values are in one column.
Example:

When you unpivot, you unpack the attribute-value pairs that represent an intersection point of the new
columns and re-orient them into flattened columns:
* Values (in blue on the left) are unpivoted into a new column (in blue on the right).
* Attributes (in green on the left) are unpivoted into a new column (in green on the right) and duplicates are
correspondingly mapped to the new Values column.
Step 2: Rename the [Attribute] column as [School] and the [Value] column as [Score[,
Step 3: Group by [Department] and [School] and..

Reference:
https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882
21c7098

Question: 98
You have a report that contains a bar chart and a column chart. The bar chart shows customer count by customer
segment. The column chart shows sales by month.
You need to ensure that when a segment is selected in the bar chart, you see which portion of the total sales for
the month belongs to the customer segment.
How should the visual interactions be set on the column chart when the bar chart is selected?

A. highlight
B. filter
C. no impact

Answer: A
Explanation:

In most visuals, highlighting doesn't remove the unrelated data. Instead it highlights the related data. The rest
of the data remains visible but dimmed.

Note: By default, visualizations on a report page can be used to cross-filter and cross-highlight the other
visualizations on the page. For example, selecting a state on a map visualization highlights the column chart
and filters the line chart to display only data that applies to that one state.

Incorrect:

Not B: Filters remove all but the data you want to focus on.

Reference:

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-reports-filters-and-highlighting

Question: 99 CertyIQ
A user creates a Power BI report named ReportA that uses a custom theme.
You create a dashboard named DashboardA.
You need to ensure that DashboardA uses the custom theme. The solution must minimize development effort.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Publish ReportA to Power BI.

B. From ReportA save the current theme.
C. Publish ReportA to the Microsoft Power BI Community theme gallery.
D. From DashboardA, create a custom theme.
E. From DashboardA, upload a JSON theme.

Answer: BE

Explanation:

B. From ReportA save the current theme.

E. From DashboardA, upload a JSON theme.

https://learn.microsoft.com/en-us/power-bi/create-reports/service-dashboard-themes

scroll down to part that says JSON themes

Question: 100

stacked area chart

Answer: C
Explanation:
Line charts can have many different lines, for example both revenue and cost over time.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-line-chart

Question: 101 CertyIQ

HOTSPOT -
You have a report in Power BI Desktop.
You add a key influencers visual as shown in the exhibit. (Click the Exhibit tab.)

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.
Hot Area:
 Answer:

Explanation:

Box 1: adding more fields to Explain By

Box 2: 3

0.30 instead of 0.10. A factor of 3 greater.

moving fields from explain to expand should not add any new factors in analysis

Reference:

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

Question: 102
You build a report to help the sales team understand its performance and the drivers of sales.

Which type of visualization should you use?

Key influencers
Line and clustered column chart

Funnel

Answer: A
 Explanation:

When to use key influencers.

The key influencers visual is a great choice if you want to:
See which factors affect the metric being analyzed.
Contrast the relative importance of these factors. For example, do short-term contracts affect churn more
than long-term contracts?

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

Question: 103 CertyIQ

HOTSPOT -
You have a table that contains the following three columns:

City -
✑ Total Sales
✑ Occupation
You need to create a key influencers visualization as shown in the exhibit. (Click the Exhibit tab.)
How should you configure the visualization? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Analy.ze·: ...
.ity

·Total s,ales

E:xpla1'in by:

Answer:
 Explanation:
Box 1: Total Sales -
The key influencers visual helps you understand the factors that drive a metric you're interested in, here Total
Sales. It analyzes your data, ranks the factors that matter, and displays them as key influencers.

Box 2: Occupation -
Measures and summarized columns are automatically analyzed at the level of the Explain by fields used.

Reference:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

Question: 104
You are using the key influencers visual to identify which factors affect the quantity of items sold in an order.

✑ Customer Country
✑ Product Category
✑ Supplier Country

✑ Supplier Name
✑ Product Name
✑ Customer City
The key influencers visual returns the results shown in the following exhibit.
What can you identify from the visual?

A. Customers in Austria order 18.8 more units than the average order quantity.
B. Customers in Boise order 20.37 percent more than the average order quantity.
C. Product Category positively influences the quantity per order.
D. Customers in Cork order lower quantities than average.

Answer: A
Explanation:
Average quantity of units is displayed.
Incorrect:
Not B: Average quantity of units is displayed, not percentage.

Reference:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

Question: 105
You have a report that contains four pages. Each page contains slicers for the same four fields.
Users report that when they select values in a slicer on one page, the selections are not persisted on other pages.
 A. Create a bookmark for each slicer value.
B. Replace the slicers with report-level filters.
C. Sync the slicers across the pages.
D. Replace the slicers with page-level filters.
E. Replace the slicers with visual-level filters.

Answer: BC
Explanation:

C: You can sync a slicer and use it on any or all pages in a report.

B: You can set filters at three different levels for the report: visual-level, page-level, and report-level.

Note: Suppose you want your report readers to be able to look at overall sales metrics, but also highlight
performance for individual district managers and different time frames. You could create separate reports or
comparative charts. You could add filters in the Filters pane. Or you could use slicers. Slicers are another way
of filtering. They narrow the portion of the dataset that is shown in the other report visualizations.

Reference:

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers

Question: 106

You need to apply the following conditional formatting to the card while minimizing design effort:
✑
✑ For values that are less than 100, the font of the data label must be dark gray.

Color scale

Answer: B
Explanation:

need to go to the Format tab of the visual, and then expand the Data Label. The right beside the Data Label's
colour you need to hover your mouse, and you will find a three dots icon appearing, which if you click on it, you
Now in the Conditional Formatting tab, you can apply it in different methods. for example, you can choose
Rules, and then

The Rules mode will give you the ability to put custom roles as below;
 Source: https://learn.microsoft.com/en-us/power-bi/consumer/end-user-insights

Question: 108 CertyIQ

You are creating a dashboard by using the Power BI service.
You have an existing report page that contains three charts.
You need to add the charts to the dashboard while maintaining the interactivity between the charts.
What should you do?

A. Edit interactions in the report and set all interactions to Filter.

B. Pin each chart as a tile.
C. Edit the dashboard theme and pin each chart as a tile.
D. Pin the report page as a live tile.

Answer: D
Explanation:
One way to add a new dashboard tile is by pinning an entire report page. This is an easy way to pin more than
one visualization at a time. Also, when you pin an entire page, the tiles are live; you can interact with them
right there on the dashboard. And changes you make to any of the visualizations back in the report editor, like
adding a filter or changing the fields used in the chart, are reflected in the dashboard tile as well.
Pinning live tiles from reports to dashboards is only available in Power BI service (app.powerbi.com).

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-pin-live-tile-from-report

Question: 109
HOTSPOT -
You need to create a visual as shown in the following exhibit.
The indicator color for Total Sales will be based on % Growth to Last Year.
The solution must use the existing calculations only.
How should you configure the visual? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 Co nditiona1I
1
.•
' ,'

1
ormat t1' ng:: Bac:kgroundlcoll,or
Data ,bars
Fon,tc:0 lor
1

l,co ,n,s
We,b IURL

F'orm,at lby.: T
Color scale,
IF' eld l·value
Rules

Answer:
Explanation:
Box 1: Background color -
To apply conditional formatting, select a Table or Matrix visualization in Power BI Desktop. In the
Visualizations pane, right-click or select the down-arrow next to the field in the Values well that you want to
format. Select Conditional formatting, and then select the type of formatting to apply.
Box 2: Rules -
To format cell background or font color by rules, in the Format by field of the Background color or Font color
dialog box, select Rules.
 https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

Question: 110 CertyIQ

DRAG DROP -
You are using existing reports to build a dashboard that will be viewed frequently in portrait mode on mobile
phones.
You need to build the dashboard.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct order.
Select and Place:

Answer:

Explanation:

Pin -> open -> edit -> rearrange

Step 1: Pin items from the reports to the dashboard

 Step 2: Open the dashboard.

Step 3: Edit the dashboard mobile view

Open a report in Editing view.

Step 4: Rearrange, resize, or remove items from the mobile layout

https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-pin-live-tile-from-report

Question: 111 CertyIQ

You plan to create the chart shown in the following exhibit.

How should you create the dashed horizontal line denoting the 40th percentile of daily sales for the period shown?

A. Add a measure to the visual that uses the following DAX expression. Measure1 = PERCENTILEX.INC
(Sales,sales[Total Sales],0.40)
B. Add a measure to the visual that uses the following DAX expression. Measure1 = PERCENTILEX.EXC
(Sales,sales[Total Sales],0.40)
C. Add a new percentile line that uses Total Sales as the measure and 40% as the percentile.
D. Create a horizontal line that has a fixed value of 24,000.

Answer: C
Explanation:
The analytics feature enables you to show percentiles across groups specified along a specific axis.
 1. Click on the analytics tab
2. Select Percentile
3. You can choose a specific percentile along with other formatting options.
4. Drag a date or non-numeric dimension into the Axis of a column chart

Reference:
https://www.dash-intel.com/powerbi/statistical_functions_percentile.php

Question: 112

Users will view the report by using their mobile device.

You need to configure the report to display data based on each user's location.
Which two actions should you perform? Each correct answer presents part of the solution.

From Power Query Editor, detect the data types of the relevant columns.
In Data Category, set the geographic data category for the relevant columns.
Create a hierarchy for columns of the geography data type.
Use the columns of the geography data type in all visuals.
For the relevant columns, set synonyms to match common geographical terms.
Answer: BD

Explanation:

B: Identify geographic data in your report

1. In Power BI Desktop, switch to Data View Data View icon.

2. Select a column with geographic data " for example, a City column.

3. On the Modeling tab, select Data Category, then the correct category " in this example, City.

4. Continue setting geographic data categories for any other fields in the model.

D: Create visuals with your geographic data

Switch to Report view Report View icon, and create visuals that use the geographic fields in your data.
 In this example, the model also contains a calculated column that brings city and state together in one
column.

Publish the report to the Power BI service.

Reference:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-mobile-geofiltering

Question: 113
You have a report that contains a donut chart and a clustered column chart. Interactions between the visuals use

You need to modify the report so that when you select a column in the column chart, the donut chart redraws by

Select the donut chart and set the column chart interaction to Filter.
Select the column chart and set the donut chart interaction to Filter.
Select the donut chart and set the column chart interaction to None.
Select the column chart and set the donut chart interaction to None.
 Answer: B
Explanation:
Filters remove all but the data you want to focus on.
Note: Enable the visual interaction controls.
1. Select a visualization to make it active.
2. Display the Visual Interactions options.
3. In Power BI Desktop, select Format > Edit interactions.

4. To display the visualization interaction controls, select Edit interactions. Power BI adds filter and highlight
icons to all of the other visualizations on the report page.
We can see that the tree map is cross-filtering the line chart and the map, and is cross-highlighting the
column chart. You can now change how the selected visualization interacts with the other visualizations on the
report page.

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

Question: 114
HOTSPOT -
You have a report page that contains the visuals 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.
Hot Area:

Answer:

Explanation:

Box 1: not affect

 Box 2: cross-filter -

The map has the cross-filter icon active.

"You can only cross-filter line charts, scatter charts, and maps. You can't cross-highlight them" So Cross-filter
for the map

https://learn.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions?tabs=powerbi-
desktop

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

Question: 115 CertyIQ

You are creating a Power BI report by using Power BI Desktop.
You need to include a visual that shows trends and other useful information automatically. The visual must update
based on selections in other visuals.
Which type of visual should you use?

A. Q&A
B. smart narrative
C. key influencers
D. decomposition tree

Answer: B
Explanation:
The smart narrative visualization helps you quickly summarize visuals and reports. It provides relevant
innovative insights that you can customize.
Use smart narrative summaries in your reports to address key takeaways, to point out trends, and to edit the
language and format for a specific audience. In
PowerPoint, instead of pasting a screenshot of your report's key takeaways, you can add narratives that are
updated with every refresh. Your audience can use the summaries to understand the data, get to key points faster,
and explain the data to others.

Reference:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-smart-narrative

Question: 116
You need to configure the visual to display the referenced image instead of the URL in the Plant Image column.
What should you do?

A. From the Formatting tab, select Values, and then set URL icons to On for the table.
B. Set the Data category of the Plant Image field to Web URL.
C. Set the Data type of the Plant Image field to Binary.
D. Set the Data category of the Plant Image field to Image URL.

Answer: D
Explanation:
Add images to your report -
1. Create a column with the URLs of the images. See Considerations later in this article for requirements.
2. Select that column. On the Column tools ribbon, for Data category, select Image URL.
3. Add the column to a table, matrix, slicer, or multi-row card.
Step 3: From powerbi.com, add a tile for Excel1 dataset to DashboardA.
In the Power BI service (app.powerbi.com), a dashboard contains tiles pinned from one or more datasets, so
you can ask questions about any of the data contained in any of those datasets.

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-images-tables https://docs.microsoft.co
m/en-us/power-bi/create-reports/power-bi-tutorial-q-and-a

Question: 117
DRAG DROP -

You need to ensure that users who can access DashboardA can ask questions based on the contents of Excel1 and

DashboardA. The solution must minimize development time.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of
 Answer:

Explanation:

Step 1: ["The solution must minimize development time", so:] format the data as a table

Step 2: From powerbi.com, import Excel1 as a dataset.

Step 3: From powerbi.com, add a tile for the Excel1 dataset to DashboarA.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-from-excel-to-stunning-report

Question: 118 CertyIQ

Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as
the axis. Salary is present in the data as a numerical amount representing US dollars.
You need to create a reference line to show which employees are above the median salary.
Solution: You create a constant line and set the value to .5.
Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:
Instead: You create a percentile line by using the Salary measure and set the percentile to 50%.
The median is the middle value or the 50th percentile of a data set.

Reference:
https://dash-intel.com/powerbi/statistical_functions_median.php

Question: 119

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as
the axis. Salary is present in the data as a numerical amount representing US dollars.
You need to create a reference line to show which employees are above the median salary.
Solution: You create an average line by using the Salary measure.
Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:
Average is not Median.
Instead: You create a percentile line by using the Salary measure and set the percentile to 50%.
The median is the middle value or the 50th percentile of a data set.

Reference:
https://dash-intel.com/powerbi/statistical_functions_median.php

Question: 120 CertyIQ

Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as
the axis. Salary is present in the data as a numerical amount representing US dollars.
You need to create a reference line to show which employees are above the median salary.
Solution: You create a percentile line by using the Salary measure and set the percentile to 50%.
Does this meet the goal?

A. Yes
B. No

Answer: A
Explanation:

The median is the middle value or the 50th percentile of a data set.

Reference:

https://dash-intel.com/powerbi/statistical_functions_median.php

Question: 121
HOTSPOT -
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.
Hot Area:

Answer:
 Explanation:

Box 1: are 20 values that occur -

There are 20 unique values.

Box 2: Elm, American -

Elm, American is below Peer, flowering species in the graphic.

“Distinct” means number of different values regardless how many times it appears in the dataset. A 'name'
appears in the list multiple times is counted as 1 distinct count.

Whereas, the “Unique” value is total number of values that only appear once.

Distinct mean : count all the values as 1, even if there was more than one.

Unique mean : count only the value that are not repeated in the particular column

Question: 122 CertyIQ

You have a Power BI report hosted on powerbi.com that displays expenses by department for department
managers.
The report contains a line chart that shows expenses by month.
You need to enable users to choose between viewing the report as a line chart or a column chart. The solution must
minimize development and maintenance effort.
What should you do?

A. Enable report readers to personalize visuals.

B. Create a separate report page for users to view the column chart.
C. Add a column chart, a bookmark, and a button for users to choose a visual.
D. Create a mobile report that contains a column chart.

Answer: A
Explanation:
1. Also C is correct but I guess the key is 'The solution must minimize development' so A should be the correct
one
2. Given explanation point out to "A"
Question: 123 CertyIQ
You have two Power BI reports named ReportA and ReportB that each uses a distinct color palette.
You are creating a Power BI dashboard that will include two visuals from each report.
You need to use a consistent dark theme for the dashboard. The solution must preserve the original colors of the
reports.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Upload a snapshot.
B. For the browser, set the color preference to dark mode.
C. When pinning visuals to the dashboard, select Use destination theme.
D. Select the dark dashboard theme.
E. Turn on tile flow.

Answer: CD

Explanation:
D: With dashboard themes you can apply a color theme to your entire dashboard, such as corporate colors,
seasonal coloring, or any other color theme you might want to apply. When you apply a dashboard theme, all
visuals on your dashboard use the colors from your selected theme.
In the dashboard pane that appears, select one of the pre-built themes. In the example below, we've selected
Dark.

C: Reports and dashboards with different themes

If your report uses a different theme from the dashboard theme, in most cases you can control whether the
visual retains the current report theme or uses the dashboard theme.
* Try re-pinning the tile and selecting Use dashboard theme.
 https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-themes

Question: 124 CertyIQ

HOTSPOT -
You have a dataset that contains revenue data from the past year.
You need to use anomaly detection in Power BI to show anomalies in the dataset.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:
 Explanation:
Box 1: Line -
Anomaly detection is only supported for line chart visuals containing time series data in the Axis field.
Box 2: Populate the axis with a date field

Anomaly Explanations doesn't work with 'Show Value As' options.

Drilling down to go to the next level in the hierarchy isn't supported.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-anomaly-detection

Question: 125
You have a line chart that shows the number of employees in a department over time.
You need to see the total salary costs of the employees when you hover over a data point.

Add salary to the drillthrough fields.

Add salary to the visual filters.
Add salary to the tooltips.

Answer: C
Explanation:
Customize tooltips with aggregation or quick measures
You can customize a tooltip by selecting an aggregation function.
Select the arrow beside the field in the Tooltips bucket. Then, select from the available options.
 Note: Tooltips are an elegant way of providing more contextual information and detail to data points on a
visual. You can customize tooltips in Power BI Desktop and in the Power BI service.
When a visualization is created, the default tooltip displays the data point's value and category.

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-tooltips

Question: 126
You have the visual shown in the Original exhibit. (Click the Original tab.)
You need to configure the visual as shown in the Modified exhibit. (Click the Modified tab.)

What should you add to the visual?

A. a measure
B. an Average line
C. a trendline
D. a forecast

Answer: D
Explanation:
For example, here's how the current forecast looks like:
 400K

35DK

II)
0,

300K

200K
Jan 2019 Jul 2019 Jan 202() Jul 2020 Jan 2021
Date

https://spreadsheeto.com/power-bi-forecasting/#intro

Question: 127 CertyIQ

You need to create a visual that enables the adhoc exploration of data as shown in the following exhibit.
Which type of visual should you use?

A. smart narrative
B. decomposition tree
C. Q&A
D. key influencers

Answer: B
Explanation:
The decomposition tree visual in Power BI lets you visualize data across multiple dimensions. It automatically
aggregates data and enables drilling down into your dimensions in any order. It is also an artificial intelligence
(AI) visualization, so you can ask it to find the next dimension to drill down into based on ce rtain criteria.
This makes it a valuable tool for ad hoc exploration and conducting root cause analysis.
Example:
 https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-decomposition-tree

Question: 128
Your company has employees in 10 states.
The company recently decided to associate each state to one of the following three regions: East, West, and North.
You have a data model that contains employee information by state. The model does NOT include region

You have a report that shows the employees by state.

Create a new aggregation that summarizes by state.

Create a new aggregation that summarizes by employee.
Create a new group on the state column and set the Group type to List.
Create a new group on the state column and set the Group type to Bin.

Answer: C
Explanation:
 Incorrect:
Not D: You can also define the bin size to put values into equally sized groups that better enable you to
visualize data in ways that are meaningful. This action is often called binning.

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

Question: 129
You have a collection of reports for the HR department of your company.
You need to create a visualization for the HR department that shows historical employee counts and predicts

Which type of visualization should you use?

Answer: C
Explanation:
The best data for forecasting is time series data or uniformly increasing whole numbers. The line chart has to

https://powerbi.microsoft.com/fr-ca/blog/introducing-new-forecasting-capabilities-in-power-view-for-office-
Question: 130 CertyIQ
You have a Microsoft Power BI dashboard. The report used to create the dashboard uses an imported dataset from
a Microsoft SQL Server data source.
The dashboard is shown in the exhibit. (Click the Exhibit tab.)
 Vari,ance·t•o Plan, Variance to Plan °/4
B,YBUSINESS AREA " REFRESHED 12: @ 3: ®6PM 1 1 1

e \far Plan e VarPl.an %

1

BU BU Dis,tribuL, lnfra.stru..,. Manufacm Offi,ce & R,&DR,&D Servioes,

BU BU Dis,tribuL. lnfra.stru..., Manufac... Adminis.t..., R,&DR,&D Services
Offi,ce&
Adminis,L

Amount
BY MONTH, SCENARIO
Scenario •Actu.al eLE1 • LE2 LE3e Plan
1

$0i,,3bn

$0i.2bn

$0.1bn

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
What occurred at 12:03:06 PM?

A. A new transaction was added to the data source.

B. The dashboard tile cache refreshed.
C. A user added a comment to a tile.
 D. A user pressed F5.

Answer: B
Explanation:

A tile is a report visual pinned to a dashboard, and dashboard tile refreshes happen about every hour so that
the tiles show recent results. You can change the schedule in the dataset settings, as in the screenshot below,
or force a dashboard update manually by using the Refresh now option.

If you press F5 or hit the refresh button, the dashboard charts gets updated.

Note: Power BI enables you to go from data to insight to action quickly, yet you must make sure the data in
your Power BI reports and dashboards is recent.

Knowing how to refresh the data is often critical in delivering accurate results.

Reference:

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

Question: 131 CertyIQ

HOTSPOT -
You need to create a Power BI report. The first page of the report must contain the following two views:
✑ Sales By Postal Code
✑ Sales by Month
Both views must display a slicer to select a value for a field named Chain.
The Sales By Postal Code view must display a map visual as shown in the following exhibit.
 Gh.ain

Fashions Direct Lindseys

. ,I Salee By Month 1·

The Sales By Month view must display a column chart visual as shown in the following exhibit.
Users must be able to switch between the views by using buttons on the report page. The selected Chain field
must be maintained when switching between views.
What is the minimum number of bookmarks required, and which property should you apply to each bookmark? To
answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
 ,P·ll•!.-Q-i l·f Ser
· A.ll."l.ia

Answer:
Explanation:
Box 1: 2 -
One for each visual.
Note: When you edit a report in Power BI Desktop and the Power BI service, you can add report bookmarks to
capture the current state of a report page.
Bookmarks save the current filters and slicers, cross-highlighted visuals, sort order, and so on. When others
view your report, they can get back to that exact state by selecting your saved bookmark.

Box 2: Display -
Users must be able to switch between the views by using buttons on the report page. The selected Chain field
must be maintained when switching between views.
You can select whether each bookmark will apply Data properties, such as filters and slicers; Display
properties, such as spotlight and its visibility; and Current page changes, which present the page that was
visible when the bookmark was added. These capabilities are useful when you use bookmarks to switch
between report views or selections of visuals, in which case you'd likely want to turn off data properties, so
that filters aren't reset when users switch views by selecting a bookmark.
Note: When you create a bookmark, the following elements are saved with the bookmark:

The current page -

Filters -
Slicers, including slicer type (for example, dropdown or list) and slicer state
Visual selection state (such as cross-highlight filters)

Sort order -

Drill location -
 https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-bookmarks

Question: 132 CertyIQ

You have the visual shown in the exhibit. (Click the Exhibit tab.)

You need to show the relationship between Total Cost and Total Sales over time.
What should you do?

A. Add a play axis.

B. From the Analytics pane, add an Average line.
C. Add a slicer for the year.
D. Create a DAX measure that calculates year-over-year growth.

Answer: A
Explanation:

When to use a slicer -

Slicers are a great choice when you want to:

Display commonly used or important filters on the report canvas for easier access.

Make it easier to see the current filtered state without having to open a drop-down list.

Filter by columns that are unneeded and hidden in the data tables.

Create more focused reports by putting slicers next to important visuals.

 https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers

Question: 133 CertyIQ

HOTSPOT -
You have the Power BI dashboard shown in the Dashboard exhibit. (Click the Dashboard tab.)

You need to ensure that when users view the dashboard on a mobile device, the dashboard appears as shown in the
Mobile exhibit. (Click the Mobile tab.)
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:

Explanation:

Box 1: Update Dashboard Mobile Layout

Box 2: Resize and move total sales and total quantity

Dashboard mobile feature already fits the tiles in the view, and when recreating same scenario you only need
to work on the 2 cards

If you use Report Mobile View feature from PowerBI desktop, you will have an empty canvas and will need to
work on all tiles

Reference:

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-create-mobile-optimized-report-about
Question: 134 CertyIQ
You are building a Power BI report to analyze customer segments.
You need to identify customer segments dynamically based on the Bounce Rate across dimensions such as source,
geography, and demographics. The solution must minimize analysis effort.
Which type of visualization should you use?

A. decomposition tree
B. funnel chart
C. Q&A
D. key influencers

Answer: A
Explanation:

The decomposition tree visual in Power BI lets you visualize data across multiple dimensions. It automatically
aggregates data and enables drilling down into your dimensions in any order. It is also an artificial intelligence
(AI) visualization, so you can ask it to find the next dimension to drill down into based on certain criteria.

This makes it a valuable tool for ad hoc exploration and conducting root cause analysis.

Reference:

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-decomposition-tree

Question: 135
You have a table that contains sales data and approximately 1,000 rows.

Which type of visualization should you use?

area

Answer: B
Explanation:
Outlier Detection in Power BI using Funnel Plot, which is a scatter plot.
Outliers are those data points that lie outside the overall pattern of distribution & the easiest way to detect

https://towardsdatascience.com/this-article-is-about-identifying-outliers-through-funnel-plots-using-the-mic

Question: 136
You have a report that contains three pages. One of the pages contains a KPI visualization.
You need to filter all the visualizations in the report except for the KPI visualization.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Edit the interactions of the KPI visualization.

B. Add the same slicer to each page and configure Sync slicers.
C. Edit the interactions of the slicer that is on the same page as the KPI visualization.
D. Configure a page-level filter.
E. Configure a report-level filter.

Answer: BC
Explanation:
Slicers are another way of filtering. They narrow the portion of the dataset that is shown in the other report
visualizations.
Control which page visuals are affected by slicers
Example: Use visual interactions to keep slicer selections from filtering this chart.
1. Go to the Overview page of the report, and then select the DM slicer you previously created.
2. On the Power BI Desktop menu, select the Format menu under Visual Tools, and then select Edit
interactions.
3. Filter controls, each with a Filter and a None option, appear above all the visuals on the page. Initially, the
Filter option is preselected on all the controls.
4. Select the None option in the filter control above the Total Sales Variance by FiscalMonth and District
Manager chart to stop the DM slicer from filtering it.
Incorrect:
Not D: A page-level filter is used to filter an entire page.
Not E: A report-level filter is used to filter an entire report.

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter

Question: 137
HOTSPOT -
You have a Power BI visual that uses indicators to show values that are out of range as shown in the following

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.
Hot Area:

Answer:

Explanation:
Box 1: a line -
Incorrect:
* not line and clustered column
The Line and Clustered Column Chart is a combo charts that combines the Line chart and Column chart
together in one visual. By combining these two visuals together, you can make a very quick comparison
between two sets of measures.

Box 2: anomaly detection -

Anomaly detection helps you enhance your line charts by automatically detecting anomalies in your time
series data. It also provides explanations for the anomalies to help with root cause analysis. With just a couple
of clicks, you can easily find insights without slicing and dicing the data.
Example:
 Reference:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-anomaly-detection

Question: 138 CertyIQ

You are creating a Power BI report to analyze consumer purchasing patterns from a table named Transactions. The
Transactions table contains a numeric field named Spend.
You need to include a visual that identifies which fields have the greatest impact on Spend.
Which type of visual should you use?

A. Q&A
B. smart narrative
C. decomposition tree
D. key influencers

Answer: D
Explanation:
The key influencers visual helps you understand the factors that drive a metric you're interested in. It analyzes
your data, ranks the factors that matter, and displays them as key influencers. For example, suppose you want
to figure out what influences employee turnover, which is also known as churn. One factor might be
employment contract length, and another factor might be commute time.

When to use key influencers -

The key influencers visual is a great choice if you want to:
See which factors affect the metric being analyzed.
 Contrast the relative importance of these factors. For example, do short-term contracts affect churn more
than long-term contracts?

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers

Question: 139 CertyIQ

HOTSPOT -
You are creating a line chart in a Power BI report 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.
Hot Area:

Answer:
 Explanation:

Box 1: an average reference line

With the Analytics pane in Power BI Desktop, you can add dynamic reference lines to visuals, and provide
focus for important trends or insights.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane

Box 2: Axis

https://radacad.com/drill-down-and-up-in-power-bi-explained

"For example, in the visual below I have SalesAmount as the Value of the column chart, and the Date field
(OrderDate) as the X-Axis.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane

Question: 140
You have a dashboard that contains tiles pinned from a single report as shown in the Original Dashboard exhibit.

You need to modify the dashboard to appear as shown in the Modified Dashboard exhibit. (Click the Modified
What should you do?

A. Change the report theme.

B. Change the dashboard theme.
C. Edit the details of each tile.
D. Create a custom CSS file.

Answer: B
Explanation:
With dashboard themes you can apply a color theme to your entire dashboard, such as corporate colors,
seasonal coloring, or any other color theme you might want to apply. When you apply a dashboard theme, all
visuals on your dashboard use the colors from your selected theme.
Incorrect:
Not A: With Power BI Desktop report themes, you can apply design changes to your entire report, such as
using corporate colors, changing icon sets, or applying new default visual formatting.

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-themes

Question: 141

No anomalies are detected.

You need to increase the likelihood that anomaly detection will identify anomalies in the report.

Increase the Expected range transparency setting.

Add a data field to the Legend field well.
Increase the Sensitivity setting.
Add a data field to the Secondary values field well.

Answer: C
 Explanation:

C. Increase the sensitivity

If you increase the sensitivity, the algorithm is more sensitive to changes in your data. In that case, even a
slight deviation is marked as an anomaly. If you decrease the sensitivity, the algorithm is more selective on
what it considers an anomaly.

reference: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-anomaly-detection

Question: 142 CertyIQ

You maintain a Power BI workspace that contains a supplier quality dashboard. The dashboard contains 10 card
visuals, two map visuals and five bar chart visuals.
The dashboard mobile layout is shown in the exhibit. (Click the Exhibit tab.)
You need to modify the dashboard mobile layout to meet the following requirements:
✑ Only show single-value visuals.
✑ Minimize scrolling.
What should you do?

A. Decrease the size of the card visuals. Remove the map and bar chart visuals.
 B. Decrease the size of the map and bar chart visuals. Move all the card visuals to the top of the layout.

D. Move the bar chart visuals to the top of the layout. Remove the map visuals. Decrease the size of the card
visuals.

Answer: A
Explanation:

Question: 143 CertyIQ

You have a Power BI report.
You have a table named Data1 that contains 10 million rows.
Data1 is used in the following visuals:
✑ A card that shows the number of records
✑ A bar chart that shows total transaction amount by territory
✑ A scatter plot that shows transaction amount and profit amount on the axes and points colored by territory
You need to modify the scatter plot to make it easier for users to identify meaningful patterns. The solution must
not affect the accuracy of the other visuals.
What should you do?

A. Add a count field of the transaction amount to the size bucket of the scatter plot.
B. Add a trend line to the scatter plot.
C. Enable high-density sampling on the scatter plot.
D. Apply a row filter to the Data1 query in Power Query Editor.

Answer: C
Explanation:

This question requires "modification" of the scatter plot and what high-density sampling essentially does is to
employ methods that capture and represent the underlying data more effectively and eliminates overlapping
points.

Remember that the table named Data1 contains 10 million rows. How do you represent all that data in a
scatter plot in a meaningful pattern for easy understanding and analysis? by use of high density sampling.

"By definition, high-density data is sampled to create visualizations reasonably quickly that are responsive to
interactivity. Too many data points on a visual can bog it down, and can detract from the visibility of trends".

This link explains it more: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-high-density-

scatter-charts#how-high-density-scatter-charts-work

Question: 144
You have a Power BI workspace named Inventory that contains a dataset, a report, and a dashboard.
You need to add an additional tile to the dashboard. The tile must show inventory by location. This information is
NOT visualized in the report. The solution must minimize the impact on the report.
Which two actions should you perform? Each correct answer presents part of the solution.
B. Hide the report page.
C. Pin the visual to the dashboard.
D. Use quick insights on the dashboard.
E. Add the visual to the report.

Answer: AC
Explanation:

In the Power BI service (app.powerbi.com), a dashboard contains tiles pinned from one or more datasets, so
you can ask questions about any of the data contained in any of those datasets. T

The answer to your question is displayed as an interactive visualization and updates as you modify the
question.

Open a dashboard and place your cursor in the question box. Even before you start typing, Q&A displays a
new screen with suggestions to help you form your question. You see phrases and complete questions
containing the names of the tables in the underlying datasets and may even see complete questions listed if
the dataset owner has created featured questions,

Reference:

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-tutorial-q-and-a
Question: 145 CertyIQ
HOTSPOT -
You have a dataset named Pens that contains the following columns:
✑ Item
✑ Unit Price
✑ Quantity Ordered
You need to create a visualization that shows the relationship between Unit Price and Quantity Ordered. The
solution must highlight orders that have a similar unit price and ordered quantity.
Which type of visualization and which feature should you use? To answer, select the appropriate options in the
answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:

Explanation:
Box 1: A scatter plot of Quantity Ordered and Unit Price by item
A scatter chart shows the relationship between two numerical values.
Note: Scatter charts are a great choice:
To show relationships between two numerical values.
To plot two groups of numbers as one series of x and y coordinates.
To use instead of a line chart when you want to change the scale of the horizontal axis.
To turn the horizontal axis into a logarithmic scale.
To display worksheet data that includes pairs or grouped sets of values.
To show patterns in large sets of data, for example by showing linear or non-linear trends, clusters, and
 Box 2: Automatically find clusters
Scatter charts are a great choice:
* To show patterns in large sets of data, for example by showing linear or non-linear trends, clusters, and
outliers.

https://github.com/Microsoft/powerbi-visuals-corrplot/

Question: 146

You need to ensure that all the filters applied to Page1 apply to Page1 and Page3 only.

On each page, modify the interactions of the slicer.

Enable visibility of the slicers on Page1 and Page3. Disable visibility of the slicer on Page2.
Sync the slicers on Page1 and Page3.

Answer: C
Explanation:

C. Sync the slicers on Page1 and Page3.

Question: 147 CertyIQ

You have a Power BI report that contains five pages.

Pages 1 to 4 are visible and page 5 is hidden.

You need to create a solution that will enable users to quickly navigate from the first page to all the other visible
pages. The solution must minimize development and maintenance effort as pages are added to the report.

What should you do first?

A. Add a blank button to page 1.

B. Add a page navigation button to page 1.
C. Create a bookmark for each page.
D. Add a bookmark navigation button to page 1.

Answer: B

Explanation:

B is correct. Add a page navigation button to page 1 because the solution must minimize development and
maintenance effort as pages are added to the report. If we add more pages the report they will be
automatically added to the page navigator. Only thing is you have to change 'show hidden pages' option to off.
But with the bookmark navigator, lot of efforts required to create individual bookmark to each page and also
the newly added pages manually. another problem is it also adds all other bookmarks to the navigator which
 we dont need. so we should create a page navigator button in page 1(and set 'show hidden pages' to off)

Question: 148
You build a Power BI report that displays IoT temperature data streaming from a refrigerator.

You publish the report to the Power BI service.

Set an alert on a KPI visual in the report.

Answer: C
Explanation:

You first have to pin a one-value visual to the dashboard (Card/KPI/Gauge) and then you can set an alert on it's

Question: 149

You need to modify the dashboard to display as shown in the following exhibit.
 Change the colors of the visuals in the report.

Upload a snapshot image of the dashboard.

Answer: A
Explanation:

The visual colors can't be changed on the dashboard from a report after the visual has already been pinned.
Applying a dashboard custom theme will do it.

Question: 150 CertyIQ

You need to create a Power BI theme that will be used in multiple reports. The theme will include corporate
branding for font size, color, and bar chart formatting.

What should you do?

A. From Power BI Desktop, customize the current theme.

B. From Power BI Desktop, use a built-in report theme.
C. Create a theme as a PBIVIZ file and import the theme into Power BI Desktop.
D. Create a theme as a JSON file and import the theme into Power BI Desktop.

Answer: D
Explanation:

D. Create a theme as a JSON file and import the theme into Power BI Desktop.

To create a Power BI theme that can be used across multiple reports and workspaces, the best approach
would be to create a theme as a JSON file and then import it into Power BI Desktop. This will allow you to
define the corporate branding for font size, color, and bar chart formatting in a single file, which can then be
easily imported into all the reports that require it.

To create a theme as a JSON file, you can use the built-in Theme Generator tool in Power BI or create the file
manually. Once you have the JSON file, you can import it into Power BI Desktop by going to the "Switch
Theme" menu and selecting "Import Theme." From there, you can select the JSON file and apply the theme to
Question: 151 CertyIQ
You have a Power BI report that contains one page. The page contains two line charts and one bar chart.

Change the visualization type.

The solution must minimize development effort.

Edit the interactions between the three visuals.

Enable personalization for the report.
Enable personalization for each visual.

Answer: C
Explanation:

Personalization can be enabled for each visual or the entire report. Here we have a single page report with 3 visuals
and all three visuals need personalization, the answer is 'enable personalization for the entire report'

Question: 152 CertyIQ

Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.

You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as
the axis. Salary is present in the data as a numerical amount representing US dollars.

You need to create a reference line to show which employees are above the median salary.

Solution: You create a median line by using the Salary measure.

Does this meet the goal?

A. Yes
B. No
 Answer: A
Explanation:

We can definitely create a median line for the measure of salary (Tested)

Also the other solution in this series is create a percentile line at 50% for the salary measure because
percentile value at 50 % is exactly equal to the median value.

Question: 153 CertyIQ

DRAG DROP
-

You have a Power BI report that contains a table visual with a measure named Revenue. The Revenue measure
returns values within a range of 0 to 5.

You need to format the visual so that the Revenue column displays a specific background color based on the value
range shown in the following table.

Which three actions should you perform in sequence in Power BI Desktop? To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation:

Open the Background color dialog for the revenue column.

 Add and configure a new rule for each value range.

Question: 154
You have a Power BI report that contains four pages.

All the pages contain a slicer for a field named Country.

You need to ensure that when a user selects a country on page 1, the selection is retained on page 2 and page 3.

Sync the Country slicer on page 1, page 2, and page 3.

Answer: D

Question: 155
DRAG DROP

You need to add the Word Cloud visual shown in the following exhibit to the report.
The solution must minimize development effort.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of
actions to the answer area and arrange them in the correct
order.

Answer:
Question: 156 CertyIQ
DRAG DROP
-

You have a Power BI report that contains five bookmarks.

You need to add an object to the report from which users can navigate between three specific bookmarks.

How should you complete the task? To answer, drag the appropriate actions to the correct steps. Each action 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.

Answer:

Question: 157 CertyIQ

What should you create?

A.a paginated report that contains a tablix

B.a dashboard that contains a table
C.an interactive report that contains a table
D.an interactive report that contains a matrix

Answer: A

Question: 158
DRAG DROP
-

You have a Power BI report that contains three pages. The pages are used to analyze sales across various
countries.

You add a slicer named Country to each page of the report.

You need to configure the report to meet the following requirements:

• When a user selects a country on the first page, the report must filter the other pages.
• The second and third pages must display only the filtered results.

Which task should you perform for each requirement? To answer, drag the appropriate task to the correct
requirement. Each task 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.

Answer:

Question: 159
You have a Power BI report that contains a page. The page contains the following:

A shape named Shape1

A clustered bar chart named Sales by Region

You need to ensure that Sales Summary renders on top of Shape1.

Tab order in the Selection pane

Layer order in the Selection pane
Maintain layer order in the General visual settings
Vertical alignment in the Canvas settings

Answer: B
Question: 160
You have a Power BI report named Report1 and a dashboard named Dashboard1. Report1 contains a line chart

A.Refresh the dataset used by Report1 and Dashboard1.

C.Select Refresh visuals for Dashboard1.

D.Edit the details for the dashboard tile of Dashboard1.

Answer: B

Question: 161
In Power BI Desktop, you are creating a report that will contain three pages.

You need to create a custom tooltip page and prepare the page for use.

Which three actions should you perform? Each correct answer presents part of the solution.

For the tooltip page, set Allow use as tooltip to On.

For the target page, set Allow use as tooltip to On.

For the tooltip page, configure filters.

Add and configure visuals on the tooltip page.

Answer: ADE

Question: 162
DRAG DROP

What the customers most often provide feedback about

Whether the customers like your company’s product
The language of the feedback

Which AI insights service should you use for each output? To answer, drag the appropriate services to the correct outputs.
Each service may be used once, more than once, or not at all. You may need to drag the split bar between
 Answer:

Question: 163
You have a Power BI report named ReportA.

You have a Power BI tenant that allows users to export data.

You need to ensure that consumers of ReportA cannot export any data from visuals.

From Power BI Desktop, modify the Report settings.

From the Power BI service, modify the Report settings.

Answer: AD

Question: 164
You have a Power BI report that will be rendered on a vertical display.

You need to maximize the portion of the screen area used by the report.

A. From the Canvas background setting of Power BI Desktop, configure the Image fit setting.
 C. From Power BI Desktop, select Personalize visuals.
D. From the Power BI service, enable the Pages pane.

Answer: B

Question: 165
You need to create a visual that compares profit across 10 product categories fora selected quarter.

What is the best visual to use to achieve the goal?

an area chart
a funnel chart
a clustered bar chart
a line chart

Answer: C

Question: 166

The finance team at your company is NOT currently a member of any Power BI workspace roles.

You need to enable the finance team to use Microsoft Excel to analyze the Finance dataset.

Grant the finance team build permissions to the Finance dataset.

Provide an Excel workbook that is connected to the Finance dataset.

Answer: A

Question: 167

You need to ensure that the report meets the following requirements:

All values must be set to two decimal places.

All negative values must be displayed in red font and parentheses.

Which two actions should you perform? Each correct answer presents part of the solution.

For the visual, apply conditional formatting to the background color.

Configure the measure to use a custom format.
 C. For the visual, apply conditional formatting to the font color.
D. For the visual, set Value decimal places to 2.

Answer: BC

Question: 168 CertyIQ

HOTSPOT -
You have a Power BI tenant that hosts the datasets shown in the following table.

You have the following requirements:

The export of reports that contain Personally Identifiable Information (PII) must be prevented.
Data used for financial decisions must be reviewed and approved before use.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:

Explanation:

Reference:
https://docs.microsoft.com/en-us/power-bi/enterprise/service-security-sensitivity-label-overview
Question: 169 CertyIQ
You have a Power BI tenant.
You have reports that use financial datasets and are exported as PDF files.
You need to ensure that the reports are encrypted.
What should you implement?

A. Microsoft Intune policies

B. row-level security (RLS)
C. sensitivity labels
D. dataset certifications

Answer: C
Explanation:
When you create a sensitivity label, you can restrict access to content that the label will be applied to.
When a document or email is encrypted, access to the content is restricted, so that it:
Can be decrypted only by users authorized by the label's encryption settings.
Remains encrypted no matter where it resides, inside or outside your organization, even if the file's renamed.
Incorrect:
Not B: Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters
restrict data access at the row level, and you can define filters within roles.
Current limitations for row-level security:

Reference:
https://docs.microsoft.com/en-us/microsoft-365/compliance/encryption-sensitivity-labels

Question: 170 CertyIQ

You have a Microsoft Excel file on a file server.
You create a Power BI report and import a table from the Excel file.
You publish the report.
You need to ensure that the data refreshes every four hours.
What should you do first?

A. Upload the Excel file to a Power BI workspace.

B. Create a subscription to the report.
C. Deploy an on-premises data gateway.
D. Edit the data source credentials.

Answer: C
Explanation:
You can scheduled refresh for the On-premises data gateway (personal mode) and the On-premises data
gateway. You specify refresh options in the following areas of the Power BI service: Gateway connection, Data
source credentials, and Scheduled refresh.

Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh
Question: 171 CertyIQ
You have a dataset that is used infrequently and refreshes every hour.
You receive a notification that the refresh was disabled due to inactivity.
Which two actions will cause the scheduled refresh schedule to resume? Each correct answer presents a complete
solution.
NOTE: Each correct selection is worth one point.

A. Enable query caching for the dataset.

B. Import the dataset to Microsoft Excel.
C. From the Power BI service, open a dashboard that uses the dataset.
D. From the Power BI service, open a report that uses the dataset.
E. From PowerShell, run the get-powerbireport cmdlet.

Answer: CD
Explanation:
After two months of inactivity, scheduled refresh on your dataset is paused. A dataset is considered inactive
when no user has visited any dashboard or report built on the dataset. At that time, the dataset owner is sent
an email indicating the scheduled refresh is paused. The refresh schedule for the dataset is then displayed as
disabled. To resume scheduled refresh, simply revisit any dashboard or report built on the dataset.
Incorrect:
Not E: get-powerbireport retrieves a list of Power BI reports that match the specified search criteria and
scope.

Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh

Question: 172 CertyIQ

You have a Power BI workspace that contains a dataset, a report, and a dashboard. The following groups have
access:
✑ External users can access the dashboard.
✑ Managers can access the dashboard and a manager-specific report.
✑ Employees can access the dashboard and a row-level security (RLS) constrained report.
You need all users, including the external users, to be able to tag workspace administrators if they identify an
issue with the dashboard. The solution must ensure that other users see the issues that were raised.
What should you use?

A. comments
B. chat in Microsoft Teams
C. alerts
D. subscriptions

Answer: A
Explanation:
Add a personal comment or start a conversation about a dashboard or report with your colleagues. The
comment feature is just one of the ways a business user can collaborate with others.
Note: Comments can be added to an entire dashboard, to individual visuals on a dashboard, to a report page,
to a paginated report, and to individual visuals on a report page. Add a general comment or add a comment
targeted at specific colleagues.

Reference:
 https://docs.microsoft.com/en-us/power-bi/consumer/end-user-comment

Question: 173 CertyIQ

You have a PBIX file that imports several tables from an Azure SQL database.
The data will be migrated to another Azure SQL database.
You need to change the connections in the PBIX file. The solution must minimize administrative effort.
What should you do?

A. From Power Query Editor, create new queries.

B. From Power Query Editor, modify the source of each query.
C. Create a PBIT file, open the file, and change the data sources when prompted.
D. Modify the Data source settings.

Answer: D
Explanation:

Open the PBIX file with Microsoft Power BI Desktop.

Then choose File -> Options and settings -> Data source settings >Right click data sources and change source.

Note:

Incorrect:

Not C: PBIT is a template file.

The PBIT file keeps your report structure and contains 'DataModelSchema File' instead of ''DataModel File''.
However, If you choose import mode, the PBIX file stores all imported data from data sources and the report
structure.

Reference:

https://windowsreport.com/open-pbix-file/

Question: 174

You need to provide a user with the ability to create a dashboard that will use the visuals from the reports.

Create a row-level security (RLS) role and add the user to the role.
Share the reports with the user.
Grant the Read permission for the datasets to the user.
Add the user as a member of the workspace.
Add the user as a Viewer of the workspace.

Answer: D
Explanation:
To grant access to a new workspace, assign those user groups or individuals to one of the workspace roles:
 Workspace roles -

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-roles-new-workspaces

Question: 175
DRAG DROP -

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of

Answer:
Explanation:
An entire report page can be pinned to a dashboard, which is called pinning a live tile. It's called a live tile
because you can interact with the tile on the dashboard.
Unlike with individual visualization tiles, changes made in the report are automatically synced with the
dashboard.

Step 2: Open the Sales report -

Step 3: Pin the page.
1. Open a report in Editing view.
2. With no visualizations selected, from the menu bar, select Pin to a dashboard.
3. Pin the tile to an existing dashboard or to a new dashboard. Notice the highlighted text: Pin live page
enables changes to reports to appear in the dashboard tile when the page is refreshed.

4. Select Pin live. A Success message (near the top right corner) lets you know the page was added, as a tile,
to your dashboard.

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-pin-live-tile-from-report
Question: 176 CertyIQ
You have a report in Power BI named report1 that is based on a shared dataset.
You need to minimize the risk of data exfiltration for report. The solution must prevent other reports from being
affected.
What should you do?

A. Clear Allow recipients to share your dashboard and Allow users to build new content using the underlying
datasets for the dataset.
B. Apply row-level security (RLS) to the shared dataset.
C. Select the Allow end users to export both summarized and underlying data from the service or Report Server
Export data option for the report.
D. Select the Don't allow end users to export any data from the service or Report Server Export data option for
the report.

Answer: D
Explanation:

Besides the various permissions you can set, there are also two different options to disable the export
functionality. First of all is the Export data in general and second the Export to Excel as a specific setting.
Both have the same setup for permissions

Export Data -

Reference:

https://data-marc.com/2020/04/13/power-bi-governance-why-you-should-consider-to-disable-export-to-
excel/

Question: 177
You need to allow Power BI users to export the summarized data used to create the visualizations but prevent the
users from exporting the underlying data.
What should you do?

A. From the Power BI service, configure the dataset permissions.

B. From Power BI Desktop, configure the Data Load settings for the current file.
C. From Power BI Desktop, modify the data source permissions.
D. From Power BI Desktop, configure the Report settings for the current file.

Answer: D

Explanation:

1.) in Power BI Desktop > File > Options > Report Settings > Export data > Allow end users to export data with
current layout, summarize data and underlying data from the service or Report Server.

2.) in Power BI Service in Report Settings > Export data section I found: "Choose the type of data you allow
your end users to export." Here you can select one option from:

- Summarized data and data with current layout

- Summarized data, with current layout and underlying data

- None

But this option is missing from offered answers, the correct answer is D.

A is incorrect as in Manage Dataset Permission you can grant access:

-allow recipients to modify dataset,

-allow recipients to share this dataset,

-allow recipients to build content with the data associated with dataset,

-send an email notification

or remove granted reshare, remove build, remove write, remove access

So here you can not change or limit data export.

https://learn.microsoft.com/en-us/power-bi/connect-data/service-datasets-permissions

Question: 178

Grant the Read and Build permissions for the Power BI datasets to the Azure network security team.
Configure custom instructions for the Request access feature that instructs users to contact the Azure

Instruct the Azure network security team to create security groups. Configure RLS to use the groups.
Add the Azure network security team as members of the RLS role.

Answer: C
 Explanation:
Configure row-level security group membership, Working with members

In the Power BI service, you can add a member to the role by typing in the email address or name of the user
or security group.
You can use the following groups to set up row level security.

Distribution Group -

Mail-enabled Group -

Security Group -
Incorrect:

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

Question: 179 CertyIQ

You have four sales regions. Each region has multiple sales managers.
You implement row-level security (RLS) in a data model. You assign the relevant mail-enabled security group to
each role.
You have sales reports that enable analysis by region. The sales managers can view the sales records of their
region. The sales managers are prevented from viewing records from other regions.
A sales manager changes to a different region.
You need to ensure that the sales manager can see the correct sales data.
What should you do?

A. Change the Microsoft Power BI license type of the sales manager.

B. From Microsoft Power BI Desktop, edit the Row-Level Security setting for the reports.
C. Manage the permissions of the underlying dataset.
D. Request that the sales manager be added to the correct Azure Active Directory group.

Answer: D
Explanation:
You can use the following groups to set up row level security.
* Distribution Group
* Mail-enabled Group - This group also contains a list of email addresses of members and can also be used to
control access to OneDrive and SharePoint.
The Mail-Enabled Security Group can be created in the Office 365 Admin Portal.
* Security Group - This is also known as an Active Directory Security Group. This group lives within Active
Directory and Azure Active Directory.

Reference:
https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls https://www.fourmoo.com/2020/04/0
1/power-bi-which-groups-can-be-used-to-set-permissions-in-power-bi/
Question: 180 CertyIQ
You have more than 100 published datasets.
Ten of the datasets were verified to meet your corporate quality standards.
You need to ensure that the 10 verified datasets appear at the top of the list of published datasets whenever users
search for existing datasets.
What should you do?

A. Promote the datasets.

B. Certify the datasets.
C. Feature the dataset on the home page.
D. Publish the datasets in an app.

Answer: B
Explanation:
Once logged in, you will be presented with a list of datasets that you can access from your various
workspaces. This is one reason why having official datasets promoted and certified is recommended, as these
will appear at the top of the list, with certified datasets appearing before promoted datasets.
Note: Power BI provides two ways you can endorse your valuable, high-quality content to increase its visibility:
promotion and certification.
Promotion: Promotion is a way to highlight content you think is valuable and worthwhile for others to use. It
encourages the collaborative use and spread of content within an organization.
Any content owner, as well as any member with write permissions on the workspace where the content is
located, can promote the content when they think it's good enough for sharing.
Certification: Certification means that the content meets the organization's quality standards and can be
regarded as reliable, authoritative, and ready for use across the organization.
Currently it is possible to endorse

Datasets -

Dataflows -

Reports -

Apps -
If dataset discoverability has been enabled in your organization, endorsed datasets can be made discoverable.
When a dataset is discoverable, users who don't have access to it will be able to find it and request access.

Reference:
https://exceleratorbi.com.au/new-power-bi-reports-golden-dataset/ https://docs.microsoft.com/en-us/power-
bi/collaborate-share/service-endorse-content

Question: 181
DRAG DROP -

You need to grant the user capabilities shown in the following table.
The solution must use the principle of least privilege.
Which user role should you assign to each user? To answer, drag the appropriate roles to the correct users. Each
role may be used once, more than once, or not at all. You may need to drag the split bar.
NOTE: Each correct selection is worth one point.
Select and Place:

Answer:

Explanation:
 Box 1: Member -
Only Admin and Member can publish, unpublish, and change permissions for an app.
Incorrect:
Contributors can update the app associated with the workspace, if the workspace Admin delegates this
permission to them. However, they can't publish a new app or change who has permission to it.

Box 2: Contributor -
Admin,Member and Contributor can create, edit, and delete content, such as reports, in the workspace.
Note: Contributor - This role can access and interact with reports and dashboards. Additionally, this role can
create, edit, copy, and delete items in a workspace, publish reports, schedule refreshes, and modify gateways.
Incorrect:
Viewer - This role provides read only access to workspace items. Read access does provide report / dashboard
consumers the ability to not only view, but also interact with visuals. Interaction does not mean changing a
visual.

Reference:
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-roles-new-workspaces https://www.ms
sqltips.com/sqlservertip/6487/power-bi-workspace-permissions-and-roles

Question: 182 CertyIQ

You create a dashboard by using the Microsoft Power BI Service. The dashboard contains a card visual that shows
total sales from the current year.
You grant users access to the dashboard by using the Viewer role on the workspace.
A user wants to receive daily notifications of the number shown on the card visual.
You need to automate the notifications.
What should you do?

A. Create a subscription.
B. Create a data alert.
C. Share the dashboard to the user.
D. Tag the user in a comment.

Answer: A
Explanation:

A is correct, you need a subscription, not an alert as alerts don't include a snapshot and they will only be sent
based on a certain condition whereas here you want daily notifications, not just when the value exceeds a
certain threshold.

Question: 183
You have a Power BI workspace named Workspace1 that contains a dataset named DS1 and a report named RPT1.

You need to provide the user with the appropriate access. The solution must minimize the number of access

Grant the Build permission for DS1 to the user.

Share RPT1 with the user.
 D. Add the user as a member of Workspace1.

Answer: B
Explanation:

More granular permissions -

Power BI provides the Build permission as a complement to the existing permissions, Read and Reshare. All
users who already had Read permission for datasets via app permissions, sharing, or workspace access at that
time also got Build permission for those same datasets. They got Build permission automatically because
Read permission already granted them the right to build new content on top of the dataset, by using Analyze
in Excel or Export.

With this more granular Build permission, you can choose who can only view the content in the existing report
or dashboard and who can create content connected to the underlying datasets.

If your dataset is being used by a report outside the dataset workspace, you can't delete that dataset. Instead,
you see an error message.

Reference:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-build-permissions

Question: 184 CertyIQ

Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have five reports and two dashboards in a workspace.
You need to grant all organizational users read access to one dashboard and three reports.
Solution: You publish an app to the entire organization.
Does this meet the goal?

A. Yes
B. No

Answer: A
Explanation:

YES, because I understand that we can add to the app the desired reports/dashboard. And for the next
question which given solution is "You enable included in app for all assets." is NO

Question: 185

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not

You need to grant all organizational users read access to one dashboard and three reports.
Does this meet the goal?

A. Yes
B. No

Answer: B

Explanation:
You need to specify the dashboard and the three reports to be included in the app.
Instead: You create an Azure Active Directory group that contains all the users. You share each selected
report and the one dashboard to the group.
Note: A published App can provide the required access.
When the dashboards and reports in your workspace are ready, you choose which dashboards and reports you
want to publish, then publish them as an app.
In Power BI, you can create official packaged content, then distribute it to a broad audience as an app. You
create apps in workspaces, where you can collaborate on Power BI content with your colleagues. Then you
can publish the finished app to large groups of people in your organization.

Reference:
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-create-distribute-apps

Question: 186 CertyIQ

Note: This question is part of a series of questions that present the same scenario. Each question in the series
contains a unique solution that might meet the stated goals. Some question sets might have more than one correct
solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not
appear in the review screen.
You have five reports and two dashboards in a workspace.
You need to grant all organizational users read access to one dashboard and three reports.
Solution: You assign all the users the Viewer role to the workspace.
Does this meet the goal?

A. Yes
B. No

Answer: B
Explanation:

In this way all users will see all workspace content not only the one dashboard and 3 reports

Question: 187

You need to ensure that the HR team members have RLS applied when they view reports based on the dataset.

From powerbi.com, add users to the HR role for the dataset.

From powerbi.com, share the dataset to the HR team members.

From Power BI Desktop, import a table that contains the HR team members.
 Answer: A
Explanation:

In the Power BI service, you can add a member to the role by typing in the email address or name of the user
or security group.

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

Question: 188 CertyIQ

You have a Power BI dashboard that monitors the quality of manufacturing processes. The dashboard contains the
following elements:
✑ A line chart that shows the number of defective products manufactured by day
✑ A KPI visual that shows the current daily percentage of defective products manufactured
You need to be notified when the daily percentage of defective products manufactured exceeds 3%.
What should you create?

A. a subscription
B. an alert
C. a smart narrative visual
D. a Q&A visual

Answer: B
Explanation:
Set alerts in the Power BI service to notify you when data on a dashboard changes above or below limits you
set. Alerts can be set on tiles pinned from report visuals or from Power BI Q&A, and only on gauges, KPIs, and
cards.

Reference:
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-alerts

Question: 189

You plan to publish the report to the Power BI Service.

What should you implement to ensure that users who consume the report from the Power BI Service have the most

an OData feed
an On-premises data gateway
a subscription
a scheduled refresh of the dataset

Answer: B
 Explanation:
After you install the on-premises data gateway, you need to add data sources that can be used with the
gateway. You can work with gateways and SQL Server

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-ssas

Question: 190 CertyIQ

You have five sales regions. Each region is assigned a single salesperson.
You have an imported dataset that has a dynamic row-level security (RLS) role named Sales. The Sales role filters
sales transaction data by salesperson.
Salespeople must see only the data from their region.
You publish the dataset to powerbi.com, set RLS role membership, and distribute the dataset and related reports
to the salespeople.
A salesperson reports that she believes she should see more data.
You need to verify what data the salesperson currently sees.
What should you do?

A. Use the Test as role option to view data as the salesperson's user account.
B. Use the Test as role option to view data as the Sales role.
C. Instruct the salesperson to open the report in Microsoft Power BI Desktop.
D. Filter the data in the reports to match the intended logic in the filter on the sales transaction table.

Answer: A
Explanation:

A, to be able to see what the specific salesperson sees (and compare it to what she should see) you should
test the report as that user account since the RLS is dynamic and based on the user accounts.

Question: 191 CertyIQ

You have multiple dashboards.
You need to ensure that when users browse the available dashboards from powerbi.com, they can see which
dashboards contain Personally Identifiable
Information (PII). The solution must minimize configuration effort and impact on the dashboard design.
What should you use?

A. Microsoft Information Protection sensitivity labels

B. tiles
C. comments
D. Active Directory groups

Answer: A
Explanation:
In the Power BI service, sensitivity labels can be applied to datasets, reports, dashboards, and dataflows.
Sensitivity labels on reports, dashboards, datasets, and dataflows are visible from many places in the Power
BI service. Sensitivity labels on reports and dashboards are also visible in the Power BI iOS and Android mobile
 https://docs.microsoft.com/en-us/power-bi/enterprise/service-security-sensitivity-label-overview

Question: 192 CertyIQ

HOTSPOT -
You have a dataset that has the permissions 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.
Hot Area:

Answer:
 Explanation:

Box 1: use Analyze in Excel -

Build permission applies to datasets. When you give users Build permission, they can build new content on
your dataset, such as reports, dashboards, pinned tiles from Q&A, paginated reports, and Insights Discovery.

Users also need Build permissions to work with the data outside Power BI:

To export the underlying data.

To build new content on the dataset such as with Analyze in Excel.

To access the data via the XMLA endpoint.

Box 2: Grant build permission

see: https://learn.microsoft.com/en-us/power-bi/connect-data/service-datasets-build-permissions

"Say you have Reshare and Build permission on a dataset. When you share a report or dashboard built on that
dataset, you can specify that the recipients also get Build permission for the underlying dataset."

Reference:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-build-permissions https://data-
marc.com/2021/07/30/transform-a-local-into-a-global-power-bi-solution-request-access-to-content/

Question: 193

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not

You need to grant all organizational users read access to one dashboard and three reports.
Solution: You create an Azure Active Directory group that contains all the users. You share each selected report

Does this meet the goal?

Yes
 Answer: A
Explanation:

Yes, from the documentation a suggestion made there to share with more than 100 separate users is to "Share
with a user group that contains all the

Question: 194 CertyIQ

DRAG DROP -
You have a Power BI table named Customer that contains a field named Email Address.
You discover that multiple records contain the same email address.
You need to create a calculated column to identify which records have duplicate email addresses.
How should you complete the DAX expression for the calculated column? 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.
Select and Place:

Answer:
 Explanation:

Question: 195
DRAG DROP

You publish a dataset that contains data from an on-premises Microsoft SQL Server database.

You need to ensure that the Power BI service can connect to the database and refresh the dataset.

Which four actions should you perform n sequence? To answer, move the appropriate actions from the list of
 Answer:

Explanation:

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install

https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

Question: 196
You have a Power BI dataset and a connected report.

You need to ensure that users can analyze data in Microsoft Excel only by connecting directly to the dataset.

Certify the dataset used by the report.

Change the default visual interaction for the report.
For the report, change the Export data setting to None.
For the report, change the Export data setting to Summarized data, data with current layout and underlying
 Answer: D
Explanation:

The correct answer is D. C is incorrect because if you change the export data setting to None, users will not be
able to export the data in any form.

Question: 197 CertyIQ

HOTSPOT
-

You have two Power BI workspaces named WorkspaceA and WorkspaceB. WorkspaceA contains two datasets
named Sales and HR.

You need to provide a user named User1 with access to the WorkspaceB. The solution must meet the following
requirements:

• Create reports that use the HR dataset.

• Publish the reports to WorkspaceB.
• Prevent the ability to modify the HR dataset.
• Prevent the ability to add users to Workspaces.

What should you do? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Answer:
 Explanation:

Question: 198 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Litware, Inc. is an online retailer that uses Power BI.
Litware plans to leverage data from an Azure SQL database that stores data for the company's live e-commerce
website.
Litware uses Azure Active Directory (Azure AD) to authenticate users.
Existing Environment. Sales Data
Litware has online sales data that has the SQL schema shown in the following table.
In the Date table, the date_id column has a format of yyyymmdd and the month column has a format of yyyymm.
The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww.
In the Sales table, the sales_id column represents a unique transaction.
The region id column can be managed by only one sales manager.
Existing Environment. Data Concerns
You are concerned with the quality and completeness of the sales data. You must ensure that negative and missing
sales_amount values do NOT contribute to the total sales amount calculation.
Existing Environment. Reporting Requirements
Litware identifies the following reporting requirements:
Executives require a visual that shows sales by region.
Executives require a visual that shows returns by region manager and the sales managers that report to them.
The sales managers must be able to see only the sales data of their respective region.
The sales managers require a visual to analyze sales performance versus sales targets.
The sales department requires reports that contain the number of sales transactions.
Users must be able to see the month in each report as shown in the following example: Feb 2020.
The customer service department requires a visual that can be filtered by both sales month and ship month
independently.
The maximum allowed latency to include transactions in reports is five minutes. Question You need to create the
required relationship for the executive's visual.
What should you do before you can create the relationship?

A. Change the data type of Sales[region_id] to Whole Number.

B. Change the data type of Sales[region_id] to Decimal Number.
C. In the Sales table, add a measure for Sum(sales_amount).
D. Change the data type of Sales[sales_id] to Text.

Answer: A
Explanation:

Executives require a visual that shows sales by region.

The data type of Sales[region_id] must be changed from varchar to Whole Number, as Sales[region_id] is
Integer.

Question: 199 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Litware, Inc. is an online retailer that uses Power BI.
Litware plans to leverage data from an Azure SQL database that stores data for the company's live e-commerce
website.
Litware uses Azure Active Directory (Azure AD) to authenticate users.
Existing Environment. Sales Data
Litware has online sales data that has the SQL schema shown in the following table.
In the Date table, the date_id column has a format of yyyymmdd and the month column has a format of yyyymm.
The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww.
In the Sales table, the sales_id column represents a unique transaction.
The region id column can be managed by only one sales manager.
Existing Environment. Data Concerns
You are concerned with the quality and completeness of the sales data. You must ensure that negative and missing
sales_amount values do NOT contribute to the total sales amount calculation.
Existing Environment. Reporting Requirements
Litware identifies the following reporting requirements:
Executives require a visual that shows sales by region.
Executives require a visual that shows returns by region manager and the sales managers that report to them.
The sales managers must be able to see only the sales data of their respective region.
The sales managers require a visual to analyze sales performance versus sales targets.
The sales department requires reports that contain the number of sales transactions.
Users must be able to see the month in each report as shown in the following example: Feb 2020.
The customer service department requires a visual that can be filtered by both sales month and ship month
independently.
The maximum allowed latency to include transactions in reports is five minutes. Question You need to get data
from the Microsoft SQL Server tables.
What should you use to configure the connection?

A. Import that uses a Microsoft account

B. Import that uses a database credential
C. DirectQuery that uses a database credential
D. DirectQuery that uses the end-user's credentials

Answer: C
Explanation:

C. DirectQuery that uses a database credential

If you used the credentials of the user (D) then all users would need to be created in the database.

Question: 200 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

General Overview -
Northwind Traders is a specialty food import company.
The company recently implemented Power BI to better understand its top customers, products, and suppliers.

Business Issues -
The sales department relies on the IT department to generate reports in Microsoft SQL Server Reporting Services
(SSRS). The IT department takes too long to generate the reports and often misunderstands the report
requirements.
Existing Environment. Data Sources
Northwind Traders uses the data sources shown in the following table.

Source2 is exported daily from a third-party system and stored in Microsoft SharePoint Online.
Existing Environment. Customer Worksheet
Source2 contains a single worksheet named Customer Details. The first 11 rows of the worksheet are shown in the
following table.

All the fields in Source2 are mandatory.

The Address column in Customer Details is the billing address, which can differ from the shipping address.
Existing Environment. Azure SQL Database
Source1 contains the following tables:
Orders
Products
Suppliers
Categories
Order Details
Sales Employees
The Orders table contains the following columns.

The Order Details table contains the following columns.

The address in the Orders table is the shipping address, which can differ from the billing address.
The Products table contains the following columns.
 S Int 5 f,orejg
---------
Int 4
Cif

The Categories table contains the following columns.

Is
-

The Suppliers table contains the following columns.

d 4
Oviedo

¥es bte

The Sales Employees table contains the following columns.

Each employee in the Sales Employees table is assigned to one sales region. Multiple employees can be assigned
to each region.
Requirements. Report Requirements
Northwind Traders requires the following reports:
Top Products
Top Customers
On-Time Shipping
The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected order
month or quarter, product category, and sales region.
The Top Products report will show the top 20 products based on the highest sales amounts sold in a selected order
month or quarter, sales region, and product category. The report must also show which suppliers provide the top
products.
The On-Time Shipping report will show the following metrics for a selected shipping month or quarter:
The percentage of orders that were shipped late by country and shipping region
Customers that had multiple late shipments during the last quarter
Northwind Traders defines late orders as those shipped after the required shipping date.
The warehouse shipping department must be notified if the percentage of late orders within the current month
exceeds 5%.
The reports must show historical data for the current calendar year and the last three calendar years.
Requirements. Technical Requirements
Northwind Traders identifies the following technical requirements:
A single dataset must support all three reports.
The reports must be stored in a single Power BI workspace.
Report data must be current as of 7 AM Pacific Time each day.
The reports must provide fast response times when users interact with a visualization.
The data model must minimize the size of the dataset as much as possible, while meeting the report requirements
and the technical requirements.
Requirements. Security Requirements
Access to the reports must be granted to Azure Active Directory (Azure AD) security groups only. An Azure AD
security group exists for each department.
The sales department must be able to perform the following tasks in Power BI:
Create, edit, and delete content in the reports.
Manage permissions for workspaces, datasets, and reports.
Publish, unpublish, update, and change the permissions for an app.
Assign Azure AD groups role-based access to the reports workspace.
Users in the sales department must be able to access only the data of the sales region to which they are assigned
in the Sales Employees table.
Power BI has the following row-level security (RLS) Table filter DAX expression for the Sales Employees table.
[EmailAddress] = USERNAME()
RLS will be applied only to the sales department users. Users in all other departments must be able to view all the
data. Question You need to create the dataset.

live connection

Answer: A
Explanation:

Question: 201 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you ca nnot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

General Overview -
Northwind Traders is a specialty food import company.
The company recently implemented Power BI to better understand its top customers, products, and suppliers.

Business Issues -
The sales department relies on the IT department to generate reports in Microsoft SQL Server Reporting Services
(SSRS). The IT department takes too long to generate the reports and often misunderstands the report
requirements.
Existing Environment. Data Sources
Northwind Traders uses the data sources shown in the following table.

Source2 is exported daily from a third-party system and stored in Microsoft SharePoint Online.
Existing Environment. Customer Worksheet
Source2 contains a single worksheet named Customer Details. The first 11 rows of the worksheet are shown in the
following table.
All the fields in Source2 are mandatory.
The Address column in Customer Details is the billing address, which can differ from the shipping address.
Existing Environment. Azure SQL Database
Source1 contains the following tables:
Orders
Products
Suppliers
Categories
Order Details
Sales Employees
The Orders table contains the following columns.

The Order Details table contains the following columns.

The address in the Orders table is the shipping address, which can differ from the billing address.
The Products table contains the following columns.
 S Int 5 f,orejg
---------
Int 4
Cif

The Categories table contains the following columns.

Is
-

The Suppliers table contains the following columns.

d 4
Oviedo

¥es bte

The Sales Employees table contains the following columns.

Each employee in the Sales Employees table is assigned to one sales region. Multiple employees can be assigned
to each region.
Requirements. Report Requirements
Northwind Traders requires the following reports:
Top Products
Top Customers
On-Time Shipping
The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected order
month or quarter, product category, and sales region.
The Top Products report will show the top 20 products based on the highest sales amounts sold in a selected order
month or quarter, sales region, and product category. The report must also show which suppliers provide the top
products.
The On-Time Shipping report will show the following metrics for a selected shipping month or quarter:
The percentage of orders that were shipped late by country and shipping region
Customers that had multiple late shipments during the last quarter
Northwind Traders defines late orders as those shipped after the required shipping date.
The warehouse shipping department must be notified if the percentage of late orders within the current month
exceeds 5%.
The reports must show historical data for the current calendar year and the last three calendar years.
Requirements. Technical Requirements
Northwind Traders identifies the following technical requirements:
A single dataset must support all three reports.
The reports must be stored in a single Power BI workspace.
Report data must be current as of 7 AM Pacific Time each day.
The reports must provide fast response times when users interact with a visualization.
The data model must minimize the size of the dataset as much as possible, while meeting the report requirements
and the technical requirements.
Requirements. Security Requirements
Access to the reports must be granted to Azure Active Directory (Azure AD) security groups only. An Azure AD
security group exists for each department.
The sales department must be able to perform the following tasks in Power BI:
Create, edit, and delete content in the reports.
Manage permissions for workspaces, datasets, and reports.
Publish, unpublish, update, and change the permissions for an app.
Assign Azure AD groups role-based access to the reports workspace.
Users in the sales department must be able to access only the data of the sales region to which they are assigned
in the Sales Employees table.
Power BI has the following row-level security (RLS) Table filter DAX expression for the Sales Employees table.
[EmailAddress] = USERNAME()
RLS will be applied only to the sales department users. Users in all other departments must be able to view all the
data. Question You need to configure access for the sales department users. The solution must the meet the
security requirements.
What should you do?

A. Share each report to the Azure Active Directory group of the sales department.
B. Add the Azure Active Directory group of the sales department as an Admin of the reports workspace.
C. Distribute an app to the users in the Azure Active Directory group of the sales department.
D. Add the sales department as a member of the reports workspace.

Answer: D

Explanation:

D - Add the sales department as a member of the reports workspace.

For the actions they need to perform (edit reports, publish app, etc) the Member role would be the least
privilege

Question: 202 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the tim e provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

General Overview -
Northwind Traders is a specialty food import company.
The company recently implemented Power BI to better understand its top customers, products, and suppliers.

Business Issues -
The sales department relies on the IT department to generate reports in Microsoft SQL Server Reporting Services
(SSRS). The IT department takes too long to generate the reports and often misunderstands the report
requirements.
Existing Environment. Data Sources
Northwind Traders uses the data sources shown in the following table.

Source2 is exported daily from a third-party system and stored in Microsoft SharePoint Online.
Existing Environment. Customer Worksheet
Source2 contains a single worksheet named Customer Details. The first 11 rows of the worksheet are shown in the
following table.
All the fields in Source2 are mandatory.
The Address column in Customer Details is the billing address, which can differ from the shipping address.
Existing Environment. Azure SQL Database
Source1 contains the following tables:
Orders
Products
Suppliers
Categories
Order Details
Sales Employees
The Orders table contains the following columns.

The Order Details table contains the following columns.

The address in the Orders table is the shipping address, which can differ from the billing address.
The Products table contains the following columns.
 S Int 5 f,orejg
---------
Int 4
Cif

The Categories table contains the following columns.

Is
-

The Suppliers table contains the following columns.

d 4
Oviedo

¥es bte

The Sales Employees table contains the following columns.

Each employee in the Sales Employees table is assigned to one sales region. Multiple employees can be assigned
to each region.
Requirements. Report Requirements
Northwind Traders requires the following reports:
Top Products
Top Customers
On-Time Shipping
The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected order
month or quarter, product category, and sales region.
The Top Products report will show the top 20 products based on the highest sales amounts sold in a selected order
month or quarter, sales region, and product category. The report must also show which suppliers provide the top
products.
The On-Time Shipping report will show the following metrics for a selected shipping month or quarter:
The percentage of orders that were shipped late by country and shipping region
Customers that had multiple late shipments during the last quarter
Northwind Traders defines late orders as those shipped after the required shipping date.
The warehouse shipping department must be notified if the percentage of late orders within the current month
exceeds 5%.
The reports must show historical data for the current calendar year and the last three calendar years.
Requirements. Technical Requirements
Northwind Traders identifies the following technical requirements:
A single dataset must support all three reports.
The reports must be stored in a single Power BI workspace.
Report data must be current as of 7 AM Pacific Time each day.
The reports must provide fast response times when users interact with a visualization.
The data model must minimize the size of the dataset as much as possible, while meeting the report requirements
and the technical requirements.
Requirements. Security Requirements
Access to the reports must be granted to Azure Active Directory (Azure AD) security groups only. An Azure AD
security group exists for each department.
The sales department must be able to perform the following tasks in Power BI:
Create, edit, and delete content in the reports.
Manage permissions for workspaces, datasets, and reports.
Publish, unpublish, update, and change the permissions for an app.
Assign Azure AD groups role-based access to the reports workspace.
Users in the sales department must be able to access only the data of the sales region to which they are assigned
in the Sales Employees table.
Power BI has the following row-level security (RLS) Table filter DAX expression for the Sales Employees table.
[EmailAddress] = USERNAME()
RLS will be applied only to the sales department users. Users in all other departments must be able to view all the
data. Question HOTSPOT -
You need to create a solution to meet the notification requirements of the warehouse shipping department.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:

Explanation:
Box 1: dashboard -
The warehouse shipping department must be notified if the percentage of late orders within the current
month exceeds 5%.
You can set alerts to notify you when data in your dashboards changes beyond limits you set.

Box 2: data alert -

Reference:
https://docs.microsoft.com/en-us/power-bi/create-reports/service-set-data-alerts

Question: 203
Introductory Info Case Study -
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board
meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four
business units.

Existing Environment -

Data and Sources -

Data for the reports comes from the sources shown in the following table.

The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.

Balance Sheet Data -

The balance sheet data is imported and includes the final monthly balances of each account in the format shown in
the following table.

The balance sheet data always includes a row for each account for each month.

Product Catalog Data -

The product catalog shows how products roll up to product categories, which roll up to the business units. The
product list is provided in the format shown in the following table.
Revenue data is provided at the date and product level. Expense data is provided at the date and department level.

Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is
usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an
Excel formula. On occasion, there are conflicting results in the reports because the products and departments that
roll up to each business unit are not defined consistently.

Requirements -

Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants
to reduce how long it takes to populate the reports to less than two days. The company wants to create common
logic for the business units, products, and departments. The logic will be used across all reports, including but not
limited to the quarterly reporting for the board.

Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and
links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.

Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group
will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the
reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.

Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the
last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the
ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.
The definitions and attributes for the products, departments, and business units must be consistent across all the
reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
The reports must be updated with the latest data by 5 AM each day. Question You need to create the relationship
between the product list and the revenue results. The solution must minimize the time it takes to render visuals.
What should you set as the relationship cardinality?

A. One to one
B. Many to many
C. Many to one
D. One to many

Answer: D

Explanation:
One product in the product list can occur many times in the revenue results.
Note 1: One to many (1:*): In a one-to-many relationship, the column in one table has only one instance of a
particular value, and the other related table can have more than one instance of a value.
Note 2:
Revenue data is provided at the date and product level.
The board must be able to get the following information from the quarterly reports:

Revenue trends over time -

The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year

Reference:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Question: 204
Introductory Info Case Study -
To start the case study -
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board
meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four
business units.

Existing Environment -

Data and Sources -

Data for the reports comes from the sources shown in the following table.

The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.

Balance Sheet Data -

The balance sheet data is imported and includes the final monthly balances of each account in the format shown in
the following table.

The balance sheet data always includes a row for each account for each month.

Product Catalog Data -

The product catalog shows how products roll up to product categories, which roll up to the business units. The
product list is provided in the format shown in the following table.
Revenue data is provided at the date and product level. Expense data is provided at the date and department level.

Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is
usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an
Excel formula. On occasion, there are conflicting results in the reports because the products and departments that
roll up to each business unit are not defined consistently.

Requirements -

Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants
to reduce how long it takes to populate the reports to less than two days. The company wants to create common
logic for the business units, products, and departments. The logic will be used across all reports, including but not
limited to the quarterly reporting for the board.

Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and
links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.

Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group
will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the
reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.

Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the
last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the
ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.
The definitions and attributes for the products, departments, and business units must be consistent across all the
reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
The reports must be updated with the latest data by 5 AM each day. Question HOTSPOT -
You need to create a measure that returns the percent of revenue by product category.
How should you complete the measure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Answer:

Explanation:

Box 1: CALCULATE -

CALCULATE evaluates an expression in a modified filter context.

Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, �]]]) Box

2: ALL-

Box 3: DIVIDE -

DIVIDE performs a division.

Example: MEASURE FactInternetSales[%Sales] = DIVIDE([TotalSales],

CALCULATE([TotalSales],REMOVEFILTERS()))

Note: The RETURN keyword consumes variables defined in previous VAR statements.

VAR AllCategoryRev =

CALCULATE(SUM([Revenue]),
 DIVIDE(SUM([Revenue]), AllCategoryRev

https://docs.microsoft.com/en-us/dax/calculate-function-dax

https://docs.microsoft.com/en-us/dax/removefilters-function-dax https://dax.guide/st/return/

Question: 205 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board
meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four
business units.

Existing Environment -

Data and Sources -

Data for the reports comes from the sources shown in the following table.

The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.

Balance Sheet Data -

The balance sheet data is imported and includes the final monthly balances of each account in the format shown in
the following table.
The balance sheet data always includes a row for each account for each month.

Product Catalog Data -

The product catalog shows how products roll up to product categories, which roll up to the business units. The
product list is provided in the format shown in the following table.

Revenue data is provided at the date and product level. Expense data is provided at the date and department level.

Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is
usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an
Excel formula. On occasion, there are conflicting results in the reports because the products and departments that
roll up to each business unit are not defined consistently.

Requirements -

Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants
to reduce how long it takes to populate the reports to less than two days. The company wants to create common
logic for the business units, products, and departments. The logic will be used across all reports, including but not
limited to the quarterly reporting for the board.

Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and
links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.

Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group
will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the
reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.

Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the
last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the
ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.

The definitions and attributes for the products, departments, and business units must be consistent across all the
reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
The reports must be updated with the latest data by 5 AM each day. Question DRAG DROP -
Once the profit and loss dataset is created, which four actions should you perform in sequence to ensure that the
business unit analysts see the appropriate profit and loss data? To answer, move the appropriate actions from the
list of actions to the answer area and arrange them in the correct order.
Select and Place:

Answer:
 Explanation:

Create four roles

add DAX filters

Contributor role give analysts a possibility to save reports to a workspace, which is not permitted by
requirements

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

Question: 206 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board
meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four
business units.

Existing Environment -

Data and Sources -

Data for the reports comes from the sources shown in the following table.

The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.

Balance Sheet Data -

The balance sheet data is imported and includes the final monthly balances of each account in the format shown in
the following table.
The balance sheet data always includes a row for each account for each month.

Product Catalog Data -

The product catalog shows how products roll up to product categories, which roll up to the business units. The
product list is provided in the format shown in the following table.

Revenue data is provided at the date and product level. Expense data is provided at the date and department level.

Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is
usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an
Excel formula. On occasion, there are conflicting results in the reports because the products and departments that
roll up to each business unit are not defined consistently.

Requirements -

Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants
to reduce how long it takes to populate the reports to less than two days. The company wants to create common
logic for the business units, products, and departments. The logic will be used across all reports, including but not
limited to the quarterly reporting for the board.

Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and
links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.

Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group
will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the
reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.

Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the
last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the
ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.

The definitions and attributes for the products, departments, and business units must be consistent across all the
reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
The reports must be updated with the latest data by 5 AM each day. Question Which DAX expression should you
use to get the ending balances in the balance sheet reports?

A. CALCULATE ( SUM( BalanceSheet[BalanceAmount] ), LASTDATE ( 'Date'[Date] ) )

B. CALCULATE ( SUM( BalanceSheet[BalanceAmount] ), DATESQTD('Date'[Date]) )
C. FIRSTNONBLANK ('Date'[Date] SUM( BalanceSheet[BalanceAmount] ) )
D. CALCULATE ( MAX( BalanceSheet[BalanceAmount] ), LASTDATE ('Date'[Date] ))

Answer: A

Explanation:

A) - LASTDATE()

as we do not sum the balances of last 3 months

The board meeting requires quarter balance. For example, Jan - Mar. So what we need is the balance as at 31
Mar, the LASTDATE is appropriate. The balance sheet already gives you the number directly. No need to
calculate up to 3 months.

In case of using DATESQTD, daily sale and expenses will be listed in a table rather than balance in balance
sheet.

Question: 207
Introductory Info Case Study -
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Litware, Inc. is an online retailer that uses Power BI.
Litware plans to leverage data from an Azure SQL database that stores data for the company's live e-commerce
website.
Litware uses Azure Active Directory (Azure AD) to authenticate users.
Existing Environment. Sales Data
Litware has online sales data that has the SQL schema shown in the following table.
In the Date table, the date_id column has a format of yyyymmdd and the month column has a format of yyyymm.
The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww.
In the Sales table, the sales_id column represents a unique transaction.
The region id column can be managed by only one sales manager.
Existing Environment. Data Concerns
You are concerned with the quality and completeness of the sales data. You must ensure that negative and missing
sales_amount values do NOT contribute to the total sales amount calculation.
Existing Environment. Reporting Requirements
Litware identifies the following reporting requirements:
Executives require a visual that shows sales by region.
Executives require a visual that shows returns by region manager and the sales managers that report to them.
The sales managers must be able to see only the sales data of their respective region.
The sales managers require a visual to analyze sales performance versus sales targets.
The sales department requires reports that contain the number of sales transactions.
Users must be able to see the month in each report as shown in the following example: Feb 2020.
The customer service department requires a visual that can be filtered by both sales month and ship month
independently.
The maximum allowed latency to include transactions in reports is five minutes. Question You need to create
relationships to meet the reporting requirements of the customer service department.
What should you create?

A. a one-to-many relationship from Date[date_id] to Sales[sales_date_id] and another one-to-many relationship

from Date[date_id] to Weekly_Returns[week_id]
B. a one-to-many relationship from Sales[sales_date_id] to Date[date_id] and a one-to-many relationship from
Sales[sales_ship_date_id] to Date[date_id]
C. an additional date table named ShipDate, a many-to-many relationship from Sales[sales_date_id] to
Date[date_id], and a many-to-many relationship from Sales[sales_ship_date_id] to ShipDate[date_id]
D. an additional date table named ShipDate, a one-to-many relationship from Date[date_id] to
Sales[sales_date_id], and a one-to-many relationship from ShipDate[date_id] to Sales[sales_skip_date_id]

Answer: D

Explanation:

Two date dims, two 1:* relationships

The customer service department requires a visual that can be filtered by both sales month and ship month
independently.

Need two date tables. Add a one-to-many relationship from both the Date tables to Sales table.

Reference:

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Question: 208 CertyIQ

You need to provide a solution to provide the sales managers with the required access.
What should you include in the solution?

A. Create a security role that has a table filter on the Sales Manager table where username = UserName().
B. Create a security role that has a table filter on the Sales Manager table where username = sales_manager_id.
C. Create a security role that has a table filter on the Region Manager table where sales_manager_id =
UserPrincipalName().
D. Create a security role that has a table filter on the Sales_Manager table where name = UserName().

Answer: A

Explanation:
The sales managers must be able to see only the sales data of their respective region.
Use the username field of the Sales_manager table.
Also use the Username() DAX function to validate the username.

Reference:
https://powerbi.microsoft.com/en-my/blog/using-username-in-dax-with-row-level-security/

Question: 209
You merge data from Sales_Region, Region_Manager, Sales_Manager, and Manager into a single table named
Region.
What should you do next to meet the reporting requirements of the executives?

A. Create a DAX calculated column that retrieves the region manager from the Weekly_Returns table based on
the sales_region_id column.
B. Apply row-level security (RLS) to the Region table based on the sales manager username.
C. Configure a bi-directional relationship between Region and Sales_Region.
D. In the Region table, create a hierarchy that has the manager name, and then the sales manager name.

Answer: D

Explanation:

D seems to be correct because the Executives will only be able to see Region managers and Sales managers
that report to them in a hierarchy, besides there is nothing to measure there so A is actually wrong

Executives require a visual that shows returns by region manager and the sales managers that report to them.

A hierarchy is a set of fields categorized in a hierarchical way that one level is the parent of another level.
Values of the parent level can be drilled down to the lower level.

Reference:

https://radacad.com/what-a-power-bi-hierarchy-is-and-how-to-use-it

Question: 210 CertyIQ

What should you create to meet the reporting requirements of the sales department?

A. a measure that uses a formula of COUNTROWS(Sales)

B. a calculated column that use a formula of COUNTA(Sales[sales_id])
C. a calculated column that uses a formula of SUM(Sales[sales_id])
D. a measure that uses a formula of SUM(Sales[sales_id])

Answer: A
Explanation:
The sales department requires reports that contain the number of sales transactions.
The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an
expression.
Incorrect:
The COUNTA function counts the number of cells in a column that are not empty.

Reference:
https://docs.microsoft.com/en-us/dax/countrows-function-dax

Question: 211
What should you do to address the existing environment data concerns?

a measure that uses the following formula: SUMX(FILTER('Sales', 'Sales'[sales_amount] > 0)),[sales_amount])
 Answer: B
Explanation:
You are concerned with the quality and completeness of the sales data. You must ensure that negative and
missing sales_amount values do NOT contribute to the total sales amount calculation.

Question: 212

FORMAT('Date'[date],MMM YYYY)
FORMAT('Date'[date_id],MMM) & & FORMAT('Date'[year], #)
FORMAT('Date'[date_id],MMM YYYY)
FORMAT('Date'[date],M YY)

Answer: A
Explanation:
Users must be able to see the month in each report as shown in the following example: Feb 2020.

Custom date/time formats -

The following format characters can be specified in the format_string to create custom date/time formats:

Display the month as an abbreviation (Jan-Dec). Localized.

https://docs.microsoft.com/en-us/dax/format-function-dax#predefined-datetime-formats

Question: 213
HOTSPOT -
You need to design the data model and the relationships for the Customer Details worksheet and the Orders table

For each of the following statements, select Yes if the statement is true. Otherwise, select No.
 Answer:

Explanation:

Box 1: No- CustomerID in Orders is text ("VINET") while CustomerID in Customer Details is number ("1").

Box 2: Yes-Relationship between Orders and Customer Details will be via column CustomerCRMID in
Customer Details and CustomerID in Orders, which are both text.

Box 3: No - the Orders table only contains shipping address, which is different from the billing address which
should be used for sales region. Thus, it should come from Customer Details table.

No - Yes - No. According to the sample data the CustomerID in Customer Details is a number (1 through 10 is
shown in the example data) and the CustomerID in the Orders table has an example value of VINET, which
looks like it corresponds to the value of CustomerCRMID instead of CustomerID from the Customer Details
worksheet so the first answer should be No. The second answer should be Yes, the CustomerID from Orders
has example value VINET, which is text.

Question: 214
HOTSPOT -
You need to create a measure that will return the percentage of late orders.
Answer:

Explanation:
Box 1: CALCULATE -
CALCULATE evaluates an expression in a modified filter context.
Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, ¦]]]) expression - The expression to be evaluated.
filter1, filter2,.. - (Optional) Boolean expressions or table expressions that defines filters, or filter modifier
functions.
Incorrect:
* COUNTX - Counts the number of rows that contain a non-blank value or an expression that evaluates to a
 Box 2: FILTER -

Box 3: Orders[ShippedDate] > Orders[RequiredDate]

Northwind Traders defines late orders as those shipped after the required shipping date.

https://docs.microsoft.com/en-us/dax/calculate-function-dax

Question: 215 CertyIQ

You need to minimize the size of the dataset. The solution must meet the report requirements.
What should you do?

A. Group the Categories table by the CategoryID column.

B. Remove the QuantityPerUnit column from the Products table.
C. Filter out discontinued products while importing the Products table.
D. Change the OrderID column in the Orders table to the Text data type.

Answer: B
Explanation:
Removing a column which isn't used in the reports reduces the dataset size.
Incorrect:
Not A: Grouping does not affect size.
Not C: Cannot filter out discontinued products as: The reports must show historical data for the current
calendar year and the last three calendar years.
Not D: OrderID must be Integer.

Question: 216

From Power Query, add a date table. Create an active relationship to the OrderDate column in the Orders

From Power Query, add columns to the Orders table to calculate the calendar quarter and the calendar

From Power Query, use a DAX expression to add columns to the Orders table to calculate the calendar
quarter of the OrderDate column, the calendar month of the OrderDate column, the calendar quarter of the
ShippedDate column, and the calendar month of the ShippedDate column.

Answer: A
Explanation:
 Because we do have visuals that need a filter on either order or shipping date, but no visual requires a filter on
both at the same time.

Question: 217 CertyIQ

HOTSPOT -
You need to create a relationship in the dataset for RLS.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:

Explanation:

Box 1: many-to-many -

Users in the sales department must be able to access only the data of the sales region to which they are
assigned in the Sales Employees table.

With composite models, you can establish a many-to-many relationship between tables, which removes
requirements for unique values in tables. It also removes previous workarounds, such as introducing new
tables only to establish relationships.

Box 2: Customer details

Sales employees should see the sales of their region only, so all sales ordered by customers whose billing
address belongs to the sales employee's region.

Therefore, the relationship between sales employees (region) and customer details (region) should be many-
 In this case, as the customer table is related to the order table, the sales employees will only be able to see
the orders of the customers in their region.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Question: 218
You need to update the Power BI model to ensure that the analysts can quickly build drill-downs from business

What should you create?

a group
a calculated table
a hierarchy
a calculated column

Answer: C
Explanation:

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

Question: 219 CertyIQ

HOTSPOT -

Which type of filter should you use, and at which level should you apply the filter? To answer, select the
 Answer:

Explanation:
Box 1: Top N -
The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected
order month or quarter, product category, and sales region.

Box 2: Visual -
The reports must show historical data for the current calendar year and the last three calendar years.
Applying specific measures to the visual-level filter of a visualization is a very powerful technique to
completely customize the items shown in a report. The presence of this filter requires special measures in
order to display values related to items not included in the visual level filter.

Reference:
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Question: 220 CertyIQ

You need to create the On-Time Shipping report. The report must include a visualization that shows the percentage
of late orders.
Which type of visualization should you create?

A. pie chart
B. scatterplot
C. bar chart

Answer: C
Explanation:
The On-Time Shipping report will show the following metrics for a selected shipping month or quarter:
The percentage of orders that were shipped late by country and shipping region
Bar and column charts are some of the most widely used visualization charts in Power BI. They can be used for
one or multiple categories. Both these chart types represent data with rectangular bars, where the size of the
bar is proportional to the magnitude of data values.

Reference:
https://www.pluralsight.com/guides/bar-and-column-charts-in-power-bi
Question: 221 CertyIQ
HOTSPOT -
You need to create a KPI visualization to meet the reporting requirements of the sales managers.
How should you create the visualization? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:
 Explanation:
The sales managers require a visual to analyze sales performance versus sales targets.
Box 1: Sales[sales_amount]
Value; The main measure which we want to evaluate
Example:
Sales = sum(FactInternetSales[SalesAmount])
Box 2: Date[month]
Trend; How Value perfoms in a time period, is it going upward, downward¦?
You can use Months as trend axis.
Box 3: Targets[sales_target]
Target; What we want to compare the Value with

Reference:
https://radacad.com/kpi-visual-in-power-bi-explained

Question: 222
HOTSPOT -
You publish the dataset to powerbi.com.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
 Answer:

Explanation:

No - Azure SQL Server, therefore no need for an on-premise Gateway as Service and Azure are in the cloud.

No - DirectQuery mode for the DB connection, so no need to schedule a refresh. DirectQuery is a live
connection.

No - Azure SQL supports the following connections from Power BI: Windows, Database and Microsoft
Account. (Basic is reserved for Power Query Online. Do not confuse Database with Basic.):

https://learn.microsoft.com/en-us/power-query/connectors/azuresqldatabase

https://learn.microsoft.com/en-us/power-bi/connect-data/service-azure-sql-database-with-direct-connect

Question: 223
Introductory Info Case Study -

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Litware, Inc. is an online retailer that uses Power BI.
Litware plans to leverage data from an Azure SQL database that stores data for the company's live e-commerce
website.
Litware uses Azure Active Directory (Azure AD) to authenticate users.
Existing Environment. Sales Data
Litware has online sales data that has the SQL schema shown in the following table.

In the Date table, the date_id column has a format of yyyymmdd and the month column has a format of yyyymm.
The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww.
In the Sales table, the sales_id column represents a unique transaction.
The region id column can be managed by only one sales manager.
Existing Environment. Data Concerns
You are concerned with the quality and completeness of the sales data. You must ensure that negative and missing
sales_amount values do NOT contribute to the total sales amount calculation.
Existing Environment. Reporting Requirements
Litware identifies the following reporting requirements:
Executives require a visual that shows sales by region.
Executives require a visual that shows returns by region manager and the sales managers that report to them.
The sales managers must be able to see only the sales data of their respective region.
The sales managers require a visual to analyze sales performance versus sales targets.
The sales department requires reports that contain the number of sales transactions.
Users must be able to see the month in each report as shown in the following example: Feb 2020.
The customer service department requires a visual that can be filtered by both sales month and ship month
independently.
The maximum allowed latency to include transactions in reports is five minutes. Question What should you create
to meet the reporting requirements of the sales department?

A. a measure column that uses the following formula: SUMX(FILTER('Sales', 'Sales'[sales_amount] > 0)),
[sales_amount])
B. a calculated column that uses the following formula: ABS(Sales[sales_amount])
C. a calculated column that uses the following formula: IF(ISBLANK(Sales[sales_amount]),0,
(Sales[sales_amount]))
D. a measure that uses the following formula: SUM(Sales[sales_amount])

Answer: C
Explanation:

According to other websites the correct options are

A. a measure that uses a formula of SUM (sales[sales_id])

B. a calculated column that use a formula of COUNTA(sales [sales_id])

C. a measure that uses a formula of COUNTROWS (Sales)

D. a calculated column that use a formula of SUM (sales [sales_id])

The sale department require reports that contain the number of sales transactions.

Correct answer is C

Question: 224
Introductory Info Case Study -

To start the case study -

the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board
meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four
business units.

Existing Environment -

Data and Sources -

Data for the reports comes from the sources shown in the following table.

The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.

Balance Sheet Data -

The balance sheet data is imported and includes the final monthly balances of each account in the format shown in
the following table.

The balance sheet data always includes a row for each account for each month.

Product Catalog Data -

The product catalog shows how products roll up to product categories, which roll up to the business units. The
product list is provided in the format shown in the following table.
Revenue data is provided at the date and product level. Expense data is provided at the date and department level.

Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is
usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an
Excel formula. On occasion, there are conflicting results in the reports because the products and departments that
roll up to each business unit are not defined consistently.

Requirements -

Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants
to reduce how long it takes to populate the reports to less than two days. The company wants to create common
logic for the business units, products, and departments. The logic will be used across all reports, including but not
limited to the quarterly reporting for the board.

Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and
links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.

Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group
will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the
reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.

Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the
last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the
ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.
The definitions and attributes for the products, departments, and business units must be consistent across all the
reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
The reports must be updated with the latest data by 5 AM each day. Question HOTSPOT -
You need to grant access to the business unit analysts.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:
 Explanation:

Box 1: The Viewer role to the workspace

The Viewer role gives a read-only experience to its users. They can view dashboards, reports, or workbooks in
the workspace, but can't browse the datasets or dataflows. Use the Viewer role wherever you would
previously use a classic workspace set to Members can only view Power BI content.

Box 2: Build -

The analysts must be able to build new reports from the dataset that contains the profit and loss data.

Scenario: The reports must be made available to the board from powerbi.com.

The analysts responsible for each business unit must see all the data the board sees, except the profit and
loss data, which must be restricted to only their business unit's data. The analysts must be able to build new
reports from the dataset that contains the profit and loss data, but any reports that the analysts build must
not be included in the quarterly reports for the board. The analysts must not be able to share the quarterly
reports with anyone.

Reference:

https://www.nickyvv.com/2019/08/the-new-power-bi-workspace-viewer-role-explained.html

Deploy and Maintain Deliverables

Question: 225
Introductory Info Case Study -
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board
meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four
business units.

Existing Environment -

Data and Sources -

Data for the reports comes from the sources shown in the following table.

The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.

Balance Sheet Data -

The balance sheet data is imported and includes the final monthly balances of each account in the format shown in
the following table.

The balance sheet data always includes a row for each account for each month.

Product Catalog Data -

The product catalog shows how products roll up to product categories, which roll up to the business units. The
product list is provided in the format shown in the following table.
Revenue data is provided at the date and product level. Expense data is provided at the date and department level.

Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is
usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an
Excel formula. On occasion, there are conflicting results in the reports because the products and departments that
roll up to each business unit are not defined consistently.

Requirements -

Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants
to reduce how long it takes to populate the reports to less than two days. The company wants to create common
logic for the business units, products, and departments. The logic will be used across all reports, including but not
limited to the quarterly reporting for the board.

Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and
links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.

Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group
will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the
reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.

Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the
last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the
ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.
The definitions and attributes for the products, departments, and business units must be consistent across all the
reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
The reports must be updated with the latest data by 5 AM each day. Question HOTSPOT -
How should you distribute the reports to the board? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer:
 Explanation:

1. Using an App

2. A mail-enabled security group in Azure Active Directory

Box 1: Using an App

Box 2: A mail-enabled security group in Azure Active Directory

Mail-Enabled Security Group -

This group also contains a list of email addresses of members and can also be used to control access to
OneDrive and SharePoint.

The Mail-Enabled Security Group can be created in the Office 365 Admin Portal

Note: The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure
AD) group will be used to share information with the board.

Incorrect:

* Distribution Group

This group can also be called and Distribution List. The Distribution Group is a group which contains a list of
email addresses of members, all of whom will be sent an email when an email is sent to the distribution groups
email address.

The Distribution Group can be created in the Azure Active Directory

Reference:

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-share-dashboards
https://www.fourmoo.com/2020/04/01/power-bi-which-groups-can-be-used-to-set-permissions-in-power-bi/

Question: 226 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board
meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four
business units.
Existing Environment -

Data and Sources -

Data for the reports comes from the sources shown in the following table.

The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.

Balance Sheet Data -

The balance sheet data is imported and includes the final monthly balances of each account in the format shown in
the following table.

The balance sheet data always includes a row for each account for each month.

Product Catalog Data -

The product catalog shows how products roll up to product categories, which roll up to the business units. The
product list is provided in the format shown in the following table.

Revenue data is provided at the date and product level. Expense data is provided at the date and department level.

Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is
usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an
Excel formula. On occasion, there are conflicting results in the reports because the products and departments that
roll up to each business unit are not defined consistently.

Requirements -
Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants
to reduce how long it takes to populate the reports to less than two days. The company wants to create common
logic for the business units, products, and departments. The logic will be used across all reports, including but not
limited to the quarterly reporting for the board.

Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and
links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.

Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group
will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the
reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.

Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the
last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the
ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.

The definitions and attributes for the products, departments, and business units must be consistent across all the
reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
The reports must be updated with the latest data by 5 AM each day. Question You need to ensure that the data is
updated to meet the report requirements. The solution must minimize configuration effort.
What should you do?

A. From each report in powerbi.com, select Refresh visuals.

B. From Power BI Desktop, download the PBIX file and refresh the data.
C. Configure a scheduled refresh without using an on-premises data gateway.
D. Configure a scheduled refresh by using an on-premises data gateway.

Answer: C
Explanation:

C is the answer. The database is on Azure database, not on-premise

 "Scheduled refresh of reports isn’t supported with Dynamics 365 (on-premises) datasets that are published to
the Power BI service. You can refresh reports using in Microsoft Power BI Desktop or Microsoft Office Excel
and then upload the reports to the Power BI service."

So D is impossible. C is correct.

Question: 227 CertyIQ

Introductory Info Case Study -
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to
complete each case. However, there may be additional case studies and sections on this exam. You must manage
your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot return
to this section.

To start the case study -

To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment and problem statements. If the case study has an
All Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.

Overview -
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board
meetings for which financial analysts manually prepare
Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four
business units.

Existing Environment -

Data and Sources -

Data for the reports comes from the sources shown in the following table.

The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.

Balance Sheet Data -

The balance sheet data is imported and includes the final monthly balances of each account in the format shown in
the following table.
The balance sheet data always includes a row for each account for each month.

Product Catalog Data -

The product catalog shows how products roll up to product categories, which roll up to the business units. The
product list is provided in the format shown in the following table.

Revenue data is provided at the date and product level. Expense data is provided at the date and department level.

Business Issues -
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is
usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an
Excel formula. On occasion, there are conflicting results in the reports because the products and departments that
roll up to each business unit are not defined consistently.

Requirements -

Planned Changes -
Contoso plans to automate and standardize the quarterly reporting process by using Power BI. The company wants
to reduce how long it takes to populate the reports to less than two days. The company wants to create common
logic for the business units, products, and departments. The logic will be used across all reports, including but not
limited to the quarterly reporting for the board.

Technical Requirements -
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and
links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.

Security Requirements -
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group
will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
Analysts must be able to access all balance sheet and product catalog data.
Analysts must be able to access only the profit and loss data of their respective business unit.
Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the
reports built by the analysts must NOT be included in the quarterly reports for the board.
Analysts must NOT be able to share the quarterly reports with anyone.
Analysts must NOT be able to make new reports by using the balance sheet data.

Report Requirements -
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the
last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the
ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.

The definitions and attributes for the products, departments, and business units must be consistent across all the
reports.
The board must be able to get the following information from the quarterly reports:
Revenue trends over time
The ending balances of each account
Changes in long-term liabilities from the previous quarter
The percent of total revenue contributed by each product category
A comparison of quarterly revenue versus the same quarter from the previous year
The reports must be updated with the latest data by 5 AM each day. Question What is the minimum number of
Power BI datasets needed to support the reports?

A. a single imported dataset

B. two imported datasets
C. two DirectQuery datasets
D. a single DirectQuery dataset

Answer: A

Explanation:

A. a single imported dataset.

There are 2 sources, but common logic must be applied (and the same time dimension). So the data will come
together in one dataset.

Minimum load time must be 2 days, so import is fine.