power bi questions

Question 1:

You need to ensure that users can effectively and efficiently navigate your reports using only the keyboard. What feature should you set up?

•

Filter Order

•

Sort Order

Layer Order

•

Tab Order

(Correct)

Explanation

Tab order determines the order in which users switch between visuals when using a keyboard

Question 2:

Your company uses Active Directory security groups to manage access. You have sales managers assigned to four different regions and each manager can only see their sales in Power BI Service. You need to ensure that when a manager changes to a different region, they can see the correct sales data. What should you do?

•

Change the workspace permissions to Viewer

•

Assign the manager to the correct Active Directory group

(Correct)

•

Remove Edit access of the underlying dataset

•

Change the RLS policy in Power BI Desktop

Explanation

Using Active Directory group, you no longer need to maintain lists of individual users

Question 3: Skipped

You've just built your first report that contains 10 AppSource visuals and 10 default visuals on a single page. You're excited to share it with you team and have published it to Power BI Service so you can share it with them. However, your team let's you know that the report visuals load really slowly. Which of the following will help improve the performance of the report?

•

Implement RLS roles

•

Change the dataset storage from Import to DirectQuery

•

Increase the number of times the report is refreshed on Service

•

Split visuals onto multiple pages

(Correct)

Explanation

Too many visuals on a single page (more than 8) can start to slow report performance and interaction

Question 4: Skipped

You set up the Power BI data model to help users ask natural language questions using the Q&A feature. What tool(s) can you use to accomplish this?

•

Send a list of approved Q&A terms to users

•

Use the Q&A tooling interface

(Correct)

•

Add commonly used synonyms to the tables

(Correct)

•

Add a description into the tool tip of the visual

Explanation

Adding synonyms to tables from the data model view and using the Q&A tooling interface to manage Q&A are both best practices

Question 5: Skipped

You need to grant access to a user so he can publish reports to a workspace and delete dashboards while using the principle of least privilege. Which role should you assign him?

•

Admin

•

Viewer

•

Member

•

Contributor

(Correct)

Explanation

Contributors can create, edit, and delete content in a workspace and publish reports to the workspace

Question 6: Skipped

You've built a line chart that shows revenue by month. Your manager has asked you to show mean monthly revenue on the chart. You need to add the value in a way that considers users adding other filters and involve minimal effort. What do you do?

•

Add a constant line at 50% from the Analytics pane

•

Create a measure that calculates average revenue

•

Add an Average line from the Analytics pane

(Correct)

•

Add a Median line from the Analytics pane

Explanation

Adding an average line from the analytics pane is the only option that satisfies both requirements of considering other users adding filters and involving minimal effort. You could create a DAX measure to calculate the average but it accomplishes the same thing as an average line from the analytics pane and requires more effort.

Question 7: Skipped

You build a report to help the sales team perform ad hoc exploration. What type of visual do you add to the report?

•

Q&A

•

Key Influencers

•

Decomposition Tree

(Correct)

•

Smart Narrative

Explanation

Decomposition trees allow you to perform exploratory and root cause analysis

Question 8: Skipped

You need to create an easy way for users to filter values across multiple report pages, based on a single selection. What are two possible ways to achieve this goal?

•

Bookmarks

•

Define RLS roles

•

Sync slicers across pages

(Correct)

•

Report-level filters

(Correct)

Explanation

Using report-level filters & syncing slicers across report pages, allows users to select a single value to filter the results on all pages

Question 9: Skipped

You have a dataset that contains the following attributes for 2,500 authors: First Name, Last Name, State, and Region. You import the dataset into the Query Editor and want to confirm Region is populated for each author. Which two actions should you perform?

•

Enable column profiling for the entire dataset

(Correct)

•

Enable column profile

(Correct)

•

Enable column distribution

•

Enable column quality

Explanation

In order to confirm region is populated for every author, you need to use the column profile tool to understand the distribution for the region column. Additionally, the default Power Query profile is only done on the first 1,000 rows. You need to enable column profiling based on the entire dataset to see all 2,500 authors.

Question 10: Skipped

You create a report page that includes two visuals: a donut chart showing profit by category, and a bar chart showing profit by subcategory. When a user selects a category or subcategory, the other chart should show only the corresponding profit without showing overall profit. How do you set the interactions between visuals?

•

Drill through

•

Filter

(Correct)

•

Highlight

•

None

Explanation

