Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

PowerBI > practice questions > Flashcards

practice questions Flashcards

1
Q

You need to create a calculated column to show the date in a format like this February 01, 2021. Which of the following DAX expressions would you use?

A. FORMAT([Date],”M”)|| FORMAT([Date],”D”)|| FORMAT([Date],”Y”)

B. FORMAT([Date],”MMMM DD, YYYY”)

C. FORMAT([Date],”MMM”)|| FORMAT([Date],”DD”)|| FORMAT([Date],”YYYY”)

D. FORMAT(‘Date_Table’ [Date],”MMM”)|| FORMAT(‘Date_Table’ [Date],”DD”)|| FORMAT(‘Date_Table’ [Date],”YYYY”)

A

B. FORMAT([Date],”MMMM DD, YYYY”)

“MMMM” is a user-defined format for date/time that displays the full month name as the month. FORMAT([Date],”MMMM DD, YYYY”) is the right DAX expression.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

You have been provided with the sales data of ABC company for the years 2005-2020 and asked to build a measure that will calculate the total sale for all years up to 2015, multiplied by a fixed multiplier. For the years 2005-2010, the multiplier was 15% and for the years 2010-2015, it was 25%.

Fill in blanks by selecting the right sequence of the words from the given option to create the required measure.

= …………… (
……….. (……………… (‘Date’[year]),
‘Date’[year]<2015)
),
……………(‘Date’[year]>=2010,
[Sales Amount]1.2,
[Sales Amount]1.1)
)
)

A. FILTER, SUM, VALUES, TABLE

B. SUMX, FILTER, VALUES, IF

C. IF, VALUES, CONCATENATE, TABLE

D. IF, TABLE, SUM, FILTER

E. SUM, FILTER, IF, VALUES

A

B. SUMX, FILTER, VALUES, IF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

The data model has an impact on the time consumed in the refreshing of a model but once the model is loaded in the memory, it has no impact on the performance of the reports.

A. True

B. False

A

B. False

In the case of a large data model, the measures need to iterate over more data and therefore the evaluation is likely to consume more time that will certainly affect the performance of the reports. Therefore, reducing the size of the data model by removing the unnecessary rows or columns will help in improving the performance of the reports.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

You build a map of 3,40,000 students using an R visual. You ensure that the values of student_id, longitude, and latitude are added in the fields sent to the visual. Each Student_id is unique representing an individual student. But when the visual is loaded by the users in Power BI, it shows the details for only some of the students; not all. What is the reason behind this issue?

A. A different version of R was used to build the visual

B. The data was duplicated

C. The data originates from a Microsoft SQL Server source

D. The number of records to be shown were higher than the capacity of R visuals

A

D. The number of records to be shown were higher than the capacity of R visuals

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Which of the following statements are true for Power BI paginated reports?

A. Power BI paginated reports are created using Power BI Desktop

B. Power BI paginated reports are created using Power BI Service

C. Power BI paginated reports are created using Power BI Report Builder

D. Power BI paginated reports are descendants of SQL Server Reporting Services

E. Power BI paginated reports are descendants of SQL Server Analysis Services

A

C. Power BI paginated reports are created using Power BI Report Builder

D. Power BI paginated reports are descendants of SQL Server Reporting Services

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which of the following data sources can be connected through the “Get Data” option in the Power BI Desktop?

  1. Power BI datasets
  2. Power BI dataflows
  3. Power BI Template Apps
  4. All of the above
A
  1. All of the above
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

For the query opened in Power Query Editor, you are interested in finding out the percentage of empty cells or values in each column as soon as possible. Which of the following data Preview Options would you select?

  1. Column profile
  2. Show Whitespaces
  3. Column quality
  4. Column distribution
A
  1. Column quality
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

You are interested in creating a query that will have one row for each student and besides Name, Registration_id, Grade, Region_id, each row should also include the country of the student. Which of the following will help in achieving the goal?

  1. Append the Students and Area tables
  2. Transpose both students and Area tables
  3. Merge the Students and Area tables
  4. Group the Students and Area table on Region_id column
  5. Apply intersection operations on the students and Area tables.
A
  1. Merge the Students and Area tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which of the following statements are true about data granularity? (Select Multiple Options)

  1. Data granularity represents the level of the details represented within the data.
  2. Data granularity represents the relationships among the various data entities.
  3. Data granularity represents the type of values contained by the data
  4. Higher data granularity, greater the level of detail within the data.
  5. Higher data granularity, high domain range for the data
A
  1. Data granularity represents the level of the details represented within the data.
  2. Higher data granularity, greater the level of detail within the data.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Which of the following statements are true for dashboards and reports? (Select Multiple Options)

  1. Dashboards can have only a single page while reports can have multiple pages
  2. Reports can have only a single page while Dashboards can have multiple pages
  3. Both Dashboards and Reports can have multiple pages
  4. Both reports and dashboards can be refreshed to display the latest data
  5. Dashboards can be refreshed to display the latest data while the reports can’t
  6. Reports can be refreshed to display the latest data while the Dashboards can’t
A
  1. Dashboards can have only a single page while reports can have multiple pages
  2. Both reports and dashboards can be refreshed to display the latest data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

You have created a dashboard with the help of Microsoft Power BI Service. The dashboard has a card visual that displays the total sales from the last year.

You provide users access to the dashboard through the Viewer role on the workspace.

A user is interested in receiving daily notifications for the number displayed on the card visual. Which of the following is the right choice to automate the notifications?

  1. Creating a data alert.
  2. Sharing the dashboard to the user.
  3. Creating a subscription.
  4. Tagging the user in a comment.
A
  1. Creating a subscription.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is the unique benefit of using a customized tooltip?

  1. With a customized tooltip, any image or color can be set as the background for the visual
  2. It can demonstrate the value and category of the data points
  3. It can demonstrate the images, visuals, and any other collection of items that are created by the user on the report page
  4. With a customized tooltip, a border can be created around the visual for isolating the visual from other elements on the canvas.
A
  1. It can demonstrate the images, visuals, and any other collection of items that are created by the user on the report page
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

You have created a line visual that displays the number of students getting scholarships over time. Now, you are interested in seeing the total scholarship cost of the students when you hover over a data point. Which of the following are the possible options to accomplish this goal? (Select Multiple Options)

  1. Add a Scholarship_amount to the Tooltips
  2. Add a Scholarship_amount to the drill through fields
  3. Add a Scholarship_amount to the Visual Filters
  4. None of the above
A
  1. Add a Scholarship_amount to the Tooltips
  2. Add a Scholarship_amount to the Visual Filters
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Which of the following chart is the great choice to identify or display the outliers?

  1. Line Chart
  2. Scatter Chart
  3. Bar chart
  4. Donut chart
A
  1. Scatter Chart
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

You need to create a report that will demonstrate the sales performance of the company. The sales team has requested you to add a single visualization (if possible) that might help them in identifying the factors that drive the sales and affect the success. Which of the following visualization would you use?

  1. Key influencers
  2. Funnel chart
  3. Donut Chart
  4. Q&A visualization
A
  1. Key influencers
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Promotion and certification are two ways to endorse dataset. Which of the following statements are true about Promotion and Certification? (Select Multiple Options)

  1. Promotion does not require specific permissions from the admin, whereas Certification needs permission from the dataset owner or admin to access the dataset.
  2. Promotion needs permission from the dataset owner or admin to access the dataset whereas Certification does not require specific permissions from the admin, whereas Certification.
  3. In terms of permission, there is no difference between promotion and certification.
  4. Certification is the ideal way to endorse the dataset when the dataset is ready for broad usage.
  5. Promotion is the ideal way to endorse the dataset when the dataset is ready for broad usage.
