Question 1: 

Incorrect
Scenario: You work for Avengers Security where you are required to build Microsoft Power BI
reports. The data resides in several different databases and files. These data repositories are
different from each other, some are in Microsoft SQL Server, some are in Microsoft Excel, but
all the data is related.

You’ve been tasked by senior leadership to create a suite of reports that are dependent on data in
several different locations. The database that tracks sales transactions is in SQL Server, a
relational database contains which customer bought which items and when. It also tracks which
employee made the sale, along with the employee name and employee ID. However, that
database doesn’t contain the employee’s hire date, their title, or who their manager is. For that
information, you need to access files that Human Resources keeps in Excel. You've been
consistently requesting that they use an SQL database, but they haven't yet had the chance to
implement it.

When an item ships, the shipment is recorded in the warehousing application, which is new to
the company. The developers chose to store data in CosmosDB, as a set of JSON documents.

Avengers Security has an application that helps with financial projections, so that they can
predict what their sales will be in future months and years, based on past trends. Those
projections are stored in Microsoft Azure Analysis Services. Below is a view of the data sources
you are asked to combine data from.

Larger image
 Required: The Human Resources (HR) team at Avengers Security has prepared a flat file that
contains some of your organization's employee data, such as employee name, hire date, position,
and manager. They've requested that you build Power BI reports by using this data, and data that
is located in several other data sources.

What is the first thing to be done?


Determine which file location that you want to use to export and store your data.
(Correct)


Use Power Query to help you clean the data
(Incorrect)


Publish your combined dataset and reports to Power BI service

Build reports in Power BI

Explanation
• Before you can create reports, you must first extract data from the various data
sources. Interacting with SQL Server is different from Excel, so you should learn the nuances of
both systems.

• After you’ve learned the particulars of each system, you can use Power Query (the query
engine used by Power BI and Excel) to help you clean the data, such as renaming columns,
replacing values, removing errors, and combining query results.

• After the data has been cleaned and organized, you are ready to build reports in Power BI.

• Finally, you will publish your combined dataset and reports to Power BI service (PBIS). From
there, other people can use your dataset and build their own reports or they can use the reports
that you’ve already built.

Flat file location

The first step is to determine which file location that you want to use to export and store your
data.

Your Excel files might exist in one of the following locations:

• Local - You can import data from a local file into Power BI. The file isn't moved into Power
BI, and a link doesn't remain to it. Instead, a new dataset is created in Power BI, and data from
the Excel file is loaded into it. Accordingly, changes to the original Excel file are not reflected in
your Power BI dataset. You can use local data import for data that doesn't change.

• OneDrive for Business - You can pull data from OneDrive for Business into Power BI. This
method is effective in keeping an Excel file and your dataset, reports, and dashboards in Power
BI synchronized. Power BI connects regularly to your file on OneDrive. If any changes are
found, your dataset, reports, and dashboards are automatically updated in Power BI.

• OneDrive - Personal - You can use data from files on a personal OneDrive account, and get
many of the same benefits that you would with OneDrive for Business. However, you'll need to
sign in with your personal OneDrive account, and select the Keep me signed in option. Check
with your system administrator to determine whether this type of connection is allowed in your
organization.

• SharePoint - Team Sites - Saving your Power BI Desktop files to SharePoint Team Sites is
similar to saving to OneDrive for Business. The main difference is how you connect to the file
from Power BI. You can specify a URL or connect to the root folder.
 https://docs.microsoft.com/en-us/power-bi/fundamentals/service-get-started
Question 2: Incorrect
Scenario: You have created a report for the Sales team at Avengers Security that displays the
sales data in the SQL Server database. The report gives a holistic view of how the Sales team is
performing. While the report is useful, the Sales team members want to be able to filter the
report so that they can view their own data only and track their performance against their sales
targets.

What used be done to accommodate this want?


Quick Insights


Query Caching


Scheduled refresh
(Incorrect)


 Lineage view


Usage Metrics report


Dynamic reports
(Correct)

Explanation
Dynamic reports are reports in which the data can be changed by a developer according to user
specifications. Dynamic reports are valuable because a single report can be used for multiple
purposes. If you use dynamic reports, you'll have fewer individual reports to create, which will
save organizational time and resources.

You can use parameters by determining the values that you want to see data for in the report, and
the report updates accordingly by filtering the data for you.

Creating dynamic reports allows you to give users more power over the data that is displayed in
your reports; they can change the data source and filter the data by themselves.

In the following example, you've created a report for the Sales team at Avengers Security that
displays the sales data in the SQL Server database. The report gives a holistic view of how the
Sales team is performing. While the report is useful, the Sales team members want to be able to
filter the report so that they can view their own data only and track their performance against
their sales targets.

Create dynamic reports for individual values

To create a dynamic report, you first need to write your SQL query. Then, you will use the Get
data feature in Power BI Desktop to connect to the database.

In this example, you will connect to your database on SQL Server by following these steps:

1. After you have entered your server details, in the SQL Server database window,
select Advanced options.

2. Paste the SQL query into the SQL statement box and then select OK.
When the connection is made, the data will be shown in the preview window.

1. Select Edit to open the data in Power Query Editor.

Next, you will create the parameter by following these steps:

1. On the Home tab, select Manage parameters → New parameter.

2. On the Parameters window, change the default parameter name to something more
descriptive so that its purpose is clear. In this case, you will change the name to SalesPerson.

3. Select Text from the Type list and then select Any value from the Suggested value list.

4. Select OK.

A new query will now be shown for the parameter that you created.
Now, you need to adjust the code in SQL query to assess your new parameter:

1. Right-click Query1 and then select Advanced editor.

2. Replace the existing value in the execute statement with an ampersand (&) followed by your
parameter name (SalesPerson), as illustrated in the following image.

3. Make sure that no errors are shown at bottom of the window and then select Done.

Though you won't notice a difference on the screen, Power BI will have run the query.

1. To confirm that the query was run, you can run a test by selecting the parameter query and
entering a new value in the Current Value box.
2. A warning icon might display next to the query. If so, select that query to view the warning
message, which states that permission is required to run this native database query. Select Edit
Permission and then select Run.

When the query runs successfully, the parameter will update and display the new value.

3. Select Close and Apply to return to the report editor.

Now, you can apply the parameter to the report:

1. Select Edit queries → Edit parameters.

2. On the Edit Parameters window, enter a new value and then select OK.

3. Select Apply changes and then run the native query again.

4. Now, when you view the data, you'll see the data for the new value that was passed through
the parameter.
You can now create a report that displays data for one particular value at a time. If you want to
display data for multiple values at the same time, you need to complete additional steps, as
outlined in the next section.

Create dynamic reports for multiple values

To accommodate multiple values at a time, you first need to create a Microsoft Excel worksheet
that has a table consisting of one column that contains the list of values.

Next, use the Get data feature in Power BI Desktop to connect to the data in that Excel
worksheet, and then follow these steps:

1. On the Navigator window, select Edit to open the data in Power Query Editor, where you'll
see a new query for the data table.
2. Rename the column in the table to something more descriptive.

3. Change the column data type to Text so that it matches the parameter type and you avoid data
conversion problems.

4. In the query Properties section, change the name of the data source to something more
descriptive. For this example, enter SalesPersonID.

Next, you need to create a function that will pass the new SalesPersonID query into Query1:

1. Right-click Query1 and then select Create function.

2. Enter a name for the function and then select OK.

Your new function will appear in the Queries pane.

1. To ensure that Query1 doesn't show up in the field list for the report, which could potentially
confuse users, you can disable it from loading in the report by right-clicking Query1 again and
then selecting Enable load (selected by default) to disable the feature.
2. Select the SalesPerson query that you loaded from the Excel worksheet and then, on the Add
Column tab, select Invoke custom function to run the custom function that you created.
3. On the Invoke Custom Function window, select your function from the Function query list.

The New column name will update automatically and the table that contains the values that
you're going to pass through the parameter will be selected by default.

1. Select OK and, if necessary, run the native query.

A new column for your GetSalesFromSalesPerson function will appear next to

the SalesPersonID column.

2. Select the two arrows icon in the new column header and then select the check boxes of the
columns that you want to load. This section is where you will determine the details that will be
available in the report for each value (sales person ID).

3. Clear the Use original column name as prefix check box at the bottom of the screen because
you don't need to see a prefix with the column names in the report.
4. Select OK

You should be able to view the data for the columns that you selected, for each value (sales
person ID).
 4.If necessary, you can add more values (sales people IDs) to the SalesPersonID column in the
Excel worksheet, or you can change the existing values.

1. Save your changes and then return to Power Query Editor.

2. On the Home tab, select Refresh Preview, and then run the native query again (if necessary).
You should see the sales from the new sales people IDs that you added into the worksheet.

3. Select Close and Apply to return to the report editor, where you'll see the new column names
in the Fields pane.

Now, you can start building your report.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

Question 3: Incorrect
Scenario: You have been hired as a consultant by Advanced Idea Mechanics to oversee a large
Power BI project. One of the analysts is working on Power BI project asks you “Which view will
show the graphical representation of data table structures in the Query Editor?”

Which of the following should you respond to them with?


Logic


Combiner


Advance Editor
(Incorrect)


List

Manage Data-source


Query Dependency
(Correct)

Explanation
Query dependency reflects the structure and hierarchy of tables in graphical format.

Query Dependencies show you how the queries are linked together inside Power BI. You can
see which queries depend on other queries or tables. This is useful when you plan to do a lot of
data transformations inside your model.

Understanding Query Dependencies Inside Power BI

To get started, click on the View ribbon, and then click Query Dependencies.

The first thing that you can see in the dependency tree is the source folder of the initial query.

Under the initial query, you can see that it branched out into several other queries. You have
the Customers, Products, Regions, and Sales table queries. Just by looking at this, you already
have an overview of how these queries are connected.

I’ll show you an example of how query dependencies work inside Power BI. For instance, we
already have our Channel column and Channel Code column.
What I want to do is create another table of this. I want to break out this information and put it
into another table.

This is a good idea because as you can see in these columns, there’s a lot of repetitive
information, especially when you have multiple columns containing similar things.

In theory, we can just have the Channel and Channel Code columns in another table by
themselves, and then draw a relationship between the Channel table down to our Sales table.

To do that, right-click the Sales table, and then select Reference.

Next, rename the duplicated sales query from Sales (2) into the Channels table. In the
new Channels table, click on the Channel column, hold down the Ctrl key, and click
the Channel Code column.

While still pressing Ctrl, right-click the said columns, and then select Remove Other Columns.
After that, only the Channel and Channel Code columns will remain in the Channels table.
Now, you need to create a lookup table for all your channels using the Channels table. But first,
you need to remove the duplicate entries by right-clicking and selecting Remove Duplicates.
This is an efficient way to create your channel codes.

As you can see, only three entries are left in both the columns. Now, click
the Add Column ribbon and then select Column From Examples. You need to do this so you
can abbreviate your channel codes using the first letter.
By doing this, you can create a new column and add the Channel Short Code table.
If you look back into the Query Dependencies view, you can now see the connection between
the Sales table query and the Channels table.
https://blog.enterprisedna.co/the-query-dependencies-feature-in-power-bi/
You can get valuable business insights by visualizing hierarchically related data. The
hierarchical modelling and visualization capabilities give you a number of benefits:

• View and explore complex hierarchical information.

• View key performance indicators (KPIs) in the contextual view of a hierarchy.

• Visually analyze key information across the web and the tablets.

For some tables, such as account and user, the visualizations are provided out-of-the-box. Other
tables, including custom tables, can be enabled for a hierarchy and you can create the
visualizations for them. Based on your needs, you can choose between using a tree view, which
shows the entire hierarchy, or a tile view, which depicts a smaller portion of the hierarchy. Both
views are shown side by side. You can explore a hierarchy by expanding and contracting a
hierarchy tree. The same hierarchical settings for visualization are set once, but apply to both
web and mobile clients. In tablets, the visuals render in a modified format suitable for the smaller
form factor. The customizable components required for hierarchical visualization are solution
aware, therefore, they can be transported between organizations like any other customization.
You can configure the attributes shown in the visualization by customizing a Quick Form using
the form editor. There is no requirement to write code.

Query hierarchical data

With Microsoft Dataverse, hierarchical data structures are supported by self-referential

relationships of the related rows. In the past, to view hierarchical data, you had to iteratively
query for the related rows. Presently, you can query the related data as a hierarchy, in one step.
You’ll be able to query rows using the Under and Not Under logic. The Under and Not
Under hierarchical operators are exposed in Advanced Find and the workflow editor.

The following examples illustrate various scenarios for querying hierarchies:

Query account hierarchy

Query account hierarchy, including related activities
Query account hierarchy, including related opportunities
 https://docs.microsoft.com/en-us/powerapps/maker/data-platform/query-visualize-hierarchical-
data
Question 4: Incorrect
Unlike other database management systems, Power BI has the concept of directionality to a
relationship. This directionality, or cardinality, plays an important role in filtering data between
multiple tables. When you load data, Power BI automatically looks for relationships that exist
within the data by matching column names.

True or False: For one-to-one relationships, both single direction or bi-directional cross-filtering

are available.


True
(Incorrect)


False
(Correct)

Explanation
Unlike other database management systems, Power BI has the concept of directionality to a
relationship. This directionality, or cardinality, plays an important role in filtering data between
multiple tables. When you load data, Power BI automatically looks for relationships that exist
within the data by matching column names. You can also use Manage Relationships to edit
these options manually.

For example, you've retrieved many tables from the Sales database, and the following image is
an example of your data model. Power BI has autodetected several relationships, but you can't
discern what they mean. You want to make sure that the relationships accurately reflect those
that exist in your data.
Cardinality

The term “cardinality” in database design has to do with counting tables and values. With that
said, cardinality has three main definitions. It can relate to counting the number of elements in a
set, identifying the relationships between tables, or describing how database tables contain a
number of values, and what those tables look like in general.

The following are different types of cardinality that you'll find in Power BI.

Many-to-one (*:1) or one-to-many (1: *) cardinality:

• Describes a relationship in which you have many instances of a value in one column that are
related to only one unique corresponding instance in another column.

• Describes the directionality between fact and dimension tables.

• Is the most common type of directionality and is the Power BI default when you are
automatically creating relationships.

An example of a one-to-many relationship would be between the CountryName and Territory

tables, where you can have many territories that are associated with one unique country.

One-to-one (1:1) cardinality:

• Describes a relationship in which only one instance of a value is common between two tables.

• Requires unique values in both tables.

• Is not recommended because this relationship stores redundant information and suggests that
the model is not designed correctly. It is better practice to combine the tables.
An example of a one-to-one relationship would be if you had products and product IDs in two
different tables. Creating a one-to-one relationship is redundant and these two tables should be
combined.

Many-to-many (.) cardinality:

• Describes a relationship where many values are in common between two tables.

• Does not require unique values in either table in a relationship.

• Is not recommended; a lack of unique values introduces ambiguity and your users might not
know which column of values is referring to what.

For instance, the following figure shows a many-to-many relationship between the Sales and
Order tables on the OrderDate column because multiple sales can have multiple orders
associated with them. Ambiguity is introduced because both tables can have the same order date.
Cross-filter direction

Data can be filtered on one or both sides of a relationship.

With a single cross-filter direction:

• Only one table in a relationship can be used to filter the data. For instance, Table 1 can be
filtered by Table 2, but Table 2 cannot be filtered by Table 1.

Tip: Follow the direction of the arrow on the relationship between your tables to know which
direction the filter will flow. You typically want these arrows to point to your fact table.

• For a one-to-many or many-to-one relationship, the cross-filter direction will be from the "one"
side, meaning that the filtering will occur in the table that has unique values.

With both cross-filter directions or bi-directional cross-filtering:

• One table in a relationship can be used to filter the other. For instance, a dimension table can be
filtered through the fact table, and the fact tables can be filtered through the dimension table.

• You might have lower performance when using bi-directional cross-filtering with many-to-
many relationships.

A word of caution regarding bi-directional cross-filtering: You should not enable bi-directional
cross-filtering relationships unless you fully understand the ramifications of doing so. Enabling it
can lead to ambiguity, over-sampling, unexpected results, and potential performance
degradation.

Cardinality and cross-filter direction

For one-to-one relationships, the only option that is available is bi-directional cross-
filtering. Data can be filtered on either side of this relationship and result in one distinct,
unambiguous value. For instance, you can filter on one Product ID and be returned a single
Product, and you can filter on a Product and be returned a single Product ID.

For many-to-many relationships, you can choose to filter in a single direction or in both
directions by using bi-directional cross-filtering. The ambiguity that is associated with bi-
directional cross-filtering is amplified in a many-to-many relationship because multiple paths
will exist between different tables. If you create a measure, calculation, or filter, unintended
consequences can occur where your data is being filtered and, depending on which relationship
that the Power BI engine chooses when applying the filter, the final result might be different.
This situation is also true for bi-directional relationships and why you should be cautious when
using them.