Filter interactions filters other charts

Question 11: Skipped

You need to create a calculated table that returns the 100 highest spending customers. Which DAX expression should you use to create the table?

•

FILTER

•

TOPN

(Correct)

•

ALLEXCEPT

•

CALCULATETABLE

Explanation

TOPN can be used to create both tables and measures and will return a subset of customers based on the N_Value

Question 12: Skipped

You've created a map visual that shows revenue by country. When you hover over a country you also want to see profit and margin. How do you accomplish this?

•

Add profit and margin as visual-level filters

•

Add profit and margin to Tooltips

(Correct)

•

Add profit and margin as drill-through filters

•

Add profit and margin to explain by field

Explanation

Tooltips allow you to add additional context to a visual that appears when you hover over a data point

Question 13: Skipped

You have a Power BI Dashboard that contains a KPI card for daily percentage of undeliverable orders and want to be notified when the percentage of undeliverable orders exceeds 5%. What should you create?

•

A Q&A visual

•

A data alert

(Correct)

•

A smart narrative visual

•

A subscription

Explanation

Data alerts let you trigger a notification when a threshold is crossed

Question 14: Skipped

You're developing a report in Power BI Desktop and connect to a Microsoft SQL Server table that contains more than 500 million records. Which of the following allows you to import a sample of the data?

•

Add a report-level filter

•

Add a WHERE clause to the query SQL statement

(Correct)

•

Adjust the Microsoft SQL Server connection timeout settings

•

Filter the table using the DAX FILTER function

Explanation

Adding a WHERE clause to your SQL query will limit the data based on the filters in the clause

Question 15: Skipped

Which of the following DAX functions is not a CALCULATE modifier?

•

ALLSELECTED

•

ALL

•

AND

(Correct)

•

ALLEXCEPT

Explanation

AND is the only function listed that is not a CALCULATE modifier

Question 16: Skipped

You create a report built from an SSAS Tabular live connection, publish it to Power BI Service, and need to make sure the dataset is updated every day. What is required in order to refresh the report everyday?

•

Large Dataset Storage Format enabled

•

RangeStart and RangeEnd parameters

•

A Data Gateway

(Correct)

•

An Incremental Refresh policy

Explanation

On-premises data sources, like SSAS Tabular live connections, require a data gateway to refresh

Question 17: Skipped

You import the below datasets into Power Query, where Customer and Address contain unique values. What should you do to create a query that has one row per customer and contains city, state, and region?

Larger image

•

Merge the tables

(Correct)

•

Append the tables

•

Relate the tables

•

Combine the tables

Explanation

There are two primary ways of combining queries: merging and appending. When you have one or more columns that you'd like to add to another query, you merge the queries.

Question 18: Skipped

You need to add a reference line to a clustered bar chart to show which employees are above the median salary. How can you accomplish this?

•

Add a constant line

•

Add a mean line

•

Add a percentile line at 50%

(Correct)

•

Create a measure that calculates average salary

Explanation

The 50th percentile is also known as the median or middle value

Question 19: Skipped

You connect to a new MySQL database table that contains 500+ million rows and loading the entire table will likely take more than 60 minutes. You want to load a sample of the data to confirm it's what you need. How can you achieve this goal while minimizing loading time?

•

Set the storage mode to dual

•

Write a SQL statement that filters the table prior to loading

(Correct)

•

Adjust the MySQL connection timeout settings

•

Use report-level filters to limit the data

Explanation

Writing a SQL statement to filter the rows prior to loading is the only option that will minimize data load time.

Question 20: Skipped

Which data preview option helps you understand the percentage of empty values in a column?

•

Show whitespace

•

Column quality

(Correct)

•

Column distribution

•

Column profile

Explanation

Column quality shows the percentage of values associated with valid, error, and empty based on a selected table or column

Question 21: Skipped

You have data in a star schema that contains 3 lookup tables (Date, Region, Product) and one fact table (Sales). The fact table contains purchase and ships dates. You need to design a model that supports analyzing data in the fact table by both purchase and ship date and minimizes model size. What data modeling design should you use?

•

Duplicate the date table and create one-to-many relationships

•

Use the auto Date/Time functionality in Power BI and do not import the date table

•

Create many-to-one relationships between the date table and purchase date and another many-to-one relationship between date and ship date

•

Create one-to-many relationships between the date table and purchase date and another one-to-many relationship between date and ship date