A
  1. Promotion does not require specific permissions from the admin, whereas Certification needs permission from the dataset owner or admin to access the dataset.
  2. Promotion is the ideal way to endorse the dataset when the dataset is ready for broad usage.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Which of the following is not the feature of the “Lineage view” in Power BI?

  1. It allows the users to troubleshoot the flow of data from its source to its destination.
  2. It allows the users to manage the workspaces and analyze the impact of a single change in one dataset to dashboards and reports.
  3. It simplifies the task of identifying the dashboards and reports that haven’t been refreshed.
  4. It helps in analyzing the data sets and finding the patterns, trends, and outliers.
A
  1. It helps in analyzing the data sets and finding the patterns, trends, and outliers.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

You need to create Power BI reports which are to be deployed to Power BI Report Server. Which of the following tools would you use?

  1. Power BI Desktop
  2. Power BI Desktop optimized for Report Server
  3. Power BI desktop optimized for Paginated reports
  4. Power BI Report Builder
A
  1. Power BI Desktop optimized for Report Server
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

You have a 200 GB data warehouse that has data consolidated from several applications and runs on SQL Server 2017 Enterprise Edition. You notice that with your reports, there is a default limitation of eight daily refreshes. Your manager asks you to ensure that the reports are never more outdated than 1 hour. Which of the following can help you in achieving the same?

  1. Building an application for monitoring the changes in the database and pushing them out utilizing the real-time streaming API
  2. Migrating the content to Power BI Premium
  3. Adding an SSAS tabular layer on top and enabling the Live connections
  4. Convert to DirectQuery to access the data warehouse.
A
  1. Convert to DirectQuery to access the data warehouse.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Suppose you have developed a query named consumers in Power BI Desktop to append the rows from 3 external tables with consumer data into a single table consumer.

Now you want to ensure that each row in the consumer table has a unique ID value. Which of the following is the best way to add a new fabricated ID column to the consumer table?

  1. Changing the data model by extending the consumers table with an Index column
  2. Changing the data model by extending consumer table with a Counter column
  3. Modifying the consumers’ query by adding an Index column
  4. Modifying the consumers’ query by adding a Counter column
A
  1. Modifying the consumers’ query by adding an Index column
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Why is it advised to avoid the NULL values in the numeric column?

  1. DAX expression having SUM function on such data will provide incorrect results.
  2. DAX expression having MAX function on such data will provide incorrect results.
  3. DAX expression having an AVERAGE function on such data will provide incorrect results.
  4. DAX Expressions can’t be applied if a numeric column has one or multiple NULL values.
  5. All the above
A
  1. DAX expression having an AVERAGE function on such data will provide incorrect results.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Consider the following table:

After you pivot the columns, the table appears as shown below:

As you can see, there is an Error in the 3rd row. How would you fix this error? The solution needs to preserve all the data.

  1. For the key column, use “Duplicate Values”.
  2. Rename column3
  3. Use “Remove Errors” for Column3
  4. Modify the Data Type of the Value Column
  5. Change the Aggregate Value Function of the Pivot
A
  1. Change the Aggregate Value Function of the Pivot
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

There is a dataset containing the survey results for the percentage of Facebook usage by Gender, year, and age. The age of the survey respondents lies between 10 and 70 years. You need to build a bar chart demonstrating the percentage of Facebook usage for equal age ranges of 15 years.

Here is the list of some actions that might be performed.

A. Set the group type to Bin

B.Set the Bin size to 4

C. Set the Bin Count to 15

D. Set the Bin count to 4

E. Set Group type to list

F. Set the Bin type to the number of Bins

G. Right-click on the column Age and Choose New Group

Choose the correct sequence of the actions (you may choose some or all actions from the given list) that you would perform.

  1. a-b-c-f-g
  2. a-c-b-g-h
  3. a-b-c-e-g
  4. g-a-f-d
  5. a-g-f-d
A
  1. g-a-f-d

G. Right-click on the column Age and Choose New Group

A. Set the group type to Bin

F. Set the Bin type to the number of Bins

D. Set the Bin count to 4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Which of the below-given settings are possible to be managed in a dataset in Power BI Service?

A. Adding a new data source

B. Adding a new DAX measure

C. Configure Scheduled Refreshes

D. View the refresh history

E. Creating a new table relationship

F. If you are not the current owner, you can take over the dataset.

A

C. Configure Scheduled Refreshes

D. View the refresh history

F. If you are not the current owner, you can take over the dataset.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

You develop a Power BI Report for a huge dataset. You are looking to enhance the performance of dataset refresh through an incremental refresh. Which of the following statements are true regarding this scenario?

A. Use Incremental Refresh feature only for the data sources supporting query folding

B. RangeStart and RangeEnd are two PowerQuery parameters that are needed for the incremental Refresh

C. Use Incremental Refresh feature for all data sources

D. A date field is needed on a table in the dataset

A

A. Use Incremental Refresh feature only for the data sources supporting query folding

B. RangeStart and RangeEnd are two PowerQuery parameters that are needed for the incremental Refresh

D. A date field is needed on a table in the dataset

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Which of the following is not a benefit offered by the workspaces?

A. Workspaces can be used to house dashboards and reports for use by multiple teams.

B. Workspaces allow you to share and present dashboards and reports in a single environment.

C. Workspaces allow you to view and edit the data model, create visualizations and transform the data.

D. Workspaces ensure the highest level of security by controlling who can access the dashboards, reports, and datasets.

A

C. Workspaces allow you to view and edit the data model, create visualizations and transform the data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

You attempt to connect Purer 81 Desktop to a Cassandra database.
From the Get Data connector list you discover that there is no specific connector for the Cassandra database, You need to select an alternate data connector that will connect to the database.
Which of connector should you choose?

A.
Microsoft SQL Server database

B.
ODBC

C.
OData

D.
OLE DB

A

ODBC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

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

A.
a SharePoint folder

B.
Power Bl dataflows

C.
an Excel workbook

D.
Power Bl dataset

A

Power Bl dataset

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

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

A.
Yes

B.
No

A

No

The 50th percentile is also known as the median or middle value where 50 percent of observations fall below.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

You build a report to help the sales team understand its performance and the drivers of sales. The team needs to have a single visualization to identify which factors affect success. Which type of visualization should you use?

A.
Key influences

B.
Funnel chart

C.
Q&A

D.
Line and clustered column chart

A

A.
Key influences

The key influencers visual helps you understand the factors that drive a metric you’re interested in. It analyzes
your data, ranks the factors that matter, and displays them as key influencers. The key influencers visual is a great choice if you want to:

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

You have a Power B1 report that contains three pages named Page1, Page2, and Page3. All the pages have the same slicers. You need to ensure that all the filters applied to Page1 apply to Page1 and Page3 only. What should you do?

A.
Sync the slicers on Page1 and Page3

B.
On each page, modify the interactions of the slicer.

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

A

B.
On each page, modify the interactions of the slicer.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

You are interested in creating a measure that will always calculate the total sales for 2020, irrespective of the year selected in any other visual in Power BI. Which of the following is the right way to create such a measure?

A. Total Sales for 2020= CALCULATE(SUM(‘Sales OrderDetails’[Total Price]), YEAR(‘Sales OrderDetails’[orderdate]) = 2020)

B. Total Sales for 2020= SUM(CALCULATE(‘Sales OrderDetails’[Total Price]), YEAR(‘Sales OrderDetails’[orderdate]) = 2020)

C. Total Sales for 2020= TOTAL(SUM(‘Sales OrderDetails’[Total Price]), YEAR(‘Sales OrderDetails’[orderdate]) = 2020)

D. It is not possible to create such a measure.

A

A. Total Sales for 2020= CALCULATE(SUM(‘Sales OrderDetails’[Total Price]), YEAR(‘Sales OrderDetails’[orderdate]) = 2020)

