Practice Exam Questions

You have the following Power Query M formula that generates a range of dates to build a calendar table. = List.Dates(#date(2020,05,31), 365, #duration(1,0,0,0)) What is the resulting date range?

A list of days starting on May 31, 2020 and ending 365 days later

You are editing a Power BI Desktop report visual that displays gross sales data with the hierarchy based on a year, quarter, month, and day. You need to prevent users from viewing the hierarchy based on the year. What should you do first?

In the Visualization pane, remove all date hierarchy fields from the Axis well.

In Power BI Desktop, you plan to use M-language to define a common date table spanning a period of 10 years. You need identify the M language function that would allow you to specify that rows in the table should represent consecutive days within the date range you designated. Your solution must minimize administrative effort. Which syntax should you use?

#duration

You need to add a new visual to a Power BI Dashboard. This visual does NOT exist on a report in the workspace. What should you do first?

Select Ask a question about your data.

You manage a Power BI workspace in shared capacity. The workspace contains a dataset named DS1. You plan to schedule refresh of DS1. What is the maximum number of data refreshes per day?

8

You have a fact table that contains sales data. The fact table includes a SalesDate column formatted as a Date data type. Auto date/time setting is disabled in both global and current file options. You load the fact table into Power BI Desktop. You need to ensure that you are able to analyze data on a yearly, quarterly, monthly, weekly, and daily basis. Your solution must minimize the model size and administrative effort. What should you do?

Add a separate date dimension table

You manage a Power BI workspace. You need to delegate the task to update workspace metadata. The solution must use the principle of least privilege. Which role should you use?

Admin

Your company has a SharePoint server located in a datacentre in Montreal. You plan to create a report in the Power BI service that will use Microsoft Excel files stored on the SharePoint server. You need to recommend a solution to ensure that the dataset for the report can automatically refresh daily. What should you include in the recommendation?

An on-premises data gateway

You plan to use Power BI Desktop to analyze sales data of your company. You need to identify two types of data you will be able to use for creating bins that group the sales data. Which two data types should you identify? Each correct answer presents a complete solution.

Answer: Date/Time & Numeric

You have Power BI Desktop. You need to create data for your model items. Data for which two model items can be created using the DAX language. Each correct answer presents a complete solution.

Calculated Table & Numeric range parameter

1. You plan to build a Power BI dashboard and set up alerts that will notify you when data presented in the visuals on the dashboard reach specific thresholds. Which three types of visuals support the alert functionality?

Card, Gauge, KPI

You plan to run Power BI Desktop Performance Analyzer. You need to ensure that the data engine cache will NOT impact the test results without restarting Power BI Desktop. What should you do first?

Connect DAX Studio to the data model.

You plan to run Power BI Desktop Performance Analyzer. You need to ensure that the data engine cache will NOT impact the test results without restarting Power BI Desktop. What should you do first?

Connect DAX Studio to the data model.

You have implemented role-based security that restricts access to a Power BI Desktop dataset for users who are assigned the role named Salespeople. You need to validate that the implementation works. What should you do?

In the Report view, activate the View as feature.

You plan to publish a dataset from Power BI Desktop. You need to ensure that a server name can be changed after the dataset has been published to the Power BI Service. Which two actions should you perform? Each correct answer presents part of the solution.

Create a parameter & From the Data source settings, update the server source to use a parameter.

You need to create a visual that will display information on a continuous axis. Which two data types can be used to display a continuous axis on a visual? Each correct answer presents a complete solution.

Date & Decimal Number

You have a Power BI Desktop model. You need to determine when to use implicit and explicit measures. What is a feature of an implicit measure that explicit measure does NOT have?

End-users can change the aggregation type of implicit measure from the Values well of a visual.

You have a Power BI Desktop model. You need to determine when to use implicit and explicit measures. What is a feature of an implicit measure that explicit measure does NOT have?

End-users can change the aggregation type of implicit measure from the Values well of a visual.

You need to create a custom R visual by using Power BI Desktop. What do you need to do first?

Install R on your computer

You have created a Power BI dataset and need to create multiple reports. What is a benefit of using a Power BI dataset as a data source for multiple reports compared to each report having a separate dataset?

It reduces the number of datasets, refreshes, and storage requirements in the Power BI service

You are creating a Power BI dashboard. You need to apply a custom theme to the dashboard from another Power BI dashboard theme. Which type of file should you use?

JSON

You are designing a data model in Power BI. You need to avoid introducing ambiguity into your data model design. Which type of cardinality should you avoid?

Many-to-many

You plan to certify a Power BI dataset. You need to identify at which level within your organization the permissions to certify a dataset are assigned. Which level should you identify?

Tenant Admin users who have permissions to certify a dataset are defined in the Dataset Certification tenant admin setting.

You plan to create a report in Power BI Desktop. You need to identify the type of visualization that displays data as a set of nested rectangles. Which visualization should you use?

Treemap

You need to assign an app workspace role for users who consume reports. Users should only have permissions to examine the report. Which role should you assign to users?

Viewer role

You plan to use the calculated table functionality to add a duplicate table in Power BI Desktop. Which characteristics of the original table will be duplicated?

data only

You plan to create a report in Power BI Desktop. You need to create a visualization that displays data in a two or more dimensions and cross-highlights with other visuals on the same report page. Which type of visualization should you use?

matrix

You have a visual that is being cross-highlighted. By default, what data will be displayed in a report tooltip?

the filtered (cross-highlighted) data

You plan to create a report in Power BI Desktop. You need to create a visualization that displays data in a two or more dimensions and cross-highlights with other visuals on the same report page. Which type of visualization should you use?

Matrix

You implement the Q&A feature in a report, so users can get answers to their questions on their own. You need to improve your Q&A search capabilities for end-users. Which two configurations can you add? Each correct answer presents a complete solution.

-Add synonyms to model fields. -Add a linguistic schema to the dataset.

You have a Power BI model. You need to assign items to a display folder. Which three items can be assigned to a display folder? Each correct answer presents part of the solution.

-Column -Calculated Column -Measure

In Power BI Desktop, you need to create a measure. Which two interfaces can you use? Each correct answer presents a complete the solution.

-Data View & Report View

You need to make changes to your data sources. Which three changes are supported by the Data Source Settings in the Power Query interface? Each correct answer presents a complete solution.

-Editing Permissions -Modifying the file path -Clearing permissions

Which three changes are supported by the Data Source Settings in the Power Query interface?

-Editing permissions, Modifying the file path, and Clearing permissions

You decide to start using variables when creating DAX formulas. What are two benefits of using variables in DAX measures over using original expressions? Each correct answer presents a complete solution.

-Improves the overall readability of the resulting DAX measure -Improves the overall performance of the resulting DAX measure

You have a Power BI Premium per user workspace. What is the maximum number of refreshes that can be scheduled each day?

48

You need to create a histogram that will display ranges of integers in groups. Which two resources do you need to create the histogram? Each correct answer presents part of the solution.

A Numerical column & A Bun Group Type

1. You have a collection of datasets and reports. You need to share the datasets and report together. This solution must NOT send any email notifications to internal users after sharing. What should you create in the Power BI Service?

An app

You upload reports to the Power BI service and pin several visuals to a dashboard. You plan create alerts rules for several visual. What are two locations you can view the alerts? Each correct answer presents a complete solution.

An email, the Notification Center

You have a Power BI Desktop dataset based on a star schema data source. You need to use Data Analysis Expression (DAX) to create a date table in the dataset and populate the table with a range of dates based on the earliest and latest date within the dataset. Which function should you use?

CALENDARAUTO

You have a Power BI Desktop dataset that includes a table named Salesperson. The table includes a column named UPN. UPN contains the user principal name of the salesperson's Azure AD account. You need to ensure that each salesperson will only be able to see the rows in the Salesperson table where the UPN column value matches their User Principal Name. Which two actions should you perform? Each correct answer presents part of the solution.

Create a role & Add a DAX Expression filter to the Salesperson table

You are creating a navigation page in a Power BI Desktop report. You need to create a button on the navigation page that will use conditional formatting to set the navigation destination based on the output of a measure in the report. What should you do first?

Create a table with a single column

You are creating a navigation page in a Power BI Desktop report. You need to create a button on the navigation page that will use conditional formatting to set the navigation destination based on the output of a measure in the report. What should you do first?

Create a table with a single column.

You need to prevent a hidden date tables from being auto generated by Power BI Desktop for every date or datetime data type column in a dataset. What should you do?

From the Current File options in Power BI Desktop, disable Auto Date/Time for new files

You are analyzing query data by using Power Query Editor. You need to ensure that the Column statistics is based on an analysis of the entire dataset. What should you do?

From the status bar, change profiling status to entire dataset.

You plan to use Power BI Desktop to analyze sales data of your company. You need to identify two types of data you will be able to use for creating bins that group the sales data. Which two data types should you identify? Each correct answer presents a complete solution.

Date/Time & Numeric

You create a Power BI data source which uses a SQL SELECT statement. The SQL statement queries multiple tables in a SQL Server database and includes subqueries. When importing data from the data source into Power BI, you receive the following error message: "Timeout expired." You verify that network connection to the SQL Server has sufficient available bandwidth and low latency. You need to minimize the occurrences of the timeout issues indicated by the message. What should you do?

Divide the SQL statement into separate data sources.

You create a Power BI data source which uses a SQL SELECT statement. The SQL statement queries multiple tables in a SQL Server database and includes subqueries. When importing data from the data source into Power BI, you receive the following error message: "Timeout expired." You verify that network connection to the SQL Server has sufficient available bandwidth and low latency. You need to minimize the occurrences of the timeout issues indicated by the message. What should you do?

Divide the SQL statement into separate data sources.

You have a bar chart and column chart visual on a report page. Selecting any column from the bar chart visual filters the column chart data to less than 1% of its unfiltered value. Which type of visual interaction should be used when the bar chart is filtering the column chart to ensure that you can easily see the data?

Filter

You plan to create a report in Power BI Desktop that will display sales opportunity by five sales stages, including lead, prospect, qualified, committed, and transacted. You need to identify the type of visualization that will display a linear process with sequentially connected stages, with one stage transitioning to the next. Which visualization should you use?

Funnel

You have a Power BI data source. You need to analyze the data to meet the following requirements: Add a Top 10 filter to a visual. Ensure that the filter does NOT display in the Filters on this visual display for that Visual Header icon. Title the visual "Top 10 Product Category by Sales Amount". What should you do?

Hide the Top 10 filter in the visual level filters for this visual in the Filters pane

You manage a Power BI workspace that includes a dataset with your company's sales data. You create a report that displays the sales data grouped by department. You need to use row-level security to ensure that, once the report is published, employees in each department can only see the sales data for their department. What should you do first?

In Power BI Desktop, create a role.

You manage a Power BI workspace in shared capacity. The workspace contains a report that uses a dataset named DS1. You need to implement an incremental refresh of DS1. What should you do first?

In Power BI Desktop, define filter parameters.

You have implemented role-based security that restricts access to a Power BI Desktop dataset for users who are assigned the role named Salespeople. You need to validate that the implementation works. What should you do?

In the Report view, activate the View as feature.

Which native AI visual helps explain correlations for a metric within the dataset?

Key influencers visual

1. You have a Power BI Desktop dataset that includes a table named Warehouse. The Warehouse table includes a column named Inventory Count, which contains the current number of items for each row, of a particular type on a given day. You have the following Data Analysis Expression (DAX) query that calculates the sum of all values in the Inventory Count column of the Warehouse table: Current Inventory Count = CALCULATE ( SUM ( 'Warehouse'[Inventory Count] )) You need to ensure that Current Inventory Count returns only the current total number of inventory items, rather than the sum of all inventory items that includes item counts from previous days. What DAX function should you include in the query?

LASTDATE

You plan to get data for a Power BI dataset from flat files. You need a location type to store the files. The data must NOT automatically synchronize between the original file and the dataset. Which location type should you recommend?

Local File

1. You have an Azure SQL database that contains two tables named SalesOrders and SalesOrderDetails. You load the SalesOrders and SalesOrderDetails tables into Power BI Desktop. The tables have a relationship based upon the sales order ID. You need to combine the two tables into one table. The solution must meet the following requirements: · There is a row for every sales order detail row in the resulting table, even if there is no corresponding sales order row · For every sales order detail row, the row in the resulting table include the corresponding sales order row data if the sales order row exists. · Any sales order row data that does not have corresponding sales order details is not included in the resulting table. What should you do to combine the two tables?

Merge the tables using the left outer join kind based on the sales order id.

You have an Azure SQL database that contains two tables named SalesOrders and SalesOrderDetails. You load the SalesOrders and SalesOrderDetails tables into Power BI Desktop. The tables have a relationship based upon the sales order ID. You need to combine the two tables into one table. The solution must meet the following requirements: There is a row for every sales order detail row in the resulting table, even if there is no corresponding sales order row. For every sales order detail row, the row in the resulting table include the corresponding sales order row data if the sales order row exists. Any sales order row data that does not have corresponding sales order details is not included in the resulting table. What should you do to combine the two tables?

Merge the tables using the left outer join kind based on the sales order id.

You plan to add data to Power BI Desktop from a new data source. You are evaluating whether you should use the DirectQuery storage mode or the Import storage mode. What are two benefits of using DirectQuery instead of Import? Each correct answer presents a complete solution.

Minimized local disk space usage & minimized need for data refresh

You have a Power BI dataset that gets data from a table in a SQL Server database. From which in Power BI Desktop can you modify the storage mode of the table?

Model View

Which two interfaces can you use?

Model view & Report view

You create a Power BI Desktop data source by importing a Cosmos DB for NoSQL item collection. You connect to the Cosmos DB account, database, and collection, but the preview displays only a list of items named Record. You need to select individual fields from items in the collection that you want to load into Power BI Desktop. What should you do first?

Open Power Query Editor.

You create a Power BI Desktop data source by importing a Cosmos DB for NoSQL item collection. You connect to the Cosmos DB account, database, and collection, but the preview displays only a list of items named Record. You need to select individual fields from items in the collection that you want to load into Power BI Desktop. What should you do first?

Open Power Query Editor.

You have an Excel spreadsheet that contains two columns as follows: · Category: Contain names of categories · Subcategory: Contain names of subcategories for each category You import the Excel spreadsheet into Power BI Desktop. You need to transform the data to meet the following requirements: Have multiple columns, where one column represents one category. Have a single row for each category that includes total count of its subcategories. Which transformation should you use?

Pivot Columns

You plan to optimize the performance of Power BI Desktop queries against a remote data source. You need to disable the default behavior that automatically applies cross highlighting and filtering of visuals within the same report. Which option should you configure in Power BI Desktop?

Reduce number of queries sent by Query reduction setting

You need to create a visual that display sales by employees, trending over months. The visual must clearly show how employees are performing against each other and have a ranking for each period. Which visual should you use?

Ribbon Chart

You have a table that has a column named Country and columns for each month of sales data. The names of the sales data columns use a format of Month Year, for example January 2022. You need to reshape the table to have a single column for sales data. A new column should also be added and contain the relevant month year for each row of data. What should you do from the Power Query Editor?

Select the Country column and then select Unpivot Other Columns.

You have a table that has a column named Country and columns for each month of sales data. The names of the sales data columns use a format of Month Year, for example January 2022. You need to reshape the table to have a single column for sales data. A new column should also be added and contain the relevant month year for each row of data. What should you do from the Power Query Editor?

Select the Country column and then select Unpivot Other Columns.

You are modifying a Power BI Desktop report. You need to create a bookmark that will capture an individual visual from the current report. Which pane should you use to designate the visual that the bookmark should reference?

Selection

You are modifying a Power BI Desktop report. You need to create a bookmark that will capture an individual visual from the current report. Which pane should you use to designate the visual that the bookmark should reference?

Selection

1. You are designing a data model for Power BI Desktop based on data stored in Azure SQL Database. You need to reduce the model size. What should you do?

Set the Storage Mode property of tables to Direct Query.

You have multiple Excel files stored in a folder synced with OneDrive for Business. You need to import data from the files. The solution must NOT use a data gateway. Which type of connector should you use?

SharePoint folder

You plan to use Power BI Desktop to create a report with multiple visualizations. You need to create a standalone chart that will be dedicated to filtering other visuals on the same report page. Which type of visualization should you use?

Slicer

You plan to use Power BI Desktop to analyze sales data for products sold by your company. You need to create a DAX formula that will list 10 best-selling products sorted by their total sales. Which DAX function should you use?

TOPN

You create a Power BI data source which uses a SQL SELECT statement. The SQL statement queries multiple tables in a SQL Server database and includes subqueries. After you import data from the data source into Power BI, you notice that one of the columns in the resulting dataset appears blank. You verify that the source table does include data. What should you do to resolve the issue?

Use the CAST function in the SQL statement

You connect Power Query Editor to a database table. You need to remove the Row ID column. Your solution must ensure that new columns do NOT display in the table model during a scheduled refresh in the future. What transformation should you use?

Use the Select Columns command and chose the columns to keep.