Power BI Certification English

You are a Power BI specialist at your organization. In your finance table, you have a column called margin, which is defined as 'margin = revenue – cost of goods sold.' In your company, there are several different ways people refer to margin. Some people call margin by other terms such as income, profit or profit margin. To use the Q&A feature successfully in your dashboard, you need to configure your dataset columns to enable users to type in variations for margin. Solution: Add Synonyms to the finance table Does this solution meet the goal?. Yes. No.

You are a Power BI specialist at your organization. In your finance table, you have a column called margin, which is defined as 'margin = revenue – cost of goods sold.' In your company, there are several different ways people refer to margin. Some people call margin by other terms such as income, profit or profit margin. To use the Q&A feature successfully in your dashboard, you need to configure your dataset columns to enable users to type in variations for margin. Solution: Add a detailed description of how you defined margin Does this solution meet the goal?. Yes. No.

You are a Power BI specialist at your organization. In your inventory table, there are several different ways people refer to inventory. Some people call inventory by other terms such as stock, supply, parts or supply. To use the Q&A feature successfully in your dashboard, you need to configure your inventory table and columns. Solution: Set the row label on the inventory table Does this solution meet the goal?. Yes. No.

You are a Power BI specialist at your organization. In your inventory table, there are several different ways people refer to inventory. Some people call inventory by other terms such as stock, supply, parts or supply. To use the Q&A feature successfully in your dashboard, you need to configure your inventory table and columns. Solution: Use Teach Q&A Does this solution meet the goal?. Yes. No.

You have the below relationship between sales, product and employees. The employees table is joined to the product table by 'category', and the product table is joined to the sales table by 'ProductKey.' Your boss asks you to build a visual for each Manager's sales by Subcategory. What do you need to configure to solve that problem?. Create a hierarchy in the product table for subcategory. In the employees table, change EmployeeID to type text. Set the cross-filter between product and employees to both. In the Model view, change the product table's toggle 'is hidden' to True.

You work for a consulting company that has a contract with a large government department. Part of the requirements for the report is to configure the page for use with a screen reader for sight-impaired users. What should you configure in your report such that the page follows a logical sequence?. Bookmarks. Tab order. Filters on all pages. Layer order.

You want to create a hierarchy in the products table for Category and Subcategory. You will then use the hierarchy as a drill down for a sales by product visualization as per the below exhibit. What three actions should you perform in sequence?. Right click on Category and select 'New Measure' -> Right click on Subcategory and select 'Add to hierarchy' -> In the visual add the hierarchy in the Axis and sales to the Values well. Right click on Category and select 'Create hierarchy' -> Add Subcategory to the drill through fields -> In the visual add the hierarchy in the Axis and sales to the Values well. Right click on Category and select 'Create hierarchy' -> Right click on Subcategory and select 'Add to hierarchy' -> In the visual add the category field to the Axis, which will automatically link the hierarchy. Right click on Category and select 'Create hierarchy' -> Right click on Subcategory and select 'Add to hierarchy' -> In the visual add the hierarchy in the Axis and sales to the Values well.

You need to create a table containing unique combinations of category and subcategory and aggregated sales. What DAX expression should you use?. DATATABLE('sales','product'[Category],'product'[Subcategory],"Sales Total",SUM(sales[Sales Amount])). SUMMARIZECOLUMNS('product'[Category],'product'[Subcategory],sales[Sales Amount]). SUMMARIZE('sales','product'[Category],'product'[Subcategory],"Sales Total",SUM(sales[Sales Amount])). SELECTCOLUMNS('sales','product'[Category],'product'[Subcategory],sales[Sales Amount]).

You work as a Power BI professional within your company's HR group. You are creating a data model to use for reporting within the group. As you start building relationships, you see there are issues connecting some of the tables. What two fixes are required to get the relationships working in your data model?. Change the data type of Employees[PayID] and hide Employees[Relocation] and Employees[HR Manager] fields. Change the data type of Employees[PayID] and BU[BUID] to Whole Number. Ensure the cardinality between BU and Employees from Many to one (*:1) to Many to many (*:*). Change the data type of Employees[PayID] and BU[BUID] to Text.

You work as a Power BI professional within your company's HR group. The report you created for retention analysis worked fine in the development environment, but when deployed has performance issues. Solution: Remove columns from the data model not used in reports Does this solution meet the goal?. Yes. No.

You work as a Power BI professional within your company's HR group. The report you created for retention analysis worked fine in the development environment, but when deployed has performance issues. Solution: Hide columns and tables from the data model not used in reports Does this solution meet the goal?. Yes. No.

You find an interesting visualization using the Python programming language that you want to run in Power BI. What are the three steps required before you can use Python in Power BI?. Install Python on your local machine. Enable R scripting. Install the libraries matplotlib and pandas. Enable Python scripting. Install the libraries seaborn and keras.

You are asked to optimize the performance of your data model. You have several intermediate queries that are not used for visualization and you have a large transactional table with a Date/Time field. What two optimizations should you do?. Change all relationship cross filter directions to single. Split the Date/Time field into a separate Date column and a separate Time column. Disable Power Query load on intermediary queries. Turn off single select on slicers.

You receive a new table to incorporate into your analysis in Power BI. You use Power Query to preview the data. For each of the following statements, select Yes if the statement is true. Otherwise, select No. Yes / Yes / Yes. No / Yes / Yes. No / Yes / No. No / No / Yes.

