PowerBI

Tree map

A graphic organizer that allows you to clearly lay out main ideas

Conditional Formatting

Formatting that is applied to a cell when a specified condition is met.

CAST function

SELECT First_Name, CAST(Score AS Integer) Int_Score FROM Student_Score

Historgram

graphical representation of a frequency distribution using vertical bars but bars touch each other to indicate variables are related

YAML (YAML Ain't Markup Language)

A data format based on JSON that is commonly used for configuration files.

You plan to build a Power BI report and set up conditional formatting of data fields. You need to identify two visuals that support the conditional formatting functionality. Which two types of visuals should you identify? Each correct answer presents a complete solution. A) Gauge B) Matrix C) Scatter chart D) Table E) Treemap

B) Matrix D) Table Matrix and table visuals support conditional formatting. Gauge chart, scatter chart, and treemap do not support conditional formatting

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. A) Binary B) True/false C) Date/time D) Numeric E) Text

C) Date/time D) Numeric Date/time and numeric data types support bins-based grouping. Binary, Boolean, and text data types do not support bins-based grouping.

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? A) Matrix B) Scatter C) Slicer

C) Slicer

You transform a Power BI Desktop data source imported from an Excel file. You need to modify the M code used for data shaping. Which interface should you use? A) Data view B) Model view C) Power Query Editor D) Advanced Editor in Power Query Editor

D) Advanced Editor in Power Query Editor Advanced Editor of Power Query Editor provides the ability to work with the M code used for shaping data in Power Query Editor. Data view provides access to data within a dataset. Model view provides access to the data model of a dataset. Power Query Editor provides the ability to apply transformations, but direct M code edits require using Advanced Editor of Power Query Editor.

one-to-one relationship

In databases, a relationship in which each record in Table A can have only one matching record in Table B, and vice versa.

many-to-many relationship

In databases, a relationship in which one record in Table A can relate to many matching records in Table B, and vice versa.

Matrix chart

It answers two questions: Where did people who were in each job category go? and Where did people now in each job category come from?

JSON

JavaScript Object Notation (JSON) - a popular data interchange format, JSON is a technology standard often used to format data when being sent or received via APIs.

Card visual

Large number tile

many-to-one relationship

One type of data relationship in which many records in a table are related to one record in another table

DATALENGTH function

Returns the number of bytes used to represent an expression

CALENDAR

The CALENDAR function returns a table with a column named Date that contains a contiguous set of dates based on the start date and end date that you specify.

CALENDARAUTO

The CALENDARAUTO function returns a table with a column named Date that contains a contiguous set of dates based on data in the model.

DISTINCTCOUNT

The DISTINCTCOUNT function counts the number of distinct values in a column, which results in additive behavior.

MAXA formula

The MAXA function returns the largest value in a column.

Scatter chart

A chart that shows the relationship between two categories of data; sometimes called an XY chart.

You have a Power BI dataset. You need to set the dataset as discoverable. Which two configurations will allow the dataset to be marked as discoverable? Each correct answer presents a complete solution. A) Certify the dataset. B) Promote the dataset. C) Enable RLS on the dataset. D) Publish dataset to a Premium workspace.

A) Certify the dataset. B) Promote the dataset. A certified dataset can be configured to be discoverable for users without access to request permissions to access. A promoted dataset can be configured to be discoverable for users without access to request permissions to access. RLS is not required on a dataset to become discoverable. Premium (capacity or shared) is not required to make a dataset discoverable.

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? A) Data only B) Data and hierarchies only C) Data and column visibility only D) Data, hierarchies, and column visibility

A) Data only A calculated table only duplicates data. Any model configurations such as column visibility or hierarchies must be recreated if needed.

In Power BI Desktop, you need to create a measure. Which two interfaces can you use? Each correct answer presents a complete the solution. A) Data view B) Model view C) Report view D) Page view E) Power Query Editor

A) Data view C) Report view The Report view provides the ability to create measures. To create a measure, use the context sensitive menu of the Fields list or the Calculations section of the ribbon. The Data view provides access to data within a dataset and includes the option to create a measure in the Calculations section of the ribbon. Model view, Page view, and Power Query Editor do not include the option to create a measure.