For this reason, many-to-many relationships and/or bi-directional relationships are complicated.
Unless you are certain what your data looks like when aggregated, these types of open-ended
relationships with multiple filtering directions can introduce multiple paths through the data.

Create many-to-many relationships

Consider the scenario where you are tasked with building a visual that examines budgets for
customers and accounts. You can have multiple customers on the same account and multiple
accounts with the same customer, so you know that you need to create a many-to-many
relationship.

To create this relationship, go to Manage Relationships → New. In the resulting window, create

a relationship between the Customer ID column in CustomerTable and AccountTable. The
cardinality is set to many-to-many, and the filter type is in both directions. Immediately, you will
be warned that you should only use this type of relationship if it is expected that neither column
will have unique values because you might get unexpected values. Because you want to filter in
both directions, choose bi-directional cross-filtering.
Select OK. You have now successfully created a many-to-many relationship.

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

Question 5: Correct
Scenario: You are working as a consultant at O'Shaughnessy’s restaurants. A member of your
team is working on a Power BI project and is currently working on adding a visualization to a
dashboard as shown below.

Larger image
 What is the specific name for visualizations when they are added to a dashboard?


Dataset


Report


Tile
(Correct)


Bar chart

Explanation
Add or edit Power BI visualizations on your dashboard

You can create rich, interactive reports and real-time visualizations with Power BI dashboards
and tiles that you add to your personal dashboards.

https://docs.microsoft.com/en-us/powerapps/user/add-powerbi-dashboards

Question 6: Correct
Which of the following selections are not features of the Q&A visual?

Searching for help topics about Power BI.
(Correct)


All the listed items are features of the Q&A visual


Adding new synonyms to fields through Q&A tooling.


Converting a Q&A answer into a visual inside your report.

Explanation
Searching for help topics about Power BI is not a feature of the Q&A visual.

The Q&A feature in Power BI lets you explore your data in your own words by allowing you to
ask natural language questions and then providing you with answers to those questions.

This ability to ask questions is valuable to you, as the report author, and to your report users. It
gives you ideas for the type of visuals that you can display in your report and lets you quickly
add those visuals. Additionally, it gives your report users an effective tool that they can use to
get quick answers to their questions about the data, independently. This self-help aspect to Power
BI saves time for everyone involved.

Power BI records all questions that are asked, and you can use this information to set up
the Q&A feature to be more effective. When the Q&A feature answers so many questions, you'll
have fewer people turning to you for those answers.

Consider a situation where you've created a report for the Supply Chain team, and now the team
members have questions about various views or insights that they are interested in. You have
become inundated with questions and don't have time to address each one individually.
Consequently, you have decided to implement the Q&A feature so that users can ask questions
and get answers by themselves.

Add the Q&A visual to your report canvas

To get access to the Q&A feature, you need to add the Q&A visual to your report. You can
double-click anywhere on the canvas, and the visual should appear. Alternatively, you can select
the Q&A icon on the Visualizations pane.
The Q&A feature is also available as a button, which is a useful option if you want to save space
on your report canvas.
When the Q&A visual or button is added to your report, you can reposition and resize it. You
can also customize the formatting in the same way that you would for any other type of visual or
button.

You can start asking questions immediately by selecting one of the suggested questions or by
entering a question into the question box. As you type, Power BI will automatically display
suggestions to help you complete your question.
Set up the Q&A feature

When you have added the Q&A visual to your report, you can set up the underlying Q&A
feature so that it improves in answering questions about your data. Basically, you will teach
the Q&A feature to better understand people. This setup can be useful from the outset so that
you can get the visual ready for active use. However, setup does not stop at that point; you can
proactively monitor and review the questions that are coming through from users, and then you
can address misunderstandings or common typos. You can also manage the key terms that are
associated with your data, so you can add a library of synonyms that might be entered by
different users across the organization when they are asking questions about the data. You can
constantly calibrate the Q&A feature so that it provides better answers to your organization's
questions.

In this example, you receive feedback from users saying that they can't get data on sales by
country. To assess the problem, you enter sales by country into the question box. The visual
does not update; it does not answer your question. The following image shows that the
word country is underlined in red. When a red underline occurs, Power BI is telling you that it
does not understand this term. Knowing that the word country is not used in your dataset, you
decide to use the term region instead and ultimately discover why the question is not being
answered. Accordingly, you realize that you need to teach Power BI what you mean by adding a
new term to its thesaurus.

Select the settings icon to the right of the question box to open the Q&A setup window. Then,
select the Teach Q&A option.
Enter your question again, and then select the Submit button. In the Define the terms Q&A
didn't understand section that displays, enter your alternative term or synonym. In this case,
you will enter region. The following image shows that Power BI displays a preview result so
that you can see if this new term will return the results that you are looking for. If this result is
correct, select Save.
Now, when users search for sales by country, Power BI will know that they really mean sales by
region and will automatically display the associated data in the visual.
Use the Q&A feature to build visuals

When Power BI answers a question, and you find the visual result to be engaging or helpful, you
can add it as a standard visual to your report.

For example, if you review the questions that are being asked, and you see that several users are
asking the same question, you can add the answer to this question as a standard visual in the
report so they no longer have to ask the question. Similarly, you can also use the Q&A feature to
start building your report by asking questions and adopting the suggested visual result formats
from Power BI.

To turn a Q&A result into a standard visual, select the icon next to the question box.
 The Q&A feature is unique in that it does not require users to have knowledge of Power BI to
use the visual; users can ask their questions and they, too, can create insightful visuals.

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-tutorial-q-and-a

Question 7: Correct
True or False: Higher cardinality leads to more optimized performance in Power BI.

True


False
(Correct)

Explanation
Cardinality is a term that is used to describe the uniqueness of the values in a column.
Cardinality is also used in the context of the relationships between two tables, where it describes
the direction of the relationship.

Identify cardinality levels in columns

Previously, when you used Power Query Editor to analyze the metadata, the Column
distribution option on the View tab displayed statistics on how many distinct and unique items
were in each column in the data.
• Distinct values count - The total number of different values found in a given column.

• Unique values count - The total number of values that only appear once in a given column.

A column that has a lot of repeated values in its range (distinct count is low) will have a low
level of cardinality. Conversely, a column that has a lot of unique values in its range (unique
count is high) will have a high level of cardinality.

Lower cardinality leads to more optimized performance, so you might need to reduce the number
of high cardinally columns in your dataset.

Reduce relationship cardinality

When you import multiple tables, it is possible that you'll do some analysis by using data from
all those tables. Relationships between those tables are necessary to accurately calculate results
and display the correct information in your reports. Power BI Desktop helps make creating those
relationships easier. In fact, in most cases, you won't have to do anything, the autodetect feature
does it for you. However, you might occasionally have to create relationships or need to make
changes to a relationship. Regardless, it's important to understand relationships in Power BI
Desktop and how to create and edit them.
When you create or edit a relationship, you can configure additional options. By default, Power
BI Desktop automatically configures additional options based on its best guess, which can be
different for each relationship based on the data in the columns.

The relationships can have different cardinality. Cardinality is the direction of the relationship,
and each model relationship must be defined with a cardinality type. The cardinality options in
Power BI are:

• Many-to-one (*:1) - This relationship is the most common, default type. It means that the
column in one table can have more than one instance of a value, and the other related table, often
known as the lookup table, has only one instance of a value.

• One-to-one (1:1) - In this relationship type, the column in one table has only one instance of a
particular value, and the other related table has only one instance of a particular value.

• One-to-many (1:*) - In this relationship type, 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.

• Many-to-many (:) - 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.

During development, you will be creating and editing relationships in your model, so when you
are building new relationships in your model, regardless of what cardinality you have chosen,
always ensure that both of the columns that you are using to participate in a relationship are
sharing the same data type. Your model will never work if you try to build a relationship
between two columns, where one column has a text data type and another column has an integer
data type.

In the following example, the ProductID field has the data type Whole number in the Product
and Sales tables. The columns with data type Integer perform better than columns with data
type Text.
Improve performance by reducing cardinality levels

Power BI Desktop offers different techniques that you can use to help reduce the data that is
loaded into data models, such as summarization. Reducing the data that is loaded into your
model will improve the relationship cardinality of the report. For this reason, it is important that
you strive to minimize the data that will be loaded into your models. This case is especially true
for large models, or models that you anticipate will grow to become large over time.

Perhaps the most effective technique to reduce a model size is to use a summary table from the
data source. Where a detail table might contain every transaction, a summary table would contain
one record per day, per week, or per month. It might be an average of all of the transactions per
day, for instance.

For example, a source sales fact table stores one row for each order line. Significant data
reduction could be achieved by summarizing all sales metrics if you group by date, customer,
and product, and individual transaction detail is not needed.

Consider, then, that an even more significant data reduction could be achieved by grouping by
date at month level. It could achieve a possible 99 percent reduction in model size; but, reporting
at day level or an individual order level is no longer possible. Deciding to summarize fact-type
data will always involve a tradeoff with the detail of your data. A disadvantage is that you may
lose the ability to drill into data because the detail no longer exists. This tradeoff could be
mitigated by using a mixed model design.

In Power BI Desktop, a Mixed mode design produces a composite model. Essentially, it allows
you to determine a storage mode for each table. Therefore, each table can have its Storage
Mode property set as Import or DirectQuery.
 An effective technique to reduce the model size is to set the Storage Mode property for larger
fact-type tables to DirectQuery. This design approach can work well in conjunction with
techniques that are used to summarize your data. For example, the summarized sales data could
be used to achieve high performance "summary" reporting. A drill-through page could be created
to display granular sales for specific (and narrow) filter context, displaying all in-context sales
orders. The drill-through page would include visuals based on a DirectQuery table to retrieve the
sales order data (sales order details).

For more information, see Data reduction techniques for Import modelling .

https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction#group-by-
and-summarize/?azure-portal=true

Question 8: Correct
Identify the missing word(s) in the following sentence within the context of Microsoft.

An app in Power BI is a collection of preset, ready-made visuals and reports that are shared with
an entire organization. To import data into the Power BI service, you can select [?] in the lower-
left corner of the home page.


Get Data
(Correct)


Upload


Import


Link

Explanation
An app is a collection of preset, ready-made visuals and reports that are shared with an entire
organization. Using an app is like microwaving a TV dinner or ordering a fast-food value meal:
you just have to press a few buttons or make a few comments, and you're quickly served a
collection of entrees designed to go together, all presented in a tidy, ready-to-consume package.

With Power BI, connecting to data is easy. From the Power BI service, you can just select
the Get Data button in the lower-left corner of the home page.
The canvas (the area in the centre of the Power BI service) shows you the available sources of
data in the Power BI service. In addition to common data sources like Microsoft Excel files,
databases, or Microsoft Azure data, Power BI can just as easily connect to a whole assortment
of software services (also called SaaS providers or cloud services): Salesforce, Facebook,
Google Analytics, and more.
For these software services, the Power BI service provides a collection of ready-made visuals
that are pre-arranged on dashboards and reports for your organization. This collection of visuals
is called an app. Apps get you up and running quickly, with data and dashboards that your
organization has created for you. For example, when you use the GitHub app, Power BI connects
to your GitHub account (after you provide your credentials) and then populates a predefined
collection of visuals and dashboards in Power BI.
There are apps for all sorts of online services. The following image shows a page of apps that are
available for different online services, in alphabetical order. This page is shown when you select
the Get button in the Services box (shown in the previous image). As you can see from the
following image, there are many apps to choose from.
For our purposes, we'll choose GitHub. GitHub is an application for online source control.
When you select the Get it now button in the box for the GitHub app, the Connect to
GitHub dialog box appears. Note that GitHub does not support Internet Explorer, so make sure
you are working in another browser.

After you enter the information and credentials for the GitHub app, installation of the app begins.
After the data is loaded, the predefined GitHub app dashboard appears.
In addition to the app dashboard, the report that was generated (as part of the GitHub app) and
used to create the dashboard is available, as is the dataset (the collection of data pulled from
GitHub) that was created during data import and used to create the GitHub report.
You can select any of the visuals and interact with them. As you do so, all the other visuals on
the page will respond. For example, when the AdamBJ in the treemap on the Pull
Requests report the other visuals on the page adjust to reflect that selection.
Update data in the Power BI service

You can also choose to update the dataset for an app, or other data that you use in Power BI. To
set update settings, select the schedule update icon for the dataset to update, and then use the
menu that appears. You can also select the update icon (the circle with an arrow) next to the
schedule update icon to update the dataset immediately.
The Datasets tab is selected on the Settings page that appears. In the right pane, select the arrow
next to Scheduled refresh to expand that section. The Settings dialog box appears on the
canvas, letting you set the update settings that meet your needs.
https://www.nigelfrank.com/blog/everything-you-ever-wanted-to-know-about-microsoft-power-
bi/

Question 9: Incorrect
 Scenario: You are an Admin in a team working at Avengers Security. Recently you were
assigned a large BI project your team has have created a query named Villains in a Power BI
Desktop to append the rows from three external tables with villain data into a single output table
named Villains.

Which would be the best approach to add a new fabricated ID column to the Villains table to
ensure that every row in the Villains table contains a unique ID value?


Modify the data model by extending the Villains table with a Counter column.


Modify the Villains query by adding a Counter column.


Modify the data model by extending the Villains table with an index column.
(Incorrect)


Modify the Villains query by adding an index column.
(Correct)

Explanation
Index column is available in query editor to add indexed columns starting from 0 to 1 or custom
number. The “Insert Index Column” option in the “Add Column” tab has the option to create a
new index column starting from 1 or to customize the starting value and now increment for the
index.

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

Question 10: Incorrect
True or False: Data can be collected from multiple data sources, including Microsoft Excel,
Oracle, SQL Server, and many more. The data has been collected from the different data sources
can be merged into a single data model in Power BI Report Builder.

False
(Correct)


True
(Incorrect)

Explanation
The first step in creating a report is to get data from a data source. Though this process might
seem similar to getting data in Power BI, it is different. Power BI paginated reports do not use
Power Query when connecting to data sources.
Getting data in a Power BI paginated report does not involve data cleaning steps. In fact, data is
not stored in a Power BI paginated report dataset. When data is refreshed on the report, it is
retrieved in an unaltered form from the data source, according to the query that was used to
retrieve it.

Data can be collected from multiple data sources, including Microsoft Excel, Oracle, SQL
Server, and many more. However, after the data has been collected, the different data sources
cannot be merged into a single data model. Each source must be used for a different purpose. For
instance, data from an Excel source can be used for a chart, while data from SQL Server can be
used for a table on a single report. Paginated reports have an expression language that can be
used to look up data in different datasets, but it is nothing like Power Query.

Power BI paginated reports can use a dataset from Power BI service. These datasets have used
Power Query to clean and alter the data. The difference is that this work was done in Power BI
Desktop or SQL Server Data Tools prior to using Power BI Report Builder, which doesn't have
that tool in the user interface.

Create and configure a data source

To retrieve data, open Power BI Report Builder. From the Getting Started screen, select New
Report. You can choose whether to create a report with a table on it, a chart, or a blank report.
For the purposes of this example, a blank report has been selected. These choices create a default
visual on your a new report, which can be changed at any time. Next, go to the Report
Data window, which is typically on the left side of the tool, though it can be moved around.

Right-click the Data Sources folder and select Add Data Source.

On the General tab, name the data source.

After naming the data source, choose the correct connection string by selecting the Build button.
After you have selected Build, the Connection Properties screen appears. The properties on this
screen will be unique for each data source. The following figure is an example of what you might
see in the screen. The figure shows the properties of a SQL Server connection that you, the
report author, will enter:

1. Server name

2. Database name

3. A button for testing the connection

4. Select OK to continue
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-paginated-report

You can also enter username and password information on the Connection Properties screen, or
you can leave it on the default setting and use your Windows credentials. Select OK again.

You've now created a data source.

Generally, authentication is beyond the scope of this course. Typically, you will receive the
connection information from your IT department, application specialist, or the software vendor.

Create and configure a dataset

A data source is the connection information to a particular resource, like SQL Server. A dataset
is the saved information of the query against the data source, not the data. The data always
resides in its original location.
Right-click Datasets in the Report View window and select Add Dataset. Ensure that the
correct data source is selected. This action will run the query against the correct data source.

From the window that displays, you can:

1. Name the query.

2. Choose whether to use a text command or a stored procedure.

3. Enter a query into the text box.

https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-data-sources
 Question 11: Correct
Scenario: You are working as a consultant at Palmer Technologies. Your team is working on
very large and complex Power BI project and at the moment, one of the analysts is using Power
Query for some of the tasks.

When using Power Query, how does referencing a query differ from duplicating a query?

Larger image


Referencing used the original query as a lookup table.


Referencing works on external data sources, while duplicating does not.


Referencing uses the original query as the source and it can be used in other queries for the further processing.
(Correct)


When you reference a query, any changes to the original query fail to reflect in the new query.