You are the Power BI administrator at your company which builds VR headsets. You need to assign appropriate workspace roles to your colleagues and must use the lowest permission necessary to accomplish the task. Which roles should you use for the below workspace requirements? 1. Update and delete workspaces 2. Publish apps 3. Publish content to the workspace. Member / Member / Contributor. Member / Member / Viewer. Admin / Member / Contributor. Admin / Admin / Contributor.

You have a table of temperatures for various cities. Some cities use Fahrenheit, while others use Celsius. You need to write a DAX expression to create a new column called Category for the following conditions. · If the value is greater than 80 and Units are F, then 'Hot'. Otherwise 'Not Hot' · If the value is greater than 26 and Units are C, then 'Hot'. Otherwise 'Not Hot' Complete the below DAX expression by replacing [VALUE]: Category = [VALUE] ( [VALUE] ( [VALUE] (Temperature[Value] > 80,Temperature[Units]="F"), [VALUE] (Temperature[Value] > 26,Temperature[Units]="C") ),"Hot","Not Hot"). SWITCH / AND / OR / AND. SWITCH / OR / OR / AND. IF / OR / AND / AND. IF / AND / OR / AND. WHILE / AND / OR / AND.

Your boss has scrolled through the Theme Gallery on the website https://community.powerbi.com/. She asks you to use a particular theme for your company's report in Power BI Service. How do you incorporate the theme in your report?. Download the JSON file and in Custom Dashboard theme > Upload JSON theme. Download the CSS file and in Custom Dashboard theme > Upload CSS theme. Download the PNG file and right-click on a tile > Upload PNG theme. Download the PDF file and right-click on the dashboard > Upload PDF theme.

You are your company's Power BI expert. You know that data lineage shows you which data sources are used by which datasets. One of your colleagues says he cannot access data lineage. What could be two possible explanations for data lineage not working?. Only admins can see the lineage view. You need a Power BI Pro license to see lineage view. Lineage view is available only to users with higher than Viewer access to the workspace. Lineage only works for DirectQuery datasets. Each user needs to be granted lineage view permission in the tenant settings.

You have a data set that shows employees and their bosses, as per the exhibit below. There is a field for each Employee called Employee_Name and a field for their boss Parent_Employee_Name. How would you create the Employee_Hierarchy column with a delimited hierarchy list in DAX?. PATHITEM(employees[Employee_Name], employees[Parent_Employee_Name]). RELATED(employees[Parent_Employee_Name], employees[Employee_Name]). CONCATENATE(employees[Parent_Employee_Name], employees[Employee_Name]). PATH(employees[Employee_Name], employees[Parent_Employee_Name]).

You work for a computer accessories company and your data model contains the following tables: 1. Accessories table: 1k rows that are updated monthly 2. Sales table: 15M rows that are updated regularly with changes that need to be shown immediately 3. Date table: 10k rows that are updated rarely For the three tables ABOVE, which storage mode should you use from the below options? · Import · DirectQuery · Dual. Accessories. Sales. Date.

You work at a sports betting company and have a table with US soccer scores and a second table with betting odds. Both tables share a common key called GameID. Your boss asks you to create a new combined table. Which transform should you use to create the dataset?. Append queries as new. Append queries. Merge queries as new. Merge queries.

You work for a transportation company that uses Azure IoT devices to monitor the temperature within containers. The head of Supply Chain has asked you to create a dashboard for the streaming data from the IoT devices and you need to configure Power BI appropriately. For each of the following statements, select Yes is the statement is true. Otherwise, select No. No / Yes / No. Yes / Yes / No. No / No / Yes.

You are part of a planning team and are investigating the forecast accuracy across multiple plants, various products and a series of demand types (e.g. stable, seasonal, growing etc.) You use Power BI and know that there is a visual that can help you with the root cause of low forecast accuracy. Which visual should you choose?. Waterfall. Treemap. Decomposition tree.

You work for a baseball hat retailer and you need to create a table of the top 3 team names by the total number of transactions. You have a table called Hat Sales and another for Team. Select the proper DAX functions to correctly complete the formula by replacing the [VALUE] field: Top Hat Sales = [VALUE] ( 3, [VALUE] ( HatSales, HatTeams[Team Name], "Transactions", COUNT(HatSales[TransactionID]) ), [Transactions],DESC). RANKX - CALCULATE. TOPN - CALCULATE. TOPN – SUMMARIZE. FILTER - SUMMARIZE.

You import a large table from Excel into Power BI and you suspect there may be missing data. How would you find the percentage of empty cells in each column?. Column quality. Column profile. Column distribution.

You work for a shoe distribution company and your firm uses Power BI for reporting. One of the reports you have created is exported as a PDF so the warehouse staff can check off items on a hard copy. Your company, however, has a policy that all exported reports need to be encrypted. How can you meet this requirement?. Use Row Level Security (RLS). Use sensitivity labels. Configure the dataset build permission security options. Set the PDF options in the tenant settings.

You work for a share trading software company and have a dashboard for user trading times. The data set used for the analysis goes back seven years. When the data reloads, it takes excessive time as the full seven years are refreshed. How can you improve the dataset performance?. Configure incremental refresh in Power BI Desktop and enable it in Power BI service. Use an on-premises data gateway. Enable incremental refresh directly in Power BI service. Manually refresh the database when users are not active.