The CALCULATE function in DAX helps in creating a measure that will override the specific portions of the context that are being utilized to express the correct result. The syntax to use the calculate function is:
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
Where expression represents the expression that is to be evaluated and Filter1, Filter2 are optional and represent the table or Boolean expressions that define filters, or filter modifier functions.</filter2></filter1></expression>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What will be the result of the following DAX formula if the year 2020 is chosen on a slicer of a report?
2019 Salary_Cost = CALCULATE([Total Salary_cost], Filter (Date, Date[Year]=2019))

A. Salary_cost for the Year 2020 as Slicer Selection overrides DAX Filter.

B. Salary_cost for the Year 2019 as DAX Filter overrides Slicer Selection.

C. Blank. It will return no records.

D. It will return Salary_cost records for the year 2019 as well as 2020.

A

C. Blank. It will return no records.

First, the given DAX expression was supposed to have a SUM function to calculate the total sales. Moreover, in the given scenario, the Slicer selection for the year 2020 will also be applied along with the DAX expression. It is not possible for any record to have both years i.e year 2019 and year 2020 at the same time. Therefore, the given DAX expression in the current scenario will return nothing.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Which of the following is the right DAX function to modify a single directional relationship to a bi-directional relationship on the fly (within the measure)?

A. TREATAS

B. USERELATIONSHIP

C. CROSSFILTER

D. RELATEDTABLE

E. RELATED

A

C. CROSSFILTER

CROSSFILTER is a DAX function that takes the two columns and set the cross filter relationship between these columns.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is the use of Row Level Security (RLS) in Power BI?

A. Assigning the security permissions for the underlying Power BI Azure SQL Database

B. Adding the ability to link Power BI Log-in to organizational Active Directory or Window Login through Single Sign On (SSO)

C. Restricting data access for the given users ( By assigning users to roles)

D. Restrictions to specific measures within Power BI reports to some specific users

E. All the above

A

C. Restricting data access for the given users ( By assigning users to roles)

The use of Row Level Security in Power BI is to restrict or limit the data access for the specified users. The Filters restrict the data access at row-level and filters can be defined within roles. In Power BI service, the members can access the datasets in the workspace. Row Level security does not put restrictions on this data access.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Consider that you want to split your query into two parts. Which of the following options would you use in Power BI?

A. Format Query

B. Split Query

C. Extract previous

D. Mark Query

A

C. Extract previous

Extract Previous is the option that helps in splitting the query into parts. If you want to split a query, right-click an applied step (as per desire or requirements) to the query and choose the exact previous option. It will split the query into 2 parts by making a new query consisting of all the steps before the selected step.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Consider the statement below.
Row Level Security can’t be applied in Power BI Desktop while connecting via a Live connection to Analysis Services, these need to be set within the AS model.

A. True

B. False

A

B. False

You can configure Row-Level Security for the data models imported in Power BI through Power BI Desktop. It is also possible to configure Row Level Security (RLS) on the datasets that use DirectQuery, such as SQL Server. But for Analysis Services or Azure Analysis Services live connections, Row-level security is configured within the model, not Power BI Desktop. The AS models hold RLS roles already that can be used by Power BI via a Live connection.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Your team manager has given you a task to create a Power BI dashboard consisting of visualizations for sales data. You have been asked to add some sample questions that can be asked by the users while using the Q&A option. To enable the sample questions, what will you modify in the settings of Power BI?

A. Dashboards

B. Reports

C. Datasets

D. Workbooks

A

C. Datasets

To enable the sample questions for the users, it is the datasets that should be modified in Power BI settings.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

You have created a dashboard with the help of Microsoft Power BI Service. The dashboard has a card visual that displays the total sales from the last year.
You provide users access to the dashboard through the Viewer role on the workspace.
A user is interested in receiving daily notifications for the number displayed on the card visual. Which of the following is the right choice to automate the notifications?

A. Creating a data alert.

B. Sharing the dashboard to the user.

C. Creating a subscription.

D. Tagging the user in a comment.

A

C. Creating a subscription.

You can subscribe your mates and yourself to the dashboards, report pages, and paginated reports that are significant to you. Power BI e-mail subscription allows you to choose how frequently you are interested in receiving the emails: monthly, weekly, daily, hourly, or once a day after the initial data refresh and even the time you are interested in receiving the email if you select monthly, weekly, daily or hourly.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

You are creating a complex report having a lot of visuals in Power BI. You want to ensure that the users read it in a logical way when they use the keyboard. From the following list, select the right option which you need to work on.

A. Page Order

B. Tab Order

C. Layer Order

D. Field Hierarchy

E. Bookmark Order

A

B. Tab Order

Tab order defines the order in which a user interacts with the things on a page through the keyboard. If you want to change the default order, open the selection pane from the view tab and choose the Tab Order.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

You have a Consumer Sales Revenue monthly report. You analyze and notice that the attrition Rate metric is sharply increased.
You are interested in understanding the variables that might affect this result.
Which of the following visuals can you use to understand and explore the potential causes in Power BI Desktop?

A. Decomposition tree visuals

B. Funnel Visual

C. Key Influencer Visual

D. Combo visual

E. Waterfall Visual

A

A. Decomposition tree visuals

C. Key Influencer Visual

Decomposition Tree is an Artificial Intelligence visual that is ideal for ad hoc exploration and conducting root cause analysis. It helps in automatically analyzing the chosen dimensions to check where the measure is having the lowest or highest value. key influencer visual is another visual that is a good choice to know the factors that influence a key metric.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

You have several dashboards. You want to ensure that when the users browse the available dashboards from powerbi.com, they should be able to view which dashboards have personally identifiable information (PII). The solution needs to minimize the configuration efforts and effect on the dashboard design.
Which of the following would you use?

A. Tiles

B. Comment

C. Active Directory groups

D. Data Classifications

E. Feedback

A

D. Data Classifications

With the help of dashboard data classification, you can raise awareness with the viewers of the dashboards about the level of security that should be utilized. You can tag your dashboards with classifications mentioned by the IT department of your company, so everyone seeing the content will have the same level of information and understanding around the sensitivity of the data.
Therefore, by using data classification, you can make sure that when the users browse the available dashboards from powerbi.com, they can view which dashboards have personally identifiable information (PII).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Which of the following Azure Cognitive Services models can be called through the AI insights feature in Power BI Desktop? (Select Multiple Options)

A. Sentiment Analysis

B. Key Phrase Extraction

C. K-mean Clustering

D. Language Detection

E. Binary Classification

F. All of the above

A

A. Sentiment Analysis

B. Key Phrase Extraction

D. Language Detection

AI Insights option in Power BI Desktop includes Azure Cognitive Services models -Sentiment Analysis, Key Phrase Extraction, and Language Detection. These models help derive the meaning or specific pieces of language from text data. For example, the Sentiment Analysis or Key Phrase Extraction option can be used to know the customer sentiments in the Help tickets and visually demonstrate the results in Power BI.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

Which of the following chart is the great choice to identify or display the outliers?

A. Line Chart

B. Scatter Chart

C. Bar chart

D. Donut chart

A

B. Scatter Chart

Scatter chart is the best choice to identify the outliers in the data. After identification of the outliers, the reasons for their existence can be investigated or explored and corrective action is taken. The following is an example of a “Scatter chart” that shows the number of orders by Orders Shipped by Product Category.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

You need to create a report that will demonstrate the sales performance of the company. The sales team has requested you to add a single visualization (if possible) that might help them in identifying the factors that drive the sales and affect the success. Which of the following visualization would you use?

A. Key influencers

B. Funnel chart

C. Donut Chart

D. Q&A visualization

A

A. Key influencers

key influencers are a specific type of visuals that help in identifying the factors that drive the target matric. It analyzes the given data, ranks the factors that matter, and shows them as key influencers. The two most important scenario where key influencers do great are :
To see the factors that affect the metric being analyzed.
To contrast the relative importance of the identified factors.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