Explanation
Duplicate is generally used when you would like to create a similar query and you do not want to
type the same code. You can make changes to this query.
Reference in Query means you would like to use that Query results in some other queries where
your original query remains as a base Query and it can be used in other queries for the further
processing.
 https://community.powerbi.com/t5/Desktop/Difference-between-Reference-and-Duplicate/m-p/
102630

Question 12: Correct
Scenario: You have been hired as a consultant by the Daily Bugle to oversee a Power BI project.
This project is focused sales data.

Various themes are available for Power BI reports and the Sales team wants to import a festive
theme for the upcoming holidays.

Which of the following is the file format used to define the theme?


Extensible Markup Language (XML)


Cascading Style Sheets (CSS)


Power BI Markup Language (PBIML)


JavaScript Object Notation (JSON)
(Correct)

Explanation
With Power BI Desktop report themes, you can apply design changes to your entire report, such
as using corporate colours, changing icon sets, or applying new default visual formatting. When
you apply a report theme, all visuals in your report use the colours and formatting from your
selected theme as their defaults. A few exceptions apply, which are described later in this article.

Report themes can be selected by navigating to the View ribbon, then selecting the drop-down
arrow button in the Themes section of the ribbon, then selecting the theme you want. Available
themes are similar to themes seen in other Microsoft products, such as Microsoft PowerPoint.
There are two types of report themes: built-in report themes and custom report theme files.

• Built-in report themes provide different kinds of predefined colour schemes that are installed
with Power BI Desktop. You select built-in report themes directly from the Power BI Desktop
menu.

• Custom report themes are created by adjusting a current theme then saving it as a custom
theme, or creating your own customize theme using a JSON file. The JSON file provides
granular control over many aspects of a report theme, as described later in this article.

How report themes work

 To apply a report theme to a Power BI Desktop report, you can select from the following
options:

• Select from the available built-in report themes that are built into Power BI Desktop

• Customize a theme, using the Customize theme dialog

• Import a custom theme JSON file.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-report-themes

Question 13: Incorrect
In the recent past, roles such as business analysts and business intelligence developers were the
standard for data processing and understanding. However, excessive expansion of the size and
different types of data has caused these roles to evolve into more specialized sets of skills that
modernize and streamline the processes of data engineering and analysis.

Which role is described by:

“Primary responsibilities include the use of on-premises and cloud data services and tools to
ingest, egress, and transform data from multiple sources. They collaborate with business
stakeholders to identify and meet data requirements. They design and implement solutions.”


Data Engineer
(Correct)


Database Administrator


Data Analyst


Business Analyst
(Incorrect)


Data Scientist

Explanation
Telling a story with the data is a journey that usually doesn't start with you. The data must come
from somewhere. Getting that data into a place that is usable by you takes effort that is likely out
of your scope, especially in consideration of the enterprise.

Today's applications and projects can be large and intricate, often involving the use of skills and
knowledge from numerous individuals. Each person brings a unique talent and expertise, sharing
in the effort of working together and coordinating tasks and responsibilities to see a project
through from concept to production.

In the recent past, roles such as business analysts and business intelligence developers were the
standard for data processing and understanding. However, excessive expansion of the size and
different types of data has caused these roles to evolve into more specialized sets of skills that
modernize and streamline the processes of data engineering and analysis.

The following sections highlight these different roles in data and the specific responsibility in the
overall spectrum of data discovery and understanding:

• Business analyst

• Data analyst

• Data engineer

• Data scientist

• Database administrator

Business Analyst

While some similarities exist between a data analyst and business analyst, the key differentiator
between the two roles is what they do with data. A business analyst is closer to the business and
is a specialist in interpreting the data that comes from the visualization. Often, the roles of data
analyst and business analyst could be the responsibility of a single person.

https://www.indeed.com/hire/c/info/what-does-a-business-analysts-do
Data Analyst

A data analyst enables businesses to maximize the value of their data assets through visualization
and reporting tools such as Microsoft Power BI. Data analysts are responsible for profiling,
cleaning, and transforming data. Their responsibilities also include designing and building
scalable and effective data models, and enabling and implementing the advanced analytics
capabilities into reports for analysis. A data analyst works with the pertinent stakeholders to
identify appropriate and necessary data and reporting requirements, and then they are tasked with
turning raw data into relevant and meaningful insights.

A data analyst is also responsible for the management of Power BI assets, including reports,
dashboards, workspaces, and the underlying datasets that are used in the reports. They are tasked
with implementing and configuring proper security procedures, in conjunction with stakeholder
requirements, to ensure the safekeeping of all Power BI assets and their data.

Data analysts work with data engineers to determine and locate appropriate data sources that
meet stakeholder requirements. Additionally, data analysts work with the data engineer and
database administrator to ensure that the analyst has proper access to the needed data sources.
The data analyst also works with the data engineer to identify new processes or improve existing
processes for collecting data for analysis.

https://www.indeed.com/hire/job-description/data-analyst

Data Engineer

Data engineers provision and set up data platform technologies that are on-premises and in the
cloud. They manage and secure the flow of structured and unstructured data from multiple
sources. The data platforms that they use can include relational databases, nonrelational
databases, data streams, and file stores. Data engineers also ensure that data services securely
and seamlessly integrate across data services.

Primary responsibilities of data engineers include the use of on-premises and cloud data services
and tools to ingest, egress, and transform data from multiple sources. Data engineers collaborate
with business stakeholders to identify and meet data requirements. They design and implement
solutions.

While some alignment might exist in the tasks and responsibilities of a data engineer and a
database administrator, a data engineer's scope of work goes well beyond looking after a
database and the server where it's hosted and likely doesn't include the overall operational data
management.
A data engineer adds tremendous value to business intelligence and data science projects. When
the data engineer brings data together, often described as data wrangling, projects move faster
because data scientists can focus on their own areas of work.

As a data analyst, you would work closely with a data engineer in making sure that you can
access the variety of structured and unstructured data sources because they will support you in
optimizing data models, which are typically served from a modern data warehouse or data lake.

Both database administrators and business intelligence professionals can transition to a data
engineer role; they need to learn the tools and technology that are used to process large amounts
of data.

https://www.indeed.com/hire/job-description/data-engineer

Data Scientist

Data scientists perform advanced analytics to extract value from data. Their work can vary from
descriptive analytics to predictive analytics. Descriptive analytics evaluate data through a process
known as exploratory data analysis (EDA). Predictive analytics are used in machine learning to
apply modelling techniques that can detect anomalies or patterns. These analytics are important
parts of forecast models.

Descriptive and predictive analytics are only partial aspects of data scientists' work. Some data
scientists might work in the realm of deep learning, performing iterative experiments to solve a
complex data problem by using customized algorithms.

Anecdotal evidence suggests that most of the work in a data science project is spent on data
wrangling and feature engineering. Data scientists can speed up the experimentation process
when data engineers use their skills to successfully wrangle data.

On the surface, it might seem that a data scientist and data analyst are far apart in the work that
they do, but this conjecture is untrue. A data scientist looks at data to determine the questions
that need answers and will often devise a hypothesis or an experiment and then turn to the data
analyst to assist with the data visualization and reporting.

https://www.glassdoor.com/Job-Descriptions/data-scientist.htm

Database Administrator

A database administrator implements and manages the operational aspects of cloud-native and
hybrid data platform solutions that are built on Microsoft Azure data services and Microsoft SQL
Server. A data administrator is responsible for the overall availability and consistent performance
 and optimizations of the database solutions. They work with stakeholders to identify and
implement the policies, tools, and processes for data backup and recovery plans.

The role of a database administrator is different from the role of a data engineer. A database
administrator monitors and manages the overall health of a database and the hardware that it
resides on, whereas a data engineer is involved in the process of data wrangling, in other words,
ingesting, transforming, validating, and cleaning data to meet business needs and requirements.

The database administrator is also responsible for managing the overall security of the data,
granting and restricting user access and privileges to the data as determined by business needs
and requirements.

https://hiring.monster.com/employer-resources/job-description-templates/database-administrator-
job-description

Question 14: Correct
Identify the missing word(s) in the following sentence within the context of Microsoft.

Each time you shape data in Power Query, you create a step in the Power Query process. Those
steps can be reordered, deleted, and modified where it makes sense. Each cleaning step that you
made was likely created by using the graphical interface, but Power Query uses the [?] language
behind the scenes. The combined steps are available to read by using the Power Query Advanced
Editor. The [?] language is always available to be read and modified directly to suit your needs.


T-SQL


SQL


M
(Correct)


C#


Python


R


VBA

Explanation
Each time you shape data in Power Query, you create a step in the Power Query process. Those
steps can be reordered, deleted, and modified where it makes sense. Each cleaning step that you
made was likely created by using the graphical interface, but Power Query uses the M language
behind the scenes. The combined steps are available to read by using the Power Query Advanced
Editor. The M language is always available to be read and modified directly. It is not required
that you use M code to take advantage of Power Query. You will rarely need to write M code,
but it can still prove useful. Because each step in Power Query is written in M code, even if the
UI created it for you, you can use those steps to learn M code and customize it to suit your needs.

After creating steps to clean data, select the View ribbon of Power Query and then
select Advanced Editor.

The following screen should appear.

Each Power Query step will roughly align with one or two lines of M code. You don't have to be
an expert in M code to be able to read it. You can even experiment with changing it. For
instance, if you need to change the name of a database, you could do it right in the code and then
select Done.
 You might notice that M code is written top-down. Later steps in the process can refer to
previous steps by the variable name to the left of the equal sign. Be careful about reordering
these steps because it could ruin the statement dependencies. Write to a query formula step by
using the in statement. Generally, the last query step is used as the in final data set result.

https://powerbi.tips/2016/05/query-editor-editing-m-code/

Question 15: Correct
What is the difference between Promotion and Certification when you are endorsing a dataset?

Promotion uses symmetric encryption while Certification used asymmetric encryption from the dataset owner to access to
the dataset.


Promotion uses dynamic permissions while Certification requires static permission from the dataset owner to access to the
dataset.


Promotion does not need specific permissions while Certification requires permission from the dataset owner to access to
the dataset.
(Correct)


Promotion needs specific permissions in the same way Certification requires permission from the dataset owner to access
to the dataset.

Explanation
Promotion is for broad usage while Certification needs permission granted on the Admin Tenant
settings.

Business intelligence involves collaboration, and sharing datasets across workspaces is a

powerful way to collaborate within your organization. However, if your organization has many
different datasets that can be accessed by many users, you might want to take measures to
manage those datasets. For instance, you might want to direct your users to the most up-to-date
and highest-quality datasets in your workspaces, or you might want to restrict the reuse of
datasets across your workspaces.

To ensure that your organization has consistent data for making decisions and a healthy data
culture, it's important to create and share optimized datasets and then endorse those datasets as
the one source of truth. Report creators can then reuse those endorsed datasets to build accurate,
standardized reports.

Power BI provides two ways to endorse your datasets:

• Promotion - Promote your datasets when they're ready for broad usage. Any workspace
member with Write permissions can promote your datasets.
• Certification - Request certification for a promoted dataset from an admin user that is defined
in the Dataset Certification tenant admin setting. This certification adds another layer of security
for your datasets. Certification can be a highly selective process, so only the truly reliable and
authoritative datasets are used across the organization.

In this example, you and the other teams are using a workspace in Power BI service to organize
all your reports and dashboards. However, you begin to receive emails from confused users who
expected to see a sales report and are now looking at a product report instead. You need to make
some changes to direct your users to the datasets that they should be accessing, and you can
accomplish this task with the endorsing capability in Power BI.

In this example, the certification type of endorsement is best suited for the Sales team because it
will require users to have special access before they can view the Sales dashboards. By
implementing the certification, you'll lead your users to the most appropriate reports and
dashboards, avoiding the inevitable confusion that might arise with building and sharing a
diversity of reports.

Promote a dataset

You can only promote a dataset if you're a Power BI admin user or the owner of that dataset.

To promote a dataset, go to your workspace in Power BI service, and then open the settings page
for the dataset that you want to promote. Select the Endorsement setting.
In the Endorsement settings, select the Promoted option, and then select Apply.
When you return to your workspace, a badge in the Endorsement column for that dataset will
appear, indicating that it's ready for viewing by all of your users.
Certify a dataset

You can only certify a dataset if you've been listed as a user in the tenant settings. The
certification option will appear dimmed for other users.

To certify a dataset, you would start the same way as you did to promote the dataset. This time,
however, you will select the Certified option in the Endorsement settings.
When you apply your change, the Certified setting will update to display a message regarding
who certified the dataset and when they did so.

1.https://docs.microsoft.com/en-us/power-bi/service-datasets-promote/
 2.https://docs.microsoft.com/en-us/power-bi/service-datasets-certify/

Question 16: Incorrect
Identify the missing word(s) in the following sentence within the context of Microsoft.

Power BI Desktop gives you a variety of options for customizing how your selected
visualizations look, such as the colours and format of the text that they contain.

As you make changes in the Format pane, notice that the visualization updates immediately to
reflect those changes. If you need to revert the changes that you make, select the [?] option at the
bottom of each section in the Format pane.


Revert to default
(Correct)


Undo
(Incorrect)


Go Back


Cancel

Explanation
Power BI Desktop gives you a variety of options for customizing how your selected
visualizations look, such as the colours and format of the text that they contain. You should take
time to explore the options to determine what impact they each have on a visual.

Start by selecting the visualization on the canvas, and then select the Format button (paint roller
icon) to display the Format pane.
The formatting options that are available will depend on the type of visualization that you
selected.

Common formatting options include the Title, Background, and Border. In the Title section,

you can add a title to the visual, if it does not have one, or edit the title, if it has one already. The
aim of the title is to clearly describe what data is being presented in the visual. You can format
the title by changing the text, text size, font, colour, background, and alignment. The subsequent
section shows an example of customizing a title.

In the Background section, you can set any colour or image as the background for the visual. If
you plan to use an image as a background, try to select an image that won't have lines or shapes
that would make it difficult for the user to read the data. It is best to keep a white background so
the presented data can be clearly seen. The subsequent section shows an example of customizing
a background.
In the Border section, you can set a border around the visual to isolate the visual from other
elements on the canvas, which helps make it easier for the user to read and understand the data.
You can change the border colour and radius to be consistent with your colour scheme.

If a General section is available, you'll be able to set the precise size and place for your visual on
your canvas. This option might be suitable if the drag-and-drop feature is not placing the visual
exactly where you want it to be. It can also be useful to ensure that you have aligned specific
visuals consistently.

You might also be able to format the colours and labels for specific data values. In the Data
colours section, you can set the colours that you want to use for the data values in the visual.
You can use different colours for different fields, but always try to be consistent when it comes
to selecting those colours. It is best to use the same colour scheme throughout the report. In
the Data labels section, you can change fonts, size, and colours for all labels in the visual. Try to
use solid colours so the labels are clearly visible. For example, if the background is white, use a
black or dark grey colour to display your labels.

The Tooltips section allows you to add a customized tooltip that appears when you hover over
the visual, based on report pages that you create in Power BI Desktop. Tooltips is a great feature
because it provides more contextual information and detail to data points on a visual. The default
tooltip displays the data point's value and category, but your custom tooltips can include visuals,
images, and any other collection of items that you create in the report page. The subsequent
section shows an example of customizing a tooltip.

As you make changes in the Format pane, notice that the visualization updates immediately to
reflect those changes. If you need to revert the changes that you make, select the Revert to
default option at the bottom of each section in the Format pane.

In the following examples, you will edit the title, change the background, and add a tooltip.

Title

You can edit a default title and add a title, if you don't have one. In this example, you will select
the column chart visualization and then, in the Format pane, scroll down and expand
the Title section. Edit the current title by adding a space between Sales and Amount, and then
increase the font size to 16 points.
Background

It is best practice to keep the default white background so the presented data can be clearly seen.
However, you can change the default background colour to make a visualization more colourful
and easier to read or to match a particular colour scheme. In this example, continue with the
column chart that is selected and then, in the Format pane, expand the Background section and
change the colour to light grey.
Tooltip

Using tooltips is a clever way of providing more contextual information and detail to data points
on a visual. When you add a visual, the default tooltip displays the data point's value and
category, but you can customize this information to suit your needs. For example, you might
want to provide your report users with additional context and information, or specify additional
data points that you want users to see when they hover over the visual.

To expand on the data points that are displayed in the default tooltip, you can drag a field (value)
from the Fields panel into the Tooltips bucket. However, you should not add many more fields
to the tooltips because adding too many fields can introduce performance issues and slow down
your visuals.

The following image shows the default tooltip first and then the customized tooltip that displays
additional data.
Another way to use tooltips is to display graphical information. The process of adding this type
of tooltip is not as straightforward, but it is worthwhile. You would begin by creating a new page
in the report.

Open the new page and then open the Format pane. Expand the Page Size section and then
select Tooltip from the Type list.
In the Page information section, turn the Tooltip slider to On so that Power BI registers this
page as a tooltip page
Tooltips have limited canvas space, so to ensure that your visuals appear in the tooltip, on
the View tab, set the Page view option to Actual size.

Next, add one or more visuals to the tooltip page, in the same way that you would on any other
report page.