You are in Power Query Editor and you see the below highlighted in the red box. What data preview option is used to create these charts?. Column quality. Column profile. Column distribution.

You have a data model with two key tables: Region and Shipping. There is a one-to-many relationship between the Region and the Shipping table. The model also has two row-level security roles named Region_Texas and Shipping_Mode The DAX filters for the two security roles are: · Region_Texas filter is: Region[state] = “Texas” · Shipping_Mode filter is: Shipping[mode] = “Rail” If a manager is a member of both the Region_Texas and Shipping_Mode roles, what will they see in a report using this data model?. The user will only see data for the state of Texas. The user will see data for which the state is Texas OR the shipping mode is rail. The user will only see data for the rail shipping mode. The user will see data for which the state is Texas AND the shipping model is rail.

You create an R visual in Power BI that has 500,000 rows. You notice that not all the data is shown and a message is displayed on the image. Your boss asks you the cause of this issue. What is the reason not all data is shown?. You are missing an R package. The visual has too many rows. The resolution is too high.

You have one report page with multiple visuals that is running slow, leading to complaints from users. What TWO solutions will help optimize your visuals?. Apply filters to reduce the max number of items that a visual displays. Apply filters to reduce the max number of items that a visual displays. Replace the default visuals with custom visuals. Limit the use of bar charts. Limit the number of visuals on a particular report page to only what is necessary.

You are asked to create a category percentage measure that will reflect the values selected when using a slicer. In the output below, you will see that the Bikes category is not selected in the slicer and the category percentage reflects the current filter context. All category percentage values add to 100% of the filter context. You are given a measure that sums the sales called Sales Total. How would you create a category percentage measure in DAX?. VAR categoryTotal = CALCULATE([Sales Total],ALLSELECTED('product'[Category])) VAR categoryPercentage = DIVIDE([Sales Total],categoryTotal). VAR categoryTotal = CALCULATE([Sales Total],ALL('product'[Category])) VAR categoryPercentage = DIVIDE([Sales Total],categoryTotal) RETURN categoryPercentage. VAR categoryTotal = CALCULATE([Sales Total],REMOVEFILTERS('product'[Category])) VAR categoryPercentage = DIVIDE([Sales Total],categoryTotal) RETURN categoryPercentage.

You work for a Japanese auto company whose fiscal year starts April 1. You have a table called Cars with orders for cars. You need to create a common date for a data model using DAX, respecting the fiscal period. How should you create a date table using DAX?. CALENDAR(4). CALENDARAUTO(3). CALANDAR( DATE ( 2000,3,1), DATE ( YEAR ( LASTDATE ( Cars[OrderDate] ) ), 31, 3 )).

You work in the spare parts department of an oil and gas company as a BI expert. The company has hundreds of parts, ten years of historic data, and hundreds of different order sizes from suppliers. You create a report in Power BI with aggregations for the following visuals: · A column chart of parts on the axis, but there are too many products showing · A stacked column chart of month number and order type, but too many months show · A histogram of supplier order sizes, but there are too many order sizes You need to combine the data to make the visual more readable. How should you combine the data? Select the GROUPING TYPE for each of the THREE solutions below from top to bottom. 1. Combined the product into categories of product 2. Combine the columns into 3 month number blocks 3. Combine order sizes into 8 groups. LIST / BIN / LIST. LIST / BIN / BIN. BIN / LIST / LIST. BIN / LIST / BIN.

You are analyzing basketball total points for the NBA for the year 2021. You have a measure [Points Total] which sums up the points and a date table with a year column. How should you complete the below DAX formula to effectively use it independently from any other filters set on the report page? Select the appropriate DAX function in place of [VALUE]. Sales 2021 = CALCULATE( [Points Total], FILTER( [VALUE] ('Date'[Year]), 'Date'[Year] = 2021) ). ALL. PATH. RELATED.

You want to use insights in Power BI Desktop to understand the increase from one quarter to the next in your sales column chart. Normally you would right-click the appropriate column and click on Analyze > Explain increase. However, this time insights does not appear to be available. What are TWO possible reasons insights is not working?. You use hierarchies. You use TOPN filters. You have filtered measures. You use a stacked column chart.

You work for an aerospace company in their satellite division. You build a workspace called 'satellite apps' in Power BI service that contains several reports and management level apps. You have a new analyst come on board and need to allow them to edit and publish reports. How would you achieve this goal?. Configure the security group from Azure Active Directory. Add the user as a member in the role section of the 'satellite apps' workspace. In Power BI Desktop set Row Level Security (RLS). Within the 'satellite apps' workspace, select update app.

You have a line chart that shows units manufactured by month. You want to add the on-time delivery metric for each month when you hover the mouse over a data point. How would you provide the additional piece of data?. Add on-time delivery column to the tooltips field. Add the on-time delivery column to the secondary values field. Add the on-time delivery column to the small multiples field.

You create a Power BI report with a line chart as per the below exhibit. How do you add the dotted horizontal line for the mean values?. Add a fixed forecast line for the time series in the analytics pane. Add a min line in the analytics pane. Add an Average line for Sales Total in the analytics pane. Add a trend line across years in the analytics pane.