(Correct)

Explanation

Creating two one-to-many relationships (one of them inactive) between the Sales table and the Date table doesn't require you to increase the model size and still allows you to analyze the data by both purchase and ship date.

Question 22: Skipped

You have a data connection that returns more than 50 million records and contains columns for sales, product id, date, product description, product name, sales person name, and sales region. You build a report to analyze the sales data but need to prevent report recipients from seeing sales person name and region. The solution must minimize the data model size. What should you do?

•

Implement row-level security roles

•

Set the sales person and region columns to hidden in the Model view

•

Remove the sales person name and region columns on import

(Correct)

•

Apply a visual-level filter that removes sales person and region from the visual

Explanation

Removing the sales person name and region columns on import is the only option that reduces the size of the data model.

Question 23: Skipped

You have marketing data contained in the tables shown below that's at the daily level. The Search data table contains 3 years of data with approximately 20 million records per month. You need to create a year-over-year visual to show the count of impressions by day, campaign name, and ad name that minimizes the data model size. What two things should you do?

Larger image

•

From the Data view, create a new group to summarize the impression count by campaign, ad, and day

•

Create relationships between the tables

(Correct)

•

Create a new calculated table using the SUMMARIZECOLUMNS function to return a summary table by campaign, ad, and day

•

Use the Query Editor to group by the impressions by campaign name, ad name, and impression date and aggregate by using the Sum function

(Correct)

Explanation

In order to minimize the data model size and build out a year-over-year visual, you first need to reduce the size of the dataset before loading it. Using the group by function in the Query Editor is the best approach here. Next, in order to create a visual, you need to create relationships between the tables.

Question 24: Skipped

When building a dataset from a JSON file, which 3 steps should you take?

•

Promote first row to header

•

Set data types

(Correct)

•

Convert list to a table

(Correct)

•

Expand the columns

(Correct)

Explanation

The three steps that need to happen in order to transform a JSON file to a usable data are first convert the list to a table, second expand the columns, and third set the column data types

Question 25: Skipped

You're building a report from a table named Development in a MySQL database. The database contains two other tables: Test and Production. You need to configure the data source settings in a way that allows you to easily change between the three tables. What should you do?

•

Create a parameter for the source location

(Correct)

•

Set query dependencies to "toggle connection"

•

Create a query for each database and hide tables not in use

•

Use the Table.ReplaceValue M function

Explanation

Using parameters allows you to easily switch between different tables and schemas in a data source or database

Question 26: Skipped

You need to add user with the ability to add new Members to a workspace. The solution must adhere to the principle of least privilege. Which role should you assign him?

•

Member

(Correct)

•

Viewer

•

Contributor

•

Admin

Explanation

Members can assign member roles and below

Question 27: Skipped

You're working with a dataset that comes from Azure Blob storage that contains data for the past 10 years. In an effort to make the dataset refresh more efficient, you set up incremental refresh but notice that the refresh always takes the same amount of time. It appears that incremental refresh isn't working, what could be the reason?

•

RangeStart and RangeEnd parameters aren't set up

•

Azure Blob storage doesn't support query folding

(Correct)

•

A data gateway isn't installed

•

You forgot to publish to Power BI Service

Explanation

Azure Blob storage queries don't support query folding and therefore cannot take advantage of incremental refresh

Question 28: Skipped

Which of the following options filters visual contained on all pages?

•

Drill-through filters

•

Report-level filters

(Correct)

•

Visual-level filters

•

Page-level filters

Explanation

Report-level filters are applied to all pages of a report

Question 29: Skipped

You import a large dataset into the Query Editor (1 million rows) that contains a column with unique Date/Time values. What can you do to reduce the size of the data model?

•

Add a new column where you keep the date only

•

Round the time component down to the nearest hour

•

Change the data type of the column to text

•

Split the column into two columns, one that contains only date and the other only time

(Correct)

Explanation

In a large dataset (1 million + rows) with a date/time column that contains unique values, the cardinality of that column is very large and leads to performance issues. Splitting out the time component from the date helps reduce the model size

Question 30: Skipped

You're an Admin of a workspace with a Premium Per User license. You want to invite other users to collaborate with you on reports and would not like for them to be able to update reports in the app or invite others to the workspace. Which role should you assign?

•

Member

•

Admin

•

Contributor

(Correct)

•

Viewer

Explanation