Now, you need to specify the fields for which you want the tooltip to display. Select the tooltip
page and then select the Values tab in the Visualizations pane. Drag the fields from
the Fields pane into the Tooltip bucket. In this example, you will drag the SalesAmount field
into the Tooltip bucket.

Return to the report page and apply the tooltip to one or more visuals on that page. Select a
visual and then, in the Format pane, scroll down to the Tooltip section. Turn the tooltip
option On and then select your tooltip page from the Page list.

When you hover over the visual, the tooltip will display.
 https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-customize-title-
background-and-legend

Question 17: Incorrect
Power BI paginated reports are with created which of the following? (Select all that apply)

Power BI Report Builder
(Correct)


Power BI Service
(Incorrect)


Power BI Desktop


Power BI Mobile

Explanation
Pagination, also known as paging, is the process of dividing a document into discrete pages,
either electronic pages or printed pages.

Power BI paginated reports allow report developers to create Power BI artifacts that have tightly
controlled rendering requirements. Paginated reports are ideal for creating sales invoices,
receipts, purchase orders, and tabular data. This module will teach you how to create reports, add
parameters, and work with tables and charts in paginated reports.

Paginated reports defined

Paginated reports give a pixel-perfect view of the data. Pixel perfect means that you have total
control of how the report renders. If you want a footer on every sales receipt that you create, a
paginated report is the appropriate solution. If you want a certain customer's name to always
appear in green font on a report, you can do that in a paginated report.

Power BI paginated reports are descendants of SQL Server Reporting Services (SSRS), which
was first introduced in 2004. Power BI paginated reports and SSRS have a lot in common. If
you're looking for information on paginated reports and can't find it, searching the internet and
Microsoft documentation on SSRS is an excellent idea because you'll find numerous blog posts,
videos, and documentation available to you.
When paginated reports are the right fit

You can use paginated reports for operational reports with tables of details and optional headers
and footers.

Additionally, you can use paginated reports when you expect to print the report on paper or when
you want an e-receipt, a purchase order, or an invoice. Paginated reports also render tabular data
exceedingly well. You can have customized sort orders, clickable-headers, and URLs in results,
which allows for simple integration with custom applications.

Power BI paginated reports can also display all of your data in a single report element, such as a
table. If you have 25,000 records, and you want the reports to print over 100 pages, you can do
that. If you want every third record to be printed with a light pink background, you can do that as
well.

Power BI paginated reports are not created in Power BI Desktop; they are only built by using
Power BI Report Builder. Power BI paginated reports are a feature of Power BI Premium.
 https://docs.microsoft.com/en-us/power-bi/consumer/end-user-paginated-report
Question 18: Incorrect
Power BI can be simple and fast, capable of creating quick insights from an Excel workbook or a
local database. But Power BI is also robust and enterprise-grade, ready not only for extensive
modelling and real-time analytics, but also for custom development.

Everything you do in Microsoft Power BI can be broken down into a few basic building blocks.

Which of these building blocks are described by:

“Can be a combination of many different sources, which you can filter and combine to provide a
unique collection for use in Power BI. For example, you can create a one of these from a
collection of database fields, one website table, an Excel table, and online results of an email
marketing campaign. This unique combination is considered a single one of these, even though it
was pulled together from many different sources.”


Tiles


Reports


Datasets
(Correct)


Dashboards
(Incorrect)


Visualizations

Explanation
Everything you do in Microsoft Power BI can be broken down into a few basic building blocks.
Here are the basic building blocks in Power BI:

• Visualizations

• Datasets

• Reports

• Dashboards

• Tiles
Visualizations

A visualization (sometimes also referred to as a visual) is a visual representation of data, like a

chart, a colour-coded map, or other interesting things you can create to represent your data
visually. Power BI has all sorts of visualization types, and more are coming all the time. The
following image shows a collection of different visualizations that were created in Power BI.
Visualizations can be simple, like a single number that represents something significant, or they
can be visually complex, like a gradient-coloured map that shows voter sentiment about a certain
social issue or concern. The goal of a visual is to present data in a way that provides context and
insights, both of which would probably be difficult to discern from a raw table of numbers or
text.

Datasets

A dataset is a collection of data that Power BI uses to create its visualizations.

You can have a simple dataset that's based on a single table from a Microsoft Excel workbook,
similar to what's shown in the following image.
Datasets can also be a combination of many different sources, which you can filter and combine
to provide a unique collection of data (a dataset) for use in Power BI.

For example, you can create a dataset from three database fields, one website table, an Excel
table, and online results of an email marketing campaign. That unique combination is still
considered a single dataset, even though it was pulled together from many different sources.
Filtering data before bringing it into Power BI lets you focus on the data that matters to you. For
example, you can filter your contact database so that only customers who received emails from
the marketing campaign are included in the dataset. You can then create visuals based on that
subset (the filtered collection) of customers who were included in the campaign. Filtering helps
you focus your data—and your efforts.

An important and enabling part of Power BI is the multitude of data connectors that are

included. Whether the data you want is in Excel or a Microsoft SQL Server database, in Azure or
Oracle, or in a service like Facebook, Salesforce, or MailChimp, Power BI has built-in data
connectors that let you easily connect to that data, filter it if necessary, and bring it into your
dataset.

After you have a dataset, you can begin creating visualizations that show different portions of it
in different ways, and gain insights based on what you see. That's where reports come in.

Reports

In Power BI, a report is a collection of visualizations that appear together on one or more pages.
Just like any other report you might create for a sales presentation or write for a school
assignment, a report in Power BI is a collection of items that are related to each other. The
following image shows a report in Power BI Desktop—in this case, it's the second page in a
five-page report. You can also create reports in the Power BI service.
Reports let you create many visualizations, on multiple pages if necessary, and let you arrange
those visualizations in whatever way best tells your story.

You might have a report about quarterly sales, product growth in a particular segment, or
migration patterns of polar bears. Whatever your subject, reports let you gather and organize
your visualizations onto one page (or more).
Dashboards

When you're ready to share a report, or a collection of visualizations, you create a dashboard.
Much like the dashboard in a car, a Power BI dashboard is a collection of visuals from a single
page that you can share with others. Often, it's a selected group of visuals that provide quick
insight into the data or story you're trying to present.

A dashboard must fit on a single page, often called a canvas (the canvas is the blank backdrop in
Power BI Desktop or the service, where you put visualizations). Think of it like the canvas that
an artist or painter uses—a workspace where you create, combine, and rework interesting and
compelling visuals. You can share dashboards with other users or groups, who can then interact
with your dashboards when they're in the Power BI service or on their mobile device.

Tiles

In Power BI, a tile is a single visualization on a dashboard. It's the rectangular box that holds an
individual visual. In the following image, you see one tile, which is also surrounded by other
tiles.
When you're creating a dashboard in Power BI, you can move or arrange tiles however you
want. You can make them bigger, change their height or width, and snuggle them up to other
tiles.
 When you're viewing, or consuming, a dashboard or report—which means you're not the creator
or owner, but the report or dashboard has been shared with you—you can interact with it, but
you can't change the size of the tiles or their arrangement.

https://www.nigelfrank.com/blog/everything-you-ever-wanted-to-know-about-microsoft-power-
bi/

Question 19: Correct
Scenario: You have been hired as a consultant by Advanced Idea Mechanics (AIM) to oversee a
large Power BI project. This project is focused towards cash-flow data.

Required: Create a visualization which helps users understand and analyze cash-flow during the
ups and downs of all months in a financial year.

Which visualization is perfect for this type of visualization?


Scatter chart


Stacked column chart


Treemap


Waterfall chart
(Correct)

Explanation
Waterfall charts show a running total as Power BI adds and subtracts values. They're useful for
understanding how an initial value (like net income) is affected by a series of positive and
negative changes.

The columns are colour coded so you can quickly notice increases and decreases. The initial and
the final value columns often start on the horizontal axis, while the intermediate values are
floating columns. Because of this style, waterfall charts are also called bridge charts.

When to use a waterfall chart

Waterfall charts are a great choice:

• When you have changes for the measure across time, a series, or different categories.

• To audit the major changes contributing to the total value.

• To plot your company's annual profit by showing various sources of revenue and arrive at the
total profit (or loss).

• To illustrate the beginning and the ending headcount for your company in a year.

• To visualize how much money you make and spend each month, and the running balance for
your account.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-waterfall-charts
Question 20: Correct
Identify the missing word(s) in the following sentence within the context of Microsoft.

Power BI Desktop gives you a variety of options for customizing how your selected
visualizations look, such as the colours and format of the text that they contain.

[?] are excellent in helping you track progress toward a specific goal over time. To use [?], you
need three pieces of information:

• A unit of measurement that you want to track, for instance total sales, number of employee
hires, number of loans serviced, or number of students enrolled.

• A goal for the measurement so that you can compare your progress with that goal.

• A time series, for instance daily, monthly, or yearly.

 The following shows a sample in the Visualizations pane.

Larger image


TCOs


APRs


ROIs


BEPs


KPIs
(Correct)

Explanation
Key performance indicators (KPIs) are excellent in helping you track progress toward a specific
goal over time. To use a KPI, you need three pieces of information:

• A unit of measurement that you want to track, for instance total sales, number of employee
hires, number of loans serviced, or number of students enrolled.

• A goal for the measurement so that you can compare your progress with that goal.

• A time series, for instance daily, monthly, or yearly.

Start by adding the KPI visual to the design service. The following screenshot shows the KPI
icon in the Visualizations pane.

When configuring the KPI visual, enter the unit of measurement that you are tracking in
the Indicator prompt. Then, enter the goal under Target goals and select the time series from
the Trend axis drop-down list, as shown in the following screenshot.
This action will produce a KPI that looks similar to the following screenshot.

KPIs work best in a series, for instance, showing the daily, monthly, and yearly goals in the
section of a Power BI report.
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi
Question 21: Incorrect
Scenario: You are working at HAMMER Industries and have loaded raw sales data from two
sources into a Power BI model.
 Some of the data came from a .csv file that was created manually in Microsoft Excel by the Sales
team. The other data was loaded through a connection to your organization's Enterprise Resource
Planning (ERP) system. The data in was in disarray and needed to be scrubbed. Now you need to
take further action to simplify the structure of the sales data and get it ready for developing
reports for the Sales team.

You have already renamed the columns, examined and updated the names of the queries (tables)
as needed.

Required: Now you now need to evaluate the columns to ensure that they have the correct data
type and correct any errors that you identify. You evaluate the OrderDate column. It contains
numeric data where the column data type is set to Text.

Larger image

Which is the best action to correct the issue?


Change the data type of all columns to Numeric


Change the data type of this column from Text to Numeric


Change the data type of all columns to Text
(Incorrect)

Change the data type of this column from Text to Date
(Correct)

Explanation
When you import a table from any data source, Power BI Desktop automatically starts scanning
the first 1,000 rows (default setting) and tries to detect the type of data in the columns. Some
situations might occur where Power BI Desktop does not detect the correct data type. Where
incorrect data types occur, you will experience performance issues.

You have a higher chance of getting data type errors when you are dealing with flat files, such as
comma-separated values (.CSV) files and Excel workbooks (.XLSX), because data was entered
manually into the worksheets and mistakes were made. Conversely, in databases, the data types
are predefined when tables or views are created.

A best practice is to evaluate the column data types in Power Query Editor before you load the
data into a Power BI data model. If you determine that a data type is incorrect, you can change it.
You might also want to apply a format to the values in a column and change the summarization
default for a column.

Implications of incorrect data types

The following information provides insight into problems that can arise when Power BI does not
detect the correct data type.

Incorrect data types will prevent you from creating certain calculations, deriving hierarchies, or
creating proper relationships with other tables. For example, if you try to calculate the Quantity
of Orders YTD, you will get the following error stating that the OrderDate column data type is
not Date, which is required in time-based calculations.

Quantity of Orders YTD = TOTALYTD(SUM('Sales'[OrderQty]), 'Sales'[OrderDate])

Another issue with having an incorrect data type applied on a date field is the inability to create a
date hierarchy, which would allow you to analyze your data on a yearly, monthly, or weekly
basis. The following screenshot shows that the SalesDate field is not recognized as type Date and
will only be presented as a list of dates in the Table visual. However, it is a best practice to use a
date table and turn off the auto date/time to get rid of the auto generated hierarchy.

Change the column data type

You can change the data type of a column in two places: in Power Query Editor and in the Power
BI Desktop Report view by using the column tools. It is best to change the data type in the Power
Query Editor before you load the data.

Change the column data type in Power Query Editor

In Power Query Editor, you can change the column data type in two ways. One way is to select
the column that has the issue, select Data Type in the Transform tab, and then select the correct
data type from the list.
Another method is to select the data type icon next to the column header and then select the
correct data type from the list.
As with any other changes that you make in Power Query Editor, the change that you make to
the column data type is saved as a programmed step. This step is called Changed Type and it
will be iterated every time the data is refreshed.

After you have completed all steps to clean and transform your data, select Close & Apply to
close Power Query Editor and apply your changes to your data model. At this stage, your data
should be in great shape for analysis and reporting.

https://docs.microsoft.com/en-us/power-query/data-types

Question 22: Correct
Scenario: You are conducting a workgroup session while working as a consultant at HAMMER
industries. Currently, you are discussing options which may result in more that two new columns
when using the “Split Column by Delimiter” transformation with the team.

Several opinions have been tabled, some valid, some not.

It is your responsibility to sort through the responses and determine valid statements so the
conversation can be continued.
 Which are valid statements as to when the result may be more that two new columns?


At the left-most delimiter.


At each occurrence of the delimiter.
(Correct)


None of the listed options.


At the right-most delimiter.