You have built several DAX measures. A senior Power BI member of your team asks you to improve the performance and readability of the below DAX expression? How would you achieve this goal? Annual Sales Growth % = DIVIDE( ([Sales] - CALCULATE([Sales], PARALLELPERIOD('Date'[Date], -12, MONTH))), CALCULATE([Sales], PARALLELPERIOD('Date'[Date], -12, MONTH)) ). Use VARIABLES and a RETURN statement. Replace DIVIDE with a simple divide calculation using the '/' symbol. Instead of PARALLELPERIOD(), use DATEADD(). Mark date table as date.

You work for a forklift manufacturer. You are building a data model in Power BI and have a sales table as per the below exhibit. Within the sales table, there is a field called Model. How would you complete the DAX expression below to generate a Model table? What would you fill in for the [VALUE] fields starting from left to right? Model Table = [VALUE] ( [VALUE] ). CALCULATE / Sales[Model]. DISTINCT / Sales[Model]. CALCULATE / (Sales[Model] , Model = “XPR”). DISTINCT / Sales[Model Desc].

You work for an eCommerce company that sells ergonomic office products. You are tasked with creating a Power BI report from your company's transactional sales data in a table called 'ergoSales'. Before you do anything with the data, you want to check that there are no negative amounts in the 'ergoSales'[quantity] field. What is the most efficient way to check your data for negatives before creating the data model?. Select Column profile and then click on 'ergoSales'[quantity]. Select Column quality and then click on 'ergoSales'[quantity]. Click on 'ergoSales'[quantity] and select replace values.

You work for a diesel engine company as an internal Power BI consultant. You have two tables as per the below exhibit. There are no unique values in either table. However, the tables are related by Model Name. You need to create relationships in the data model to enable a visual containing data from both tables. How would you model these two tables in the most efficient manner?. Set the relationship between the two tables as one-to-one. Set the relationship between the two tables as many-to-many. Create a bridging table using unique IDs and create two one-to-many relationships. Set the relationship between the two tables as one-to-many.

You work for a manufacturing company with a metric called Supplied in Full on Time (SIFOT). In Power BI, you have built a dashboard to help management keep track of key summary data and metrics. One of your visuals is a gauge chart that shows the SIFOT values. The management team want a notification to be sent when SIFOT drops below 85%. You need to configure email alerts to management when this happens. Which four actions should you perform to create this alert?. Select + Add alert rule, ensure the Active slider is set to On. In the alert conditions choose 'below' and enter in a value of 85. Choose More options on the SIFOT gauge visual and select Manage alerts. In the dropdown for notification type, select email. In the alert conditions choose 'below' and enter in a value of 0.85. Check the 'Send me email too' and then save and close.

You are the head of analytics for a software company, and you want to understand the importance of factors that drive sales in your pre-sales team. What visual in Power BI can help you achieve your goal?. Custom R Dumbell Plot. Q&A. Decomposition Tree. Key Influencers.

You are editing the 'Model' column in a 'product' table Power Query. You need to replace instances of 'LL Road Frame' with 'JJ Roadster' and the previous step is 'Changed Type.' What M code functions would you use for the two [VALUE] fields?. ReplaceValue / ReplaceText. ReplaceRows / ReplaceText. ReplaceText / ReplaceValue. ReplaceRows / ReplaceValue.

You work in the marketing division for a sports apparel company. You have a sales fact table and multiple dimension tables as per the below exhibit. The head of marketing wants to encourage self- service analytics within the company and asks that you set up Q&A in the reports. Which of the following configurations will allow Q&A to work in your data model?. Object level security with any type of data source. Import. Composite models. Reporting Services.

You have sales data by day in a time series chart and need to produce a 25 day forecast with a 90% confidence interval as per the below exhibit. There are some data anomalies in the last 5 days of data that you want to ignore for the forecast. Select the THREE actions required to create the forecast. Set forecast length to 25 months and ignore last 5 months. Go to the analytics pane and under forecast select '+ Add'. Set the seasonality interval to 90% and click on apply. Set forecast length to 25 points and ignore last 5 points. Set the confidence interval to 90% and click on apply. Go to the analytics pane and under forecast select 'Add new trend'.

You have a busy report page called Sales and decide to use a detailed chart in a separate page using the drill through feature. The Sales page shows a visual of total sales by month. When you click on drill through on the sales by month chart, it should redirect to a detail page called Sales Detail that shows sales for the selected month by category. The Sales Detail page should also preserve the filters from the original sales page. What FOUR actions should you perform?. On the Sales Detail page, under drill through option add month as the drill through field. On the Sales page, under drill through option add month as the drill through field. Create a new page called Sales Detail. On the Sales Detail page under Drill through, toggle Keep all filters on. Create a table visual to show total sales by month and category. On the Sales page under Drill through, toggle Keep all filters on.

You are asked to present your findings on your company's warehousing performance across several years. You have a number of column and line charts and a date slicer. You decide you will create a presentation using Power BI and will create a narrative using the date slicer. What should you do to save the views across different years for your presentation?. Create page level filters and create a new groups. Filter the charts using the date slicer, then create bookmarks. Create drill throughs for each of the warehouse charts. Create report level filters and create a new groups.

You work in a marketing department as a Power BI professional. You are asked to create three different types of visuals for the marketing department. Which visualization should you use for the below three requirements? 1. Show progress of conversion rates against a target 2. Identify outliers in sentiment scores 3. Show the factors that influence sentiment scores. KPI / Waterfall / Treemap. Card / Scatter / Funnel. KPI / Scatter / Key influencers. Card / Funnel / Scatter.