There is a dashboard named Salary_Cost having Q&A enabled on it. When a user tries to get the count of Employees, the query gets failed. On the other hand, when the user asks for the count of workers, the query returns the expected result.
You need to ensure that the same result is returned by both queries. Which of the following actions would you perform?

A. Editing synonyms from the Power BI service

B. Editing Relationships from the Power BI Desktop

C. Editing synonyms from the Power BI Desktop

D. Editing tables from the Power Query in Power BI Desktop

A

C. Editing synonyms from the Power BI Desktop

When you are interested in improving the Q&A experience, it is always a good practice to add synonyms from the Power BI Desktop Model view.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

Which of the following tasks can’t be performed using the dataset settings option of the Power BI Service?

A. Modify query editor parameter values

B. Check the cardinality for the columns in the dataset

C. Mark the dataset as certified or promoted

D. Change the credentials used for connecting to the underlying data source

E. Define the sensitivity label for the dataset

A

B. Check the cardinality for the columns in the dataset

Using the dataset settings option in Power BI, you are able to modify query editor parameter values, mark the dataset as certified or promoted, change the credentials used for connecting to the underlying data source and define the sensitivity label for the dataset. But it is not possible to view or check the cardinality of the columns using this option.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

Here is the list of configuration steps (given in improper sequence) for Row-level security (RLS).

  1. Create a report in Microsoft Power BI Desktop that involves import the data, confirm the data model between both tables, and create the report visuals.
  2. Create RLS roles in Power BI Desktop by using DAX.
  3. Test the roles in Power BI Desktop.
  4. Add members to the role in the Power BI service.
  5. Deploy the report to Microsoft Power BI service.
  6. Test the roles in the Power BI service.
    The correct sequence of steps for RLS configuration is :

A. 1-3-2-4-5-6
B. 1-2-3-5-4-6
C. 1-3-2-5-4-6
D. 1-2-6-4-5-3
E. 1-2-3-4-6-5

A

B. 1-2-3-5-4-6

The correct sequence of steps for RLS configuration is to create a report in Microsoft Power BI Desktop that involves import the data, confirm the data model between both tables and create the report visuals, Create RLS roles in Power BI Desktop by using DAX, Test the roles in Power BI Desktop, Deploy the report to Microsoft Power BI service, Add members to the role in Power BI service and Test the roles in Power BI service.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

Which of the following tasks can be done only by the workspace admin, not by the users with lesser authorities and privileges?

A. Creating, editing, or deleting the content in the workspace

B. Publishing workspace content in the app

C. Adding or removing the other users from the workspace

D. Creating a report in another workspace depending upon the dataset of this workspace

E. Scheduling the data refreshes

A

C. Adding or removing the other users from the workspace

Out of the given tasks, only adding or removing the other users is the only task that can be performed only by the workspace admin. All other given tasks can be performed even by the members of the workspace.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

Which of the following options is not a privacy level in Power BI desktop?

A. Private

B. Public

C. Organizational

D. Confidential

A

D. Confidential

In Power BI Desktop, a privacy level specifies an isolation level that specifies the degree to which one data source is isolated from other data sources. There are three possible privacy levels for data sources – private, public, and organizational. Here is the table describing the different privacy levels.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

For the query opened in Power Query Editor, you are interested in finding out the percentage of empty cells or values in each column as soon as possible. Which of the following data Preview Options would you select?

A. Column profile

B. Show Whitespaces

C. Column quality

D. Column distribution

A

C. Column quality

In Power Query Editor, Column Quality, Column distribution, and Column Profile are data profiling functionalities under the View tab in Data Preview Section. These functionalities help in understanding the data anomalies and statistics. Out of these three functionalities, Column quality is the one that can be used to show the percentages of data that is in error, empty and valid.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

There is an Azure SQL database having the records for sales transactions. The database is updated on a frequent basis. You need to create reports from the sales data to identify fake transactions. The data needs to be visible within 5 minutes of an update.
How can the data connection be configured to achieve the target?

A. By adding a SQL statement

B. By setting the data connectivity mode to DirectQuery

C. By setting the Command timeout in minutes setting

D. By setting the data connectivity mode to Import

E. By adding a PL/SQL statement

A

B. By setting the data connectivity mode to DirectQuery

While connecting to a data source in Power BI Desktop, it is always possible to import a copy of the data in Power BI Desktop. Some data sources have the option of connecting directly to the data source using DirectQuery. With the DirectQuery option, no data is imported or copied to Power BI Desktop. For relational sources, the chosen columns and tables appear in the Fields list. In multi-dimensional sources such as SAP Business Warehouse, the dimensions and measures for the chosen cube appear in the Fields list. As you interact with the visualization, Power BI queries the underlying data source and you always view the current data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

Which of the following is a potential way to improve the performance while getting the data in Power BI?

A. Combining the date and time columns in a single column

B. Pulling the data only into Power BI Service, nor Power BI Desktop

C. Performing some calculations in the original data source

D. Using SELECT SQL statement in SQL queries while pulling the data from a relational database

A

C. Performing some calculations in the original data source

Power Query and Power Query Editor are designed to allow you to process the data, but the processing power needed to do this may decrease the performance in other areas of your reports. Therefore, it is always a good practice to process as much as possible data in the original data source.

54
Q

Suppose you have developed a query named consumers in Power BI Desktop to append the rows from 3 external tables with consumer data into a single table consumer.
Now you want to ensure that each row in the consumer table has a unique ID value. Which of the following is the best way to add a new fabricated ID column to the consumer table?

A. Changing the data model by extending the consumers table with an Index column

B. Changing the data model by extending consumer table with a Counter column

C. Modifying the consumers’ query by adding an Index column

D. Modifying the consumers’ query by adding a Counter column

A

C. Modifying the consumers’ query by adding an Index column

Query editor has an option Index Column to add an indexed column starting from 1,0 or any custom number. “Insert Index Column” is present in the “Add Column” tab that allows the creation of a new index column starting from 0, 1, or any custom number. Users can customize the starting value and row increment for the index.

55
Q

What is dual storage mode?

A

Tables with this setting can act as either cached or not cached, depending on the context of the query that’s submitted to the Power BI dataset. In some cases, you fulfill queries from cached data. In other cases, you fulfill queries by executing an on-demand query to the data source.
You can set the dimension tables to Dual to reduce the number of limited relationships in the dataset, and improve performance.
CUSTOMER TABLE
DATE TABLE

56
Q

What is direct query mode?

A

Tables with this setting aren’t cached. Queries that you submit to the Power BI datasetג€”for example, DAX queriesג€”and that return data from
DirectQuery tables can be fulfilled only by executing on-demand queries to the data source. Queries that you submit to the data source use the query language for that data source, for example, SQL.
SALES

57
Q

What is import mode?

A

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

58
Q

You have a project management app that is fully hosted in Microsoft Teams. The app was developed by using Microsoft Power Apps.
You need to create a Power BI report that connects to the project management app.
Which connector should you select?

A. Microsoft Teams Personal Analytics
B. SQL Server database
C. Dataverse
D. Dataflows

A

C. Dataverse

The correct answer is C (Dataverse) because it is a database platform often used in conjunction with power apps. In other words, if your project management app is built using Power Apps and stores its data in Dataverse.

59
Q

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

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

A

B. a SharePoint folder

60
Q

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

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

A

A. Merge the Customer and Address tables.

The correct answer is A (Merge the Customer and Address tables), it is because merging in power query is used to combine data from multiple tables or queries into a single query based on one or more common columns. Merging is typically used to bring related data together, enrich or denormalize data, or create new composite datasets.

61
Q

You have two Azure SQL databases that contain the same tables and columns.
For each database, you create a query that retrieves data from a table named Customer.
You need to combine the Customer tables into a single table. The solution must minimize the size of the data model and support scheduled refresh in powerbi.com.
What should you do? To answer, select the appropriate options in the answer area.