Contributors can update reports in a workspace but not update an app. Additionally, they cannot add other users to a workspace

Question 31: Skipped

You create a visual that shows the number of employees in an office location over time and want to see the Total Salary for that location when you hover over the data point. How can you achieve this goal?

•

Add Total Salary to the tooltips

(Correct)

•

Add Total Salary to the drill through filter

•

Add Total Salary to the page-level filter

•

Add Total Salary to the report-level filter

Explanation

Tooltips allow you to add additional context to a visual that appears when you hover over a data point

Question 32: Skipped

You have the following table and need to transform it so you can visualize all measures in the data over time and year-over-year calculations. Which four actions should you perform in sequence?

Larger image

•

1. Promote first row to headers 2. Rename the column attribute to year 3. Unpivot all the columns other than Measure 4. Change the datatype of year to date

•

1. Promote first row to headers 2. Change the datatype of year to date 3. Unpivot all the columns other than Measure 4. Rename the column attribute to year

•

1. Promote first row to headers 2. Unpivot all the columns other than Measure 3. Rename the column attribute to year 4. Change the datatype of year to date

(Correct)

•

1. Promote first row to headers 2. Rename the column attribute to year 3. Change the datatype of year to date 4. Unpivot all the columns other than Measure

Explanation

In order to transform the visual and make it useful for analysis we need to promote the headers first, then unpivot all columns except for the "Measure" column. After the unpivot, we'll need to rename the column attribute column to year and then update the datatype.

Question 33: Skipped

You're an analyst supporting the Chief Security Officer for a national sporting good retailer that uses an Azure SQL database to store retail transactions. You need to create a dashboard so the loss prevention team can identify possible fraudulent transactions within 5 minutes of a sale. How should you configure the data connection?

•

Set the data connectivity mode to Import

•

Set the data connectivity mode to DirectQuery

(Correct)

•

Set the data connectivity mode to Dual

•

Adjust the SQL query timeout settings

Explanation

When querying relational data sources, like Azure SQL databases, DirectQuery in Power BI queries the underlying data source so you're always viewing current data

Question 34: Skipped

You have two tables in a data model, one called Clients and the other called Schedule, which are related via a Client_ID column. There are many instances of Client_ID in the Schedule table, who should you create the relationship to connect these tables?

•

Many-to-many from Schedule to Clients

•

One-to-many from Schedule to Clients

•

Many-to-one from Clients to Schedule

•

One-to-many from Clients to Schedule

(Correct)

Explanation

Each client_id can have many rows in the schedule table

Question 35: Skipped

Your company has a series of training videos on a website they want to show directly in a Power BI dashboard. What type of tile should you add?

•

Web Content

(Correct)

•

Video

•

Text Box

•

Custom Streaming Data

Explanation

The web content tile allows you to add and customize any HTML content, like websites. Web content tiles preserve the functionality of the page so you can access other videos on the site. The video tile only allows you to post a single video

Question 36: Skipped

You create a matrix visual that shows Sales by Customer Name and want to dynamically filter the visual to only show the top 20 customers. What should you do?

•

Use the basic filter options to manually select the top 20 customers

•

Created a calculated column in the Sales table for top 10 sales and add it to the visual

•

Use the RANKX function to build the Sales measure

•

Add a Top N filter to the visual

(Correct)

Explanation

Top N visual-level filters are used to only display the top performing records

Question 37: Skipped

The director of your teams asks you to create a single visual that explains the factors that contribute to sales volume the most. Which visualization should you choose?

•

Funnel Chart

•

100% Stacked Column

•

Decomposition Tree

•

Key Influencers

(Correct)

Explanation

The key influencers visual shows the most important factors based on the fields provided

Question 38: Skipped

You're a Power BI consultant and have just been hired by a company to help improve their Power BI reports. You take a look at the workbook and notice many complex DAX expressions along with frequent use of RELATED and RELATEDTABLE. You need to recommend a solution that will minimize the use of RELATED and RELATEDTABLE. What do you recommend?

•

Append the tables

•

Hide unused columns

•

Transpose the tables

•

Merge the tables

(Correct)

Explanation

Combining the data using merge will cut down on the use of functions like RELATED and RELATEDTABLE that return a related value from another table

Question 39: Skipped

You create a dashboard in Power BI Service. What's the best way for users in your organization to receive daily notifications about the number shown on a card visual?

•

Publish an app

•

Tag the user in a comment

•

Create a data alert