You have built a report with multiple visuals for your food manufacturing company. As many senior members are often on the road, they ask that the reports be available for mobile devices. You need to optimize the report for mobile devices for the most critical visuals. Which three things can you do to optimize reports for mobile?. Set slicers to be responsive. Set the page size as tablet or smartphone. Resize the visuals to fit the mobile canvas. Set the page size as tablet or smartphone. Add haptic feedback (vibration) for when a user taps a visual.

You work for a College Football team as a Power BI sports analyst. You have built a report in Power BI Desktop that contains many visuals. The coaching team is pleased with the content of the visuals but wants the colors to match the college's color style guide. What should you do?. Adjust the report CSS file. Customize the current theme. Change the colors on each visual. Change the fonts on each text box.

You work for a microbrewery as head of data visualization. You have a Power BI data model that relies on several Excel files that reside on your company's internal server. You are emailed a new Excel workbook from the CEO that contains updated data. You place the new Excel workbook on your server and append the text 'v1.2' to the name. For each of the following statements, select Yes if the statement is true. Otherwise, select No. No / Yes / No. Yes / No / No. No / Yes / No.

Your company uses Power BI Premium and your manager wants to create printed invoices. However, the reports must fit perfectly on a page. What would you suggest to your manager?. Change the page view in Power BI Desktop. Use paginated reports. Use gridlines and snap to grid. Use lock objects.

You work in a shoe company's reporting team and have been tasked with building a Power BI report showing procurement analytics. You have an on-premises Microsoft SQL Server database that you use to build the procurement report using a DirectQuery connection. You thoroughly test the report and then upload it to Power BI Service. However, once in Power BI Service, the visualizations no longer work. How would you solve this problem?. Change the permissions on your Microsoft SQL Server database. Upgrade your Power BI Desktop. Install an on-premise data gateway. Install Power BI Report Builder.

You work as a BI analyst for a software company that allows users to manipulate images online. You have built a report that shows the conversion from free customers to premium customers. Your boss has asked you to provide a filter for the 'marketing' industry. Solution: · Apply a filter on all pages of the report for the 'marketing' industry · Share the report · Mark share report with current filters and slicer options Does this solution meet the requirements?. Yes. No.

CASE 1 You work as a BI analyst for a software company that allows users to manipulate images online. You have built a report that shows the conversion from free customers to premium customers. Your boss has asked you to provide a filter for the 'marketing' industry. Solution: · Create a text parameter for industry · Set the parameter equal to 'marketing' · Create a query parameter on the industry column Does this solution meet the requirements?. Yes. No.

CASE 1 You work as a BI analyst for a software company that allows users to manipulate images online. You have built a report that shows the conversion from free customers to premium customers. Your boss has asked you to provide a filter for the 'marketing' industry. Solution: · Add a slicer to the report and select the 'marketing' industry · Sync slicers to all relevant pages of the report Does this solution meet the requirements?. Yes. No.

CASE 2 You work for a financial institution, and you have built a data model and report in Power BI desktop. The report ran fine during testing, but when in production, users are complaining of performance issues. How can you improve the model's performance? Solution: Hide intermediate tables in the model view Does this help improve performance?. Yes. No.

CASE 2 You work for a financial institution, and you have built a data model and report in Power BI desktop. The report ran fine during testing, but when in production, users are complaining of performance issues. How can you improve the model's performance? Solution: Convert source PO text column into a number by removing prefix, e.g. 'PO12399' to 12399 Does this help improve performance?. Yes. No.

CASE 2 You work for a financial institution, and you have built a data model and report in Power BI desktop. The report ran fine during testing, but when in production, users are complaining of performance issues. How can you improve the model's performance? Solution: Remove unnecessary columns Does this help improve performance?. Yes. No.

CASE 3 You have the schema for the tables used in a bicycle franchise. In your role as a BI consultant, you have been asked to generate some insights from the data. Company data schema: You are asked to create a visualization for the sales by product. However, what should you do before creating a relationship?. Change the data type of Sales[ProductKey] to fixed decimal. Create a calculated column of Product[ProductKey]. Change the data type of Product[ProductKey] to whole number. Create a measure for the sum of Sales[Order Quantity].

CASE 3 You have the schema for the tables used in a bicycle franchise. In your role as a BI consultant, you have been asked to generate some insights from the data. Company data schema: The product table's color column has abbreviations as well as names. For instance, there is both Black and BLK. What do you need to do to ensure consistency in the color column?. Edit the source applied step in M code. Export the table to Excel. Use replace values in the transform ribbon in Power Query Editor. Remove duplicates in Power Query Editor.

CASE 3 You have the schema for the tables used in a bicycle franchise. In your role as a BI consultant, you have been asked to generate some insights from the data. Company data schema: When reviewing the customer table, you find that some of the postcodes appear to be missing. For instance '0800' does not seem to exist. What do you need to do to ensure all the Postcodes from the original data are in your model?. Use 'add columns from examples' to fix discrepancies. Use 'transform' and then adjust the rounding. Go to the transform ribbon and click on 'Use first row as header'. Make sure the postcode data type is 'text' and not 'whole number'.