A

Option used to combine the customer tables:
Append Queries as New -
When you have additional rows of data that you’d like to add to an existing query, you append the query.

Action to perform on the original 2 SQL database queries:
Disable loading the query to the data model
By default, all queries from Query Editor will be loaded into the memory of Power BI Model. You can disable the load for some queries, especially queries that used as intermediate transformation to produce the final query for the model.

62
Q

In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product.
Every Product has a ProductSubCategory.
Not every ProductsubCategory has a parent ProductCategory.
You need to merge the three queries into a single query. The solution must ensure the best performance in Power Query.
How should you merge the tables?

A

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

Left outer -
Not every ProductsubCategory has a parent ProductCategory.
One of the join kinds available in the Merge dialog box in Power Query is a left outer join, which keeps all the rows from the left table and brings in any matching rows from the right table.

63
Q

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

Products: Contains the product catalog
Orders: Contains high-level info about orders
Order line items: contains the product ID, qty and price details of an order

You need to perform the following analyses:
✑ Orders sold over time that include a measure of the total order value
Orders by attributes of products sold

The solution must minimize update times when interacting with visuals in the report.
What should you do first?

A. From Power Query, merge the Order Line Items query and the Products query.

B. Create a calculated column that adds a list of product categories to the Orders table by using a DAX function.

C. Calculate the count of orders per product by using a DAX function.

D. From Power Query, merge the Orders query and the Order Line Items query.

A

D. From Power Query, merge the Orders query and the Order Line Items query.

64
Q

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

A. Get data from a SharePoint folder and enter the site URL Select Transform, then filter by the folder path to the manufacturing reports library.

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

C. Get data from a SharePoint folder, enter the site URL, and then select Combine & Load.

D. Get data from a SharePoint list, enter the site URL, and then select Combine & Load.

A

A. Get data from a SharePoint folder and enter the site URL Select Transform, then filter by the folder path to the manufacturing reports library.

65
Q

You have a Microsoft Excel workbook that contains two sheets named Sheet1 and Sheet2.
Sheet1 contains the following table named Table1.

**Products **
abc
def
ghi
jkl
mno

Sheet2 contains the following table named Table2.

**Products **
abc
xyz
tuv
mno
pqr
stu

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

**Products **
abc
xyz
tuv
mno
pqr
stu
def
ghi
jkl

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

  • From power query editor, remove errors from the table
  • from power query editor, select table 1 & then select Remove Duplicates
  • from Power query editor, merge table1 and table2
  • from power BI desktop, import the data from Excel & select Table1 and Table2
  • from power query editor, append Table2 to Table1
A
  • from Power query editor, merge table1 and table2
  • From power query editor, remove errors from the table
  • from power query editor, select table 1 & then select Remove Duplicates
66
Q

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

A. Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.

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

C. Split the Logged column by using at as the delimiter.

D. Apply a transformation to extract the first 11 characters of the Logged column.

A

D. Apply a transformation to extract the first 11 characters of the Logged column.

CSV files have no data types.

67
Q

You have a Microsoft Excel file in a Microsoft OneDrive folder.
The file must be imported to a Power BI dataset.
You need to ensure that the dataset can be refreshed in powerbi.com.
Which two connectors can you use to connect to the file? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. Excel Workbook
B. Text/CSV
C. Folder
D. SharePoint folder
E. Web

A

A. Excel Workbook
C. Folder

68
Q

You are profiling data by using Power Query Editor.
You have a table named Reports that contains a column named State. The distribution and quality data metrics for the data in State is shown in the following exhibit.

State
69 distinct
4 unique

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

There are [answer choice] different valuesin State including nulls
4
65
69
73

There are [answer choice] non-null values that occur only once in State
4
65
69
73

A

There are 69 different values in State including nulls

There are 4 non-null values that occur only once in State

69
Q

You have two CSV files named Products and Categories.
The Products file contains the following columns:
✑ ProductID
✑ ProductName
✑ SupplierID
✑ CategoryID
The Categories file contains the following columns:
✑ CategoryID
✑ CategoryName
✑ CategoryDescription
From Power BI Desktop, you import the files into Power Query Editor.
You need to create a Power BI dataset that will contain a single table named Product. The Product will table includes the following columns:
✑ ProductID
✑ ProductName
✑ SupplierID
✑ CategoryID
✑ CategoryName
✑ CategoryDescription
How should you combine the queries, and what should you do on the Categories query?

Combine the queries by performing a:
* append
* merge
* transpose

On the queries category:
* delete the query
* disable the query load
* exclude the query from report refresh

A

Combine the queries by performing a merge

On the queries category disable the query load

70
Q

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

A. Add a SQL statement.
B. Set the Command timeout in minutes setting.
C. Set Data Connectivity mode to Import.
D. Set Data Connectivity mode to DirectQuery.

A

D. Set Data Connectivity mode to DirectQuery.

71
Q

You have a folder that contains 100 CSV files.
You need to make the file metadata available as a single dataset by using Power BI. The solution must NOT store the data of the CSV files.
Which three actions should you perform in sequence. To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:

  • from power BI desktop, select Get Data, and then select Folder
  • from power query editor, expand the Attributes column
  • from power query editor, remove the Content column
  • from power query editor, remove the Attributes column
  • from Power BI desktop, select Get Data, and then select Text/CSV
  • from power query editor, combine the Content column
A
  • from power BI desktop, select Get Data, and then select Folder
  • from power query editor, expand the Attributes column
  • from power query editor, combine the Content column
72
Q

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

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

A

C. Create a dataflows connection that has Import mode selected and schedule a daily refresh.

C, because one of the requirements is ‘Minimize online processing operations’. Although the dataflow uses DirectQuery, the Dataset can be refreshed with Import

73
Q

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

The dataset must be refreshed daily.

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

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

  • Add the dataset owner to the data source
  • configure an on-premises data gateway
  • configure a virtual network data gateway
  • add a data source
  • configure a scheduled refresh
A
  1. configure an on-premises data gateway
  2. add a data source
  3. add the dataset owner to the data source
  4. configure a scheduled refresh
74
Q

You attempt to connect Power BI Desktop to a Cassandra database.

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

You need to select an alternate data connector that will connect to the database.

Which type of connector should you choose?

A. Microsoft SQL Server database
B. ODBC
C. OLE DB
D. OData

A

B. ODBC

The ODBC connector lets you import data from any third-party ODBC driver simply by specifying a Data Source Name (DSN) or a connection string. As an option, you can also specify a SQL statement to execute against the ODBC driver.

75
Q

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

  • Visualizes the Sales value over a period of years and months
  • Adds a slicer for the month
  • Adds a slicer for the year

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

  • select the Month and MonthNumber columns
  • select Unpivot other columns
  • rename the Attribute column as Year and the Value column as Sales
  • select the 2019, 2020 and 2021 columns
  • select transpose
A
  1. Select the Month and MonthNumber columns
  2. Select Unpivot other columns
  3. Rename the Attribute column as Year and the Value column as Sales
76
Q

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

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

The default timeout for the connection from Power BI Desktop to the Database will be:
* unlimited
* one minute
* 10 minutes

The navigator will display:
* all the tables
* only tables that contain data
* only tables that contain hierarchies

A

The default timeout for the connection from Power BI Desktop to the Database will be 10 minutes

The navigator will display only tables that contain data

77
Q

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

  • Data must be consumed from the database that corresponds to each stage of the development lifecycle.
  • Power BI deployment pipelines must NOT be used.
  • The solution must minimize administrative effort.

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

Create:
One parameter
Two parameters
Three parameters

Parameter type:
Text
True/False
Decimal number

A

Create one parameter

Parameter type text

78
Q