Explanation
A delimiter is one or more characters that separate text strings.
Common delimiters are commas (,), semicolon (;), quotes ( ", ' ), braces ({}), pipes (|),
or slashes ( / \ ). When a program stores sequential or tabular data, it delimits each item of data
with a predefined character.

https://www.computerhope.com/jargon/d/delimite.htm

Using “At each occurrence of the delimiter”, the number of new columns will depend on the
number of times a delimiter is used in a string/text.

https://docs.microsoft.com/en-us/power-query/split-columns-delimiter

Question 23: Incorrect
The first step in designing an exceptional report layout is to choose the correct format. You will
need to consider the audience and speculate about the kind of format that they'll want.

Power BI is committed to accessibility standards under the Web Content Accessibility

Guidelines (WCAG). The standards help ensure that your Power BI experiences are accessible to
as many people as possible.

Which of the following accessibility features are built in to Power BI Desktop and do not require
any configuration? (Select all that apply)


Tab order
(Incorrect)


Show data table
 (Correct)


High contrast colours view
(Correct)


Keyboard navigation
(Correct)


Themes
(Incorrect)


Screen-reader compatibility
(Correct)


Alt text
(Incorrect)


Markers
(Incorrect)


Titles and labels
(Incorrect)


Focus mode
(Correct)

Explanation
The page layout of the reports that you create in Power BI Desktop will likely depend on the
business requirements, the context of the underlying data, and the output requirements. For
example, if you are designing a dashboard, you'll need to present high-level information on a
single page. If you are designing a report, it is a multi-perspective view into your dataset, with
visuals that represent different findings and insights from that dataset.

Report design best practice

The first step in designing an exceptional report layout is to choose the correct format. Likely,
your manager (or whoever requested the report) will give you some requirements in terms of
format. If not, you'll need to consider the audience and speculate about the kind of format that
they'll want.

For example, if your report users have a technical background and are looking for specific facts,
you can use multiple, complex visuals that offer the most detail, along with interactive slicers.
Conversely, if your users are looking for quick data insights at a high level, you could use a
small range of basic visuals.

If you have been given some layout requirements, you still need to carefully consider the report
audience. Your goal is to provide the audience with the information that they need, in an optimal
way. While you might have a strong opinion about what data to display and how to display it,
ultimately, the report is not for you, it is for a dedicated audience that needs to make business
decisions based on your report.

Considering the different needs of the report audience is crucial. Your report audience might
have hearing, motor, cognitive, or visual impairment. To accommodate those needs, you should
create a report that offers an accessible experience, which means that the report will be simple to
navigate and understand by keyboard or screen-reader users. You'll learn more about design and
configuring your report for accessibility in subsequent sections.

Carefully consider each visual and element that you plan on using in the report. Everything
should have a purpose, and you should consider how each element will appear to your report
users. While you might consider using different types of visuals for the sake of variety or to
demonstrate your skill set, sometimes a simple visual is all that you need. Likely, your
organization will have style guidelines for reports, in which case, you'll have to adhere to
particular colour scheme and font. Do keep in mind that the more visuals you use in your report,
the more that they impact the performance of your report.

Other key guidelines for creating a well-designed report layout include:

• Draw a sketch of your report layout. This approach will help you get an idea of what it will
look like before you spend considerable time physically designing it. Alternatively, you could
draw multiple sketches, where you try out different ideas and then discuss these ideas with your
team to help select the best layout design.

• Focus on the most important information. Highlight key parts of your report with a bright
colour or summary icon so that it stands out and draws users to the most critical metrics.

• Select the right background for the context of your report. A white background can make your
report look clean and professional, whereas a black background can draw attention to colourful
highlights on the report. Using images as a background can add visual interest.

The following image shows a poorly designed report layout, which you should avoid. A later
section will provide an example of the same report but with an improved design.
The following sections provide more detailed guidance for setting up the report page and using
visuals.

Report page

It is important to consider that you and the report users might view the reports on screens with
different aspect ratios and sizes.

The default display view is Fit to page, which means that the contents are scaled to best fit the
page. If you need to change this view, go to the View tab, select Page view, and then select your
preferred page view option, as illustrated in the following screenshot.
To access the page settings, select the white space on your report canvas to open
the Format pane. You can then configure the following settings to suit your needs: Page
information, Page alignment, Page size, Wallpaper, Page background, and Filter pane.
Visuals

You might want to use a combination of visuals in your report, such as cards, charts, tables,
slicers, and so on. It is important to use the right number of visuals on a page, and then size and
position those visuals strategically.

Number of visuals

Consider the number of visuals (including slicers) that you want to use on each report page.
Using more visuals might have the opposite effect to what you are trying to achieve. More
visuals might make your report look too busy, causing users to feel overwhelmed because they
don't know where to focus their attention. Also, visuals are key factors in the performance of
your report; they contribute to performance issues. The fewer visuals you use, the better the
performance will be.

It's best to limit the number of visuals that you use on a page. Examine each visual and ask
yourself if it's necessary. If a visual does not add value to the audience, you should not use it in
your report.

Rather than using multiple visuals, you can provide information in other ways, such as
drillthrough pages and report page tooltips. You'll learn more about these elements later in this
module.

Position of visuals

When you add visualizations to a report, you can move those visuals to specific locations on the
page and make them larger or smaller for a more effective display.

It is best practice to place the most important visual in the upper-left corner of your report
because your users most likely read left-to-right and top-to-bottom. You might also want to place
your organization's logo in or near this area. You can then arrange the other visuals accordingly.

To move a visualization, select any area of the visualization and then drag it to the new location.

To evenly distribute distance between visuals that are located on the canvas, you can also use
the Align function. Use CTRL+click to select all visuals that you want to align, select
the Format tab, and then select Distribute horizontally.
The visuals will then be evenly distributed.

Size of visuals

When you add a visual to a report, Power BI determines the size of that visual by default. You
can resize that visual to ensure that it presents the displayed information most optimally. For
example, if the visual is of a small car, you might want to make the visual smaller. Similarly, if
you are working with a scatter chart visual with extensive data, you might want to make that
visual larger in size so users can see the data clearly.

To resize a visual, select the visual to display its border, and then select and drag the dark frame
handles to the size that you want it to be.
Interaction of visuals

The visuals that you add to your report will interact with each other. For example, when you
select an element in one visual, such as a product category, the other visuals will update in
relation to that element; they might highlight or filter the specific data that they display.
Therefore, when you are designing the report, it is important to understand these interactions and
consider how they might affect the overall user experience of the report. You have control over
how interactions flow between the visuals; therefore, you might want to change a filter action to
a highlight (and contrariwise), or prevent an interaction from happening. This process will be
further explained later in this module.

Hierarchies in visuals

Likely, you'll have a number of hierarchies in your data, so you should consider how these
hierarchies will affect how the data displays in the visuals and the navigation experience of your
report users. You can set how hierarchies are presented in the visuals. You can also determine
the hierarchical path of visuals so that you have full control over what level of detail can be
accessed. You'll learn more about hierarchies later in this module.

Report accessibility

It is essential that you consider the possibility that your users might have hearing, motor,
cognitive, or visual impairments. Therefore, make sure that you design a report that adheres to
accessibility standards and that uses the available accessibility features within Power BI
Desktop.

Designing a report that offers an accessible experience will benefit all report users because it
ensures that your report has an effective design and uses consistent formatting and colour scheme
or theme.

Generally, when you are using Power BI with a screen reader, we recommend that you turn scan
mode or browse mode off.

To improve the process of creating reports with screen readers, a context menu is available. The
menu allows you to move fields in the well up or down in the Fields list. The menu also allows
you to move fields to other wells, such as Legend, Value, or others.
Accessibility standards

Power BI is committed to accessibility standards under the Web Content Accessibility

Guidelines (WCAG). The standards help ensure that your Power BI experiences are accessible to
as many people as possible. When you build accessible reports or dashboards, that content is
accessible for anyone who views them by using Power BI Mobile.

Web Content Accessibility Guidelines (WCAG) help make web content accessible to people
with disabilities. The key principles of the guidelines are:

• Perceivable - Information and user interface components must be presentable to users in ways
that they can perceive.

• Operable - User interface components and navigation must be operable.

• Understandable - Information and the operation of the user interface must be understandable.

Accessibility features

The following accessibility features are built in to Power BI Desktop, so you don't need to do
any configuration in this regard:

• Keyboard navigation

• Screen-reader compatibility
• High contrast colours view

• Focus mode

• Show data table

Accessibility features that you do need to configure include:

• Alt text

• Tab order

• Titles and labels

• Markers

• Themes

Alt text

To accommodate report consumers who use screen readers, you can use alternative (alt) text to
describe the appearance and function of objects (such as a visual, shape, and so on) on the report
page. Alt text helps you ensure that users understand what you are trying to communicate with
those objects, even if they are unable to see them.

To add alt text to an object, select that object and, in the Visualizations pane, open
the Format pane. Expand the General section, scroll to the bottom of the options, and then enter
a description in the Alt Text box. Repeat this step for every object that conveys meaningful
information on a report.

If you do not want to use static text, you can use Data Analysis Expressions (DAX) measures
and conditional formatting to create dynamic alt text. Screen readers will then call out values that
are specific to the data that a report user is viewing.

To apply conditional formatting, right-click the Alt Text box, select Conditional formatting,

and then configure the settings as required.
Tab order

To help keyboard users navigate your report in an order that matches the way that visual users
would, you can set the tab order.

To set the tab order, select the View tab in the ribbon and then select Selection Pane. On
the Selection pane that displays, use the arrow buttons to move the objects to the correct order,
or select an object with your mouse and drag it into the position that you want in the list.

To hide an object from the tab order, select the number next to that object. For example, it's best
to hide decorative shapes and images that you have in your report.
Titles and labels
To help all users, you should provide clear, concise, descriptive titles for your visuals and report
pages. Avoid using acronyms or jargon that new users or users who are external to your
organization will not understand.

Compare the following images, where the image on the left shows a visual with an acronym in
the title, and the image on the right shows a visual with a clearer title.

Make sure that all labels within a visual are easy to read and understand. You can turn on or off
the labels for each series in your visual or position them above or below a series to make them
clearer. Don't turn on labels for every visual because it might have the opposite effect by
distracting users and making your report less accessible.

Compare the following images, where the first image has fewer numbers or descriptions of the
data, whereas the second has several.
Markers

It's best practice to avoid using colour (including the feature's conditional formatting) as the only
way of conveying information. Instead, you can use markers to convey different series. For line,
area, and combo visuals, including scatter and bubble visuals, you can turn on markers and use a
different shape for each line.
Keep in mind that, if you turn on markers for every visual, it might be distracting and make your
report less accessible for users.

To turn on markers, in the Format pane, expand the Shapes section, and then scroll down and
move the Show marker slider to the On position.
Themes

To make your reports even more accessible, ensure that enough contrast exists between the text
and background colours; the contrast ratio should be at least 4.5:1. Several tools are available for
you to use to check your report colours, such as Colour Contrast Analyzer, WebAIM, and
Accessible Colours.

Some of your report viewers might have colour vision deficiencies. Using fewer colours or a
monochrome palette in your report can help mitigate the creation of inaccessible reports. The
following colour combinations are difficult for users with colour vision deficiencies to
distinguish, so you should avoid using them together in a chart or on the same report page.

• Green and red

• Green and brown

• Blue and purple

• Green and blue

• Light green and yellow

• Blue and grey

• Green and grey

• Green and black

Power BI Desktop has built-in themes that you can use to make your report more accessible and
generally look better. You can access these themes from the View tab. Select the Expand button
to view all available themes and related options. Select any theme and it will automatically apply
across the whole report. All visuals will use the colours and formatting from your selected theme
as their defaults.

You can also import or create your custom themes by expanding the following Themes options.
 The following image depicts the same report that was shown at the beginning of this unit.
However, in this image, the report has a better design; it has a planned layout and consistent
colour scheme and formatting.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboards-design-tips

Question 24: Correct
One of the following is not a filter type available within Power BI reports. Which is it?

Page level

Report level


Page type
(Correct)


Drill through

Explanation
Page type filters are not available in Power BI reports.

Drill through

With drill through in Power BI reports, you can create a page in your report that focuses on a
specific entity such as a supplier, customer, or manufacturer. When your report readers use drill
through, they right-click a data point in other report pages, and drill through to the focused page
to get details that are filtered to that context. You can also create a button that drills through to
details when they click it.

You can set up drill through in your reports in Power BI Desktop or the Power BI service.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-drillthrough

Levels of filters in the Filters pane

Whether you're using Power BI Desktop or Power BI service, the Filters pane displays along the
right side of the report canvas. If you don't see the Filters pane, select the ">" icon from the
upper-right corner to expand it.

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

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

Question 25: Correct
The Power BI Quick insights feature is available in which of the following?

Power BI Web Service
(Correct)


Power BI mobile

Power BI Desktop


DirectQuery

Explanation
The Quick insights feature in Power BI uses machine learning algorithms to go over your entire
dataset and produce insights (results) for you quickly. This feature is a great way to build
dashboards when you don't know where to start. It also helps you find insights you might have
missed when building your reports. From the insights that Power BI discovers, you can generate
appealing, interactive visualizations.

Note: This feature is available in the Power BI web service only. Also, this feature doesn't work
with DirectQuery; it only works with data that is imported to Power BI.

Consider the scenario where one of the datasets that you've been given contains substantial data
concerning the Help tickets that were created for the Customer Service team. Because of the
extensive data, you don't know where to begin analyzing, so you decide to let Power BI do it for
you.

Get quick insights on your dataset

To get quick insights on your dataset, open your Power BI web service and then select
the Content tab. Locate your report for which you want to get quick insights, which in this case
is AvengersSecurity. Then, select More options (...) → Quick insights.
Power BI will use various algorithms to search for trends in your dataset. This process might
take a few seconds, but when it is finished, you'll see a message in the upper-right corner letting
you know that the results are ready to be viewed.

Select View insights to open the Quick Insights page for the selected dataset, and then view the
insights that Power BI has found for you. The Quick Insights page contains up to 32 separate
insight cards, and each card has a chart or graph plus a short description. In this example, one of
the insights is a card that displays a visual for Count of Product by Category Name, as
illustrated in the following image.
Add a Quick insights result card to a report

If you see an insight card that is particularly compelling, you can add it to your report. On
the Quick Insights page, hover over the card, then select the pin icon. The visual is added to
your dashboard, where you can reposition it as required.

Interact with the quick insights results

To take a closer look at a particular insight card on the Quick Insights page, select an insight
card to open. The insight screen opens in Focus mode.
 You can then perform the following actions:

• Filter the visualization by using the available options in the Filters panel.

• Pin the insight card to a dashboard by selecting Pin visual.

• Run insights on the card (scoped insights) by selecting Get insights in the upper-right corner.
The scoped insights allow you to drill into your data.

• Return to the original insights canvas by selecting Exit Focus mode in the upper-left corner.

https://key2consulting.com/how-to-use-power-bi-quick-insights-and-power-bi-analyze/

Question 26: Correct
Scenario: You are working as a data analyst and one of the tasks you are performing has a
critical performance impact on reporting and data analysis.

Which type of data analyst task are you performing?


Analyze


Manage


Visual


Visualize


Model
(Correct)

Explanation
An optimized and tuned data model performs better and provides a better data analysis
experience.

Model

When the data is in a proper state, it's ready to be modelled. Data modelling is the process of
determining how your tables are related to each other. This process is done by defining and
creating relationships between the tables. From that point, you can enhance the model by
defining metrics and adding custom calculations to enrich your data.
 Creating an effective and proper data model is a critical step in helping organizations understand
and gain valuable insights into the data. An effective data model makes reports more accurate,
allows the data to be explored faster and more efficient, decreases time for the report writing
process, and simplifies future report maintenance.

The model is another critical component that has a direct effect on the performance of your
report and overall data analysis. A poorly designed model can have a drastically negative impact
on the general accuracy and performance of your report. Conversely, a well-designed model with
well-prepared data will ensure a properly efficient and trusted report. This notion is more
prevalent when you are working with data at scale.

From a Power BI perspective, if your report is performing slowly, or your refreshes are taking a
long time, you will likely need to revisit the data preparation and modelling tasks to optimize
your report.

The process of preparing data and modelling data is an iterative process. Data preparation is the
first task in data analysis. Understanding and preparing your data before you model it will make
the modelling step much easier.

https://www.rasmussen.edu/degrees/technology/blog/what-does-a-data-analyst-do/

Question 27: Correct
Scenario: You are developing dashboards at Avengers Security. These dashboards are
published; however, you begin receiving emails from users who are asking questions about the
underlying data and are inquiring if you could build other visuals that are specific to their needs.
A few questions are be manageable to answer, but the volume of messages is high, keeping up
with demand is not sustainable.

What is the best option to deal with the situation?


SophIA within Power BI Desktop.


LOUIS within Power BI Desktop.


Q&A visual within Power BI Desktop.
(Correct)


Query visual within Power BI Desktop.

Explanation
Power BI dashboards are about having a user-friendly experience. Dashboards in Power BI
service are comprised of a canvas of interactive tiles, or report elements, that tell a data story.
Power BI solves this problem with the Q&A visual. From the dashboard view, people can ask
questions by using the Ask a question about your data search bar at the top of the dashboard,
which increases engagement between users and the dashboard.

Q&A feature

The Q&A feature is a tool within Power BI Desktop that allows you to ask natural-language
questions about the data.

To locate the Q&A feature, go to your dashboard in Power BI service. Along the top ribbon is
the Ask a question about your data search box.

After selecting this box, you will be routed to the following page.

The Q&A visual consists of three main elements:

• Question box - In this element, the user can enter their question about the data. An example of
a question could be: What was the average sales amount by category? Entering this question will
trigger Power BI's natural-language analysis engine to parse and determine the appropriate data
to display.
• Pre-populated suggestion tiles - This element contains tiles with pre-populated suggestions
for questions that the user can consider asking. When the user selects one of these tiles, they will
be shown analysis. For example, if you select the top product category named by march
sales tile, you would get the following visual that is converted from the Q&A visual.

• Pin visual icon - This icon is located in the upper right of the visual, as shown in the following
image.
Selecting the pin visual icon will allow you to pin the visual onto a new or existing dashboard,
as you have done previously.

With the Q&A feature, you can return to your users with a solution to their questions. Now, they
can interact directly with the visual to ask their data questions, which will increase their
interactions with the visual and help them save time.

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-tutorial-q-and-a

Question 28: Incorrect
Scenario: You work for Avengers Security where you are required to build Microsoft Power BI
reports. The data resides in several different databases and files. These data repositories are
different from each other, some are in Microsoft SQL Server, some are in Microsoft Excel, but
all the data is related.

You’ve been tasked by senior leadership to create a suite of reports that are dependent on data in
several different locations. The database that tracks sales transactions is in SQL Server, a
relational database contains which customer bought which items and when. It also tracks which
employee made the sale, along with the employee name and employee ID. However, that
database doesn’t contain the employee’s hire date, their title, or who their manager is. For that
information, you need to access files that Human Resources keeps in Excel. You've been
consistently requesting that they use an SQL database, but they haven't yet had the chance to
implement it.

When an item ships, the shipment is recorded in the warehousing application, which is new to
the company. The developers chose to store data in CosmosDB, as a set of JSON documents.

Avengers Security has an application that helps with financial projections, so that they can
predict what their sales will be in future months and years, based on past trends. Those
projections are stored in Microsoft Azure Analysis Services. Below is a view of the data sources
you are asked to combine data from.

Larger image
 Required: The Sales team at Avengers Security has requested that you connect to the
organization's on-premises SQL Server database and get the sales data into Power BI Desktop so
you can build sales reports.

You have chosen to employ an SQL query to import data to specify only the tables and columns
needed. You do not want to import all the data in the tables.

Which of the following is a valid SQL statement to do this?


IMPORT ID, NAME, SALESAMOUNT FROM SALES


SELECT ID, NAME, SALESAMOUNT FROM SALES
(Correct)

IMPORT * FROM SALES


IMPORT ‘ID’, ‘NAME’, ‘SALESAMOUNT’ FROM ‘SALES’
(Incorrect)


SELECT ‘ID’, ‘NAME’, ‘SALESAMOUNT’ FROM ‘SALES’


SELECT * FROM SALES