CASE 3 You have the schema for the tables used in a bicycle franchise. In your role as a BI consultant, you have been asked to generate some insights from the data. Company data schema: You have been asked to add a column for profit, revenue – price. What steps do you need to do to calculate profit in the model?. Unpivot the Unit Price column -> Create custom column named Profit -> Set formula Sales Amount – (Order Quantity * UnitPrice). Create custom column named Profit -> Set formula Sales Amount – (Order Quantity * UnitPrice) -> Set the data type to Fixed decimal. Duplicate the Order Quantity Column -> Create a new column and multiply duplicated column by UnitPrice-> Create custom column named Profit. Create a Group By on SalesOrderLineKey -> Sum by Sales Amount and rename Profit -> Subtract Unit Price.

You have some new staff joining your Power BI team. To help them get started in Power BI Desktop, you want to create a way for them to connect to the data sources that you are using on in your project. All they will need to do to connect to the data is enter their user credentials to authenticate. How can you achieve this?. Create a dynamic M query. Use an Excel Macro to build connections. Create a PBIDS file with your key data sources. Create a template in lineage view.

You use an R script to build a custom visual. However, you find that you cannot see data marked 'N/A' and you are getting an error when importing your data as a list. What could be TWO reasons for these issues?. NA' is transformed into 'NULL' by Power BI. You have not used the correct R version. Power BI only supports R dataframes. You need to first install the library 'tidyr'.

You have an inactive relationship between your date table and your shipping table. How would you use an inactive relationship in a measure?. Use the LOOKUPVALUE() function. Delete the active relationship and make the new relationship active. Use the USERELATIONSHIP() function. Change the crossfilter direction to both in the modelling tab E. Explanation: The USERELATIONSHIP() function specifies an existing.

You build a key influencers visual and you want to see how sales amount is affected by product category and product color. How do you complete the three fields below?. Analyze -> Sales[Sales Quantity]; Explain by -> Product[Category], Product[Color]. Analyze -> Sales[Sales Amount]; Explain by -> Product[Category], Product[Color]. Analyze -> Sales[Sales Amount]; Expand by -> Product[Category], Product[Model].

You work for NASA's Supply Chain division. NASA purchases parts for the international space station. There are two key tables: SpaceStationInventory and DateTable. The SpaceStationInventory table contains the inventory counts for the parts and the date of the counts. The DateTable is a date table you created using M code. The Supply Chain division works Monday to Friday and is closed on state and federal holidays. As senior BI specialist you have been tasked to show the inventory level for the international space station warehouse on the last day of each month. Which DAX function will meet your requirement?. CALCULATE( SUM(SpaceStationInventory[Inventory Count]), LASTDATE(DateTable[Date] ). CLOSINGBALANCEMONTH( SUMX(SpaceStationInventory[Inventory Count]), SpaceStationInventory[Date] ). CALCULATE( SUM(SpaceStationInventory[Inventory Count]), LASTDATE(SpaceStationInventory[Date] ). CALCULATE( SUM(SpaceStationInventory[Inventory Count]), FILTER(SpaceStationInventory , SpaceStationInventory[Inventory Count] = MAX(SpaceStationInventory[Inventory Count]) ).

You work for an electric car manufacturer and you need to import a Supplier table into your data model. The Supplier table list the supplier id, full name, short name and address. When reviewing the data in Power BI, you want to check the distinct and unique rows for each column. Column profile. Column distribution. Column quality. Show whitespace.

You work for a large multinational company that has 23 departments. You have been tasked with creating a report with all the first aid trained staff . You need to create the same report for each department. How can you create the same report for each department in the most efficient manner?. Create separate Power BI Desktop report for each department and email the PBIX file. Add a parameter to the report to filter department. Create a separate workspace for each department and create a report in each. Create a separate dataset for each department and add a separate report.

You have produced reports for a space tourism startup. The founder is thrilled with the Power BI service setup. However, he has some questions on how to promote and secure the datasets to other users in the company. His three requirements are below: 1. When Power BI Service launches, I want the competition report to be displayed on their screen. 2. For the financial dashboard, I want the classification of the data clearly displayed. 3. I want you to highlight particular reports because it is valuable and worthwhile for others to use. I also don't want you to ask permission from the Power BI Admin each time. For the above requirements, them as either: · Certified · Promoted · Featured · Sensitivity Label. Promoted / Certified / Featured. Sensitivity Label / Featured / Promoted. Featured / Sensitivity Label / Promoted. Certified / Sensitivity Label / Promoted.

You work for an electric scooter manufacturer. You have a report with five pages, and you have been asked to filter all five pages when you select a particular scooter category on the first page. What are the two ways you could do this?. Add a page level filter on all five pages. Create a report level filter using category in the 'Filter all pages' well. Create a slicer for category on each page and then Sync slicers on all pages. Select paginated report and configure the settings.

A colleague built a Power BI desktop report. Your colleague imported a flat file into Power BI from his local machine. You have inherited the Power BI report and you need to update the source for the file. How would you go about this?. On the home ribbon, click on properties and change the file path. Click on the Applied step 'source' and click on the cog to change the file path. On the home ribbon, click on manage -> then file, then change the file path. Click on the Applied step 'changed type' and click on the cog in the corner to change the file location.

A colleague calls you for help. She wants to build an app in Power BI and asks you how to publish the app. What are the three most important parts of publishing an app? Select THREE from the below. On the permissions tab, decide who has access to the app. On the title tab, fill in the name and description to help people find the app. Before releasing the app, create a digital signature, so people know who built the app. On the navigation tab, select the content to be visible in the app. Click the Sync to organization button.