You are creating a query to be used as a Country dimension in a star schema.

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

Country City
USA Seattle
USA New York
USA Denver
UK Manchester
UK London
Japan Toyko
Brazil Rio
Brazil Sao Paulo

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

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

NOTE: Each correct selection is worth one point.

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

A

D. Delete the City column.

You are creating a query to be used as a Country dimension in a star schema.

79
Q

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

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

  • Select the discount column
  • Select the price column
  • For the discount column, change Data Type to Decimal Number
  • For the discount column, change Data Type to Whole Number
  • Select Replace Numbers to replace each error value with 0.05
A
  1. select the discount column
  2. select replace numbers to replace each error value with 0.05
  3. for the discount column, change Data Type to Decimal Number
80
Q

You attempt to use Power Query Editor to create a custom column and receive the error message shown in the following exhibit.

Expression.Error: We cannot apply operator & to types Text and Number.
Details:
Operator=&
Left=A
Right=1

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

The error is caused by:
* error values in the source data
* mismatched data types
* NULL values

The desired outcome of the custom column is:
* 1A
* A&1
* A1

A

The error is caused by mimatched data types

The desired outcome of the custom column is A1

81
Q

From Power Query Editor, you attempt to execute a query and receive the following error message.

Datasource.Error: Could not find file.

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

A. You do not have permissions to the file.
B. An incorrect privacy level was used for the data source.
C. The file is locked.
D. The referenced file was moved to a new location.

A

A. You do not have permissions to the file.

D. The referenced file was moved to a new location.

82
Q

You need to use Power Query to clean and transform the dataset. The solution must meet the following requirements:

  • If the discount column returns an error, a discount of 0.05 must be used.
  • All the rows of data must be maintained.
  • Administrative effort must be minimized.

What should you do in Power Query Editor?

A. Select Replace Errors.
B. Edit the query in the Query Errors group.
C. Select Remove Errors.
D. Select Keep Errors.

A

A. Select Replace Errors.

83
Q

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

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

What should you do?

A. Apply the Parse function from the Data transformations options to the Logged column.
B. Change the data type of the Logged column to Date.
C. Split the Logged column by using at as the delimiter.
D. Create a column by example that starts with 2018-12-31.

A

C. Split the Logged column by using at as the delimiter.

84
Q

You have two Microsoft Excel workbooks in a Microsoft OneDrive folder.

Each workbook contains a table named Sales. The tables have the same data structure in both workbooks.

You plan to use Power BI to combine both Sales tables into a single table and create visuals based on the data in the table. The solution must ensure that you can publish a separate report and dataset.

Which storage mode should you use for the report file and the dataset file? To answer, drag the appropriate modes to the correct files.

Storage modes:
DirectQuery/import/LiveConnect/Push

A

Report file: Import storage mode
Dataset file: DirectQuery storage mode

Report file: Import: In Power BI, when you import data, it means that the data is loaded into the Power BI Desktop file. In this case, you would import the data from both Excel workbooks into your Power BI Desktop report file. This allows you to create visuals and reports based on the imported data. Importing the data ensures that you can work with the data even when you’re not connected to OneDrive.

Dataset: DirectQuery: To keep the data in OneDrive and maintain a live connection to the source, you should use DirectQuery for the dataset. DirectQuery allows Power BI to retrieve and query data from the original data source (in this case, the Excel workbooks in OneDrive) in real-time without importing it into the dataset. This ensures that your dataset is always up-to-date and reflects changes made to the source data.

85
Q

You use Power Query to import two tables named Order Header and Order Details from an Azure SQL database. The Order Header table relates to the Order Details table by using a column named Order ID in each table.

You need to combine the tables into a single query that contains the unique columns of each table.

What should you select in Power Query Editor?

A. Merge queries
B. Combine files
C. Append queries

A

A. Merge queries

86
Q

You are creating a report in Power BI Desktop.
You load a data extract that includes a free text field named coll.
You need to analyze the frequency distribution of the string lengths in col1. The solution must not affect the size of the model.
What should you do?
A. In the report, add a DAX calculated column that calculates the length of col1
B. In the report, add a DAX function that calculates the average length of col1
C. From Power Query Editor, add a column that calculates the length of col1
D. From Power Query Editor, change the distribution for the Column profile to group by length for col1

A

A. In the report, add a DAX calculated column that calculates the length of col1

The LEN DAX function returns the number of characters in a text string.

87
Q

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

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

A

A. Publish the reports in an app and grant the HR managers access permission.

88
Q

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

A. Viewer
B. Admin
C. Contributor
D. Member

A

D. Member

89
Q

Users only use the date part of the Sales_Date field. Only rows with a Status of Finished are used in analysis.
You need to reduce the load times of the query without affecting the analysis.
Which two actions achieve this goal? Each correct answer presents a complete solution.

A. Remove the rows in which Sales[Status] has a value of Canceled.
B. Remove Sales[Sales_Date].
C. Change the data type of Sale[Delivery_Time] to Integer.
D. Split Sales[Sale_Date] into separate date and time columns.
E. Remove Sales[Canceled Date].

A

A. Remove the rows in which Sales[Status] has a value of Canceled.
Users only use the date part of the Sales_Date field. Only rows with a Status of Finished are used in analysis.

90
Q

You import the tables.
Which relationship should you use to link the tables?
A. one-to-many from Transaction to Customer
B. one-to-one between Customer and Transaction
C. many-to-many between Customer and Transaction
D. one-to-many from Customer to Transaction

A

D. one-to-many from Customer to Transaction

91
Q

You have a custom connector that returns ID, From, To, Subject, Body, and Has Attachments for every email sent during the past year. More than 10 million records are returned.
You build a report analyzing the internal networks of employees based on whom they send emails to.
You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size.
What should you do?

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

A

B. Remove the Subject and Body columns during the import

Removing the Subject and Body columns during the import process ensures that they are not included in the model, which prevents report recipients from being able to access the analyzed email content.

92
Q

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

A. Create a single role that filters Country[Manager_Email] by using the USERNAME DAX function.

B. Create a single role that filters Country[Manager_Email] by using the USEROBJECTID DAX function.

C. For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions.

D. Create one role for each country.

E. For the relationship between Purchase and Purchase Detail, change the Cross filter direction to Single.

A

A. Create a single role that filters Country[Manager_Email] by using the USERNAME DAX function.

C. For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions.

93
Q

Changing the … setting of the relationships will improve query performance:
* cardinality
* cross filter direction
* assume referential integrity

A

assume referential integrity

When connecting to a data source using DirectQuery, you can use the Assume Referential Integrity selection to enable running more efficient queries against your data source. This feature has a few requirements of the underlying data, and it is only available when using DirectQuery.

94
Q

You have a Power BI model that contains a table named Sales and a related date table. Sales contains a measure named Total Sales.
You need to create a measure that calculates the total sales from the equivalent month of the previous year.
How should you complete the calculation? To answer, select the appropriate options in the answer area.

Sales Previous Year =
CALCULATE/EVALUATE/SUM/SUMX

[Total Sales],
DATESMTD/PARALLELPERIOD/SAMEPERIODLASTYEAR/TOTALMTD (

			 [Date]/'Date'[Date]/'Date'[Month]
			 ) )
A

Sales Previous Year =
CALCULATE

[Total Sales],
PARALLELPERIOD (

			 'Date'[Month]
			 ) )
95
Q

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

Publish the report
Assign users to the role
Add a filter to the report
Create a role definition
Import the data to Power BI Desktop

A

Import the data to Power BI Desktop
Create a role definition
Assign users to the role
Publish the report