You have a Power BI report that uses a dataset that is imported from a database. You add a slicer to a report. You need to sync the slicer to use it on other report pages. What is required to sync slicers between report pages? A) Each slicer must use the same column. B) Each slicer must be visible. C) Each slicer must be the same slicer type. D) Each slicer must have the same title.

A) Each slicer must use the same column. The same column must be used in each slicer for them to recognize each other in the sync slicers settings. Slicers can sync even when hidden. Slicers do not need to be the same slicer type. One slicer could be a list, and the other a dropdown. Slicers do not need the same title to allow them to be synced

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 Import instead of DirectQuery? Each correct answer presents a complete solution. A) Full support for the Q&A Power BI service B) Full support for the Quick Insights Power BI service C) Minimized local disk space usage D) Minimized need for data refresh

A) Full support for the Q&A Power BI service B) Full support for the Quick Insights Power BI service The Import storage mode is fully supported with the Q&A and Quick Insights Power BI services. The Import storage, unlike DirectQuery does not minimize local disk space usage and does not eliminate the need for data refresh. Both the DirectQuery and Import storage modes support per table configuration

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. A) Improves the overall readability of the resulting DAX measure B) Improves the overall performance of the resulting DAX measure C) Has additional functions that can be used in the DAX measure D) Allows users to change the data source connection in the DAX measure

A) Improves the overall readability of the resulting DAX measure B) Improves the overall performance of the resulting DAX measure Variable names are often shorter than the original expressions, and the final variable returned at the end of the measure will be more readable with variable names included. Variables cache the defined calculation, allowing it to be reference multiple times without additional impacts from recalculations. The functions in DAX are universal, and there are no specific functions that can only be used within variables. The data source connection is set in the model, and cannot be changed from within a DAX measure.

You create a Power BI Desktop data source by importing an Excel file that contains 10,000 rows. You plan to identify data anomalies within the data source. You need to ensure that column distribution considers all rows in the Excel file. What should you do? A) In the Power Query Editor window, modify the profiling status. B) In the Power Query Editor window, modify the Query Settings. C) In the Data Source Settings, modify the Advanced settings. D) In the Data Source Settings, modify the Edit Permissions settings.

A) In the Power Query Editor window, modify the profiling status. By default, Power BI uses top 1,000 rows for profiling. To ensure that column distribution considers all rows in the Excel file, you need to modify the Power Query Editor profiling status setting. The Power Query Editor settings, Advanced settings, and Permissions settings have no bearing on the profiling characteristics.

You need to create a custom R visual by using Power BI Desktop. What do you need to do first? A) Install R on your computer. B) Configure global R scripting options in Power BI Desktop. C) Enable the script visuals option in the Visualization pane of Power BI Desktop. D) Enable preview features in Power BI Desktop.

A) Install R on your computer. To create a custom R visual by using Power BI Desktop, you first need to install R on your computer. Configuring global R scripting options in Power BI Desktop might be required once you install R on your computer. Enabling the script visuals option in the Visualization pane of Power BI Desktop is done once R is installed and configured using the global R script options in Power BI Desktop. Creating a custom R visual by using Power BI Desktop has no dependency on enabling preview features.

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? A) It reduces the number of datasets, refreshes, and storage requirements in the Power BI service. B) It increases the speed of the reports and visuals connected to the Power BI datasets. C) It prevents sensitive data from being shared with report consumers. D) It increases the number of scheduled refreshes that can be configured for the Power BI dataset.

A) It reduces the number of datasets, refreshes, and storage requirements in the Power BI service. When multiple reports use the same Power BI dataset vs each having their own import model, you reduce the number of datasets that are needed to be published to the Power BI service. Fewer datasets mean fewer refreshes, less storage space requirements, and less overhead for admins

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? Select only one answer. A) JSON B) CSV C) YAML D) XML

A) JSON To copy themes between workspaces, you can download and upload them as JSON files. Themes use the JSON file format, not CSV, YAML, or XML.

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? A) LASTDATE B) DISTINCTCOUNT C) CALENDAR D) CALENDARAUTO

A) LASTDATE The LASTDATE function will ensure that the SUM function applies only to the last date of the time period, resulting in a semi-additive behavior. The DISTINCTCOUNT function counts the number of distinct values in a column, which results in additive behavior. The CALENDAR function returns a table with a column named Date that contains a contiguous set of dates based on the start date and end date that you specify. The CALENDARAUTO function returns a table with a column named Date that contains a contiguous set of dates based on data in the model.

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 B) Date/time column C) List group type D) Bin group type

A) Numerical column D) Bin group type The group is created against a numerical column using bins The Bin group type is an auto grouping of items into bucketed bins (groups). It will display well as a histogram. A DateTime column isn't required to display histogram type data against a numerical column. A List group type is a manual grouping of items into new group names/categories. It doesn't create auto (binned) groups.

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? A) Open Power Query Editor. B) Switch to the model view. C) Retrieve the Cosmos DB account key. D) Retrieve the Cosmos DB connection string.

A) Open Power Query Editor. This behavior is by design. The Preview pane in Power BI shows a list of Record items when connecting to a collection of JSON formatted items. To view individual item fields, open the Power Query window and use the Expander button on the right hand side of the Column1 header to display the list of fields. Switching to the model view would not benefit us in any way, since the data has not been imported yet. Retrieving Cosmos DB account key or connection string at this point is meaningless, since that was required to connect to Cosmos DB account, which has been already completed.

You have a Power BI Desktop dataset that includes a table named Employees containing a row for each employee with the following columns: Employee ID Employee Name Manager ID Manager Name You need to flatten the parent-child hierarchy in the Employees table by adding an extra column that will contain a listing of employee IDs for all direct and indirect managers of each employee. Which two Data Analysis Expression (DAX) functions should you use? Each correct answer presents part of the solution. A) PATH B) PATHITEM C) EXCEPT D) CROSSJOIN E) RELATED

A) PATH B) PATHITEM The PATH function returns a string with identifiers of all the parents of the current identifier, which is used for flattening. The PATHITEM function returns the item at the specified position of a string, which is also used for flattening. The EXCEPT function returns rows from one table which do not appear in another table, which would require another table. The CROSSJOIN function returns a Cartesian product of all rows from all tables that the function references. The RELATED function returns a related value from another table, which would require another table.

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? A) Pivot Columns B) Unpivot Columns C) Rename Columns D) Transpose Table

A) Pivot Columns The pivot column operation converts data into a table by aggregating unique values in a column. In this case, you can pivot the columns to calculate the quantity of product subcategories in each product category. The unpivot column operation performs the opposite task, converting columns into rows. Renaming columns would result in a table with different column names but the same data content. Transposing would simply switch tables and columns, without the required aggregation.

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? A) Ribbon chart B) Treemap C) Clustered bar chart D) Scatterplot

A) Ribbon chart A ribbon chart places the highest (ranked) value at the top of the stacked column each month, and shows those ranked changes over time. A treemap is not meant for displaying changes over time and wouldn't easily show ranked comparisons between employees. The clustered bar chart can be used to show changes over time, and a clustered bar chart will show comparisons between employee, but no ranking data is provided between employees. A scatterplot is typically used to compare a relationship between two (or more) calculations and their categorical distribution between each other.

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? A) Set the Storage Mode property of tables to Direct Query. B) Set the Storage Mode property of tables to Import. C) Configure Query reduction option Slicers settings. D) Configure Query reduction option Filters settings.

A) Set the Storage Mode property of tables to Direct Query. Setting the Storage Mode property of tables to Direct Query reduces the model size since only schema of the data source is stored locally. Setting the Storage Mode property of tables to Import will import data into Power BI desktop, effectively increasing the model size. Configuring Query reduction option Slicers settings controls whether to instantly apply slicer changes and add an Apply button to each slicer. Configuring Query reduction option Filters settings allows you to apply basic filter changes.

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? A) The Reduce number of queries sent by Query reduction setting B) The Slicers Query reduction settings C) The Filters Query reduction settings D) The Persistent filters of Report settings

A) The Reduce number of queries sent by Query reduction setting The Reduce number of queries sent by Query reduction setting disables the default behavior that automatically applies cross highlighting and filtering of visuals within the same report. The Slicers Query reduction settings allow you to instantly apply slicer changes and add an Apply button to each slicer. The Filters Query reduction settings allow you to instantly apply basic filter changes. The Persistent filters of Report settings allow you to prevent users from saving filters in the Power BI service.

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? A) Use the CAST function in the SQL statement. B) Use the DATALENGTH function in the SQL statement. C) Set the privacy levels on the data source. D) Clear permissions on the data source in Power BI.

A) Use the CAST function in the SQL statement. The issue indicates that Power BI is incorrectly interpreting the data type used by the source column. To resolve it, you need to explicitly specify the intended data type, which can be done by using the CAST function. DATALENGTH displays the number of bytes used to represent an expression. Clearing permissions could prevent Power BI from being able to access the target database. Setting the privacy levels of the data source would have no impact on addressing the issue of missing data.

You import data from a .csv file to Power Query Editor. The data includes a column named ZIP that contains zip codes. You notice that Power Query Editor automatically applies the Whole Number data type to the ZIP column. You need to ensure that the ZIP column uses the Text data type. What should you do? A) From Power Query Editor, add a new applied step at the end of the query to convert the ZIP column from number back to text. B) From Power Query Editor, update the current changed type step and replace convert from number to text for the ZIP column. C) From Power Query Editor, delete the changed type step. D) From Power BI desktop, change the column data type from number to text.

B) From Power Query Editor, update the current changed type step and replace convert from number to text for the ZIP column. To correctly update the data to text you need to replace the number type conversion with a text conversion, and to keep all other data type column transformations. This needs to be done in the Power Query Editor.

In Power Query Editor, you notice that the column statistics for each column shows a count of exactly 1,000. You need to ensure that the column statistics for each column shows the statistics based on all rows that are returned by the query. What should you do? A) Add a Table.Buffer applied step to the query. B) From the query window, select Column profiling on the entire dataset. C) Change the query load type from Import to DirectQuery. D) Create a Top N row count parameter for the query.

B) From the query window, select Column profiling on the entire dataset. Selecting column profiling for the entire dataset will change the column profiler to analyze the entire query dataset. Adding a Table.Buffer applied step will only cache the entire query in memory during refresh. The query load type in the model will not impact how many rows are used by the column profiler. Using a Top N row count applied step or applying a parameter will not change how the column statistics feature calculates.

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? A) Card B) Matrix C) Scatter D) Table

B) Matrix A matrix visualization displays data in a two or more dimensions and cross-highlights with other visuals on the same report page. A card visualization displays a single data point. A scatter visualization is a chart, not a grid. It has two value axes, with one set of numerical data along a horizontal axis and another set of numerical values along a vertical axis. A table visualization is a grid that contains related data in a grid format, with support for two dimensions only

You create a Power BI Desktop dataset by importing and transforming a data source based on multiple tables in a SQL Server database. You need to modify the relationship between the tables. Which interface should you use in the Power BI Desktop? A) Data view B) Model view C) Report view D) Page view

B) Model view The relationship between tables is configurable from the Model view. The Data view provides access to data within a dataset. The Report view provides the ability to build reports. The Page view is an option available from within the Report view and is intended to simplify designing and building reports.

You plan to create a report in Power BI Desktop that will display the relationship between the number of orders and the number of orders shipped by product category. You need to identify the visual that will clearly identify outliers in the data set by displaying them away from the bulk of data. A) Card B) Scatter C) Treemap D) Gauge

B) Scatter A scatter visual displays a relationship between values associated with two axes: one set of numerical data along a horizontal axis and another set of numerical values along a vertical axis. It is particularly suitable for identifying outliers because it displays them away from the bulk of data. A card visual displays a single data point. A treemap visual displays data as a set of nested rectangles. A gauge visual displays a circular arc including a single value that measures progress toward a goal or target.

You have Power BI Desktop. You need to determine query timings for a report page visual. Which method should you use? A) Refresh the data model. B) Use the Performance analyzer. C) Use Session Diagnostics from the Power Query Editor. D) Run the Best Practices analyzer in Tabular Editor.

B) Use the Performance analyzer. The Performance analyzer will show the query timings for each object on a report page. Session diagnostics measures Power Query query performance as it relates to refresh times. It is unrelated to measuring DAX performance or report page query timings. The Best Practices analyzer reviews the model for best practices around things like model design, relationships, field naming conventions, and measures. But is unrelated to any query timings for report page visuals.

You plan to get data for a Power BI dataset from flat files. You need a location type that requires you to provide credentials of a Microsoft account. Which location type should you recommend? A) Local file B) SharePoint - Team Sites C) Personal OneDrive account D) OneDrive for Business

C) Personal OneDrive account A personal OneDrive account provides the ability to automatically synchronize flat files residing in a user's OneDrive and Power BI datasets. Since it relies on OneDrive access, it requires the user's credentials of the corresponding Microsoft account. The OneDrive - Business option uses Azure Active Directory credentials. The SharePoint - Team Sites option uses the same Azure Active Directory credentials as the ones used to access SharePoint Online. For the local file option, no additional credentials are required to access them.

You need to create a dashboard from a published report in Power BI. Which option in the report should you use? A) Copy visual as image B) Export data C) Pin visual D) Spotlight

C) Pin visual The Pin visual option allows you to pin the visual to an existing dashboard or create a new one. The Copy visual as image option allows you to copy a visual as an image to Clipboard. The Export data option allows you to export data in the xlsx or csv formats, but not create a dashboard. The Spotlight option allows you to highlight a visual on the report page, but not create a dashboard.

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? A) MAXA B) MAXX C) RANKX D) TOPN

D) TOPN The TOPN function returns Top N rows of the specified table, such as, for example, top 10 best-selling products sorted by their total sales. The MAXA function returns the largest value in a column. The MAXX function evaluates an expression for each row and returns the largest value. The RANKX function returns ranking of a number in a list of numbers for each row of a target table.

You plan to create a Power BI workspace. You need to ensure that you will be able to configure a Microsoft 365 group whose SharePoint Online document library is available to workspace users once the workspace is created. Which setting should you configure when creating the workspace? A) Allow contributors to update the app B) Develop a template app C) License mode D) Workspace OneDrive

D) Workspace OneDrive The Workspace OneDrive setting allows you to configure a Microsoft 365 group whose SharePoint Online document library is available to workspace users once the workspace is created. The Allow contributors to update the app for this workspace setting is meant to provide additional permissions for workspace contributors. The Develop a template app setting allows you to set up a template app workspace. The license mode allows you to choose between Pro, Premium per user, Premium per capacity, and Embedded licensing.

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? A) one-to-one B) one-to-many C) many-to-one D) many-to-many

D) many-to-many Many-to-many cardinality in Power BI should be avoided due to ambiguity, resulting from the presence of non-unique values. One-to-one cardinality in Power BI should be avoided and, if present, should be eliminated by combining the related tables. However, this type does not introduce ambiguity. One-to-many and many-to-one cardinality are two common cardinality types, used for the relationships between a fact and dimension tables.

Gauge chart

A data visualization that shows a single result within a progressive range of values

XML (Extensible Markup Language)

A language used by many databases for inputting or exporting data. XML uses formatting rules to describe the data.

one-to-many relationship

A relationship between two tables in a database in which one record in the primary table can match many (zero, one, or many) records in the related table.

You have a query named FactSales that retrieve a table to a SQL Server table. You need to update the query to prevent new columns that may be added to the table in the future from getting imported during scheduled refreshes. Which two applied steps can you use in Power Query Editor to achieve the goal? Each correct answer presents a complete solution. A) Remove Columns B) Choose Columns C) Remove Other Columns D) Transpose

B) Choose Columns C) Remove Other Columns Using the Choose Columns and Remove Other Columns options will allow you to explicitly select the columns that you want to keep. Enforcing the requirement that new columns will not be automatically added in the future. Using Remove Columns will delete the selected columns in the table, but new columns that get added in the future will still be imported automatically. Transpose treats rows as columns, and columns as rows. It will not limit the number of columns being imported.

You need to create a custom Python visual by using Power BI Desktop. What do you need to do first? A) Install Python on your computer. B) Configure global Python scripting options in Power BI Desktop. C) Enable the script visuals option in the Visualization pane of Power BI Desktop. D) Enable preview features in Power BI Desktop.

C) Enable the script visuals option in the Visualization pane of Power BI Desktop. Enabling the script visuals option in the Visualization pane of Power BI Desktop is required before creating custom Python visuals in Power BI Desktop. Installing Python is not required. Configuring global Python scripting options in Power BI Desktop is not required to create Python visuals. The ability to create a custom Python visual by using Power BI Desktop has no dependency on enabling preview features.

You need to create a row level security (RLS) role for a dataset. What should you do first? A) In Power BI Desktop, open the Advanced Editor in the Power Query Editor. B) In the Power BI service, open the security settings for the dataset. C) In Power BI Desktop, open Manage roles. D) Assign workspace members to the security group in the Power BI service.

C) In Power BI Desktop, open Manage roles. RLS roles are created or modified from the Manage roles space. You can assign AD users/groups to an existing role in the security settings, but new RLS roles must be created in Power BI desktop or other model authoring external tool. RLS configurations cannot be accessed from any Power Query window. Before you can assign users to a security group, it first needs to be created for the model in Power BI Desktop.

You have a Power BI Premium per user workspace. What is the maximum number of refreshes that can be scheduled each day? A) 1 B) 8 C) 16 D) 48

D) 48 In a Premium per user workspace the limit is 48 refreshes per day for a dataset.

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? A) Implement groupings in the SQL statement. B) Implement aggregations in the SQL statement. C) Replace subqueries with nested queries. D) Divide the SQL statement into separate data sources.

D) Divide the SQL statement into separate data sources. Dividing the SQL statement into separate data sources would minimize the amount of processing on the SQL Server side. This would minimize or even eliminate the timeout issues. Groupings, aggregations, and using nested queries would either have no impact on timeout issues or further increase the amount of processing on the SQL Server side, resulting in more frequent timeout issues.

Types of DAX formulas

MAXA MAXX RANKX TOPN

CSV (comma-separated values)

The file type that saves an Excel file so that there is a comma between each cell and a paragraph return at the end of each row.

KPI (Key Performance Indicator)

a measure of achievement that can be attributed to an individual, team, or department

Table chart

a visual representation of data, a visual depiction of information that is quick and easy to read

You need to create a bookmark that toggles the visibility of a visual. This bookmark must only toggle the visibility state and perform no other action. Which three configurations should you make? Each correct answer presents part of the solution. A) Enable the Data option. B) Disable the Data option. C) Enable the Display option. D) Disable the Display option. E) Enable the Current Page option. F) Disable the Current Page option

B) Disable the Data option. C) Enable the Display option. F) Disable the Current Page option. The current page capture allows the bookmark to navigate back to the page of capture. The data capture updates filters and drillthough which should not occur for this bookmark. The display capture updates visual visibility on the report page.

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? A) Enable Mark as date table for the Calendar table. B) From the Global options in Power BI Desktop, disable Auto Date/Time for new files. C) From the Current File options in Power BI Desktop, disable Auto Date/Time for new files. D) Set the Data Category to None for all Date and DateTime fields.

C) From the Current File options in Power BI Desktop, disable Auto Date/Time for new files. Disabling Auto Date/Time for new files from the Current File options will disable all Auto Date/Time tables in this dataset. Enabling Mark as date table for the Calendar table will only disable the auto datetime tables for this table. Other date or datetime columns in the model will still have auto datetime tables associated with them. Disabling the Global option Auto Date/Time for new files means that new files will no longer have Auto Date/Time enabled, but files in the current dataset will still have it enabled until it is disabled. Changing the data category will not impact the auto date table feature.

LASTDATE

The LASTDATE function will ensure that the SUM function applies only to the last date of the time period, resulting in a semi-additive behavior.

RANKX formula

The RANKX function returns ranking of a number in a list of numbers for each row of a target table.

TOPN formula

The TOPN function returns Top N rows of the specified table, such as, for example, top 10 best-selling products sorted by their total sales.

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? A) Admin B) Member C) Contributor D) Viewer

A) Admin The Admin role is the only one that grants the permissions to update workspace metadata.

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? A) Funnel B) Slicer C) Treemap D) Waterfall

A) Funnel

MAXX formula

The MAXX function evaluates an expression for each row and returns the largest value.

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. A) Add synonyms to model fields. B) Configure the dataset as a composite model. C) Add a linguistic schema to the dataset. D) Organize the dataset columns and measures into logical folders in the Fields pane.

A) Add synonyms to model fields. C) Add a linguistic schema to the dataset. Adding synonyms to model fields will help users search for them. For example, you can give a synonym of (Actuals) for the (Sales) measure. A linguistic schema describes terms and phrases that Q&A should understand for objects within a dataset, including parts of speech, synonyms, and phrasings that relate to that dataset. Composite models are not supported by Q&A today. Organizing fields by display folders will not impact Q&A search capabilities.

You have Power BI Desktop. You need to create DAX calculated columns and measures. In which two places can a DAX calculated column be used, but a DAX calculated measure cannot be used? Each correct answer presents a complete solution. A) As an item in the Fields well of a slicer B) As a filter in the "Filters on this visual" well of the Filters pane C) As a filter in the "Filters on this page" well of the Filters pane D) As an item in the "Add drill-through fields here" well of the Visualizations pane

A) As an item in the Fields well of a slicer B) As a filter in the "Filters on this visual" well of the Filters pane C) As a filter in the "Filters on this page" well of the Filters pane D) As an item in the "Add drill-through fields here" well of the Visualizations pane Unlike a measure, a calculated column can be used in a slicer to place filter options on the report page. DAX measures cannot be placed in the "Filters on this page" well. They can only be placed per visual, in the "Filters on this visual" well of the Filters Pane. Both DAX columns and measures may be used as a visual-level filter. Both DAX columns and measures can be used in the Drillthrough well.

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? Each correct answer presents a complete solution. A) Card B) Gauge C) KPI D) Treemap E) Waterfall

A) Card B) Gauge C) KPI Alerts are available with KPI visuals, gauges, and cards. Treemaps and waterfall visuals do not support alerts.

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. A) Column B) Calculated column C) Measure D) Table E) Report

A) Column B) Calculated column C) Measure Tables and reports cannot be assigned a display folder. Columns, calculated columns, and measures can be assigned a display folder.

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? A) Select the Country column and then select Pivot Column. B) Select the first sales data column and then select Unpivot Columns. C) Select the Country column and then select Unpivot Other Columns. D) Select Transpose.

C) Select the Country column and then select Unpivot Other Columns. Selecting Unpivot Other Columns will unpivot all current and future month/year columns into a new sales data column. Transposing treats rows as columns, and columns as rows. It will not create a single sales data column. Selecting Unpivot Columns in the first sales data column will successfully unpivot these columns into a new single column for sales data, but the other sales month/year columns will not be included in the new sales column. Pivoting the data takes values on rows in a column and turns them into new columns.

You need to create a Power BI dashboards. Which tool should you use? A) Power BI Desktop B) The Power BI mobile app C) The Power BI service D) Power Query Editor

C) The Power BI service The Power BI service provides support for creating Power BI dashboards. The Power BI mobile app can be used to view dashboards, but not to create them. Power BI Desktop does not provide support for creating Power BI dashboards. Power Query is a Microsoft Excel tool that is used for importing data, but not for creating dashboards.

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? A) Use the Remove Column command on the Row ID column. B) Use the Transpose command, then filter the rows to remove Row ID. C) Use the Select Columns command and chose the columns to keep. D) Select Row ID, then use the Remove Other Columns command.

C) Use the Select Columns command and chose the columns to keep. Only the Select Columns command will let you choose columns to keep, delete the columns you do not want, and prevent new columns from showing up in the table in the future.