Explanation
While SELECT * FROM SALES query will execute and return data, it does not filter the columns – it
will select all data which is not the intention. “You have chosen to employ an SQL query to
import data to specify only the tables and columns needed. You do not want to import all the
data in the tables.”

The query SELECT ID, NAME, SALESAMOUNT FROM SALES  will select the data in the specific columns
noted in the query only.
Import data by writing an SQL query

Another way you can import data is to write an SQL query to specify only the tables and
columns that you need.

To write your SQL query, on the SQL Server database window, enter your server and database
names, and then select the arrow next to Advanced options to expand this section and view your
options. In the SQL statement box, write your query statement, and then select OK. In this
example, you will use the Select SQL statement to load
the ID, NAME and SALESAMOUNT columns from the SALES table.
 https://docs.microsoft.com/en-us/power-bi/connect-data/service-get-data

Question 29: Incorrect
Scenario: You are working as a consultant at Palmer Technologies. Your team is working on
very large and complex Power BI project and at the moment, there is a debate over the
advantages of Power BI Dashboards over Power BI Reports.

Which of the following are advantages of Power BI Reports over Power BI Dashboards ?


Report definitions can be saved using an external file format (PBIX) and checked into source control.
(Correct)


Reports provide better real-time experience when displaying data from a streaming dataset.


Reports provide a better interactive filtering and drill-down experience.
(Correct)


Reports provide story telling capabilities using bookmarks.
(Correct)

Explanation
Reports and dashboards seem similar because they are both canvases filled with visualizations.
But there are major differences. A Power BI dashboard is a single page, often called a canvas,
that tells a story through visualizations. Because it is limited to one page, a well-designed
dashboard contains only the highlights of that story.

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

Advantages of advantages of Power BI Dashboards over Power BI Reports:

• Dashboards update automatically whenever the data in the underlying dataset is updated.

• Dashboards provide support for data-driven alerts.

The other options are not correct because dashboards and reports both offer Q&A functionality,
and are not interactive. The only case of interaction is if you pin a live page. Even in that
aforementioned case, the visual which contains the live report page will be interactive, however
it cannot interact with other files.

Microsoft Power BI is the tool that perfectly complements whatever Office 365 app you’re
using. It is astonishingly visual and easy to use. There are multiple benefits, dozens of use cases,
and countless appraisals from users. And yet, one bit of confusion exists that Power BI users
keep wondering about: what is the difference between dashboards and reports in Power BI?
Well, here’s the answer that we’ve prepared for you.

Generally speaking, dashboards display the most important decision-making facts to run a
business, while reports are information providers to dashboards that can be supplied in any
format: text, table, chart, or a number.

This is what primarily distinguishes reports and dashboards, and we now know that it would be
wrong to say that Power BI dashboards and reports are same. But there’s a lot more to look into
to help you differentiate and decide on the best way of using them both in Power BI. So, let’s
dive in.

The purpose

Power BI dashboards are essentially a collection of different data sets with key performance
indicators, headlines, or most important data that is presented in a one-page display format.
Therefore, dashboards make it very convenient for users to make decisions by just looking at
them, as well as monitor key parameters on a regular basis.

At the same time, reports have their own attributes and metrics to display. They’re spread across
multiple pages and have drill-down and drill-up capabilities.

The structure
While reports are based on one dataset or one business unit data, dashboards usually contain
several sets of information for a wider view. Furthermore, while reports are more detailed data
displayed in many formats like chart, graphs, etc., Power BI dashboards are one placeholder to
display only the most important decision-making facts to run a business.

Visual presentation

The dashboard has a one-page display, but in a report, we have multiple pages to navigate
through, since reports represent more detailed data. In Power BI reports, users can be drilled
down to different levels of data sets, exported to any format, or download to a local desktop for
further analysis.

Sharing capabilities

Users can publish both reports and dashboards on the web, as well as subscribe through email.
However, dashboards can only be exported to limited formats that allow visualizing the
important data rather than analyzing it.

Additionally, Power BI dashboards are not available for desktop, while reports can be created
and viewed in a desktop.

Unique features
To summarize, dashboards and reports are not the same—there are many differences between the
two. The major ones are that dashboards represent a day-to-day single-page view of main
business parameters, can be automatically refreshed, and display real-time data. Reports, on the
other hand, can be used by users to slice the data and interact with to investigate numbers in
more details. To make the best use out of the Power BI you would need both, since dashboards
and reports are designed to complement each other, not to replace.

https://powerbi.team/news/power-bi-dashboards-vs-reports/

Question 30: Incorrect
Review the following image.

Larger image
 Which of the following are true statements? (Select all that apply)


By default, any slicing on the date table will not interact with the Date and OrderDate columns.


None of the statements are true.
(Incorrect)


By default, any slicing on the date table where measures in the Sales data are being displayed will be along
the OrderDate column.
(Correct)

By default, any slicing on the date table where measures in the Sales data are being displayed will be along
the ShipDate column.


By default, any slicing on the date table will not interact with the Date and ShipDate columns.
(Correct)

Explanation
The USERELATIONSHIP DAX function allows you to override default behaviour.

Consider the following data model example.

The preceding screenshot shows an established relationship between the Date and
OrderDate columns, as shown by the highlighted line connecting the two. The solid line
between the two tables indicates that it is the active relationship, meaning that by default, any
slicing on the date table where measures in the Sales data are being displayed will be along
the OrderDate column. A dashed relationship exists between the Date and ShipDate columns,
indicating that it is the inactive relationship. This relationship will never be used unless explicitly
declared in a measure.

The goal is to build the following report, where you have two visuals: Sales by Ship
Date and Sales by Order Date.
 These visuals show the sales over time, but the first visual is by order date and the second is by
ship date so, though they are both dates, a different data point is associated with them to get both
sets of data on the same visual.

To create this calculated measure for Sales by Ship Date, you can use the DAX
function USERELATIONSHIP(). This function is used to specify a relationship to be used in a specific
calculation and is done without overriding any existing relationships. It is a beneficial feature in
that it allows developers to make additional calculations on inactive relationships by overriding
the default active relationship between two tables in a DAX expression, as shown in the
following example:

Sales by Ship Date = CALCULATE(Sales[TotalPrice], USERELATIONSHIP('Calendar'[Date],

Sales[ShipDate]))

https://powerbidocs.com/2020/12/23/dax-userelationship-function/
Question 31: Incorrect
When you create a new app workspace in the power BI service, there are several resources which
are automatically provisioned.

Which of the following is are they?


A storage container for power BI resource is including data sets, reports, and dashboards.
(Correct)


An Office 365 group which triggers the creation of a SharePoint team site.


A shared capacity which is used to host the workspace.


And Azure Active Directory user account to serve as the workspace identity.
(Incorrect)

Explanation
Apps are collections of dashboards, reports, and data sets that connect two third party services
and organizational data. Apps make it easy to get data from the services such as Microsoft
Dynamics CRM, Salesforce, and Google Analytics.

If you want to distribute official content to a large audience in your organization, you can publish
an app from your workspace. When your content is ready, you choose which dashboards and
reports you want to publish, and publish them as an app. You can create one app from each
workspace.

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

Question 32: Incorrect
 Power BI paginated reports is an evolved technology that was built from which original tool?

Microsoft SharePoint


SQL Server Analysis Services
(Incorrect)


SQL Server Reporting Services
(Correct)


Microsoft PowerPoint

Explanation
Paginated reports are designed to be printed or shared. They're called paginated because they're
formatted to fit well on a page. They display all the data in a table, even if the table spans
multiple pages. They're also called pixel perfect because you can control their report page layout
exactly. Power BI Report Builder is the standalone tool for authoring paginated reports for the
Power BI service.

Power BI paginated reports was built from SQL Server Reporting Services.

https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-reports-report-builder-
power-bi

A major advantage of paginated reports is their ability to print all the data in a table, no matter
how long. Picture that you place a table in a Power BI report. You see some of its rows in the
table on the page, and you have a scroll bar to see the rest. If you print that page, or export it to
PDF, the only rows that print are the ones you saw on the page.

Now say you place the same table in a paginated report. When you print it or export it to PDF,
the paginated report has as many pages as necessary to print every row in that table.
 https://www.youtube.com/watch?list=PL1N57mwBHtN1icIhpjQOaRL8r9G-
wytpT&v=jXTiYJKw1Rs&feature=youtu.be
Question 33: Incorrect
To conduct a time series analysis in Power BI, you need to use a visualization type that is
suitable for displaying trends and changes over time.

Which of the following are most conducive for this type of work? (Select three)


Bar or column chart


Line chart
(Correct)


 Scatter chart
(Correct)


Area chart
(Correct)


Doughnut chart


Funnel chart

Explanation
Time series analysis involves analyzing a series of data in time order to identify meaningful
information and trends and make predictions. The result of time series analysis is the best data
that you can use for forecasting activities.

Time series analysis often involves the use of visuals such as Gantt charts, project planning, and
stock movement datasets. In Power BI, you can use visuals to view how your data is progressing
over time, which in turn allows you to make observations, such as if any major events disrupted
your data.

To conduct a time series analysis in Power BI, you need to use a visualization type that is
suitable for displaying trends and changes over time, such as a line chart, area chart, or scatter
chart. You can also import a time series custom visual into Power BI Desktop from Microsoft
AppSource. The following example uses a standard scatter chart.

In addition to the range of time series custom visuals, Microsoft AppSource has an animation
custom visual called Play Axis that works like a dynamic slicer and is a compelling way to
display time trends and patterns in your data without user interaction.

In this example, you are developing a Sales report. The Sales team wants to study the quarterly
sales trends and identify which models sell better, depending on the time of the season. You
decide to use two visuals, a scatter chart and line chart, for the purpose of time series analysis,
and then enhance those visuals with animation so the Sales team can see how the sales data
changes over time.

You start by adding your visuals to the report page to show the sales data.

Next, you will import the animation custom visual to use with the visuals. In
the Visualizations pane, select the Get more visuals icon and then select Get more visuals. On
the Power BI Visuals window that displays, search for play axis, and then select the Add button
for the Play Axis (Dynamic Slicer) visual.
A message will display, stating that the visual was successfully imported. When you return to
Power BI Desktop, you'll see the new Play Axis icon in the Visualizations pane. Select the page,
and then select the Play Axis icon to add that visual to the page.

With the new visual selected, select the field (Quarter) that you want to use as the slicer in
the Play Axis animation. Animation controls become available on the visual.
You can now resize and reposition the new visual and customize its formatting so that it's
consistent with the other visuals on the page. Specific formatting options that you might want to
use include:

• In the Animation Settings section, you can control the play functionality of the Play

Axis visual, such as making the animation automatically start, continue looping, and then change
the speed at which the animation occurs.

• In the Colours section, you can change the appearance of the Play Axis visual by adjusting its
overall colour, or selecting the Show all option, and then changing the colour of each control
button.

• The Enable Caption On section allows you to turn on/off the text that is displayed next to the
visual or adjust the formatting of it.

When you have set up the Play Axis visual to meet your requirements, you are ready to use it
with your other visuals. Select the Play button and then watch how the data in each visual on the
page evolves over the time. You can use the control buttons to pause the animation, restart it, and
so on.
 https://www.analyticsvidhya.com/blog/2020/07/time-series-forecasting-using-microsoft-power-
bi/
Question 34: Correct
When building a star schema, you will have Dimension and Fact tables. Which of the following
describes Fact tables?

Contain transactional information to record the factual activities of items accessed by users.


Contain accessibility permissions used for RBAC.


Contain information about events such as sales orders, shipping dates, resellers, and suppliers.
(Correct)


Contain details about business entities, such as products or time.

Explanation
When building a star schema, you will have dimension and fact tables. Fact tables contain
information about events such as sales orders, shipping dates, resellers, and
suppliers. Dimension tables store details about business entities, such as products or time, and
are connected back to fact tables through a relationship.

You can use hierarchies as one source to help you find detail in dimension tables. These
hierarchies form through natural segments in your data. For instance, you can have a hierarchy of
dates in which your dates can be segmented into years, months, weeks, and days. Hierarchies are
useful because they allow you to drill-down into the specifics of your data instead of only seeing
the data at a high level.

Hierarchies

When you are building visuals, Power BI automatically enters values of the date type as a
hierarchy (if the table has not been marked as a date table).

In the preceding Date column, the date is shown in increasingly finer detail through year,
quarters, months, and days. You can also manually create hierarchies.

For example, consider a situation where you want to create a stacked bar chart of Total Sales by
Category and Subcategory. You can accomplish this task by creating a hierarchy in
the Product table for categories and subcategories. To create a hierarchy, go to the Fields pane
on Power BI and then right-click the column that you want the hierarchy for. Select New
hierarchy, as shown in the following figure.
Next, drag and drop the subcategory column into this new hierarchy that you've created. This
column will be added as a sublevel on the hierarchy.

Now, you can build the visual by selecting a stacked bar chart in the Visualizations pane. Add
your Category Name Hierarchy in the Axis field and Total Sales in the Values field.

You can drill-down on the visual to view both Category and Subcategory, depending on what

you want to see. Hierarchies allow you to view increasing levels of data on a single view.
Now that you have learned about hierarchies, you can take a step further and examine parent-
child hierarchies and their role in multiple relationships between fact tables and dimension
tables.

Parent-child hierarchy

In the following example, you have an Employee table within the database that tells you
important information about the employees, their managers, and their IDs. When looking at this
table, you notice that Roy F has been repeated multiple times in the Manager column. As the
image shows, multiple employees can have the same manager, which indicates a hierarchy
between managers and employees.

The Manager column determines the hierarchy and is therefore the parent, while the "children"
are the employees. For this example, you want to be able to see all levels of this hierarchy.
Power BI does not default to showing you all levels of the hierarchy, so it is your responsibility
to ensure that you see all levels of this hierarchy or "flatten" it so that you can see more data
granularity.

Flatten parent-child hierarchy

The process of viewing multiple child levels based on a top-level parent is known as flattening
the hierarchy. In this process, you are creating multiple columns in a table to show the
hierarchical path of the parent to the child in the same record. You will use PATH(), a simple
DAX function that returns a text version of the managerial path for each employee,
and PATHITEM() to separate this path into each level of managerial hierarchy.

Important: DAX has not been covered yet; however, it will be in another module. This function is
included in this section because it's explaining hierarchies. If use of DAX in this capacity is
confusing, refer to the DAX module and then return to this section afterward.

While on the table, go to the Modelling tab and select New Column. In the resulting formula
bar, enter the following function, which creates the text path between the employee and manager.
This action creates a calculated column in DAX.

DAX

Path = PATH(Employee[Employee ID], Employee[Manager ID])

The completed path between the employee and the manager appears in the new column, as
shown in the following screenshot.
If you look at Roger M, the path of IDs is 1010 | 1011 | 1013, which means that one level above
Roger M (ID 1013) is his manager, Pam H (ID 1011), and one level above Pam H is her manager
Roy F (ID 1010). In this row, Roger M is on the bottom of the hierarchy, at the child level, and
Roy F is at the top of the hierarchy and is at the parent level. This path is created for every
employee. To flatten the hierarchy, you can separate each level by using the PATHITEM
function.

To view all three levels of the hierarchy separately, you can create four columns in the same way
that you did previously, by entering the following equations. You will use the PATHITEM
function to retrieve the value that resides in the corresponding level of your hierarchy.

• Level 1 = PATHITEM(Employee[Path],1)

• Level 2 = PATHITEM(Employee[Path],2)

• Level 3 = PATHITEM(Employee[Path],3)

After you have finished, notice that you now have each level of hierarchy within your table. Roy
F is at the top of the hierarchy and, as you go through Levels 2-3, notice that the managers and
employees map with each other.
Now, you can create a hierarchy on the Fields pane, as you did previously. Right-click Level 1,
because this is the first hierarchy level, and then select New Hierarchy. Then, drag and
drop Level 2 and Level 3 into this hierarchy.

You have now successfully flattened a hierarchy so that you can view individual levels.

Previously, you've considered dimensions that have only one relationship with a fact table.
However, situations do occur where your dimension table will have multiple relationships with a
fact table.

Role-playing dimensions

Role-playing dimensions have multiple valid relationships with fact tables, meaning that the
same dimension can be used to filter multiple columns or tables of data. As a result, you can
filter data differently depending on what information you need to retrieve. This topic is complex,
so it is only introduced in this section. Working with role-playing dimensions requires complex
DAX functions that will be discussed in later sections.
The preceding visual shows the Calendar, Sales, and Order tables. Calendar is the dimension
table, while Sales and Order are fact tables. The dimension table has two relationships: one with
Sales and one with Order. This example is of a role-playing dimension because the Calendar
table can be used to group data in both Sales and Order. If you wanted to build a visual in which
the Calendar table references the Order and the Sales tables, the Calendar table would act as a
role-playing dimension.

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-personalize-visuals

Question 35: Incorrect
Scenario: You have been hired as a consultant by Cobalt Chemical to oversee a Power BI
project. This project is focused sales data.