96
Q

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

  • merge [Region_Manager] and [Manager] by using an inner join
  • merge [Sales_Manager] and [Sales_Region] by using a left join
  • merge [Sales_Region] and [Sales_Manager] by using an inner join
  • merge [Sales_Region] and [Sales_Manager] by using an inner join as a new query named [Sales_Region_and_Region_Manager]
  • merge [Sales_Region] and [Region_Manager] by using a right join as a new query named [Sales_Region_and_Region_Manager]
  • merge [Sales_Region] and [Region_Manager] by using an inner join
A
  • merge [Sales_Region] and [Sales_Manager] by using an inner join
  • merge [Region_Manager] and [Manager] by using an inner join
  • merge [Sales_Region] and [Region_Manager] by using a right join as a new query named [Sales_Region_and_Region_Manager]
97
Q

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

A. Change any DAX measures to use iterator functions.
B. Enable visual interactions.
C. Replace the default visuals with AppSource visuals.
D. Split the visuals onto multiple pages.

A

D. Split the visuals onto multiple pages.

98
Q

You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: From Power Query Editor, you import the table and then add a filter step to the query.
Does this meet the goal?

A

No

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

99
Q

You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: You write a DAX expression that uses the FILTER function.
Does this meet the goal?

A

No

Instead: You add a WHERE clause to the SQL statement.
Note: DAX is not a language designed to fetch the data like SQL rather than used for data analysis purposes. It is always a better and recommended approach to transform the data as close to the data source itself. For example, your data source is a relational database; then, it’s better to go with T-SQL.
SQL is a structured query language, whereas DAX is a formula language used for data analysis purposes. When our data is stored in some structured database systems like SQL server management studio, MySQL, or others, we have to use SQL to fetch the stored data.

100
Q

You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: You add a WHERE clause to the SQL statement.
Does this meet the goal?

A

Yes

101
Q

You need to prepare the data to support the following:
✑ Visualizations that include all measures in the data over time
✑ Year-over-year calculations for all the measures
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  • use headers as the first row
  • rename the Measure column as Year
  • rename the Attribute column as Year
  • use the first row as headers
  • transpose the table
  • unpivot all the columns other than the measure
  • change the data type of the Year column to Date
A
  • transpose the table
  • use the first row as headers
  • unpivot all the columns other than the measure
  • rename the Measure column as Year
102
Q

You are creating an analytics report that will consume data from the tables shown in the following table.

There is a relationship between the tables.
There are no reporting requirements on employee_id and employee_photo.
You need to optimize the data model.
What should you configure for employee_id and employee_photo? To answer, select the appropriate options in the answer area.

Employee_id:
* change type
* delete
* hide
* sort

Employee_photo:
* change type
* delete
* hide
* sort

A

Employee_id:
* hide

Employee_photo:
* delete

103
Q

You plan to create Power BI dataset to analyze attendance at a school. Data will come from two separate views named View1 and View2 in an Azure SQL database.

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

Teacher first name:
* attendance fact
* class dimension
* teacher dimension
* teacher fact

period number:
* attendance fact
* class dimension
* teacher dimension
* teacher fact

A

Teacher first name:
teacher fact

period number:
attendance fact

Fact tables store observations or events, and can be sales orders, stock balances, exchange rates, temperatures, etc. A fact table contains dimension key columns that relate to dimension tables, and numeric measure columns.

104
Q

There are four departments in the Departments table.
You need to ensure that users can see the data of their respective department only.
What should you do?
A. Create a slicer that filters Departments based on DepartmentID.
B. Create a row-level security (RLS) role for each department, and then define the membership of the role.
C. Create a DepartmentID parameter to filter the Departments table.
D. To the ConfidentialData table, add a calculated measure that uses the CURRENTGROUP DAX function.

A

B. Create a row-level security (RLS) role for each department, and then define the membership of the role.

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

105
Q

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

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

A

B. Create an active relationship between the tables and select Apply security filter in both directions.

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

106
Q

UnitsInStock has 75 non-null values, of which 51 are unique.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

When a table visual is created in a report and UnitsInStock is added to the values, there will be … in the table:
* 0 rows
* 1 row
* 51 rows
* 75 rows

Changing the Summarize by setting of the UnitsInStock column, and then adding the column to a table visual, will … the number of rows in the table visual:
* maintain
* reduce
* increase

A

When a table visual is created in a report and UnitsInStock is added to the values, there will be … in the table:
* 75 rows

Changing the Summarize by setting of the UnitsInStock column, and then adding the column to a table visual, will … the number of rows in the table visual:
* reduce

107
Q

You have a PowerBI report with the following tables:
* Balances: the table contains daily records of closing balances for every active bank account. The closing balances appear for every day the account is live, including the last day
* Date: the table contains a record per day for the calendar years of 2000 to 2025. There is a hierarchy for financial year, quarter, month and day.