Your client asks you to connect to their procurement department's Azure Analysis Service. You call up their data analyst, and he asks you what you need for the connection. What do you need to make the connection and what type of query connection can you use? Select the THREE correct responses. Connection Requirement: Server name, e.g. asazure://westcentralus.asazure.windows.net/abc. Connection Requirement: Admin name. Connection Option: Import. Connection Option: Dual. Connection Option: Direct Query.

You are a BI analyst for a digital marketing company. You have built a complex report that requires many custom DAX measures and tables. After releasing your report, senior management complains that it takes too long to load some key visuals. How would you identify the cause of the poorly performing report elements?. Use the Dataverse button to drill into likely performance issues. Use the Performance Analyzer to examine report element performance. Use the cross report drillthrough feature to examine performance across the report. Run an R script to determine the performance issues in report elements.

You have a Premium workspace at your company and you want to use automatic page refresh. What are TWO key considerations to enable automatic page refresh?. Use must first disable sharing of your workspace. Your capacity administrator must enable the feature. Your interval must be greater than the minimum refresh interval. Refresh time in a premium capacity is rounded to the nearest 30 minutes.

A medical devices company has contracted you to investigate their transportation provider's performance. The company has a series of metrics they use to monitor the transporation provider's performance, including on-time pickup, on-time delivery, billing accuracy etc. You are asked to find potential factors that contribute to the transportation provider's performance. What sort of visualization can help?. Scatter chart. Q&A chart. Key influencers chart. Decomposition tree chart.

The board of your company would like to see a report on their mobiles. So you go ahead and optimize the report for mobile. For each of the following statements, select Yes if the statement is true. Otherwise, select No. Yes / No / No. No / No / Yes. Yes / Yes / No. Yes / No / Yes.

You discover a quality data source on your company's Azure SQL database. You decide to connect using direct query to the database. What two things are necessary to successfully connect?. Specify the fully qualified server name when connecting. Ensure firewall rules for the database are configured to 'allow access to Azure services'. Select automatic schema change detection. Tiles are refreshed once per day, so you must configure the schedule refresh.

Your company buys out a transportation company. You find that the company has several tables for customer records, all with the same structure and headings. You need to combine these tables to form a new single table for a customer segmentation analysis prior to merging the data into the parent company's system. What transformation should you use?. Merge queries. Append queries as new. Append queries. Merge queries as new.

You are asked by management to allow a consultant's custom Power BI visual. However, by default, management wants no custom visuals. How would you enable the consultant's visual while maintaining the default requirement in the most efficient manner?. Temporarily disable the tenant setting for custom visualization. Use Power BI Desktop .pbiviz files with the custom visual. Add the custom visuals to the organizational store. Build a separate Power BI service instance for the custom visual.

Temporarily disabling the tenant settings is not an efficient way to manage your configuration. While a local Power BI Desktop file could have custom visuals, when uploading to Power BI service, the custom visual would still be blocked. Building a separate instance of Power BI service is an expensive and inefficient solution to allow a single custom visual. What Text Analytics AI Insights are NOT available in Power BI. Language Detection. Sentiment Analysis. Key Phrase Extraction. Word Sense Disambiguation.

You create a Power BI report for a software company that uses AI for transportation companies. The company has a sales team across several countries and you create row-level security (RLS) in the model. Sales managers are restricted to access the data from their own country. In Power BI Desktop you create roles for each country e.g. USA, Canada, UK, France etc. You add a DAX expression for each role in the region table to filter country. You then add an Azure Active Directory security group for the country to each role. A new user starts in France. What do you need to do for the new user such that they can only see the sales data for France?. In Power BI Desktop, edit the DAX expression on the role for France. In Power BI Service, change the Power BI workspace security and change the user's email address to 'contributor. Add the user to the Azure Active Directory for France. In Power BI Service, use 'test as role' and use the email address of the new employee.

You build a dashboard that has an important gauge visual for a furniture manufacturer. The head of manufacturing want an alert whenever the gauge exceeds a certain value. What are the two options for configuring an alert in Power BI Service?. Use Microsoft Dataverse alert API. Send an email. Send a notification to mobile. Use Microsoft Power Automate to trigger additional actions.

What is NOT part of the Power BI Service dashboard theme?. Dark. Light. Color-blind friendly. Custom. Tab-order friendly Answer.

You decide to add a KPI visual for your company's dashboard. You need to show total units this year, show the historic values at a monthly level and for the comparison use total units last year. What fields should be used from the exhibit below in the KPI wells: Indicator, Trend Axis and Target Goals?. Indicator: Sales Units This Year , Trend axis: Fiscal Month , Target Goals: Sales Units This year. Indicator: Sales Units This Year , Trend axis: Fiscal Month , Target Goals: Sales Units Last year. Indicator: Sales Units Last Year , Trend axis: Fiscal Month , Target Goals: Sales Units This year. Indicator: Sales Units This Year , Trend axis: Fiscal Year , Target Goals: Sales Units Last year.

You import a customer table from a flat file into your data model. You have been asked to investigate the performance of your model and decide to check all the data sources 'view native query' . When you come to the customer table, you find that the 'view native query' does not display. What is a possible cause for 'view native query' being disabled?. Flat files do not support query folding. In the transformation, there is a promoted headers step that blocks the native query. You have used import as the storage mode. There is row-level security on the dataset.