You hold a meeting with the team and are discussing the similarities between filter level
behaviour and Slicers in a Power BI Desktop report.

Which of the following filter levels, by default, behaves the same as a Slicer in a Power BI
Desktop report?

Query level filter


Visual level filter
(Incorrect)


Report level filter


Page level filter
(Correct)

Explanation
When you create a Slicer in a report, the Slicer will filter the visualization on the current page,
similar to the page level filter.

https://www.unitygroup.com/blog/filters-vs-slicers-which-is-a-better-choice-when-designing-
reports-in-power-bi/

Question 36: Incorrect
In Power BI Desktop, your report is dynamic. When you make a selection on one visual in the
report, other visuals might change to reflect that selection. Similarly, if hierarchies are in your
data, you can move up and down the hierarchy to see the data at different levels.

A hierarchy is a structure in which groups are ranked one above the other according to a specific
status.

The hierarchy icons are located above the visual, as illustrated in the following image.

Larger image
 When you add fields to the visual you can then use the hierarchy icons to navigate through the
hierarchy. Power BI creates a predefined drill path for the data.

Larger image

How would you prevent users from viewing a particular hierarchy level?


Right-click on the hierarchy name, then select Mask.

Right-click on the field you want to hide, then select Hide.
(Incorrect)


Remove all fields from the Axis well and then, in the Fields pane, right-click the field that you want to set as the top level
of the hierarchy and select New hierarchy.
(Correct)


Select Create New hierarchy, Select Concealed hierarchy, then place the fields within the new hierarchy.

Explanation
In Power BI Desktop, your report is dynamic. When you make a selection on one visual in the
report, other visuals might change to reflect that selection. Similarly, if hierarchies are in your
data, you can move up and down the hierarchy to see the data at different levels.

The following sections explain how the basic interactions work and how you can use hierarchies
in your visuals.

View interactions

When multiple visuals exist on the same report page, they all interact with each other. For that
reason, you should become familiar with these interactions to see how your report changes.

Compare the following two images. In the first image, the data displays at a high level.
When you select an element in a visual, such as Components in the Product Category visual,
the other visuals update to reflect your selection, as illustrated in the second image.
When you have become familiar with the interactions, you might want to make changes to
control how those interactions flow between the visuals.

Use hierarchies

A hierarchy is a structure in which groups are ranked one above the other according to a specific
status. Think of your own organizational hierarchy, where the CEO is at the top level, then
managers in the middle level, and employees at the lower level. Similarly, you'll likely have
hierarchies for your data in Power BI. For example, you could have a time hierarchy with levels
such as year, quarter, month, and day, or a product hierarchy with levels such as category,
subcategory, and product.

Regarding dates, Power BI creates hierarchies for you automatically. When you select the
hierarchy in the Fields pane, the date hierarchy is added to the Axis field on
the Visualizations pane and a blank visual is created for you, which is ready for additional
fields. The hierarchy icons are located above the visual, as illustrated in the following image.
When you add another field to the visual, the visual becomes useful, and you can then use the
hierarchy icons to navigate through the hierarchy. Power BI creates a predefined drill path for the
data. In the following image, the Gross Sales field was added to the visual and you can see the
data at the highest level (year). Then, when the Expand all down one level in the
hierarchy button was selected, the hierarchy was expanded down by one level (quarter). If you
were to select the button again, the visual would update to display the next lower level in the
hierarchy (month), and so on.
Another navigation option is the Go to the next level in the hierarchy button. When you select
either hierarchy option, select the Drill up button to move back up the hierarchy.
The default date hierarchy feature in Power BI is quick and simple to use. However, if you have
your own date table, you can use it to create the hierarchy instead. In the following image,
the Year, Month, Quarter, and Day fields were added to the Axis field well on
the Visualizations pane. The data result is the same as the default hierarchy, so you can navigate
through the hierarchy in the same way.

You can also predefine the hierarchy path for your report users to remove any guesswork. For
example, you might want to prevent users from viewing a particular hierarchy level. To do so,
remove all fields from the Axis well and then, in the Fields pane, right-click the field that you
want to set as the top level of the hierarchy and select New hierarchy. The new hierarchy
displays in the list in the Fields pane. You can now drag and drop other fields into the new
hierarchy or right-click each field and select Add to hierarchy.
Ensure that your visual is selected, and then select the New hierarchy field in the Fields pane to
apply it to the visual.

You can use the hierarchy buttons above the visual to expand the hierarchy and drill back up
again.

https://docs.microsoft.com/en-us/javascript/api/overview/powerbi/embedding-basic-interactions

Question 37: Incorrect
Identify the missing word(s) in the following sentence within the context of Microsoft.
 Cloud services, such as SharePoint, do not require a gateway because the data is already in the
cloud. You only need to provide your authorization credentials to set up a data source
connection.

If your report fails to refresh, the first thing to do is … [?]


clear the Power BI cache.


ensure that your data source credentials are up to date.
(Correct)


verify the endpoints of the source link.
(Incorrect)


verify the proper type of endorsement is utilized.

Explanation
Cloud services, such as SharePoint, do not require a gateway because the data is already in the
cloud. You only need to provide your authorization credentials to set up a data source
connection.

If your report fails to refresh, ensure that your data source credentials are up to date.
 If your data source credentials are not up to date, you'll need to take further action to investigate
and resolve the issue.

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

Question 38: Incorrect
Scenario: As more reports and dashboards are increasingly added to the Avengers Security
workspace, the Sales team becomes concerned as they realize the urgency of securing their data.
The team is concerned about the possibility of new users exporting data without permission. The
Sales team doesn't want to roll back reports or dashboards, so they have asked you to implement
comprehensive security measures that protect data access within and outside of Power BI.

Which it the most appropriate method to implement these security measures?


Microsoft RBAC
(Incorrect)


Data protection Labels in Power BI
(Correct)

Security Centre in Power BI


Power BI Protected Containers

Explanation
As enterprises grow, so does their data. Often, strict requirements and regulations must be
applied to ensure that this sensitive data is secure. Power BI provides a few different ways to
help you accomplish this task:

• Use Microsoft sensitivity labels to label dashboards, reports, datasets, and dataflows by using
the same taxonomy that is used to classify and protect files in Microsoft 365.

• Add more protection measures such as encryption and watermarks when you are exporting the
data.

• Use Microsoft Cloud App Security to monitor and investigate activities in Power BI.

To continue with the module scenario, as more reports and dashboards are increasingly added to
the Avengers Security workspace, the Sales team becomes concerned as they realize the urgency
of securing their data. The team is concerned about the possibility of new users exporting data
without permission. The Sales team doesn't want to roll back reports or dashboards, so they have
asked you to implement comprehensive security measures that protect data access within and
outside of Power BI. You can complete this task by configuring data protection labels in Power
BI.

Before you begin, ensure that you have the appropriate licensing, as shown here.

Sensitivity labels

Sensitivity labels specify which data can be exported. These labels are configured externally to
Power BI, and Power BI allows you to quickly use them in your reports and dashboards. These
labels allow you to define and protect content, even outside of Power BI. Datasets, dataflows,
reports, and dashboards can use this mechanism, and all users in your corporation can use this
feature unless exceptions have been defined.

After you have verified your ability to add labels, go to any workspace and choose an object to
secure. For this example, you will add a sensitivity label to Sales Data by going to the
workspace and, under the ellipsis (...), selecting Settings.
This selection will take you to a window, where you can assign a sensitivity label to your data.
For this example, the following labels have been externally configured, so you can now apply
them to the data: None, Personal, General, Confidential, and Highly confidential. You can
also go to Microsoft 365 Security Centre to define your own labels.

For example, if you want to assign a Confidential label to your Sales Data report, when you
change this label on the Settings pane, it will appear as a label on the report, as shown in the
following figure.
 This factor is crucial when you are exporting data. Data that is exported to Microsoft Excel,
Microsoft PowerPoint, and PDF files will have sensitivity labels enforced. For instance, if you
wanted to export data from Sales Data into an Excel file, if you are an authorized user, you will
see the following Excel view when you export into Excel.

However, if you didn't have established permissions, you would be denied access to see the data.
This verification ensures that only appropriate users have access to view the data, which helps
make sure that your data is secured.

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-security-apply-data-
sensitivity-labels

Question 39: Incorrect
Which are the possible options to share a dashboard with other users?

Share directly from Power BI Desktop.


 From your browser, click Share.


Click the ellipsis next to the dashboard name on the Dashboards list, and then click Share.
(Correct)


From the Dashboard list, click the share button from the actions tab.
(Correct)


Click Share from the upper right of the dashboard you want to share.
(Correct)

Explanation
When you share a dashboard or report, the people you share it with can view it and interact with
it, but can't edit it. They see the same data that you see in the dashboard and reports and get
access to the entire underlying dataset unless row-level security (RLS) is applied to the
underlying dataset. The coworkers you share with can also share with their coworkers, if you
allow them to. The people outside your organization can view and interact with the dashboard or
report, but can't share it.

You can share dashboards and reports from many places in the Power BI service: Favourites,
Recent, My Workspace. You can share from other workspaces, too, if you have the Admin,
Member, or Contributor role in the workspace. And you can share dashboards and reports in
Shared with me, if the owner allows it.

The Power BI service offers other ways to collaborate and distribute dashboards and reports, too.
Read Ways to collaborate and share in Power BI to see which way works best for your
circumstances.
 With sharing, whether you share content inside or outside your organization, you need a Power
BI Pro license. Your recipients also need Power BI Pro licenses, unless the content is in
a Premium capacity.

You can't share directly from Power BI Desktop. You publish reports from Power BI Desktop to
the Power BI service. However, you can share a dashboard from the Power BI mobile apps.

Sharing from the browser will share the URL only, however the user will not get access to the
dashboard.

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-share-dashboards

Question 40: Correct
What type of expression do you use to extract data from Microsoft SQL Server?

T-SQL
(Correct)


MDX


U-SQL


DAX

Explanation
T-SQL is the query language that you would use for SQL Server.

Transact-SQL, often abbreviated to T-SQL or even TSQL, is Microsoft’s and Sybase’s

proprietary extension to SQL. Transact-SQL expands on the SQL standard to include extra
features that aren’t included in the SQL standard.

T-SQL Features

Some of the features of T-SQL include:

• Procedural programming

• Local variables

• Various support functions for string processing, date processing, mathematics, etc
 • Changes to the DELETE and UPDATE statements. In particular, they allow a FROM clause to be added,
which allows joins to be included.

• T-SQL has a BULK INSERT statement that allows you to import a data file into a database
table or view in a user-specified format.

One of the main benefits of the above extensions is that you have much more programmability
when using T-SQL vs SQL. Stored procedures for example allow you to incorporate
programming logic into your database. You can pass one or more parameters to a stored
procedure, and the stored procedure will return an output which varies depending on the values
of the parameters.

T-SQL also includes a large number of built-in functions, as well as supporting user-defined
functions.

https://database.guide/what-is-t-sql/

Question 41: Correct
Scenario: You have built several visuals for the Avengers Security Customer Service team, and
now you want to understand the factors that most influence your metrics. Specifically, you want
to figure out what factors are affecting the total number of logged tickets. One factor is the client
type and the other is location, but there may be others.

Which visual will show this information?


Ribbon chart visual


Funnel chart visual


Decomposition Tree visual


Matrix visual


Stacked area chart visual


Scatter chart


KPI visual

Key influencers visual
(Correct)

Explanation
The Key influencers visual helps you understand the factors that are affecting a specific metric.
It analyzes your data for you, ranks the factors that matter, and then displays those factors as key
influencers. The visual also helps you to contrast the relative importance of these factors,
meaning that you can build your visuals while understanding what factors impact those visuals
and why the visuals appear the way that they do.

In this example, you've built several visuals for the Customer Service team, and now you want to
understand the factors that most influence your metrics. Specifically, you want to figure out what
factors are affecting the total number of logged tickets. One factor might be the client type and
another might be location, but you're uncertain. The Key influencers visual will find that
information for you.

To establish the key influencers, first add the Key influencers visual to your report by selecting
the Key influencers icon on the Visualization pane. Then, populate the visual with the metrics
that you want to measure. In this case, you're interested in the logged tickets, so in
the Analyze field well, you will add the Sales field, and in the Explain by field well, you will
add the City and Product Name fields. The visual updates according to the fields that you added
and then shows the influence that those fields have on your data.
 You can now use the What influences... drop-down list to see what caused the data to decrease
or increase. The previous image shows that the sales for California are likely to be USD
130,700.00 higher than sales in other cities.

For more information, see Create key influencers visualizations.

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

Question 42: Incorrect
What does data granularity mean?

Data granularity is the level of detail that is represented in the data.
(Correct)


Data granularity is the filter direction that is associated with the relationship between two columns.
(Incorrect)


Data granularity is a many-to-many relationship.


Data granularity is a type of schema design.
Explanation
Data granularity is the detail that is represented within your data, meaning that the more
granularity your data has, the greater the level of detail within your data.

Data granularity is an important topic for all data analysts, regardless of the Power BI tools that
you are using. Defining the correct data granularity can have a big impact on the performance
and usability of your Power BI reports and visuals.

https://www.techopedia.com/definition/31722/granular-data

Question 43: Correct
Scenario: You work for Avengers Security where you are required to build Microsoft Power BI
reports. The data resides in several different databases and files. These data repositories are
different from each other, some are in Microsoft SQL Server, some are in Microsoft Excel, but
all the data is related.

You’ve been tasked by senior leadership to create a suite of reports that are dependent on data in
several different locations. The database that tracks sales transactions is in SQL Server, a
relational database contains which customer bought which items and when. It also tracks which
employee made the sale, along with the employee name and employee ID. However, that
database doesn’t contain the employee’s hire date, their title, or who their manager is. For that
information, you need to access files that Human Resources keeps in Excel. You've been
consistently requesting that they use an SQL database, but they haven't yet had the chance to
implement it.

When an item ships, the shipment is recorded in the warehousing application, which is new to
the company. The developers chose to store data in CosmosDB, as a set of JSON documents.

Avengers Security has an application that helps with financial projections, so that they can
predict what their sales will be in future months and years, based on past trends. Those
projections are stored in Microsoft Azure Analysis Services. Below is a view of the data sources
you are asked to combine data from.

Larger image
 Before you can create reports, you must first extract data from the various data sources.
Interacting with SQL Server is different from Excel, so you should learn the nuances of both
systems. After you’ve learned the particulars of each system, you can use Power Query (the
query engine used by Power BI and Excel) to help you clean the data, such as renaming columns,
replacing values, removing errors, and combining query results. After the data has been cleaned
and organized, you are ready to build reports in Power BI. Finally, you will publish your
combined dataset and reports to Power BI service (PBIS). From there, other people can use your
dataset and build their own reports or they can use the reports that you’ve already built.

Required: The Compliance team at Avengers Security has stringent security requirements

around data that make it impossible to directly import a copy to Power BI.

Which storage mode should be used in this case?


Mirror

Composite


Import


DirectQuery
(Correct)

Explanation
The most popular way to use data in Power BI is to import it into a Power BI dataset. Importing
the data means that the data is stored in the Power BI file and gets published along with the
Power BI reports. This process helps make it easier for you to interact directly with your data.
However, this approach might not work for all organizations.

To continue with the scenario, you are building Power BI reports for the Sales department at
Avengers Security, where importing the data is not an ideal method. The first task you need to
accomplish is to create your datasets in Power BI so you can build visuals and other report
elements. The Sales department has many different datasets of varying sizes. For security
reasons, you are not allowed to import local copies of the data into your reports, so directly
importing data is no longer an option. Therefore, you need to create a direct connection to the
Sales department’s data source. The following section describes how you can ensure that these
business requirements are satisfied when you are importing data into Power BI.

However, sometimes there may be security requirements around your data that make it
impossible to directly import a copy. Or your datasets may simply be too large and would take
too long to load into Power BI, and you want to avoid creating a performance bottleneck. Power
BI solves these problems by using the DirectQuery storage mode, which allows you to query the
data in the data source directly and not import a copy into Power BI. DirectQuery is useful
because it ensures you are always viewing the most recent version of the data.

The three different types of storage modes you can choose from:

• Import

• DirectQuery

• Dual (Composite)
You can access storage modes by switching to the Model view, selecting a data table, and in the
resulting Properties pane, selecting which mode that you want to use from the Storage
mode drop-down list, as shown in the following visual.
 Import mode

The Import mode allows you to create a local Power BI copy of your datasets from your data
source. You can use all Power BI service features with this storage mode, including Q&A and
Quick Insights. However, data refreshes must be done manually. Import mode is the default for
creating new Power BI reports.

DirectQuery mode

The DirectQuery option is useful when you do not want to save local copies of your data because
your data will not be cached. Instead, you can query the specific tables that you will need by
using native Power BI queries, and the required data will be retrieved from the underlying data
source. Essentially, you are creating a direct connection to the data source. Using this model
ensures that you are always viewing the most up-to-date data, and that all security requirements
are satisfied. Additionally, this mode is suited for when you have large datasets to pull data from.
Instead of slowing down performance by having to load large amounts of data into Power BI,
you can use DirectQuery to create a connection to the source, solving data latency issues as well.

