70-778 (use)

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,000.00 D. -$5.00

($5.00)

A retail analysis dashboard contains a small collection of datasets including Sales, Items and Stores. You have dragged the Category field from the Item dataset onto the report canvas. You now want to give users the option to select one, many or all Categories to filter the visuals on the report. Which element should you pick from the visualizations pane? A. Slicer B. Multi-row card C. Funnel D. Card

A. Slicer

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. You cannot refresh data after publishing to the web, the visuals are static C. The user does not have the appropriate row level security permissions D. It takes up to an hour for data to refresh

D. It takes up to an hour for data to refresh After you create your Publish to web embed code and share it, the report is updated with any changes you make. However, it's important to know that it can take a while for update to be visible to your users. Updates to a report or visual take approximately one hour to be reflected in Publish to web embed codes.

Text.StartsWith

Returns a logical value indicating whether a text value substring I was found at the beginning of a string

Text.EndsWith

Returns a logical value indicating whether a text value substring was found at the end of a string.

Text.Insert

Returns a text value with new Value inserted into a text 1 value starting at a zero-based offset.

Text.PositionOf

Returns the first occurrence of substring in a string and returns its position starting at startOffset.

The following table contains a Parent-Child hierarchy on the columns: Employee Key and Parent Employee Key. From the table you can see that employee 112 has no parent defined, employee 14 has employee 112 as manager (ParentEmployeeKey), employee 3 has employee 14 as manager and employees 11, 13, and 162 have employee 3 as manager. The above helps to understand that employee 112 has no manager above her/him and she/he is the top manager for all employees. The PATHLENGTH Function (DAX) returns the number of levels in a given PATH(), starting at current level until the oldest or top most parent level. What would be the PATHLENGTH value for the third row below (values 3 and 14)? Employee KeyParent Employee Key 112 14 112 3 14 11 3 13 3 162 3 117 162 221 162 81 162 A. 2 B. 3 C. 4 D. 1

3 The PATH expression reveals that the third row has a path of: 112|14|3, which of course means the path has a depth of 3, or a PATHLENGTH of 3.

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. ACMESQL\SQL2005 B. SQL2005\ACMESQL C. ACMESQL:SQL2005 D. ACMESQL(SQL2005)

A. ACMESQL\SQL2005 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.

The marketing team needs a new dashboard to show the uptake of social users on the company's corporate forum. The dashboard will contain one particular report that shows the total number of users that registered for the site in each of the months of the year. For this report you want to create a dataset with a calculated column that displays the date with the abbreviated month name followed by the four digit year e.g. Jan 2018 Which DAX expression should you use? A. FORMAT( [RegistrationDate], "MMM yyyy" ) B. FORMAT( [RegistrationDate], "MM yyyy" ) C. FORMAT( [RegistrationDate], "mm yyyy" ) D. FORMAT( [RegistrationDate], "M yyyy" )

A. FORMAT( [RegistrationDate], "MMM yyyy" )

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 change any filters/slicers available on the reports connected to the dashboard and save their changes B. They can't edit any content in this dashboard or report C. Only your direct recipients can see the shared dashboard. For example, if you sent the email to [email protected], only Vicki can see the dashboard D. People outside your organization can't see any data if role- or row-level security is implemented on on-premises Analysis Services tabular models

A. They can change any filters/slicers available on the reports connected to the dashboard and save their changes

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 change any filters/slicers available on the reports connected to the dashboard and save their change B. They can't edit any content in this dashboard or report C. Only your direct recipients can see the shared dashboard. For example, if you sent the email to [email protected], only Vicki can see the dashboard D. People outside your organization can't see any data if role- or row-level security is implemented on on-premises Analysis Services tabular models

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

Power BI comes with a range of visualizations to portray any data story you wish. There is one particular visual that's perfect to help users understand and analyse cashflow during the ups and downs of all months in a financial year. Which visualization is this? A. Waterfall chart B. Treemap C. Scatter chart D. Stacked column chart

A. Waterfall chart

