Microsoft Power BI 70-778 Certification Exam questions fully solved & updated 2024

You have a Power BI model for sales data. You need to create a measure to calculate the year-to-date sales and to compare those sales to the previous year for the same time period. Which DAX function should you use? a) PARALELLPERIOD b) SAMEPERIODLASTYEAR c) DATESYTD d) PREVIOUSYEAR c) DATESYTD You have a Power BI report that displays a bar chart and a donut chart on the same page. The bar chart shows the total sales by year and the donut chart shows the total sale by category. You need to ensure that when you select a year on the bar chart, the donut chart remains unchanged. What should you do? a) Set a visual level filter on the bar chart. b) Edit the interactions form the Format menu. c) Set a visual level filter on the donut chart. d) Add a slicer to the page that uses the year column. b) Edit the interactions form the Format menu. You plan to create a dashboard in the Power BI service that retrieves data from a Microsoft SQL Server database. The dashboard will be shared between the users in your organization. You need to ensure that the users will see the current data when they view the dashboard. How should you configure the connection to the data source? a) Deploy an on-premises data gateway (personal mode). Import the data by using the Import Data Connectivity mode. b) Deploy an on-premises data gateway. Import the data by using the Import Data Connectivity mode. c) Deploy an on-premises data gateway. Import the data by using the DirectQuery Data Connectivity mode. d) Deploy an on-premises data gateway (personal mode). Import the data by using the DirectQuery Data Connectivity mode. d) Deploy an on-premises data gateway (personal mode). Import the data by using the DirectQuery Data Connectivity mode. You have a Power BI dashboard that displays different visualizations of company sales. You enable Q&A on the dashboard. You need to provide users with sample questions that they can ask when using Q&A. Which settings should you modify from the Power BI Settings? a) Subscriptions b) Dashboards c) Dataset d) Workbooks c) Dataset In the Power BI service, you create an app workplace that contains several dashboards. You need to provide a user named with the ability to edit and publish dashboards What should you do? a) From the app workspace, click Update app, and then configure the Access settings. b) Share the dashboard, and then modify the Access settings of the dashboard. c) Configure security for the dataset used by the app. d) Modify the members of the app workspace. d) Modify the members of the app workspace. You plan to use Power BI Desktop optimized for Power BI Report Server to create a report. The report will be published to Power BI Report Server. You need to ensure that all the visualization in the report can be consumed by users. Which two types of visualizations should you exclude from the report? Each correct answer presents part of the solution. a) funnel charts b) custom visuals c) bubble maps d) breadcrumbs e) R visuals d) breadcrumbs e) R visuals You plan to use Power BI Embedded to deliver reports in a web application. You need to ensure that the reports display live data. Which data source you should use? a) Microsoft Azure Data Lake Store b) Microsoft Azure Table Storage c) Microsoft Azure HD lnsight d) Microsoft Azure SQL Database a) Microsoft Azure Data Lake Store You have an on-premises Power BI Report Server. You plan to create a report in Power BI Desktop and to publish the report to the report server. Which data source should the report use? a) Microsoft Azure SQL Database b) a Microsoft SQL Server database c) a Microsoft SQL Server Analysis Services (SSAS) database d) Microsoft Excel c) a Microsoft SQL Server Analysis Services (SSAS) database You have a Power BI report in an app workspace. You plan to embed a report from the app workspace into a line-of-business application by using Power BI Embedded. Which information should you provide to the application developers? a) the application token and the report URL b) the report URL and a user name c) the app workspace name and the access key d) the access key and the report ID c) the app workspace name and the access key You have an app workspace named Retail Store Analysis in the Power BI service. You need to manage the members that have access to the app workspace using the least amount of administrative effort. What should you do? a) From the Power BI Admin portal, click Usage metrics b) From the Office 365 Admin center, click Users c) From the Power BI Admin portal, click Tenant settings d) From the Office 365 Admin center, click Groups d) From the Office 365 Admin center, click Groups With Power BI Publish to web, you can easily embed interactive Power BI visualizations online, such as in blog posts, websites, through emails or social media, on any device.You have published a visualization but unfortunately changes to the underlying data are not immediately visible to users. What might be wrong? a) You must refresh the data manually after publishing to the web b) It takes up to an hour for data to refresh c) You cannot refresh data after publishing to the web, the visuals are static d) The user does not have the appropriate row level security permissions b) It takes up to an hour for data to refresh When you share a dashboard with tiles that link to reports, those reports are also shared at the same time. But what if you want to share just a report? Just send the report page URL to your colleagues. As long as they have Power BI Pro licenses, are members of the same distribution group, in the same email domain as you, or have at least one dashboard that links to that same report (the dashboard has tiles that were pinned from that report), they'll be able to open the report. You want to share a report that is prefiltered on the Store Territory with a value of NC. What should you add to the end of the report URL before you share it? a) ?filter=Store/Territory = NC b) ?filter=Store/Territory eq NC c) ?filter=Store/Territory == NC d) ?filter=Store[Territory] eq NC b) ?filter=Store/Territory eq NC The correct format is: ?filter=tablename/fieldname eq value. The field must be of type string and neither the tablename or fieldname can contain spaces. As part of a large HR project you are working with a dataset of company employees, both past and present. The data includes columns for EthnicGroup, PayTypeID, HireDate (the date they started work), TermDate (the date they left) and several other columns. You want to create a new Calculated Column that determines if the person was a bad hire based on a set of rules. If they were a bad hire then the result is 1, otherwise the Calculated Column displays a zero. The bad hire rule is: BadHire = If the person stayed at the company less than 61 days Example: If a person joined the company on 27th June 2013 and left on 21st August 2013, then they would be a bad hire. Which DAX expression would you use for this Calculated Column? a) Slicer b) IF(OR((([HireDate]-[TermDate])) >= 61,ISBLANK([TermDate])),0,1) c) IF(((([HireDate]-[TermDate])*-1) < 61),1,0) d) IF((([TermDate]-[HireDate])*-1) >= 61,0,1) b) IF(OR((([HireDate]-[TermDate])) >= 61,ISBLANK([TermDate])),0,1) For this expression you must use a combination of IF, OR and ISBLANK. If you forget to use ISBLANK then the difference between a date and a blank date is always 0, causing problems with the numeric comparison and potentially always showing BadHire as 1. Remember some employees won't have left the company and hence have no TermDate value. The internal finance team needs a new dashboard creating to show projected revenue for the coming years so they can adequately plan for future budgets. All of the data for this dashboard will come from SQL Server 2005 that is running on the company's main SQL Server machine. The IT team has informed you that the SQL Server machine has many instances of SQL Server installed including 2005, 2008, 2012 and even 2016. If the server name was ACMESQL and the instance name provided by IT was SQL2005, what would you type into the Server name textbox when connecting through Power BI? a) ACMESQLSQL2005 b) SQL2005ACMESQL c) ACMESQL:SQL2005 d) ACMESQL(SQL2005) a) ACMESQLSQL2005 When connecting to an instance of the Database Engine you must specify the name of the instance of SQL Server. If the instance of SQL Server is the default instance (an unnamed instance), then specify the name of the computer where SQL Server is installed, or the IP address of the computer. If the instance of SQL Server is a named instance (such as SQLEXPRESS), then specify the name of the computer where SQL Server is installed, or the IP address of the computer, and add a slash and the instance name. You need a Power BI Pro license to share your dashboard but those you share it with do not need a license. When you share a dashboard, they can view it and interact with it, but can't edit it. They see the same data that you see in the dashboard and reports unless row-level security (RLS) is applied to the underlying dataset. The colleagues you share it with can share the dashboard with their colleagues, if you allow them to. a) TRUE b) FALSE b) FALSE The first sentence is in fact FALSE. You need a Power BI Pro license to share your dashboard, and those you share it with do, too. When you enable Snap objects to grid, all visuals on the Power BI Desktop canvas that you move (or resize) are automatically aligned to the nearest grid axis, making it much easier to ensure two or more visuals align to the same horizontal or vertical location or size.You can also manage the overlap of each element on the design surface, often referred to as? a) Vertical stacking b) Stack-order c) a-order d) z-order d) z-order You can manage the front-to-back order of visuals in a report, often referred to as the z-order of elements. This allows you to overlap visuals in any way you want, then adjust the front-to-back order of each visual. You have loaded data from a database table which contains a list of filenames that have been processed by an internal team. One particular column called filename contains the name of each file processed e.g. Which Transform function would you use to dissect the filename values into two separate columns, one for the filename and the other for the file extension e.g. xml a) Extract - Text before delimiter b) Split Column - Split at Each occurrence of the delimiter c) Extract - Text after delimiter d) Split Column - Split at Right-most delimiter d) Split Column - Split at Right-most delimiter By using the Split Column you can split each value into two separate columns, using the right most delimiter option to preserve the entire filename that could potentially contain additional full stops (periods). When you import multiple tables, chances are you're going to do some analysis using data from all those tables. Relationships between those tables are necessary in order to accurately calculate results and display the correct information in your reports.Imagine an example of a model that has a Sales actuals table with a lookup table for department and also a budget sales table that records target budget for each department. The department table is connected to both the sales and the budget table.Which kind of Cross Filter Direction would you use in this example? a) Single b) Both a) Single Although both is the most common, this means for filtering purposes, both tables are treated as if they're a single table. This works well with a single table that has a number of lookup tables that surround it. An example is a Sales actuals table with a lookup table for department. This is often called a Star schema configuration (a central table with several Lookup tables.) However, if you have two or more tables that also have lookup tables (with some in common) then you wouldn't want to use the Both setting. To continue the previous example, in this case, you also have a budget sales table that records target budget for each department. And, the department table is connected to both the sales and the budget table. Avoid the Both setting for this kind of configuration. When you share with people outside your organization, they get an email with a link to the shared dashboard. They need a Power BI Pro license, and they have to sign in to Power BI to see the dashboard. After they sign in, they see the shared dashboard in its own browser window without the left navigation pane, not in their usual Power BI portal. They have to bookmark the link to access this dashboard in the future. Which of the following statements is FALSE when sharing visualizations outside of your organisation? a) They can't edit any content in this dashboard or report b) They can change any filters/slicers available on the reports connected to the dashboard and save their changes c) People outside your organization can't see any data if role- or row-level security is implemented on on-premises Analysis Services tabular models d) Only your direct recipients can see the shared dashboard. For example, if you sent b) They can change any filters/slicers available on the reports connected to the dashboard and save their changes Although users can filter and slice your report, they cannot save any changes. With DAX, there are many functions available to shape, form, or otherwise analyze your data. These functions can be grouped into a handful of categories including aggregation, counting, logical etc.The Union DAX command creates a union (join) table from a pair of tables but which statement below is FALSE regarding Union? a) The two tables must have the same number of columns b) The column names in the return table will match the column names in table_expression1 c) Columns are combined by position in their respective tables d) Duplicate rows are removed d) Duplicate rows are removed Duplicate rows are NOT removed The DAX FORMAT function accepts a number of different format strings, some of which can be constructed to create a custom formatter for your exact needs. Study the DAX expression below and choose the right result: FORMAT(-5, "$#,##0.00;($#,##0.00)") a) $5.00 b) ($5.00) c) -$5.00 d) $5,000.00 b) ($5.00) The DAX FORMAT function is used to convert a value to text according to the specified format that you provide as the second argument to the function. Which DAX expression below would format the number so that it's displayed as 20.00%? a) FORMAT( 0.2, "Percent" ) b) FORMAT( 0.2, "p" ) c) FORMAT( 0.2, "%" ) d) FORMAT( 20, "Percent" ) a) FORMAT( 0.2, "Percent" ) The format strings supported as an argument to the DAX FORMAT function are based on the format strings used by Visual Basic (OLE Automation), not on the format strings used by the .NET Framework. Therefore, you might get unexpected results or an error if the argument does not match any defined format strings. For example, "p" as an abbreviation for "Percent" is not supported. Which type of visualisation would you choose if you wanted to show relationships between 3 numerical values and turn the horizontal axis into a logarithmic scale? The worksheet data would include grouped sets of values and you wanted to show patterns in large sets of data, for example by showing linear or non-linear trends, clusters, and outliers? a) Scatter chart b) Bubble chart c) Treemap d) Pie chart b) Bubble chart Bubble charts are a good choice when your data has 3 data series that each contain a set of values. Particularly to present financial data when you want different bubble sizes to visually emphasize specific values. Bubble charts are also great for use with quadrants. Some of the most powerful data analysis solutions in Power BI Desktop can be created by using measures. Measures help us by performing calculations on our data as we interact with our reports. Understanding aggregations is fundamental to understanding measures, because every measure will perform some type of aggregation.Which of the below is TRUE regarding measures in Power BI? a) IF(OR((([HireDate]-[TermDate])*-1) >= 61,ISBLANK([TermDate])),0,1) b) You can reference another measure in a DAX expression by just typing an opening bracket ([) c) Every time you interact with your report, you are changing the context in which a measure calculates and displays its results d) You can create a new measure by clicking on the New Measure button in the ribbon on Power BI Desktop's Home tab a) IF(OR((([HireDate]-[TermDate])*-1) >= 61,ISBLANK([TermDate])),0,1) Information Functions (DAX) look at a table or column provided as an argument to another function and tells you whether the value matches the expected type. What is the result of the following DAX expression? =IF(ISLOGICAL("true"), "Is Boolean type or Logical", "Is different type") a) Is Boolean type or Logical b) Is different type b) Is different type ISLOGICIAL returns TRUE if the value is a logical value; FALSE if any value other than TRUE OR FALSE. The value "true" is a text value and not a logical (boolean) value. As part of a large data project you want to create a dashboard using the Power BI Service. Data will come from a variety of different sources. Do you know which of the following sources would require a Power BI Enterprise Gateway? a) Spark on Azure HD Insight b) SQL Server Analysis Services (on-premise) c) Azure SQL Data Warehouse d) CSV file saved on OneDrive for Business b) SQL Server Analysis Services (on-premise) For databases in the cloud, connections from Power BI to these databases are live, that is, when you've connected to say an Azure SQL Database, and you begin exploring its data by creating reports in Power BI, anytime you slice your data or add another field to a visualization, a query is made right to the database.For databases on-premises, a Power BI Enterprise Gateway is required.If you save your files on OneDrive for Business or SharePoint - Team Sites, and then connect to or import them into Power BI, your dataset, reports, and dashboard will always be up-to-date. Because both OneDrive and Power BI are in the cloud, Power BI can connect directly to your saved file, about once every hour, and check for updates. You can easily add a new custom column of data to your Power BI Desktop model. You can create and rename your custom column using easy buttons to create M formulas that define your custom column.How would you add a custom column? a) Home ribbon - Query Editor - Datasource Settings b) Home ribbon - External Data - Enter Data c) Home ribbon - Relationships - Manage Relationships d) Home ribbon - Query Editor - Edit Queries d) Home ribbon - Query Editor - Edit Queries Once Query Editor is launched and you have some data loaded, you can add a custom column by selecting the Add Column tab on the ribbon, and then selecting Custom Column.