•

Create a subscription

(Correct)

Explanation

Subscriptions allow you to send daily emails with snapshots of metrics and visuals

Question 40: Skipped

You assign a user to multiple row-level security roles, "Sales Region = North" and "Year = 2021". Because the user is a member of both roles, what data will they be able to see in the report?

•

Only data where the Year = 2021

•

Data where the Sales Region is North or the Year is 2021

(Correct)

•

No data. An individual user cannot belong wo multiple security groups

•

Only data where the Sales Region = North

Explanation

When a user is assigned to multiple roles, the RLS filters become additive and create a union between the filters

Question 41: Skipped

You need to create a visual that compares revenue and cost over time. What type of visual should you use?

•

Stacked area chart

•

Line chart

(Correct)

•

Box plot

•

Waterfall chart

Explanation

Line charts are used to visualize change over time

Question 42: Skipped

You have a dataset that contains approximately 10,000 rows and need to identify outliers in the table. What visual should you use?

•

Area chart

•

Map visual

•

Donut chart

•

Scatter plot

(Correct)

Explanation

Scatter plots and box plots help identify data points that are outside of an expected distribution

Question 43: Skipped

You're prioritizing report development updates and need to understand how making changes to a dataset will influence other downstream reports and dashboards. What tool should you use?

•

Quick Insights

•

Deployment Pipelines

•

Key Influencers

•

Impact Analysis

(Correct)

Explanation

Impact analysis provides you the tools to understand how making changes to a dataset will impact downstream reports and dashboards

Question 44: Skipped

Your organization needs to separate development, test, and production environments to better optimize your application lifecycle management. What tool makes this possible?

•

Apps

•

Workspaces

•

Deployment Pipelines

(Correct)

•

Goal Setting

Explanation

Deployment pipelines allow you to manage the lifecycle of your organizations content

Question 45: Skipped

You need to create a visualization that shows historic performance and also predicts trends in the next 3 months. What type of visual should you use?

•

Ribbon chart

•

Decomposition Tree

•

Line chart

(Correct)

•

Key Influencers

Explanation

Line charts, with a single line, have a forecasting analytics option that can be enabled

Question 46: Skipped

You create multiple dashboards in Power BI Service and need to make sure users can see which dashboards contain Personally Identifiable Information (PII). What should you use?

•

Data Labels

•

Sensitivity labels

(Correct)

•

Comments

•

Active Directory Groups

Explanation

Sensitivity labels provide a simple way for users to classify content in Power BI Service

Question 47: Skipped

You add an R visual to Power BI to visualize 250,000 customer from your Customer Lookup table but can only see the first 150,000? What is the cause of the issue?

•

The dataset isn't normalized

•

The data comes from SSAS Tabular which can't be visualized with R

•

The proper R libraries are not installed

•

Too many records are being sent to the R visual

(Correct)

Explanation

R visuals can only accommodate a maximum of 150,000 records

Question 48: Skipped

Which of the following DAX functions lets you use an inactive relationship for the duration of the calculation?

•

USERELATIONSHIP

(Correct)

•

CROSSFILTER

•

TREATAS

•

RELATEDTABLE

Explanation

USERELATIONSHIP let's you traverse inactive relationships in your data model

Question 49: Skipped

You have a CSV file that contains information about customer feedback that contains a column called "Complaint Time" and is in the following format: 2020-03-15 at 09:34. You need to be able to analyze the customer feedback by date and use a built-in date hierarchy. What should you do?

•

Create a conditional column that transforms the column and leaves the first 4 digits and then set the data type to Whole Number

•

Update the data type of the "Complaint Time" column to Date

•

Create a column by example that starts with 2020-03-15 and set the new column data type to Date

(Correct)

•

Load the CSV into the data model and change the format of the column in the Data view

Explanation

Column by example will allow you to change the data type from Text to Date. In order to use a built-in date hierarchy, you need to set the data type of the column to Date

Question 50: Skipped

You need to update product IDs in a column by replacing 6275 with 6276 and write the following M code as an applied step: =Table.ReplaceValue(#Source,Products,"6275","6276",Replacer.ReplaceText,{"product_id"}) What is the output of this step where a row has a value of "pid_186275" ?

•

6276

•

pid_186275

•

pid_186276

(Correct)

•

6275

Explanation

The Table.ReplaceValue function replaces and old value with a new value in a specified column, product_id in this example.