You're creating a fancy little dashboard using Power BI Service. The dashboard shows the accumulative increase in the global population over the last several decades. The dashboard will contain an introductory YouTube video that you have uploaded to the YouTube website. After uploading the video you copy the embed HTML code and paste it into your dashboard.Unfortunately the video is fixed to 560 pixels wide and you would like it to expand to fit the tile size. What should you do? A. Remove the allowfullscreen parameter from the code B. Change the iFrame width and height attribute values to 100% C. Add an extra outer DIV with a style width:auto and height:auto value D. Videos are fixed size and can't expand to fit tiles

B. Change the iFrame width and height attribute values to 100%

As part of a large BI project you have created a new dashboard for consumption by the HR team. After opening the Get Data menu option and selecting SQL Server, you are not sure which Data Connectivity mode to select. If you want the data to automatically refresh as you interact with visualizations which option should you choose? A. Import B. DirectQuery

B. DirectQuery DirectQuery - no data is imported or copied into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop queries the underlying data source, which means you're always viewing current data.

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.

Many data modeling and data transformations are available when using DirectQuery, though with some limitations. Which of the following is NOT a limitation of using DirectQuery? A. All tables must come from a single database B. There is a 1 million row limit for returning data C. Relationship filtering is limited to a single direction, rather than both directions D. 1 GB dataset limitation

D. 1 GB dataset limitation

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

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

You want to create a DAX formula that automatically calculates the previous month number. So if the month was June then the formula would return 5. You have already created the DAX formula below: Today Month = MONTH (Sales[Todays Date]) Which of the DAX formulas below would be the right choice next? A. Previous Month = Sales[Today Month]-1 B. Previous Month = PREVIOUSMONTH(Sales[Today Month]) C. Previous Month = STARTOFMONTH(Sales[Today Month])-1 D. Previous Month = IF (Sales[Today Month] =1, 12,Sales[Today Month]-1)

D. Previous Month = IF (Sales[Today Month] =1, 12,Sales[Today Month]-1) You would need to use an IF DAX function to check if it was December, then simply subtract one from the current month number. The first DAX formula above already returned the month number so it's simply a case of subtracting one. Note, PREVIOUSMONTH is incorrect because it returns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context.

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 B. InsightAzure SQL Data Warehouse C. CSV file saved on OneDrive for Business D. SQL Server Analysis Services (on-premise)

D. 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 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. energy.batch1453.xmlWhich 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

A complicated dashboard for the finance department requires you to combine three separate text fields together to form a concatenation of Country, Region and City. Which DAX expression below would successfully concatenate the three fields together into a single string result? A. CONCATENATE( [Country], ", ", [Region], ", ", [City] ) B. CONCATENATEX( [Country], ", ", [Region], ", ", [City] ) C. [Country] + ", " + [Region] + ", " + [City] D. [Country] & ", " & [Region] & ", " & [City]

D. [Country] & ", " & [Region] & ", " & [City] The CONCATENATE function joins two text strings into one text string. The joined items can be text, numbers or Boolean values represented as text, or a combination of those items. You can also use a column reference if the column contains appropriate values. The CONCATENATE function in DAX accepts only two arguments, whereas the Excel CONCATENATE function accepts up to 255 arguments. If you need to concatenate multiple columns, you can create a series of calculations or, better, use the concatenation operator (&) to join all of them in a simpler expression.

Having created a new blank model you want to load in data from a SQL Server database which is hosted on the same PC as the Power BI Desktop application. You click the Get Date ribbon option and select SQL Server from the drop down list. What should you enter in the Server textbox? A. local B. [blank] C. host D. localhost

D. localhost

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" )

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.

Read the following paragraph carefully and decide whether the entire paragraph is TRUE or FALSE? 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. TRUE FALSE

False

When connected to an Azure SQL Database via Direct Query, any changes made to the table schema are automatically reflected in Power Bl True False

False Schema changes are not picked up automatically. See Azure SQ.la Database with DirectQ_for details on cotltlections to this specific data source.

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)

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.

Say you and your teammates want to distribute your Power BI insights to your organization. The best way to do that is to create an app. An app is a collection of dashboards and reports built to deliver key metrics for your organization.To create an app, you need a app workspace, with your teammates as members. Think of the app workspace as a staging area where you and they can collaborate on your Power BI dashboards and reports. All of you can create reports in Power BI Desktop and publish those reports to the app workspace, and all of you need Power BI Pro licenses.If you just want to share a finished dashboard with colleagues should you add those colleagues to the app workspace? No Yes

No

Which of the following elements can Power Bl Desktop import from an Excel Workbook with a Power Pivot Data Model in it? Raw Data held in the Excel Data Model (2013-2016) KPls (created in Excel PowerPivot) Hierarchies created in Excel PowerPivot Data stored as Tables in Excel PowerPivot External Data Connections Power Query Queries (Add-in for Excel 201 0; Native in Excel 2016) Data Model/PowerPivot Calculated Columns and Measures Data Model Relationships Data Stored in cells on worksheets Raw Data held in PowerPivot add-in (Excel 2010-2013)

Raw Data held in the Excel Data Model (2013-2016) Hierarchies Created in Excel PowerPivot PowerPivot External Data Connections Power Query Queries (Add-in for Excel 201 0; Native in Excel 2016) Data Model/PowerPivot Calculated Columns and Measures Data Model Relationships

Text.Remove

Removes all occurrences of a character or list of characters from a text value.

Which of the following Transformation functions can be applied in Edit Queries? Replace all occurrences of a character/some text with something else in a column Transpose Data Split Column by Delimiter Apply conditional formatting rules to values Define Named Ranges for values/columns to be referred to elsewhere in Edit Queries Unpivot Data

Replace all occurrences of a character/some text with something else in a column Transpose Data Split Column by Delimiter Unpivot Data

Text.Replace

Replaces all occurrences of a substring with a new text value.

Reports are often confused with dashboards since they too are canvases filled with visualizations. But there are some major differences. Which one allows you to filter, highlight, and slice, and also see dataset tables and fields and values? Dashboard Report

Report With a dashboard you can NOT see underlying dataset tables and fields but can export data but can't see tables and fields in the dashboard itself. Also you can't filter or slice

Text.Contains

Returns true if a text value substring was found within a text value string; otherwise, false.

You want to write a DAX expression that checks if the product code contains the letters "ex". Which DAX function below is the right one to use? A. SEARCH( "ex", [ProductCode] ) B. FIND( "ex", [ProductCode] )

SEARCH( "ex", [ProductCode] ) SEARCH returns the number of the character at which a specific character or text string is first found, reading left to right. Search is case-insensitive and accent sensitive, whilst FIND is case-sensitive.

Which type of Power BI visualisation is being described below?Always has two value axes to show one set of numerical data along a horizontal axis and another set of numerical values along a vertical axis. The chart displays points at the intersection of an x and y numerical value, combining these values into single data points. These data points may be distributed evenly or unevenly across the horizontal axis, depending on the data. A. Scatter chart B. Treemap C. Funnel chart D. None of these

Scatter chart

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? Single Both

Single

A large corporate project requires the data team to create a number of different Power BI reports for the company's internet sales data. You need to create a measure that estimates the variance of the SalesAmount_USD column from your InternetSales_USD table, for the entire population. The mathematical formula for this calculation should follow the agreed standard: ∑(x - x̃)²/n Which DAX formula should you use? VAR.P VAR.S

VAR.P VAR.P assumes that the column refers the entire population. If your data represents a sample of the population, then compute the variance by using VAR.S.

What is the data size limit for data models within Power Bl when Power Bl Premium is not being used? A. A 1 GB limit applies to PBI Desktop files (PBIX) regardless of whether it is stored locally or hosted on the PBI Service B. There is no size limit on PBI data models (Either in desktop or PBI Portal) C. It depends on licensing - if you have a Power Bl Pro Licence you can upload files greater than 1 GB (Up to 10GB) to the Power Bl Service, with a free licence you are limited to 1 GB D. There is no size limit on models within Power Bl Desktop, (PBIX files) stored locally however data models larger than 1 GB cannot be uploaded to the PBI Portal under a PBI Pro/Free licence

Whilst Pro licences offer 10GB of space on the PBI Portal per account (as opposed to !GB on a free account), there is an individual size limit of !GB per data model (PBIX file) uploaded. Note that this 1 GB represents highly compressed data so the underlying data source can usually be many times larger. Power BI Premium will, in the future, remove this restriction for premium capacity.