Please complete the below sentence. If your dataset resides on a Premium capacity you can schedule up to refreshes per day in the settings. You can also trigger an refresh by selecting Refresh now in the dataset menu. 24 / Power BI Desktop dataset / on-demand. 16 / Power BI Service dataset / auto. 8 / Power BI Service dataset / auto. 48 / Power BI Service dataset / on-demand.

You create a report for a hospital and use row-level security (RLS) for doctors to view their metrics and salary using a 'doctors' role. You also create a 'hospital manager' role that can see all the data. The employee table you use for RLS has id, name, role and email address. For each of the following statements, select Yes if the statement is true, otherwise select No. Yes / Yes / No. Yes / Yes / Yes. Yes / No / No. No / Yes / No.

You import the below Transport table into your model and have been instructed to find the totals across destination ZIP codes. Which summarization option would you recommend for aggregating the below three columns? · DestinationZIP · Weight · Units. MAX / NONE / SUM. COUNT / SUM / NONE. NONE / SUM / SUM. NONE / MAX / SUM.

Your manager at a construction materials company wants to know how category sales have changed over time with an animation. You decide that a chart with an animation to show changes across months would help visualize the data. How would you go about achieving this objective?. Create a line chart and add month to the axis well. Create a waterfall chart and add month to the breakdown well. Create a scatter chart and add month to the play axis well.

You are asked to create a measure to calculate the sales amount including the value added tax with the ability to go to the lowest level of detail (row level.) In addition, the value added tax is only relevant for order dates after Jan/1/2018. Prior to 2018, there is no tax applicable. See the exhibit below as an example of the output. How would you achieve this goal using a DAX expression?. SUM(sales[Sales Amount]) + SUM(sales[Sales Tax]). SUMX(sales,if(sales[OrderDate]>=DATE(2018,1,1),sales[Sales Amount]+sales[Sales Tax],sales[Sales Amount])). SUMX(filter(sales,sales[OrderDate]>=DATE(2018,1,1)),sales[Sales Amount]+sales[Sales Tax]). SUM(sales,if(sales[OrderDate]>='1/1/2018',sales[Sales Amount]+sales[Sales Tax],sales[Sales Amount]).

At your company, you are the Power BI administrator. Your company policy states that only DA-100 certified people are allowed to create new workspaces. The people who are DA-100 certified are grouped into a security group called DA100Workspace. You hire several new people who have recently completed their DA-100 certification and assign them to the DA100Workspace security group. The new users compain that they cannot create new workspaces. What three actions do you need to take in sequence in the admin portal to fix this issue? Possible actions: i. Navigate to Power BI admin portal and select Tenant settings ii. Navigate to office 365 admin security and select settings iii. Choose specific security groups to apply to and add DA100Workspace iv. Click on Workspace settings and then click on create workspaces v. Click refresh. i / iv / iii. ii / iii / v. ii / iv / v.

You have built a dashboard for the board of a soft drink company. One of the board members has some ideas he wants to test with several visuals from a report page. However, he he wants to be able to immediatelty see the updates in the dashboard after you make changes. How would you go about doing this in the most efficient manner?. Create a new dashboard for every new request and pin the new tiles. Pin the report page as live to the dashboard. On the report page, select embed in dashboard.

You work for a rapid fashion company and are tasked with calculating the percentage change in sales from last month. You are given a measure Sales Total that aggregates sales and you have date table you built in DAX. How would you calculate last month's sales percentage change?. A. VAR SalesLastMonth = CALCULATE([Sales Total],DATEADD('Date'[Date],-1,MONTH)) VAR SalesPercentageChange = DIVIDE([Sales Total]-SalesLastMonth,SalesLastMonth) Return SalesPercentageChange. VAR SalesLastMonth = CALCULATE([Sales Total], PARALLELPERIOD ('Date'[Date],-1,QUARTER)) VAR SalesPercentageChange = DIVIDE([Sales Total]-SalesLastMonth,SalesLastMonth) Return SalesPercentageChange. VAR SalesLastMonth = CALCULATE([Sales Total], SAMEPERIODLASTYEAR ('Date'[Date]) ) VAR SalesPercentageChange = DIVIDE([Sales Total]-SalesLastMonth,SalesLastMonth) Return SalesPercentageChange.

Senior management wants you to add data classifications to your dashboards to raise awareness with those viewing your dashboards about what level of security should be used. For each of the following statements, select Yes if the statement is true. Otherwise, select No. Yes / No / Yes. No / Yes / No. Yes / Yes / Yes.

You work for a wine maker who has a selection of white and red wines that are fermented in barrels. The process can take between 5 and 35 months. Your data set has columns for fermentation time, start and end date You need to create a bar chart that shows wine fermentation time in ranges of 5 months. When creating the bar chart, which four actions should you perform in sequence?. A. Select the fermentation time column, right click and select New Group -> Set Group type to Bin -> Set the Bin type to number of bins -> Make the bin count 6. Set Group type to list -> Select the fermentation time column, right click and select New Group -> Set the Bin type to number of bins -> Make the bin count 30. Select the fermentation time column, right click and select New Group -> Set the Bin type to count of bins -> Set Group type to Bin -> Make the bin size 6.