Dual (Composite mode)

In Dual mode, you can identify some data to be directly imported and other data that must be
queried. Any table that is brought in to your report is a product of both Import and DirectQuery
modes. Using the Dual mode allows Power BI to choose the most efficient form of data retrieval.

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

Question 44: Correct
True or False: You can optimize your dashboards for mobile view in Power BI service. To see a
dashboard in mobile view, select the ellipsis (...) on the home ribbon and select Mobile view.

True
(Correct)


False

Explanation
Power BI reports are built in Power BI Desktop and then deployed to Power BI service, where
they can be viewed and shared. However, if you are building dashboards for the Sales team at
your organization and you receive a requirement that the dashboards should also be viewable on
mobile devices, Power BI will help you to set dashboards to mobile view.

To navigate to mobile view in Power BI Desktop, select View on the ribbon and then
select Mobile Layout, which will redirect you to the mobile view, as shown in the following
figure.
In the mobile view in Power BI Desktop, you are able to accomplish several tasks. This view
emulates the view of a user who is looking at visuals on their phone, so you can add visuals to
this view, resize them, and change the formatting on them, as shown in the ensuing screenshot.
In the June 2020 release of Power BI Desktop, a new grid has been added to this view so that
you can orient your visuals with more ease and overlay visuals on top of each other. This feature
can be useful if you want to insert a visual on top of an image.

After you have published to Power BI service, you can view your visuals on a mobile device.

Alternatively, you can also optimize your dashboards for mobile view in Power BI service. To
see a dashboard in mobile view, select the ellipsis (...) on the home ribbon and select Mobile
view, as shown in the following Sales dashboard.
This selection will take you to the following view, where you can choose which tiles that you
want to see on the phone view.
You can also resize and reorient the tiles and visuals in whichever order you want. This phone
view is customizable for each person who uses the dashboard; selecting Phone view will allow
you to create a new view that you can see on your phone when signing in to Power BI service.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-create-dashboard-mobile-
phone-view/

Question 45: Incorrect
When you publish a report, Power BI Desktop packages your report and data, including all your
visualizations, queries, and custom measures, and then uploads them to Power BI service.
 True or False: For production reports, MS recommends that you publish to an app workspace.


True
(Correct)


False
(Incorrect)

Explanation
When you are finished designing your report, you can publish it to your Power BI workspace.
You also have options to export to Microsoft Excel.

Publish reports

When you publish a report, Power BI Desktop packages your report and data, including all your
visualizations, queries, and custom measures, and then uploads them to Power BI service.

To publish your report, select the Publish button on the Home tab.

You might be prompted to save your changes, in which case, select Save and then proceed to
save your Power BI (.pbix) file.

You might also be required to sign in to Power BI. If so, enter your sign-in credentials to
continue.

In the Publish to Power BI window, select the destination in which you want to publish the
report. For example, you can publish to a workspace within Power BI. For production reports,
MS recommends that you publish to an app workspace.

When the report is successfully published, you'll get a success message that contains a link to
your report in your Power BI site. Select Got it to close the Publishing to BI window and return
to your report in Power BI Desktop. From the report page, you can select the provided URL to
go to Power BI service and see your new report.
 Export reports

Power BI allows you to export visual data, reports, and datasets. You can export to several
different formats including CSV, Excel, and PDF.

https://www.bmc.com/blogs/power-bi-publish-reports/

Question 46: Correct
Identify the missing word(s) in the following sentence within the context of Microsoft.

[?] is the process of determining how your tables are related to each other. This process is done
by defining and creating relationships between the tables. From that point, you can enhance it by
defining metrics and adding custom calculations to enrich your data.


Data Modelling
(Correct)


Data Analyzation


Data Managing


Data Preparation


Data Visualization

Explanation
A data analyst is one of several critical roles in an organization, who help uncover and make
sense of information to keep the company balanced and operating efficiently. Therefore, it's vital
that a data analyst clearly understands their responsibilities and the tasks that are performed on a
near-daily basis. Data analysts are essential in helping organizations gain valuable insights into
the expanse of data that they have, and they work closely with others in the organization to help
reveal valuable information.

The following figure shows the five key areas that you'll engage in during the data analysis
process.
Prepare

As a data analyst, you'll likely divide most of your time between the prepare and model tasks.
Deficient or incorrect data can have a major impact that results in invalid reports, a loss of trust,
and a negative effect on business decisions, which can lead to loss in revenue, a negative
business impact, and more.

Before a report can be created, data must be prepared. Data preparation is the process of
profiling, cleaning, and transforming your data to get it ready to model and visualize.

Data preparation is the process of taking raw data and turning it into information that is trusted
and understandable. It involves, among other things, ensuring the integrity of the data, correcting
wrong or inaccurate data, identifying missing data, converting data from one structure to another
or from one type to another, or even a task as simple as making data more readable.

Data preparation also involves understanding how you're going to get and connect to the data and
the performance implications of the decisions. When connecting to data, you need to make
decisions to ensure that models and reports meet, and perform to, acknowledged requirements
and expectations.

Privacy and security assurances are also important. These assurances can include anonymizing
data to avoid oversharing or preventing people from seeing personally identifiable information
when it isn't needed. Alternatively, helping to ensure privacy and security can involve removing
that data completely if it doesn't fit in with the story that you're trying to shape.

Data preparation can often be a lengthy process. Data analysts follow a series of steps and
methods to prepare data for placement into a proper context and state that eliminate poor data
quality and allow it to be turned into valuable insights.
Model

When the data is in a proper state, it's ready to be modelled. Data modelling is the process of
determining how your tables are related to each other. This process is done by defining and
creating relationships between the tables. From that point, you can enhance the model by
defining metrics and adding custom calculations to enrich your data.

Creating an effective and proper data model is a critical step in helping organizations understand
and gain valuable insights into the data. An effective data model makes reports more accurate,
allows the data to be explored faster and more efficient, decreases time for the report writing
process, and simplifies future report maintenance.

The model is another critical component that has a direct effect on the performance of your
report and overall data analysis. A poorly designed model can have a drastically negative impact
on the general accuracy and performance of your report. Conversely, a well-designed model with
well-prepared data will ensure a properly efficient and trusted report. This notion is more
prevalent when you are working with data at scale.

From a Power BI perspective, if your report is performing slowly, or your refreshes are taking a
long time, you will likely need to revisit the data preparation and modelling tasks to optimize
your report.

The process of preparing data and modelling data is an iterative process. Data preparation is the
first task in data analysis. Understanding and preparing your data before you model it will make
the modelling step much easier.

Visualize

The visualization task is where you get to bring your data to life. The ultimate goal of the
visualize task is to solve business problems. A well-designed report should tell a compelling
story about that data, which will enable business decision makers to quickly gain needed
insights. By using appropriate visualizations and interactions, you can provide an effective report
that guides the reader through the content quickly and efficiently, therefore allowing the reader
to follow a narrative into the data.

The reports that are created during the visualization task help businesses and decision makers
understand what that data means so that accurate and vital decisions can be made. Reports drive
the overall actions, decisions, and behaviours of an organization that is trusting and relying on
the information that is discovered in the data.

The business might communicate that they need all data points on a given report to help them
make decisions. As a data analyst, you should take the time to fully understand the problem that
the business is trying to solve. Determine whether all their data points are necessary because too
much data can make detecting key points difficult. Having a small and concise data story can
help find insights quickly.
With the built-in AI capabilities in Power BI, data analysts can build powerful reports, without
writing any code, that enable users to get insights and answers and find actionable objectives.
The AI capabilities in Power BI, such as the built-in AI visuals, enable the discovering of data by
asking questions, using the Quick Insights feature, or creating machine learning models directly
within Power BI.

An important aspect of visualizing data is designing and creating reports for accessibility. As you
build reports, it is important to think about people who will be accessing and reading the reports.
Reports should be designed with accessibility in mind from the outset so that no special
modifications are needed in the future.

Many components of your report will help with storytelling. From a colour scheme that is
complementary and accessible, to fonts and sizing, to picking the right visuals for what is being
displayed, they all come together to tell that story.

Analyze

The analyze task is the important step of understanding and interpreting the information that is
displayed on the report. In your role as a data analyst, you should understand the analytical
capabilities of Power BI and use those capabilities to find insights, identify patterns and trends,
predict outcomes, and then communicate those insights in a way that everyone can understand.

Advanced analytics enables businesses and organizations to ultimately drive better decisions
throughout the business and create actionable insights and meaningful results. With advanced
analytics, organizations can drill into the data to predict future patterns and trends, identify
activities and behaviours, and enable businesses to ask the appropriate questions about their data.

Previously, analyzing data was a difficult and intricate process that was typically performed by
data engineers or data scientists. Today, Power BI makes data analysis accessible, which
simplifies the data analysis process. Users can quickly gain insights into their data by using
visuals and metrics directly from their desktop and then publish those insights to dashboards so
that others can find needed information.

This feature is another area where AI integrations within Power BI can take your analysis to the
next level. Integrations with Azure machine learning, cognitive services, and built-in AI visuals
will help to enrich your data and analysis.

Manage

Power BI consists of many components, including reports, dashboards, workspaces, datasets, and
more. As a data analyst, you are responsible for the management of these Power BI assets,
overseeing the sharing and distribution of items, such as reports and dashboards, and ensuring
the security of Power BI assets.
 Apps can be a valuable distribution method for your content and allow easier management for
large audiences. This feature also allows you to have custom navigation experiences and link to
other assets within your organization to complement your reports.

The management of your content helps to foster collaboration between teams and individuals.
Sharing and discovery of your content is important for the right people to get the answers that
they need. It is also important to help ensure that items are secure. You want to make sure that
the right people have access and that you are not leaking data past the correct stakeholders.

Proper management can also help reduce data silos within your organization. Data duplication
can make managing and introducing data latency difficult when resources are overused. Power
BI helps reduce data silos with the use of shared datasets, and it allows you to reuse data that you
have prepared and modelled. For key business data, endorsing a dataset as certified can help to
ensure trust in that data.

The management of Power BI assets helps reduce the duplication of efforts and helps ensure
security of the data.

https://www.rasmussen.edu/degrees/technology/blog/what-does-a-data-analyst-do/

Question 47: Correct
Which two functions will help you compare dates to the previous month?

ENDOFMONTH and PREVIOUS MONTH and CALCULATE


TOTALYTD and PREVIOUS MONTH


CALCULATE and TOTALTYD


CALCULATE and PREVIOUSMONTH
(Correct)

Explanation
CALCULATE and PREVIOUSMONTH help you compare dates to the previous month.
DAX
Total Sales Previous Month = CALCULATE
(
sum('Sales OrderDetails'[Total Price])
, PREVIOUSMONTH(Dates[Date])
)

This measure uses the CALCULATE function, indicating that you're overriding the context to evaluate
this expression the way that you want to. You're summing Total Price, as you've been doing
 throughout this module. For the second argument, you're using PREVIOUSMONTH for the override,
which tells Power BI that, no matter what month is the default, the system should override it to
be the previous month.

The following screenshot shows the results in a table visual.

When you examine the months side-by-side, notice that the total sales for July compare to the
total sales for June.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Question 48: Correct
What benefit do you get from analyzing the metadata?

The benefit of analyzing the metadata is to determine which features and parameter properties to adjust in the metadata.


The benefit of analyzing the metadata is to know the number of rows, columns and tables being loaded into your model.


The benefit of analyzing the metadata is to get familiar with your data.


The benefit of analyzing the metadata is that you can clearly identify data inconsistencies with your dataset.
(Correct)
 Explanation
The benefit of analyzing the metadata is that you can clearly identify data inconsistencies with
your dataset.

Metadata describes data containing specific information like type, length, textual description and
other characteristics. Metadata serves a variety of purposes, with resource discovery one of the
most common. Here, it can be compared to effective cataloguing, which includes identifying
resources, defining them by criteria, bringing similar resources together and distinguishing
among those that are dissimilar.

https://www.villanovau.com/resources/bi/metadata-importance-in-data-driven-world/

Question 49: Incorrect
Scenario: You have been hired as a consultant by Cobalt Chemical to oversee a Power BI
project. This project is focused sales and expense data.

The head analyst ran Quick Insights on the sales data which generated the chart below.

Larger image

Should this chart be added to your report?


No, Quick Insights is solely for data exploration.


 No, seasonality on the SalesPersonID field is illogical.
(Correct)


No, a line chart is not the ideal way to demonstrate seasonality.


Yes, Quick Insights identified key correlations in the data
(Incorrect)


Yes, seasonality serves as an important factor in sales projections.

Explanation
The seasonality on the SalesPersonID field is illogical and needs to be refined if it is to be used.

Optimize your data for Power BI Quick Insights

Want to improve quick insights results? If you are a dataset owner, try these:

• Hide or unhide columns in your dataset. Power BI quick insights doesn't search hidden
columns. So hide duplicate or unnecessary columns and unhide interesting columns.

• Use a mix of data types such as names, times, dates, and numbers.

• Avoid (or hide) columns with duplicate information. This takes valuable time away from
searching for meaningful patterns. For example, one column with state names spelled out and
another column with state name abbreviations.

• Do you get an error message stating that your data isn't statistically significant? This can
happen with models that are very simple, or that don't have much data, or that don't have date or
numeric columns.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-insights-optimize

Here is a quick insights demo on a dataset and open an insight in Focus mode. The demonstrator
pins an insight as a tile on the dashboard, then gets insights for a dashboard tile.
https://www.youtube.com/watch?v=et_MLSL2sA8
Question 50: Correct
While the process of data analysis focuses on the tasks of cleaning, modelling , and visualizing
data, the concept of data analysis and its importance to business should not be understated. To
analyze data, core components of analytics are divided into the following categories:

• Descriptive

• Diagnostic

• Predictive

• Prescriptive

• Cognitive
 Which of these categories asks the question:

“Why it is happening in your business?”


Diagnostic
(Correct)


Cognitive


Prescriptive


Descriptive


Predictive

Explanation
Data-driven businesses make decisions based on the story that their data tells, and in today's
data-driven world, data is not being used to its full potential, a challenge that most businesses
face. Data analysis is, and should be, a critical aspect of all organizations to help determine the
impact to their business, including evaluating customer sentiment, performing market and
product research, and identifying trends or other data insights.
While the process of data analysis focuses on the tasks of cleaning, modelling , and visualizing
data, the concept of data analysis and its importance to business should not be understated. To
analyze data, core components of analytics are divided into the following categories:

• Descriptive

• Diagnostic

• Predictive

• Prescriptive

• Cognitive

Descriptive analytics
Descriptive analytics help answer questions about what has happened based on historical data.
Descriptive analytics techniques summarize large datasets to describe outcomes to stakeholders.

By developing key performance indicators (KPIs), these strategies can help track the success or
failure of key objectives. Metrics such as return on investment (ROI) are used in many
industries, and specialized metrics are developed to track performance in specific industries.

An example of descriptive analytics is generating reports to provide a view of an organization's

sales and financial data.

Diagnostic analytics

Diagnostic analytics help answer questions about why events happened. Diagnostic analytics
techniques supplement basic descriptive analytics, and they use the findings from descriptive
analytics to discover the cause of these events. Then, performance indicators are further
investigated to discover why these events improved or became worse. Generally, this process
occurs in three steps:

1. Identify anomalies in the data. These anomalies might be unexpected changes in a metric or a
particular market.

2. Collect data that's related to these anomalies.

3. Use statistical techniques to discover relationships and trends that explain these anomalies.

Predictive analytics

Predictive analytics help answer questions about what will happen in the future. Predictive
analytics techniques use historical data to identify trends and determine if they're likely to recur.
Predictive analytical tools provide valuable insight into what might happen in the future.
Techniques include a variety of statistical and machine learning techniques such as neural
networks, decision trees, and regression.

Prescriptive analytics

Prescriptive analytics help answer questions about which actions should be taken to achieve a
goal or target. By using insights from predictive analytics, organizations can make data-driven
decisions. This technique allows businesses to make informed decisions in the face of
uncertainty. Prescriptive analytics techniques rely on machine learning strategies to find patterns
in large datasets. By analyzing past decisions and events, organizations can estimate the
likelihood of different outcomes.

Cognitive analytics

Cognitive analytics attempt to draw inferences from existing data and patterns, derive
conclusions based on existing knowledge bases, and then add these findings back into the
knowledge base for future inferences, a self-learning feedback loop. Cognitive analytics help you
learn what might happen if circumstances change and determine how you might handle these
situations.

Inferences aren't structured queries based on a rules database; rather, they're unstructured
hypotheses that are gathered from several sources and expressed with varying degrees of
confidence. Effective cognitive analytics depend on machine learning algorithms, and will use
several natural language processing concepts to make sense of previously untapped data sources,
such as call centre conversation logs and product reviews.

https://www.weirdgeek.com/2018/11/types-of-analytics/

Continue
Retake test