You have the following DAX measure.
Accounts :=
CALCULATE (
DISTINCTCOUNT (Balances[AccountID]),
LASTDATE (‘Date’[Date])
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Answer yes/no for the following:

  • a table visual that displays the date hierarchy at the year level and the [Accounts] measure will show the total number of accounts that were live throughout the year
  • a table visual that displays the date hierarchy at the month level and the [Accounts] measure will show the total number of accounts that were live throughout the month
  • a table visual that displays the date hierarchy at the day level and the [Accounts] measure will show the total number of accounts that were live throughout the day
A
  • a table visual that displays the date hierarchy at the year level and the [Accounts] measure will show the total number of accounts that were live throughout the year
  • NO - It will show the total number of accounts that were live at the last day of the year only.
  • a table visual that displays the date hierarchy at the month level and the [Accounts] measure will show the total number of accounts that were live throughout the month
  • NO - It will show the total number of accounts that were live at the last day of the month only.
  • a table visual that displays the date hierarchy at the day level and the [Accounts] measure will show the total number of accounts that were live throughout the day
  • YES
108
Q

The Impressions table contains approximately 30 million records per month.
You need to create an ad analytics system to meet the following requirements:
✑ Present ad impression counts for the day, campaign, and site_name. The analytics for the last year are required.
Minimize the data model size.

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

A. Create one-to-many relationships between the tables.
B. Group the Impressions query in Power Query by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function.
C. Create a calculated table that contains Ad_id, Site_name, and Impression_date.
D. Create a calculated measure that aggregates by using the COUNTROWS function.

A

A. Create one-to-many relationships between the tables.
B. Group the Impressions query in Power Query by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function.

109
Q

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

Cardinality:
* one-to-many
* one-to-one
* many-to-many

cross-filter direction:
* single
* both

A

Cardinality:
** one-to-many **

cross-filter direction:
** single **

110
Q

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

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

A. Change Summarization for DateKey to Don’t Summarize.
B. Remove the relationship between Date and Product Inventory
C. Change the data type of UnitCost to Integer.
D. Remove MovementDate.

A

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

111
Q

You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: You add a report-level filter that filters based on the order date.
Does this meet the goal?

A

No

You want the raw data, not a report with the data.
Instead add a WHERE clause to the SQL statement.

112
Q

You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: For each date foreign key, you add inactive relationships between the sales table and the date table.
Does this meet the goal?

A. Yes
B. No

A

NO

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

113
Q

You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query twice to make the queries for Order Date and
Delivery Date.
Does this meet the goal?

A. Yes
B. No

A

No

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

114
Q

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables.
Does this meet the goal?

A. Yes
B. No

A

A

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

115
Q

You plan to create several visuals from the data, including a visual that shows revenue split by year and product.
You need to transform the data to ensure that you can build the visuals. The solution must ensure that the columns are named appropriately for the data that they contain.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  • select department and Product and Unpivot Columns
  • select Use First Row as Headers
  • select Department and Product and Unpivot Other Columns
  • rename the Attribute column to Year and the Value column to Revenue
  • select Use Header as First Row
  • rename the Attribute column to Revenue and the Value column to Year
A
  • select department and Product and Unpivot Columns
  • rename the Attribute column to Revenue and the Value column to Year
  • select Use First Row as Headers
116
Q

You have a Power BI report named Orders that supports the following analysis:
✑ Total sales over time
✑ The count of orders over time
✑ New and repeat customer counts
The data model size is nearing the limit for a dataset in shared capacity.
The model view for the dataset is shown in the following exhibit.
The data view for the Orders table is shown in the following exhibit.
The Orders table relates to the Customers table by using the CustomerID column.
The Orders table relates to the Date table by using the OrderDate column.
For each of the following statements, select Yes if the statement is true, Otherwise, select No.

  • Summarizing Orders by the CustomerID, OrderID, and OrderDate columns will reduce the model size while still supporting the current analysis
  • Removing the CustomerID column from Orders will reduce the model size while still supporting the current analysis
  • Removing the UnitPrice and Discount columns from Orders will reduce the model size while still supporting the current analysis
A
  • Summarizing Orders by the CustomerID, OrderID, and OrderDate columns will reduce the model size while still supporting the current analysis
    No
  • Removing the CustomerID column from Orders will reduce the model size while still supporting the current analysis
    No
  • Removing the UnitPrice and Discount columns from Orders will reduce the model size while still supporting the current analysis
    Yes
117
Q

You are building a financial report by using Power BI.
You have a table named financials that contains a column named Date and a column named Sales.
You need to create a measure that calculates the relative change in sales as compared to the previous quarter.
How should you complete the measure? To answer, select the appropriate options in the answer area.

Sales QoQ% =
IF(
ISFILTERED(‘financials’[Date]),
ERROR(“Uh Oh”),
VAR PREV_QUARTER =
CALCULATE/CALCULATETABLE/DATEADD/DIVIDE/FILTER/FIND
(SUM(‘financials’[Sales]),
CALCULATE/CALCULATETABLE/DATEADD/DIVIDE/FILTER/FIND
(‘financials’[Date].[Date], -1, QUARTER)
RETURN
CALCULATE/CALCULATETABLE/DATEADD/DIVIDE/FILTER/FIND
(SUM(‘financials’[Sales]) - PREV_QUARTER, PREV_QUARTER)

A
  1. CALCULATE
  2. DATEADD
  3. DIVIDE
118
Q

You are creating a Power BI model and report.
You have a single table in a data model named Product. Product contains the following fields:
✑ ID
✑ Name
✑ Color
✑ Category
✑ Total Sales
You need to create a calculated table that shows only the top eight products based on the highest value in Total Sales.
How should you complete the DAX expression?

Top 8 Products = …. (8, ‘Product’, ‘Product’[Total Sales], …..)

Options = ASC/DESC/RELATEDTABLE/CALCULATETABLE/MAXX/TOPN

A

Top 8 Products = TOPN (8, ‘Product’, ‘Product’[Total Sales], DESC)

TOPN returns the top N rows of the specified table

DESC - descending order to get the highest values first

119
Q

You are creating a sales report in Power BI for the NorthWest region sales territory of your company. Data will come from a view in a Microsoft SQL Server database.

The report will facilitate the following analysis:
✑ The count of orders and the sum of total sales by Order Date
✑ The count of customers who placed an order
✑ The average quantity per order
You need to reduce data refresh times and report query times.
Which two actions should you perform?

A. Set the data type for SalesOrderNumber to Decimal Number.
B. Remove the CustomerKey and ProductKey columns.
C. Remove the TaxAmt and Freight columns.
D. Filter the data to only the NorthWest region sales territory.

A

C. Remove the TaxAmt and Freight columns.
D. Filter the data to only the NorthWest region sales territory.

120
Q

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

Name Data Type
Store ID Whole Number
Store Name Text
City Text
State/Province Text
Country Text

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

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

A

C. Set the data category of City, State/Province, and Country.

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

121
Q

You are building a data model for a Power BI report.
You have data formatted as shown in the following table.

Machine-User DownloadMB
ABC-123 75
BAC-657 125

You need to create a clustered bar chart

What should you do?

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

A

A. From Power Query Editor, split the Machine-User column by using a delimiter.

122
Q

You need create a date table in Power BI that must contain 10 full calendar years, including the current year.
How should you complete the DAX expression?

Date =
var var1 = … (….())
return
… (
DATE(var1 -9, 01, 01),
DATE(var1, 12, 31)
)

OPTIONS
* CALENDAR
* CALENDARAUTO
* DATE
* EOMONTH
* TODAY
* YEAR

A

Date =
var var1 = YEAR(TODAY())
return
CALENDAR (
DATE(var1 -9, 01, 01),
DATE(var1, 12, 31)
)

123
Q

You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: You create measures that use the USERELATIONSHIP DAX function to filter sales on the active relationship between the sales table and the date table.
Does this meet the goal?

A

No

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

124
Q

You have a Power BI report that contains a measure named Total Sales.
You need to create a new measure that will return the sum of Total Sales for a year up to a selected date.
How should you complete the DAX expression?

Measure = TOTALYTD/CALCULATE/SUM/EVALUATE
([Total Sales],’Date’[Date]/TODAY()/EOMONTH(‘Date’[Date])/LASTDATE’Date’[Date])

A

Measure = TOTALYTD
([Total Sales],’Date’[Date])

125
Q

You are modifying a Power BI model by using Power BI Desktop.
You have a table named Sales that contains the following fields.

Name Data type
Transaction ID Whole Number
Customer Key Whole Number
Sales Date Key Date
Sales Amount Whole Number

You have a table named Transaction Size that contains the following data.

Transaction size ID | Transaction Size | Min | Max
1 | Small | 0 | 10,000
2 | Medium | 10,001 | 100,000
3 | Large | 100,001 | 999,999,999

You need to create a calculated column to classify each transaction as small, medium, or large based on the value in Sales Amount.
How should you complete the code?

Transaction Size =
VAR SalesTotal = ‘Sales’[Sales]
VAR FilterSegment =
….. (
‘Transaction Size’,
…..(
‘Transaction Size’[Min] <= SalesTotal,
‘Transaction Size’[Max] <= SalesTotal
)
)
VAR Result =
….(DISTINCT(‘Transaction Size’[Transaction Size]), FilterSegment)
RETURN
Result

OPTIONS
* ALL
* AND
* CALCULATE
* FILTER
* OR
* SUM

A

Transaction Size =
VAR SalesTotal = ‘Sales’[Sales]
VAR FilterSegment =
CALCULATE (
‘Transaction Size’,
AND(
‘Transaction Size’[Min] <= SalesTotal,
‘Transaction Size’[Max] <= SalesTotal
)
)
VAR Result =
FILTER(DISTINCT(‘Transaction Size’[Transaction Size]), FilterSegment)
RETURN
Result

126
Q

What is import?

a. data of that table will be stored in-memory storage of the powerBI server
b. keeps the data in the data source
c. can act as directquery or dual respective to the relationship to other tables

A

data of that table will be stored in-memory storage of the powerBI server

127
Q

What is Direct Query?

a. data of that table will be stored in-memory storage of the powerBI server
b. keeps the data in the data source
c. can act as import or dual respective to the relationship to other tables

A

keeps the data in the data source

128
Q

What is Dual?

a. data of that table will be stored in-memory storage of the powerBI server
b. keeps the data in the data source
c. can act as import or dual respective to the relationship to other tables

A

can act as direct query or import respective to the relationship to other tables

129
Q

You use Power BI Desktop to load data from a Microsoft SQL Server database.

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

You need to resolve the error.

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

A. Reduce the number of rows and columns returned by each query.
B. Split log running queries into subsets of columns and use Power Query to merge the queries.
C. Use Power Query to combine log running queries into one query.
D. Disable query folding on long running queries.

A

A. Reduce the number of rows and columns returned by each query.
B. Split log running queries into subsets of columns and use Power Query to merge the queries.

130
Q
A

PowerBI (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions