Power BI

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. - Add a calculated column to the Salesperson table - Add a conditional column to the Salesperson table - Add a DAX expression filter to the Salesperson table - Create a role - Create a what-if parameter.

- Add a DAX expression filter to the Salesperson table - Create a role As part of setting up row-level security, it is necessary to create a role. In addition, it is necessary to add a DAX expression filter to the Salesperson table. This expression checks for a match between the salesperson's User Principal Name attribute and the value in the UPN column. If the two match, the salesperson is allowed to view the content of the corresponding row from the Salesperson table.

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

- Add a linguistic schema to the dataset - Add synonyms to model fields 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 a query named FactSales that retrieves data from 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 dataset refreshes. You select the existing columns in the query. Which two applied steps can you use in Power Query Editor to achieve the goal? Each correct answer presents a complete solution. - Choose Columns - Remove Columns - Remove Other Columns - Transpose

- Choose Columns - 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. This enforces the requirement that new columns will not be automatically added in the future. Using Remove Columns will delete the existing columns in the table, and 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 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? - Add a blank page to the .pbix file and select it - Connect DAX Studio to the data model - Invoke the Clear Cache function - Invoke the Refresh Metadata function.

- Connect DAX Studio to the data model DAX Studio, once connected to the data model, can be used to clear the data engine cache. The Clear Cache function can be invoked from DAX Studio, once you connect it to the data model. The Refresh Metadata function can be invoked from DAX Studio to update the metadata of the currently selected model. Adding a blank page to the .pbix file and selecting it is the first step in clearing the visual cache, not the data engine cache.

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 Power Query Editor, add a conditional column to the Salesperson table - In the Report view, activate the View as feature. - In the Report view, add a DAX expression filter to the Salesperson table - In the Report view, create a what-if parameter.

- In the Report view, activate the View as feature.

From Power BI Desktop, you open a Power BI report that contains three pages named Main, Error Rate, and On-time Rate. You add a button to the Main page for navigation. You need to implement a solution that meets the following requirements: The navigation destination must change based on the output of a DAX measure named [Error Rate]. If [Error Rate] is greater than 5%, the button must display the text "Error Rate" and navigate to the Error Rate page. Otherwise, the button must display the text "On-time Rate" and navigate to the On-Time Rate page. What three actions should you perform? Each correct answer presents part of the solution. - Create a DAX measure that outputs the correct page name based on the value of [Error Rate]. - Set the button type to Page Navigation and then use conditional formatting to specify the destination. - Set the button type to Bookmark and then use conditional formatting to specify the destination. - Set the navigation destination to the Error Rate page. - Use conditional formatting to set the button text.

- Create a DAX measure that outputs the correct page name based on the value of [Error Rate]. - Set the button type to Page Navigation and then use conditional formatting to specify the destination. - Use conditional formatting to set the button text. To configure a button for conditional page navigation, you need to create a DAX measure that outputs the correct destination page name. Then configure the button to use page navigation and use the newly created DAX measure to specify the navigation destination. To change the button text to match the page name, conditional formatting must be used to set the text to equal the newly created DAX measure. No bookmarks are necessary. It is not necessary to set the destination to a specific page since conditional formatting is used to specify the destination.

You need to reduce the size of a Power BI model that contains two dimension tables named Date and Location, and one fact table named Temperatures. The Temperatures table contains the following fields: - Reading Time (datetime) - DateKey (date) - LocationKey (whole number) - Temp C (decimal) You have one row for every 5-minute interval for each location. The Temperatures table is related to the Date and Location dimensions by using many-to-one relationships. You need to reduce the cardinality of the table. The solution must ensure that the dataset supports reports that analyze average temperature by hour and location. What two actions should you perform? Each correct answer presents part of the solution. - Create a column that contains the time values for the start of the hour of the Reading Time value. - Disable the query load on the Temperatures query in the Power Query Editor. - Remove the rows that occur exactly at 0 minutes and 0 seconds on the hour. - Use the Group By functionality to aggregate the rows by hour, DateKey, and LocationKey and then create an average Temp C value per row. - Use the Group By functionality to aggregate the rows by DateKey, Reading Time, and LocationKe

- Create a column that contains the time values for the start of the hour of the Reading Time value. - Use the Group By functionality to aggregate the rows by hour, DateKey, and LocationKey and then create an average Temp C value per row. Creating a column that displays the hour is necessary to summarize by hour when there is no Time or Hour dimension. Summarizing the table by using the Group By functionality reduces the number of rows. Reporting requirements dictate that the aggregated temperature value should be an average. The table should be summarized by hour, DateKey, and LocationKey in order to support the required reports. Removing rows that occur exactly on the hour removes data and does not support the reporting requirements. Disabling the query load removes the table from the model completely.

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. - Create a query for the server name. - From the Data source settings in Power BI Desktop, update the permissions. - From the Data source settings, update the server source to use a parameter. - Update the Source applied step of all related queries to reference the server name query.

- Create a parameter. - From the Data source settings, update the server source to use a parameter A parameter is the only part of a query that can be updated or changed in the Power BI service, by accessing the dataset settings. Updating the server source to use a parameter will update all existing queries pointing to the current server to instead use a parameter with that server name. This parameter can now be changed once this dataset is published to the Power BI service.

You have a fact table that contains sales data and the following two date columns: OrderDate ShipDate Both columns have a relationship to the Date column in the Calendar table, and DAX measures have been configured to use these relationships for calculations related to order or ship dates. You need to ensure that by default, the Calendar table does NOT filter the fact table, unless it is using a DAX measure that uses these relationships. What should you do? - Disable Make this relationship active for both relationships - Enable Apply security filter in both directions for both relationships - Enable Make this relationship active for both relationships - Set the cross-filter direction to both for each relationship.

- Disable Make this relationship active for both relationships You can have multiple inactive relationships between two tables in Power BI datasets. DAX measures can then use the USERELATIONSHIP function to activate a relationship for calculations. Relationship direction is not required for either the relationships or measures to work in this model setup. Only one active relationship can exist between two tables in a Power BI dataset. Applying a security filter in both directions isn't required for this model setup.

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. - Disable the Data option. - Disable the Current Page option. - Disable the Display option. - Enable the Current Page option. - Enable the Data option. -Enable the Display option.

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

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

- 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 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. - PATHCROSSJOIN - EXCEPT PATHITEM - EXCEPTPATH - PATHITEM CROSSJOIN - RELATED

- EXCEPTPATH - PATHITEM CROSSJOIN 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 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? - Each slicer must be the same slicer type - Each slicer must be visible - Each slicer must have the same title - Each slicer must use the same column.

- 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 need to prevent hidden date tables from being auto generated by Power BI Desktop for every date or datetime data type column in a dataset. What two tasks should you perform? Each correct answer presents a complete solution - Enable Mark as date table for the Calendar table. - From the Current File options in Power BI Desktop, disable Auto Date/Time - From the Global options in Power BI Desktop, disable Auto Date/Time for new files. - Set the Data Category to None for all Date and DateTime fields.

- Enable Mark as date table for the Calendar table. - From the Current File options in Power BI Desktop, disable Auto Date/Time 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 also disable the auto datetime tables in the dataset. Disabling the Global option Auto Date/Time for new files means that new files will no longer have Auto Date/Time enabled, but the file containing the current dataset will still have it enabled until it is disabled. Changing the data category will not impact the auto date table feature.

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. - Implicit measures can be used as a Drillthrough field - Implicit measures can be used to create Quick measures. - Implicit measures can be used with Field Parameters.

- End-users can change the aggregation type of implicit measure from the Values well of a visual. Implicit measures can select from one of nine aggregations when placed in the Values well of a visual. Both Implicit and Explicit measures can be used as a Drillthrough field, to create quick measures, and with Field Parameters.

You are analyzing query data by using Power Query Editor. You need to ensure that the Column statistics are based on an analysis of the entire dataset. What should you do? - Change the query connection type from Import to DirectQuery - From the status bar, change profiling status to entire dataset - In Power Query Editor, enable column profiling from the View ribbon. - Load the data into the data model.

- From the status bar, change profiling status to entire dataset By default, column profiling is only based on the first 1000 rows of the preview. Changing the column profile status to entire dataset will query the entire dataset for the column profiling information.

You are modifying a report in Power BI Desktop. The report contains a single report page that has three card visuals and one clustered bar chart. You need to create a bookmark that displays only the clustered bar chart and hides the cards. The solution must minimize development effort. What should you do first? - Add a bookmark in the Bookmarks Pane. - Hide the three cards in the Selection Pane. - Publish the report to the Power BI Service - Select the Spotlight option on the clustered bar chart.

- Hide the three cards in the Selection Pane. Before you create a bookmark, you need to configure the report page to show the state you want to capture in the bookmark. Visuals are visible by default, therefore, you must mark them as hidden. To add a bookmark, you first need to update the bookmark after hiding the visuals. Bookmarks can be created in Power BI without publishing to the Power BI service. While spotlight does focus on the clustered bar chart, it does not completely hide the cards.

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. - In Power BI Desktop, define the incremental refresh policy. - Publish the dataset to the Power BI service. - Upgrade the Power BI workspace to the Premium SKU.

- In Power BI Desktop, define filter parameters. To implement an incremental refresh of the dataset, you first need to define an incremental refresh policy, which, in turn, requires defining filter parameters. An incremental refresh policy does not require upgrading to a Premium SKU. Publishing the dataset to the Power BI service is required for the incremental refresh policy to take effect, so it's the last step of setting up an incremental refresh.

From Power BI Desktop, you create a 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? - In the Data Source Settings, modify the Advanced settings. - In the Data Source Settings, modify the Edit Permissions settings. - In the Power Query Editor window, enabled the Column Profile view. - In the Power Query Editor window, modify the Query Settings.

- In the Power Query Editor window, enabled the Column Profile view. By default, Power BI uses the 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 have a Power BI data source that contains the following tables: ProductCategory: Lists the product category ID and category name ProductSubcategory: Lists the product subcategory ID, subcategory name, and the related category ID ProductList: Lists the product ID, name, and subcategory ID You need to optimize the data to create a dimension for use in a star schema data model. How should the tables be transformed before loading into the model? - Import all three tables into the data model and connect them using relationships - Import only the ProductName table into the model - Merge the queries to create a single loaded table for Product - Use the append command to create a single loaded table for product.

- Merge the queries to create a single loaded table for Product A star schema should have a single table for each dimension or product, so using the combine command is what is required to create a single product table and aim towards a star schema design.

You are designing a data model for Power BI Desktop. You need to reduce the model size. Which technique should you use to reduce the model size? - Configure the Query reduction option Filters settings. - Configure the Query reduction option Slicers settings. - Set the Storage Mode property of tables to Direct Query. - Set the Storage Mode property of tables to Import.

- 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 the Query reduction option Slicers or Filters settings has no effect on data model size.

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? - MAXA - MAXX - RANKX - TOPN

- 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 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? - Dataset - Tenant - Workbook - Workspace

- Tenant

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

- 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 have Power BI Desktop. You need to determine query timings for a report page visual. Which method should you use? - Refresh the data model - Run the Best Practices analyzer in Tabular Editor - Use Session Diagnostics from the Power Query Editor - Use the Performance analyzer.

- 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.

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? - a Point to Site virtual private network (VPN) - a Site-to-Site virtual private network (VPN) - an on-premises data gateway - Azure Data Box

- an on-premises data gateway An on-premises SharePoint server requires the use of a Power BI gateway since it's an on-premises data source. VPN-based solutions would provide connectivity to an Azure virtual network, but not Power BI service. Azure Data Box is a solution for migrating data to Azure, which is not applicable in this scenario.

You create a data model in Power BI Desktop that contains DAX calculated columns and measures. You now need to create a report. 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. - as a filter in the "Filters on this page" well of the Filters pane - as a filter in the "Filters on this visual" well of the Filters pane - as an item in the "Add drill-through fields here" well of the Visualizations pane - as an item in the Fields well of a slicer

- as a filter in the "Filters on this page" well of the Filters pane - as an item in the Fields well of a slicer

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. - adding a column - clearing permissions - editing permissions - modifying the file path - renaming a column

- clearing permissions - editing permissions - modifying the file path The Data Source Settings in the Power Query interface supports editing permissions, clearing permissions, and modifying the path of the data source file. Any structural changes to a file, such as adding, removing, or renaming columns are not supported by the Data Source Settings in the Power Query interface.

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. - full support for the Q&A Power BI service - full support for the Quick Insights Power BI service - minimized local disk space usageminimized need for data refresh - support for per table configuration

- full support for the Q&A Power BI service - 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 a 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. - allows users to change the data source connection in the DAX measure - has additional functions that can be used in the DAX measure - improves the overall performance of the resulting DAX measure - improves the overall readability of the resulting DAX measure

- improves the overall performance of the resulting DAX measure - improves the overall readability 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 referenced 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 decide to remove unnecessary columns from your data model. What are two potential performance benefits of doing this? Each correct answer presents a complete solution. - decreasing report page load times - increasing DAX performance - increasing the refresh speed - reducing the size of the data model

- increasing the refresh speed - reducing the size of the data model Fewer columns mean there is less data to import and will reduce the model size and decrease the time it takes to refresh the model. Since row counts aren't changing, the calculation speed of any existing DAX measures won't change. Report page load times are primarily determined by number of visuals (objects) on the page, and DAX performance. Neither of which are impacted by unnecessary model columns.

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. - gauge - matrix - scatter chart - table - treemap

- matrix - table Matrix and table visuals support conditional formatting. Gauge chart, scatter chart, and treemap do not support conditional formatting.

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. - full support for the Q&A Power BI service - full support for the Quick Insights Power BI service - minimized local disk space usage - minimized need for data refresh

- minimized local disk space usage - minimized need for data refresh DirectQuery minimizes local disk space use and eliminates the need for data refresh. DirectQuery is not fully supported with the Q&A and Quick Insights Power BI services. Both the DirectQuery and Import storage modes support per table configuration.

You need to create a visual that displays 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? - clustered bar chart - ribbon chart - scatterplot - treemap

- 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 employees, 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 import an Excel file into Power BI Desktop and begin to analyze the data in Power Query Editor. You need to identify outliers in a text column within the data source. Which information should you use from Power Query Editor? - the min and max values in Column profile - the top and bottom entries in Value distribution - the value of the Distinct entry in Column statistics - the value of the Unique entry in Column statistics

- the top and bottom entries in Value distribution The top and bottom entries in Value distribution identify outliers, which appear, respectively, the greatest and the smallest number of times in that column. The value of the Distinct entry in Column statistics indicates the total count of different values. The value of the Unique entry in Column statistics indicates the total count of different values that appear only once. The min and max values in the Column profile of a text column designate the entries which appear, respectively, first and last in alphabetical order.

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? - bar chart - column chart - slicer - treemap

- treemap

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

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

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

8 The maximum number of refreshes per day for a Power BI workspace in a shared capacity is 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 - Add a year, month, and week columns to the fact table - Enable the Auto date/time current file option - Enable the Auto date/time global option.

Add a separate date dimension table Adding a separate date dimension table that includes year, month, and week information is the optimal approach which provides the required functionality, while minimizing the model size and administrative effort. Adding a year, month, and week columns to the fact table would increase the amount of administrative effort. Enabling the Auto date/time global or current file option would increase the model size. In addition, it would not provide the ability to describe weekly time periods (only year, quarter, month, and day).

You use Power BI Desktop to transform a query that retrieves data from an Excel spreadsheet. You need to view and modify multiple steps in all the M code. Which interface should you use? - Advanced Editor in the Power Query Editor - Data view - Formula Bar in the Power Query Editor - Model view

Advanced Editor in the Power Query Editor The Advanced Editor in the Power Query Editor provides the ability to work with all the M code used for shaping data in a query at once. Data view provides access to data within a dataset. Model view provides access to the data model of a dataset. The Formula Bar in the Power Query Editor provides the ability to edit only one step at a time.

You have several on-premises Microsoft SQL Server databases. You need to provide Power BI Service users access to the data sources without exposing the database servers directly to the internet. The solution must minimize the configurations that must be performed by each user. What should you deploy? - a virtual network data gateway - an ExpressRoute connection - an on-premises data gateway - an on-premises data gateway (personal mode)

An on-premises gateway is designed to allow multiple users to access multiple data sources. An on-premises data gateway only allows one user to access multiple data sources. A virtual network data gateway is designed to allow multiple users to access multiple online data sources. However, it isn't installed locally and only works with data sources secured by virtual networks.

You are creating a report in a Power BI Desktop by using a dataset that contains sales data. You need to create a measure that always provides the value of total sales for the year 2022, regardless of which year is selected in any visual in the same report. Which DAX function should you use in combination with the SUM function to override the context and provide the result? - CALCULATE - FILTER - IGNORE - SUMX

CALCULATE The CALCULATE function provides the result of the calculation with the ability to override the context. The IGNORE function modifies the behavior of the SUMMARIZECOLUMNS function by omitting specific expressions from the BLANK/NULL evaluation. The FILTER function returns a table that represents a subset of another table or expression. The SUMX function returns the sum of an expression evaluated for each row in a table.

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? - CALENDAR - CALENDARAUTO - DATE - DATEDIFF

CALENDARAUTO The CALENDARAUTO function generates a range of dates based on the earliest and latest date within the dataset. The CALENDAR function generates a range of dates, but it requires providing the start and end date. The DATE function returns the specified date in the datetime format. The DATEDIFF function returns an interval between two dates.

You need to enhance a data model by using Power BI Desktop. Data for which two model items can be created using the DAX language? Each correct answer presents a complete solution. - calculated table - display folder - hierarchies - numeric range parameter

Calculated table and Numeric range parameter Calculated tables are generated with DAX queries. Numeric range parameters create a table and measure, both generated with DAX queries. Hierarchies are helpful for drilldown and are defined as part of the data model, but they are not generated by using DAX. Display folders are a way to visually organize measures, columns, or hierarchies. DAX is not used to create them.

You need to develop a quick measure in Power BI Desktop. Which two elements can you use? Each correct answer presents a complete solution. - Calculations - Conditional columns - Data Analysis Expression (DAX) queries - Fields - Power Query M functions

Calculations and Fields When creating a quick measure in Power BI Desktop, you apply calculations to fields. You do not explicitly create a DAX query, but you choose calculations and fields, which result in automatic generation of a DAX query. Conditional columns are separate from quick measures. Unlike quick measures, they create a value for each row in a table and are stored in the .pbix file. Power Query M functions are not directly accessible from the Quick Measure interface.

You need to add an analytics line to a visual. The value for the line must equal the result of a DAX measure that always returns a consistent single value. The DAX measure must only be used to populate the analytics line. Which type of analytics line should you use? - Average line - Constant line - Max line - Min line

Constant line Either the X- or Y-axis constant line allows a DAX measure to be used to provide this value for the visual. The Min line populates the minimum value for a series already present in the visual. The Max line populates the maximum value for a series already present in the visual. The Average line populates the average value for a series already present in the visual.

You have a Power BI dashboard that is comprised of pinned visuals that are taken from a variety of reports. Which three objects from a report page can be pinned to the dashboard? - Custom visual - Filter - Image - Ribbon chart - Slicer

Custom visual, Image, Ribbon chart Individual filters and slicers cannot be pinned to a dashboard. This is because dashboards are designed to be an "at-a-glance" perspective. Reports are built to be filtered and interactive. Custom visuals, ribbon charts, and images can be pinned from a report page.

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. - Binary - Date - Decimal Number - Text

Date and Decimal number Date data types display well on a continuous (range) axis where report users understand chronological order. Number data types display well on a continuous (range) axis where report users understand the order between min and max values. Binary data and text (categorical) data cannot be displayed on a continuous (range) axis. Date, time, or number are the only data types comparable with a continuous axis.

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. - Binary - Date/time - Numeric - Text - True/false

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

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. - Implement aggregations in the SQL statement. - Implement groupings in the SQL statement. - Replace subqueries with nested queries.

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.

You have a Power BI model with the following fact tables and storage modes. FactStoreSales (Import mode) FactOnlineSales (DirectQuery mode) You have a dimension table named DimCalendar that has a relationship to both fact tables. You need to assign a storage mode for DimCalendar. The solution must minimize the time to execute queries that combine data from the dimension table and the fact tables. Which storage mode should you use? - DirectQuery - Dual - Import - none

Dual Using Dual mode means that either an import query can be run when accessing data from FactStoreSales, or a DirectQuery query can be run when accessing data from FactOnlineSales. Using Import mode means the queries are only optimized for FactStoreSales (Import). Using DirectQuery mode means the queries are only optimized for FactOnlineSales (DirectQuery). A storage mode of Import, DirectQuery, or Dual must be assigned.

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

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 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? - expand - drillthrough - filter - highlight

Filter Filter will show you the filtered data in this visual. So even when showing filtered data that is less than 1% of the unfiltered value, it will still display well in the column visual. Highlight shows you both the unfiltered and filtered values in the visual, for comparison purposes. Drillthrough is a page navigation experience that takes you from one page to another plus applies a set of filters to page navigated to. Expand is a way to navigate down a level using the hierarchy controls.

You are editing a report in Power BI Desktop. You need to adjust the behavior of the report by editing interactions. Which two types of interaction behavior can you change between visuals? Each correct answer presents a complete solution. - drillthrough - expand - filter - highlight

Filter and Highlight A filter will show you the filtered data in this visual. Highlight is the default interaction between visuals. It shows you both the unfiltered and filtered values in the visual, for comparison purposes. Drillthrough is a page navigation experience that takes you from one page to another plus applies a set of filters to page navigated to. Expand is a way to navigate down a level using the hierarchy controls.

From the Power Query Editor, you import data from a .csv file. The data includes a column named ZIP that contains postal codes from the United States. 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 and that all values remain 5 characters long. What should you do? - From the data view in Power BI Desktop, change the column data type from number to text. - 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 - From Power Query Editor, delete the changed type step - From Power Query Editor, update the current changed type step and replace convert from number to text for the ZIP column.

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. Adding a new applied step at the end of the query would result in loosing zip codes that start with 0. Changing the data type in Data View is equivalent to adding an applied step at the end of the query and would not preserve leading zeros. Deleting the changed type step would not set the data type to Text.

You need to create a new hierarchy in Power BI Desktop. What should you do first? - From the Model view, drag-and-drop one column onto another column in the Fields pane - From the Model view, right-click and select Create hierarchy - From the Report view, drag-and-drop one column onto another column in the Fields pane.

From the Model view, right-click and select Create hierarchy. To create a new hierarchy in Power BI Desktop, you must select Create hierarchy from the Model view or Report view. The option to create hierarchies by dragging and dropping was removed as an option in 2021 because too many hierarchies were being accidentally created during development. You cannot drag-and-drop one field onto another to create a new hierarchy. You can only use this method to add additional fields to an already existing hierarchy.

You plan to create a report in Power BI Desktop that will display sales opportunities 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 - slicer - treemap - waterfall

Funnel A funnel visualization displays a linear process with sequentially connected stages, with one stage transitioning to the next. A slicer visualization is available in different formats, including list, drop-down, and buttons, but not sequentially connected stages, with one stage transitioning to the next. A treemap visualization displays data as a set of nested rectangles. A waterfall visualization displays a running total as values are added or subtracted.

When importing data from an Excel workbook into Power BI, you receive the error message: "We couldn't find any data formatted as a table." What should you do to resolve the error? In the Excel workbook, select the data you want to import, select the Data Validation button, and save the change - In the Excel workbook, select the data you want to import, create a table, and save the change - In Power BI, add a template app - In Power BI, add an organizational app.

In the Excel workbook, select the data you want to import, create a table, and save the change. The error message indicates that the Excel workbook does not contain a table. To create it, in the Excel workbook, you need to select the data you want to import, press Ctrl+T or choose the Table button, click OK and save the change. Using the Data Validation button in Excel does not create a table. Organizational and template apps provide a way to implement functionality within Power BI, but, in this case, the issue is caused by an absence of a table in the Excel workbook, so installing an app would have no effect on resolving the issue.

You are using Power BI Desktop to edit a stacked column chart that displays gross sales data with a date hierarchy that contains year, quarter, month, and day. You need to prevent users from viewing the hierarchy based on the year. What should you do? - In the Filters pane, add a filter based on the quarter, month, and day. - In the Filters pane, add a filter based on the year - In the Format pane, modify the Maximum Range setting. - In the Visualization pane, remove the year field from the date hierarchy fields in the X-axis.

In the Visualization pane, remove the year field from the date hierarchy fields in the X-axis. To prevent users from viewing the hierarchy based on the year, you remove just the year from the date hierarchy fields in the x-axis in the Visualization pane. The quarter, month, and day will remain populated and usable. Modifying the visual Range settings in the Format pane would affect the range of values displayed in the visual. Adding a filter based on the year in the Filters pane would filter the values displayed in the visual, not remove a drilldown level. Adding a filter based on the quarter, month, and day in the Filters pane would filter the values displayed in the visual but would not prevent users from viewing the hierarchy based on the year.

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

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

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? - CSV - JSON - XML - YAML

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 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. - card - gauge - KPI - treemap - waterfall

KIP, Gauge, Card Alerts are available with KPI visuals, gauges, and cards. Treemaps and waterfall visuals do not support alerts.

Which native AI visual helps explain correlations for a metric within the dataset? - Decomposition Tree visual - Key influencers visual - Q&A visual - Smart Narrative visual

Key influencers visual The Key influencers visual helps you understand correlated factors impacting a particular metric. The Q&A visual allows end-users to ask natural language questions to create AI generated charts based on the questions. The Decomposition Tree visual lets you visualize data between multiple dimensions and drill down in any order. The Smart Narrative visual lets you combine natural language text with metrics from your model in sentence forms.

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? - CALENDAR - CALENDARAUTO - DISTINCTCOUNT - LASTDATE

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 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 SalesOrderID column. You need to combine the two tables into one table. The solution must meet the following requirements: - There is a row for every SalesOrderDetails row in the resulting table, even if there is no corresponding SalesOrders row. - For every SalesOrderDetails row, the row in the resulting table includes the corresponding SalesOrders row data if the SalesOrder row exists. - Any SalesOrder row data that does not have corresponding SalesOrderDetails rows is not included in the resulting table. What should you do to combine the two tables? - Append the tables - Merge the tables using the full outer join kind based on the sales order id - Merge the tables using the inner join kind based on the sales order id - Merge the tables using the left outer join kind based on the sales order id.

Merge the tables using the left outer join kind based on the sales order id. To construct the resulting table, it is necessary to merge tables using the left outer join kind with the SalesOrderDetails as the first table. Appending tables in this context would not make sense, since they do not have the same columns. Merging using the inner join kind would not produce a table that includes a sales order details row where there is no corresponding sales order row. Full outer join would produce a table that includes sales order row data that do not have corresponding sales order details.

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? - Data view - Model view - Page view - Report view

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 have a Power BI dataset that gets data from a table in a SQL Server database. From which view in Power BI Desktop can you modify the storage mode of the table? - Data view - Model view - Page view - Report view

Model view The storage mode of a table in Power BI Desktop is configurable from the Model view, not the Data or Report view. Page view is an option available from within the Report view.

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 - Model view - Page view - Power Query Editor - Report view

NB! FEIL I SVAR FRA TEST. "Grønne" svar og begrunnelse sammkjører ikke Data view and Power Query Editor (Alternativt svar (i følge beskrivelsen) er data view og repport 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.

From Power BI Desktop, you create a data source by importing an Excel file. You plan to identify data anomalies within the data source. Which interface should you use to display the column distribution and column quality graphs? - Advanced Editor in Power Query Editor - Power Query Editor - the Data view - the Model view

Power Query Editor Power Query Editor provides the ability to display the column distribution and column quality graphs. Data view provides access to data within a dataset. Model view provides access to the data model of a dataset. Advanced Editor of Power Query Editor provides the ability to work with the M code used for shaping data in Power Query Editor.

You plan to get data for a Power BI dataset from flat files. You need to select a location for the files. The location must provide the ability to configure scheduled refresh for the dataset by using Microsoft 365 credentials. Which two location types should you recommend? Each correct answer presents a complete solution. - local file - OneDrive for Business - Personal OneDrive account - SharePoint - Team Sites

OneDrive for Business SharePoint - Team Sites 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 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 Filters on this visual icon from the Header configuration for this visual. - Hide the Filters pane panel - Hide the Top 10 filter in the visual level filters for this visual in the Filters pane - Hide the visual header for this visual.

Only hiding the top 10 filter from the visual level filters in the filters pane will hide just this filter but allow other filters to show under the Filters on this visual icon for this visual.

From Power BI Desktop, you create a 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 - Retrieve the Cosmos DB account key - Retrieve the Cosmos DB connection string - Switch to the model view.

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 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 need to create a dashboard from a published report in Power BI. Which option in the report should you use? - Copy visual as image - Export data - Pin visual - Spotlight

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 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 that includes the total count of subcategories for each category Which transformation should you use? - Pivot Columns - Rename Columns - Transpose Table - Unpivot Columns

Pivot Columns The pivot column operation converts data into a table by aggregating values in a column. In this case, you can pivot the columns to calculate the count 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 rows and columns, without the required aggregation.

You need to reduce the cardinality of a column in a Power BI dataset. What should you do? - Add a prefix to each value in the column - Add a suffix to each value in the column - Replace null values with a value of 0 - Reduce the number of distinct values

Reduce the number of distinct values A column that has a lot of repeated values and a low number of distinct values will have a low cardinality. Reducing the number of distinct values reduces cardinality. Replacing nulls does not change the number of distinct values. A static prefix or suffix does not change cardinality.

You have a table that contains a column named ProductID. The ProductID column contains values that have a prefix of PD followed by five numbers. For example: pd15332. You plan to convert the ProductID values to numbers (integer). You need to remove the leading two letters from the current ProductID values. Which command should you use? - Remove Rows - Replace Errors - Replace Values - Trim

Replace values Only the Replace Values command allows the removal of leading characters, such as "pd" from the column values. Trim removes leading and trailing whitespace. Remove Rows doesn't affect the values in a column, only the included rows. The prefix of PD is not an error, so Replace Errors cannot remove the string.

You need to create a report in Power BI Desktop that will display the sales data of your company such that customers of a similar age will be grouped into clusters. Which visual should you add to the report? - card - gauge - scatter - treemap

Scatter Creating a scatter visual is the first step of applying the clustering technique that groups data into clusters. Card, gauge, and treemap visuals do not support clustering.

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? - Open See Related Content. - Open the File menu. - Select Add a tile. - Select Ask a question about your data.

Select Ask a question about your data. The Q&A feature lets you create a visual by typing in a question about your data. This new visual can then be pinned to the dashboard, without adding it to a report. Inside the file menu and the tiles menu there are no "visual" options. See Related Content displays related content like reports, datasets, etc. You can't create a new visual from here.

You have a query 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. The month columns contain the Sales Amount values for each month. You need to reshape the table to have all sales amount values in a single column. 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 Pivot Column - Select the Country column and then select Unpivot Other Columns - Select the first sales data column and then select Unpivot Columns - Select Transpose.

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 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? - Excel workbook - Folder - SharePoint folder - SharePoint list

SharePoint folder A SharePoint folder is the only connector that will allow the import of multiple Excel (or CSV) files stored in a OneDrive for Business folder, without using a data gateway. While you can connect to the folder on an on-premises device, it would require a data gateway to refresh in the service. An Excel workbook would only connect to a single Excel file and would also require a data gateway. A SharePoint list connector only connects to SharePoint lists and cannot connect to Excel files.

You plan to get data from flat files for a Power BI dataset. You need to select a location to store the files. Which location requires an on-premises data gateway? - OneDrive for Business - Personal OneDrive account - SharePoint Online Team Sites - Shared folder on a local network

Shared folder on a local network The shared folder on a local network will require a gateway as it is not available to the internet. SharePoint Online, OneDrive, and OneDrive for Business all can be refreshed as cloud data sources without a gateway.

You manage a Power BI workspace. You need to delegate the task to schedule data refreshes. The solution must use the principle of least privilege. Which role should you use? - Admin - Contributor - Member - Viewer

The Contributor role is the least privileged role that grants permissions to schedule data refreshes. The Member role grants permission to schedule data refreshes but is more privileged than Contributor. The Admin role grants the permissions to schedule data refreshes but is more privileged than Member. The Viewer role does not grant the permissions to schedule data refreshes.

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? - Admin role - Contributor role - Member role -Viewer role

The Viewer role only has reader access to view reports and dashboards. The Contributor role can create, update, or publish content. The Member role has many edit permissions in a workspace, similar to an admin. The Admin role has all the edit permission in a workspace

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? - Allow contributors to update the app - Develop a template app - License mode - 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 have a row level security role (RLS) in a dataset called User Security Group. This role needs to filter a RLS table that contains a column with the emails of users in the following format: [email protected] Which function should be used in the code for the RLS role to match the users Azure AD login credentials with the emails contained in the RLS table column? - NAMEOF - TREATAS - USEROBJECTID - USERPRINCIPALNAME

USERPRINCIPALNAME USERPRINCIPALNAME would return the Azure AD login in the format of "[email protected]". USEROBJECTID would not return the Azure AD login in the same format. NAMEOF is a function that is used for Field Parameters. TREATAS is a function related to column filtering.

You have an Excel spreadsheet that contains three columns labeled Year, 2021, and 2012. The entries in rows for the first column consists of names of the individual months in the year while the other two columns contain the sales amount for each month for the corresponding year. You import data from the Excel spreadsheet into Power BI Desktop. You need to transform the data so it will consist of three columns, with the first one containing month, the second containing year, and the third containing the sales amount for that month and year. Which transformation should you use first? - Pivot - Remove Columns - Transpose Table - Unpivot

Unpivot Selecting Unpivot will allow you to shape the current table into the one with the year, month, and sales amount columns, which will need to be renamed afterwards. Pivot would be applicable in the opposite scenario, in which flat data needs to be reorganized into one containing aggregate values for each unique value in each column. Transposing would switch rows and columns. Removing columns would result in a table with insufficient data to perform unpivot.

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 and column visibility only - data and hierarchies only - data, hierarchies, and column visibility - data only

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

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

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.

You plan to create a report in Power BI Desktop. You need to create a visualization to display a running total. The solution must meet the following requirements: The initial and the final value columns must start on the horizontal axis. The intermediate values must be floating columns. Which type of visualization should you use? - combo - funnel - scatter - waterfall

Waterfall A waterfall visualization is a chart that displays a running total, with the initial and the final value columns starting on the horizontal axis while the intermediate values are floating columns. A combo visualization is a chart that combines a column chart and a line chart and can have one or two Y axes. A funnel visualization is a chart that has sequential connected stages, where items flow sequentially from one stage to the next. A scatter visualization is a chart with two value axes, with one set of numerical data along a horizontal axis and another set of numerical values along a vertical axis.

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? - a deployment pipeline - a workspace - an app - metrics

Workspace apps can be installed automatically for any user in an organization, and without requiring notifications of installation. Deployment pipelines are used for moving artifacts between test, development, and production environments. Metrics are where you can create goals based off existing datasets. You can add members to a workspace, but they receive a notification that they've been added to the workspace.

You are evaluating whether you need to create a dashboard in Power BI. In which scenario do you need a dashboard instead of a report in Power BI? - You need to add filters. - You need to add slicers. - You need to display visuals on multiple pages. - You need to display visuals that use different datasets.

You need to display visuals that use different datasets. The ability to combine data from multiple datasets is one of the features that is available in dashboards but not supported in reports. Dashboards do not include the Filter, Visualization, and Fields panes that are available in Power BI Desktop when working with dashboards. Dashboards, unlike reports, contain only a single page.

You have the following Power Query M formula that generates a range of dates. = List.Dates(#date(2020,05,31), 365, #duration(1,0,0,0)) What is the resulting date range? - a list of days ending on May 31, 2020 and starting 365 days earlier - a list of days starting on May 31, 2020 and ending 365 days later - a list of months ending in May 2020 and starting 12 months earlier - a list of months starting in May 2020 and ending 12 months later

a list of days starting on May 31, 2020 and ending 365 days later The #date element designates the starting date, 365 designates the duration counter, and #duration(1,0,0,0) designates the duration interval in days, so this formula results in a listing of days starting on May 31, 2020 and ending 365 days later.

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 - many-to-one - one-to-many - one-to-one

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.

You have designed a star schema to simplify your data. You need to understand the relationship between the tables in the star schema. What is the relationship between the fact table and dimension tables? - many-to-many - many-to-one - one-to-many - one-to-one

many-to-one There is a many-to-one relationship between a fact table and dimension table. For each row in a dimension table there may be multiple matching rows in the fact table.

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. Which visual should you use? - card - gauge - scatter - treemap

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 need to create a Power BI dashboard. Which tool should you use? - Power BI Desktop - Power Query Editor - the Power BI mobile app - the Power BI service

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 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? - the Filters Query reduction settings - the Persistent filters of Report settings -the Reduce number of queries sent by Query reduction setting -the Slicers Query reduction settings

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 have a visual that is being cross-highlighted. By default, what data will be displayed in a report tooltip? - a report page tooltip with cross-highlighted data - both the un-filtered and filtered (cross-highlighted) data - the filtered (cross-highlighted) data - the un-filtered (non-cross-highlighted) total data

the filtered (cross-highlighted) data By default, report tooltips will apply the filter from the cross-highlighted data into the tooltip. Report page tooltips need to be manually created and are not assigned to a visual by default.

You need to use DAX quick measures to generate results to use in a report. Which type of DAX quick measure calculations will NOT work against a DirectQuery table? - aggregate per category - mathematical operations - time intelligence - X-functions

time intelligence Time intelligence functions have performance implications and are disabled for quick measures against DirectQuery tables. Mathematical operations, aggregate per category, and X-functions are all supported against DirectQuery.