DA-100 Comprehensive Review
What is the difference between "Choose Columns" and "Go to column" features?
"choose columns" removes the deselected columns "go to column" navigates to a specific column in a large dataset
The Impressions table contains approximately 30 million records per month. You need to create an ad analytics system to meet the following requirements:✑ Present ad impression counts for the day, campaign, and Site_name. The analytics for the last year are required.✑ Minimize the data model size. Which two actions should you perform?
A. Group the impressions by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function. B. Create one-to-many relationships between the tables.
What is the purpose of the new Selection pane in the report designer in Power BI Desktop?
A. Its used to hide and show visuals on a page before recording a bookmark --- Selection pane is a story telling features as combined with bookmarks. With selection page you can hide and show visuals, with bookmark you can save the current state of the page.
Which three fields are available when creating a Pie chart visualization?
A. Legend B. Values C. Details
The Customer ID and Address ID columns represent unique rows. You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country for each customer. What should you do?
A. Merge the Customer and Address tables.
In Power BI Desktop Report view, which three options will create a measure on the Sales table?
A. On the fields list click the ellipses next to sales B. Select the sales table in the fields list and then click new measure in the modeling ribbon C. Create the measure from any table and then set the home table properties to Sales. 1. First two options allow you to create a measure directly in the Sales table. 2. Last option allows you to create a measure in any table, and then move it to the Sales table.
You embed a Power BI report in a Microsoft SharePoint Online page.A user named User1 can access the SharePoint Online page, but the Power BI web part displays the following error message: "This content isn"™t available."User1 is unable to view the report.You verify that you can access the SharePoint Online page and that the Power BI report displays as expected.You need to ensure that User1 can view the report from SharePoint Online.What should you do?
A. Publish the app workspace.
You have a Power BI model that contains the following two tables:-> Sales(Sales_ID, sales_date, sales_amount, CustomerID)-> Customer(CustomerID, First_name, Last_name)There is a relationship between Sales and Customer.You need to create a measure to rank the customers based on their total sales amount. Which DAX formula should you use?
A. RANKX(ALL(Sales), SUMX(RELATEDTABLE(Customer), [Sales_amount]))
Which three statements describe the Applied Step's area in the Query Editor window?
A. Records list of steps from connecting to the data source to all the steps applied to the data B. The recorded steps follows the sequence of steps applied to the data C. You can modify the connection to the data source by modifying the source steps settings -- `The Applied steps area records the list of steps you performed on the data starting from the time you connect to the data source. The recorded list follows the sequence of steps you applied to the data and you can modify any of these steps by click on gear button after every step or you can delete that step. Applied steps can abe modified from Advance Editor also.
Which three views are available in Power BI Desktop?
A. Report B. Data C. Relationships
Consider the following DAX formula for a calculated column in the Sales table: UnitPrice = Sales[Revenue] / Sales[Units] Which three statements describe the DAX formula?
A. The value of the calculated column is dependent on the units column B. The formula creates a calculated column called unitprice C. Dependent on the revenue column
Reporting Requirements -Litware identifies the following technical requirements: Executives require a visual that shows sales by region. Regional managers require a visual to analyze weekly sales and returns. Sales managers must be able to see the sales data of their respective region only. The sales managers require a visual to analyze sales performance versus sales targets. The sale department requires reports that contain the number of sales transactions. Users must be able to see the month in reports as shown in the following example: Feb 2020.The customer service department requires a visual that can be filtered by both sales month and ship month independently. You need to review the data for which there are concerns before creating the data model. What should you do in Power Query Editor?
A. Transform the sales_amount column to replace negative values with 0.
What are the limitations of live connection?
A. Transformation cannot be applied to data B. It is not possible to define relationships in live connection
Some of the most powerful data analysis solutions in Power BI Desktop can be created 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. You can reference another measure in dax by starting with [ B. You can create a new measure by clicking on the New Measure button in the ribbon of the PBI desktop home tab
Date[Date] is in the mm/dd/yyyy format. Date[DateKey] is in the ddmmyyyy format. Date[MonthNumber] is in the mm format. Date[MonthName] is in the mmm format.You create the report shown in the exhibit. (Click the Exhibit button.) You need to ensure that the months appear in the order of the calendar. How should you sort the MonthName column?
A. by MonthNumber
View
An object in a relational database, similar to a table. Views have rows and columns, and can contain almost every operator in the SQL language.
You have the following two queries in Power BI Desktop:-> A query named Query1 that retrieves a table named SMB_Customers from a Microsoft SQL Server database-> A query named Query2 that retrieves a table named Enterprise_Customers from an Oracle databaseBoth tables have the same columns.You need to combine the data from SMB_Customers and Enterprise_Customers.Which command should you use?
Append queries
When using the "Split Column by Delimiter" transformation, which option may result in more than two new columns?
At each occurrence of the delimiter Using "At each occurrence of the delimiter", number of columns will depend on number of times a delimiter is used in string/text.
Azure Analysis Services
Azure product that allows you to ingest data from multiple data sources, build relationships between the data, and create calculations on the data. The calculations are built using data analysis expressions (DAX).
Auto-filter is helpful to hide or show rows but rows that are hidden will be loaded to data model. Is this statement correct ? A. Yes B. No
B
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
Q&A can be used against a Direct Query data source connected to Azure SQL Database. Is this statement TRUE ? A. TRUE B. FALSE
B
What is the difference between "Appending queries" and "Merging queries"? A. Nothing, Both are same but different names. B. Appending queries is combining queries vertically and merging queries is combining them horizontally. C. Appending queries is combining queries horizontally and merging queries is combining them vertically.
B
What is the difference between COUNT and COUNTA? A. COUNT counts distinct rows and COUNTA counts all rows. B. COUNT counts non-blank values but COUNTA counts all values including blanks. C. Both are same but different names
B
When writing the DAX expression for a measure, you can assume your DAX code automatically has row context for the table in which the measure is defined. Is this statement TRUE ? A. True B. False
B
Which of the following statements is FALSE with respect to writing DAX expressions that reference calculated columns and measures? A. When referencing a calculated column in a DAX expression, you must include the table name. B. When referencing a measure in a DAX expression, you must include the table name. C. When referencing a measure in a DAX expression, you can optionally include the table name. D. When referencing a measure in a DAX expression, you should prefer not to include the table name.
B
Which option can be used to reverse the sort order of rows in Query Editor? A. There is no option like that. B. Transform > Table > Reverse Rows C. Autofilter D. Sort Descending
B
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
Which programming language is used in creating custom visuals? A. C# B. Java C. TypeScript. D. Python
B, C
Which of the following DAX expressions are valid and can be used for table filters when configuring row-level security (RLS). (Choose multiple) A. [State]="WA" OR [State]="OR" OR [State]="CA" B. OR([State]="WA", OR([State]="OR", [State]="CA")) C. [State]="WA" || [State]="OR" || [State]="CA" D. [State] IN {"WA", "OR", "CA"}
B, C, D
Which shapes are available in Power BI Desktop Report View? (Choose multiple) A. Flowchart B. Triangle C. Rectangle D. Oval
B, C, D
When using Live Connection, what type of transformations can be applied to data?
Basically none. You can't unpivot tables, add custom columns, or transpose
Which option describes when you categorize a numerical identifier into specific ranges?
Binning
You create a dashboard by using the Microsoft Power BI Service. The dashboard contains a card visual that shows total sales from the current year. You grant users access to the dashboard by using the Viewer role on the workspace. A user wants to receive daily notifications of the number shown on the card visual. You need to automate the notifications. What should you do?
C. Create a subscription.
You have a Microsoft SQL Server Analysis Services (SSAS) cube that contains historical data. In Power BI Desktop, you have the following query for the cube.The query retrieves 25,499 records.When you check the data warehouse that is the source of the cube, you discover that there are 26,423 records.You need to ensure that the query retrieves all 26,423 records.What should you do?
C. Delete the Remove Duplicates step.
You have a query that retrieves sales data. A sample of the data is shown in the following table.You need to ensure that the values in the Date column contain a date. Null values must be replaced with the date from the previous row.What should you click on the Transform tab in Query Editor?
C. Fill, and then Down
You have a Microsoft Excel spreadsheet that contains a table named Sales. You need to add the Sales table to a Power BI dashboard as a tile. How should you configure the tile?
C. From the Power BI tab in Excel, pin the table.
From Power BI service, you publish an app that contains one dashboard and one report. Q&A is enabled on the dashboard. In Q&A, a user types the query count of clients and fails to receive any results. The user then types the query count of subscribers and receives the expected results. You need to ensure that the user can use both queries to receive the same results. Which four actions should you perform in sequence?
1. Delete and publish the app 2. Edit the dashboard settings from powerbi.com 3. Edit the synonyms 4. Update the app from powerbi.com
You have a Microsoft Excel workbook that contains two tables. From Power BI, you create a dashboard that displays data from the tables. You update the tables each day. You need to ensure that the visualizations in the dashboard are updated daily. Which three actions should you perform in sequence?
1. Download and install an-onpermises data gateway (personal) 2. Download and install Power BI Desktop 3. Configure the Schedule Refresh settings for the dataset
You are building a dataset from a JSON file that contains an array of documents. You need to import attributes as columns from all the documents in the JSON file. The solution must ensure that date attributes can be used as date hierarchies in Microsoft Power BI reports. Which three actions should you perform in sequence?
1. Expand the records 2. Adds columns that use data type conversions 3. Convert the list to a table
Get Data from Relational Data Sources
1. Get data feature in Power BI Desktop and select the applicable option for your relational database.2. Select the SQL Server option (example)3. enter your database server name and a database name in the SQL Server database window.4. Choose connectivity mode5. Sign in with verification6. Select data to important
How to create a chart by "Asking Question" feature?
1. Modeling -> ask question 2. Double click on report view You can create chart by double clicking on any point on canvas also by clicking ---Ask Question button inside insert tab -> modeling toolbar page
What are the properties of Parameters?
1. Name 2. Description 3. Required 4. Type 5. Suggested Values (any value, list of values)
Which of the following platforms are R visuals supported?
1. Pbi service 2. Reports hosted on pbi embedded(V2) 3. Pbi mobile app 4. Pbi desktop
You have a workspace that contains 10 dashboards. A dashboard named Sales Data displays data from two datasets. You discover that users are unable to find data on the dashboard by using natural language queries. You need to ensure that the users can find data by using natural language queries. What should you do?
C. From the properties of the datasets, modify the Q&A and Cortana settings.
You have a data model that contains many complex DAX expressions. The expressions contain frequent references to the RELATED and RELATEDTABLE functions. You need to recommend a solution to minimize the use of the RELATED and RELATEDTABLE functions. What should you recommend?
C. Merge tables by using Power Query.
You create a report in Power BI Desktop.You need to embed the report into a Microsoft SharePoint Online site.Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.Select and Place:
1. Publish the report to the Power BI Service 2. Obtain and embed link for Sharepoint 3. Add a webpart to a page
Which of the following types of real-time data set in Power BI always store their data in Azure SQL Database?
1. Push datasets 2. Hybrid data sets - Streaming data set don't store data in Azure SQL database
Why are query parameters used?
1. Refine a dataset 2. Add dynamic values 3. Reduce size of a dataset
You open a query in Power Query Editor. You need to identify the percentage of empty values in each column as quickly as possible. Which Data Preview option should you select?
Column Quality
Which of the following actions can you take to get advantage of Power BI Premium?
Configure one or more app workspaces to run within the scope of a premium capacity
How do you connect to excel files that contain target figures? The solution should involve minimum manual work when excel files are created.
Connect to the files with folder connector and use the combine binaries functionality. Connecting to files separately will take time if there are lot of files in the folder. Its always better to connect to folder if files have same structure. If you copy new files in that folder with same structure, that file will be loaded automatically without doing any change to data sources.
1. *Update an app 2. Feature dashboards on colleagues' home 3. Create, edit, delete content in workspace 4. Publish reports to workspace 5. Create a report in another workspace 6. Copy a report 7 Create goals 8. Schedule data refreshes with on-premise gateway 9. Modify gateway connection settings 10. View and interact with an item 11. Read data stored in workspace flow Contributor
Contributor
You possess a Power BI Report with 5 tables arranged in a star schema. The data set is approximately 50 megabytes in size. Your report currently connects to the SQL Server backend using DirectQuery. What would you do to enable Quick Insights?
Convert the report from directquery mode to import mode
You encounter the 1 GB size limitation when uploading and refreshing your PBIX files. What is one way to work around this limitation?
Convert to directquery for your data connections
Your work for a public school system. You organized your data as City, District, School, Grade . Your users want to easily drill down from the highest level of organization to grade. Which of the following would support this in the most user-friendly way?
Create a custom hierarchy with each as a level of granularity
Import Mode
Create a local Power BI copy of your datasets from your data source. You can use all Power BI service features with this storage mode, including Q&A and Quick Insights. However, data refreshes must be done manually.
When you connect to a model in SQL Server Analysis Service, what is shown in the Navigator window? A. List of tables in the model B. List of tables and columns in the model C. List of tables, columns, and fields in the model D. Summary showing the available dimensions and measures.
D
What do you need to append to the URL of a Power BI dashboard in order to open it with the navigation pane collapsed by default?
?collapseNavigation = true When you append ?collapseNavigation=true at the end of a Power BI dashboard URL, it will take you to the Power BI Dashboard with the navigation pane collapsed
Having created a new blank model you want to load in data from 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 be entered in Server text box in the dialog box appeared for database connectivity? A. localhost B. local C. host D. [blank]
A
In Query Editor, blank values are zero-length text string, while nulls are empty values. Is this statement correct ? A. Yes B. No
A
Query B is appended to query A. Which query will be loaded to data model when user click Close & Apply? A. Query A B. Query B C. Both Queries D. None of them
A
What is the difference between "Unpivot columns" and "Unpivot other columns" ? A. "Unpivot columns" will unpivot selected columns where "Unpivot other columns" will unpivot deselected columns. B. "Unpivot columns" will unpivot deselected columns where "Unpivot other columns" will unpivot selected columns. C. Both functions have similar effect.
A
When writing the DAX expression for a calculated column, you can assume your DAX code automatically has row context for the table in which the calculated column is defined. Is this statement TRUE ? A. TRUE B. FALSE
A
When you make a copy of a page in your report by using Duplicate Page, which visualizations from the page are duplicated? A. All visualizations B. Only the selected visualizations C. Only visualizations that have filter relationship D. All visualizations, except the static images
A
Which of the following resources are automatically provisioned when you create a new app workspace in the Power BI Service?
A storage container for pbi resources including datasets, reports, and dashboards. The new workspace experience changes the relationship between Power BI workspaces and Office 365 groups. You aren't automatically be creating an Office 365 group every time you create one of the new workspaces
Flat file
A type of file that has only one data table and every row of data is in the same structure such as comma-separated values (.csv) files, delimited text (.txt) files, and fixed width files, Microsoft Excel workbooks (.xlsx).
Which of the following Azure Active Directory (AAD) security principals can be used to configure the membership of a security role created using the Row-Level Security (RLS) features supported by the Power BI service? (Choose multiple A. An Azure AD user account B. An Azure AD security group C. An Azure AD mail-enabled security group D. An Office 365 group in an Azure Active Directory tenant
A, B, C
Which four options modify the Z order of a visualization? (Choose multiple) A. Send to Back B. Bring to Front C. Bring Forward D. Set Transparent E. Send Backward
A, B, C, E
Which of the following Power BI subscriptions provides licensing which allows user to view Power BI content through the Power BI mobile apps? (Choose multiple) A. Power BI Free (user license) B. Power BI Pro (user license) C. Power BI Premium EM3 (capacity license) D. Power BI Premium (capacity license)
A, B, D
Which of the following field types can be added to the Field well of a Slicer visual? (Choose multiple) A. Native columns returned by a query. B. Calculated columns created using a DAX expression. C. Measures created using a DAX expression. D. Groups created inside a table using the "New Group" command in Power BI Desktop.
A, B, D
Which three data sources allow you to use direct connection? (Choose multiple) A. Azure SQL Database B. Azure SQL Data Warehouse C. Microsoft Access D. SQL Server Analysis Services
A, B, D
What is true about VALUES and DISTINCT functions? (Choose multiple) A. Both functions return distinct rows. B. Values function return all rows but distinct function returns unique values C. Values function receives only physical table and Distinct can also work with table expression D. Distinct function receives only physical table and Values can also work with table expression
A, C
What are the types of values in query parameter? (Choose Multiple) A. Any value B. Dataset C. List of Values D. Query
A, C, D
When designing a data model in Power BI Desktop, which of the following statements are true regarding the rules for creating table relationships? (Choose multiple) A. A relationship between two tables must be defined using a single field from each table. B. A relationship between two tables can be defined using multiple fields from each table. C. At least one column defined in a table relationship must contain unique values. D. You can define multiple relationships between two tables in a data model but only one of the relationships can be marked as active.
A, D
Which two privacy levels can be assigned to a user group? (Choose multiple) A. Public B. Internal C. External D. Private
A, D
Which of the following statements are true with regard to using the Slicer visual in a Power BI report?
A. A slicer visual is based on pbi reports to provide that report consumer with interactive filtering behavior B. A slicer visual based on a date column can be configured to use a slider control C. Slicer cannot be pinned to dashboard because dashboards are not interactive. Measure are not allowed to use in slicer.
You manage a Power BI model that has two tables named Sales and Product. You need to ensure that a sales team can view only data that has a CountryRegionName value of Unites States and a ProductCategory value of Clothing. What should you do from Power BI Desktop?
A. Add the following filters to a report. CountryRegionName is United States ProductCategory is Clothing
You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2019-12-31 at 07:30. You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy. What should you do?
A. Apply a transform to extract the first 11 characters of the Logged column ---- With M Query you can Split Date and Time into Separate Columns by using a transform. ---- Here , A and D both can be used however, since we have to use built-in hierarchy and functions, option C is correct.
What are the possible options to share a dashboard with other users?
A. Click share from the upper right of the dashboard you want to share B. Click the ellipses next to the dashboard name in the dashboard list and then click share button from the actions list C. From the dashboard list click share - Sharing from browser will share URL only however user will not get access to dashboard.
A data analyst publishes several Power BI visualizations to a blog.You discover that some of the visualizations contain data that is considered private by your company.You need to prevent the visualizations from being published to the blog.What should you do?
A. From the Power BI Admin portal, disable the Publish to web setting.
You have a Power BI app named App1. The privacy for the App1 app workspace is set to Private.A user named User1 reports that App1 does not appear in the My organization AppSource. App1 appears in the My organization AppSource for your account.You need to ensure that User1 sees App1 from the My organization AppSource.What should you do?
A. From the app workspace, click Update app, configure the Access setting, and then click Update app.
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 about 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
You have a list of salesmen, you want to merge sales queries to display items sales by salesman. Some salesmen don't have any sales. Which join kind should be used? A. Inner B. Full Outer C. Right Outer D. Left Outer
D
You want to write a measure that calculates the ratio of a month of sales in a year. Which of the following function do you need to use? A. FILTER B. ENDOFYEAR C. WHOLEYEAR D. ALL
D
You have a Power BI model that contains the following two tables:-> Assets (AssetID, AssetName, Purchase_DateID, Value)-> Date (DateID, Date, Month, Week, Year)The tables have a relationship. Date is marked as a date table in the Power BI model.You need to create a measure to calculate the percentage that the total assets value increased since one year ago.Which DAX formula should you use?
D. (sum(Assets[Value]) "" CALCULATE(sum(Assets[Value]), SAMEPERIODLASTYEAR("˜Date"™[Date]))/
You create the following step by using Power Query Editor.- Table.ReplaceValue(SalesLT_Address,"1318","1319",Replacer.ReplaceText,{"AddressLine1"})A row has a value of 21318 Lasalle Street in the AddressLine1 column. What will the value be when the step is applied?
D. 21319 Lasalle Street
You open a query in Power Query Editor. You need to identify the percentage of empty values in each column as quickly as possible. Which Data Preview option should you select?
D. Column quality
You plan to deploy a Power BI app workspace that will be viewed by 10,000 users.You need to ensure that dashboard data can be updated every 30 minutes. What should you do?
D. Configure the app workspace for Premium capacity.
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?
D. Datasets
Reporting Requirements -Litware identifies the following technical requirements: Executives require a visual that shows sales by region. Regional managers require a visual to analyze weekly sales and returns.Sales managers must be able to see the sales data of their respective region only.The sales managers require a visual to analyze sales performance versus sales targets.The sale department requires reports that contain the number of sales transactions.Users must be able to see the month in reports as shown in the following example: Feb 2020.The customer service department requires a visual that can be filtered by both sales month and ship month independently.You need to create a calculated column to display the month based on the reporting requirements.Which DAX expression should you use?
D. FORMAT('Date' [date_id], "MMM YYYY")
Your organization has a Microsoft Office 365 subscription. When the users attempt to access the Power BI Service, they receive the error message shown in the exhibit. You need to ensure that all the users can access the Power BI service. What should you do first?
D. From Microsoft Azure PowerShell, run the Set-MsolCompanySettings cmdlet.
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?
D. From the Office 365 Admin center, click Groups.
You have a new table named Fiscal that has the same schema as the Date table, but contains the fiscal dates of your company.You need to create a report that displays the total sales by fiscal month and calendar month. What should you do?
D. Merge Fiscal into the Date table.
You have a sales report in an app workspace. The report displays a map of sales by location and a bar chart of sales by year. The report has a slicer to filter the data by year.You need to create a dashboard that contains visualizations. The solution must ensure that you can use the slicer to filter the data by year.What should you do?
D. Pin the report as a live page.
Import Data for SQL Query
Import data is to write an SQL query to specify only the tables and columns that you need.To write your SQL query, on the SQL Server database window, enter your server and database names, and then select the arrow next to Advanced options to expand this section and view your options. In the SQL statement box, write your query statement, and then select OK.
Capabilities: 1. Add members or others with lower permissions 2. Publish, unpublish, change permission for app 3. Update an app 4. Share an item or share app 5. Allow others to reshare 6. Feature apps on colleagues' Home 7. Manage dataset permission 8. Feature dashboards on colleagues' home 9. Create, edit, delete content in workspace 10. Publish reports to workspace 11. Create a report in another workspace 12. Copy a report 13. Create goals 14. Schedule data refreshes with on-premise gateway 15. Modify gateway connection settings 16. View and interact with an item 17. Read data stored in workspace flow
Member
You have a data model that contains many complex DAX expressions. The expressions contain frequent references to the RELATED and RELATEDTABLE functions.You need to recommend a solution to minimize the use of the RELATED and RELATEDTABLE functions. What should you recommend?
Merge tales by using powerquery
You import two Microsoft Excel tables named Customer and Address into Power Query. Customer contains the following columns:-> Customer ID-> Customer Name-> Phone-> Email Address-> Address IDAddress contains the following columns:-> Address ID-> Address Line 1-> Address Line 2-> City-> State/Region-> Country-> Postal CodeThe Customer ID and Address ID columns represent unique rows.You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country for each customer.What should you do?
Merge the customer and address tables Explanation: There are two ways of combining queries: merging and appending.-> When you have one or more columns that you need to add to another query, you should merge the queries.-> When you have additional rows of data that you need to add to an existing query, you should append the query
Months are sorted alphabetically in Stacked bar chart. What can you do to sort it by month number?
Modeling -> select month column -> sort by month number
Imagine you have created a query named Customers in a Power BI Desktop to append the rows from three external tables with customer data into a single output table named Customers. What would be the best approach to add a new fabricated ID column to the Customers table to ensure that every row in the Customers table contains a unique ID value?
Modify the customers query by adding an index column Index column option is available in query editor to add indexed column starting from 0, 1 or custom number.
You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records. During the development process, you need to import a sample of the data from the Order table. Does this meet the goal?
NO Solution: From Power Query Editor, you import the table and then add a filter step to the query. YES Solution: You add a WHERE clause to the SQL statement.
You create a parameter named DataSourceExcel that holds the file name and location of a Microsoft Excel data source. You need to update the query to reference the parameter instead of multiple hard-coded copies of the location within each query definition. Does this meet the goal?
NO Solution: In the Power Query M code, you replace references to the Excel file with DataSourceExcel. YES Solution: You modify the source step of the queries to use DataSourceExcel as the file path. NO Solution: You create a new query that references DataSourceExcel.
Moving a query into group increases the performance of the query. Is this statement correct?
No
The account you use for registering an app for embedding Power BI content doesn't need a Power BI Pro license allocated to it. Is this statement true?
No
You want to import data from Microsoft Access database to Power BI desktop but there is an error appearing on the screen. "The 'Microsoft.ACE.OLEDN.12.0' provider is not registered on the local machine". What is the reason for this error?
Provider is not installed in the system
You have four queries named Query 1, Query 2, Query 3, and Query 4 in the Power BI desktop. You are creating Query 5, which will be appended at the end of Query 3. When you load the data, which queries will be refreshed?
Query 3 and query 5 Query 5 is new query so it should be refreshed. Query 5 is appended to query 3 so there is change in query 3 that should be refreshed in data model also.
Which view shows the graphical representation of data tables structure in query editor?
Query dependency. Query dependency reflects the structure and hierarchy of tables in graphical format.
Power BI tries to translate transformations into the data source's native language where possible. What is this step called?
Query folding
Which two of the following strategies are recommended when designing the queries to import data from an OLTP-style database into a Power BI Desktop project?
Refactor imported tables into a star schema denormalize the data model to make it better suited for data analysis and reporting. Denormalization and star schema is desired to transform the data in the format that is best fit for visualizations.
Which command in the Power BI Desktop query editor is used to create a new query that uses the output of another query as its input source?
Reference. When you reference a query, the new query will have only one step - sourcing from the original query. A referenced query, will not have the applied steps of the original query
What can you accomplish with data gateway?
Refresh data. Gateway is used to refresh the data from on-premises servers to online Power BI Service
Which three transformations are available when you right-click a column header in the Query Editor window?.
Remove Duplicate Change type Count Rows option is not there when you right click on column
Data has been scraped from a table on a popular retirement website. However, the Health care quality column's scores were not automatically transformed from text to numbers when Query Editor loaded the table. You thus right-clicked the column header, and selected Change Type > Whole Number to change them.Unfortunately, the Health care quality column contains a few times in states' rankings, which was noted on the website by the word (tie) after their numbers. Query Editor thus reports a few errors.What is the consequence of using the Remove Errors option (ribbon or the right-click menu option) to resolve this?
Removes any rows with errors The Remove Errors (using the ribbon or the right-click menu) removes any rows that have errors
In Power BI Desktop, what entity creates new hierarchies?
Report author (you)
Which views can be used in Power BI Desktop Data Model to create a new dimensional hierarchy?
Report view Data view
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 data set tables and fields and values?
Reports
Which of the following are advantages that Power BI reports have over Power BI dashboards?
Reports provide a better interactive filtering and drilldown experience Reports provide story telling capabilities using bookmarks Report definitions can be saved using an external file format (PBIX) and checked into source control Reports don't support streaming datasets. This feature is available in dashboard tiles.
What is the function of Row Level Security in Power BI?
Restrict data access to given users
Which feature allows different users to view the same report but access different subsets of data?
Row level security
You have a prospective customer list that contains 1,500 rows of data. The list contains the following fields: -> First name -> Last name -> Email address -> State/Region -> Phone number You import the list into Power Query Editor. You need to ensure that the list contains records for each State/Region to which you want to target a marketing campaign. Which two actions should you perform? Each correct answer presents part of the solution.
Select column profile Select column distribution
You have the following three versions of an Azure SQL database: ✑ Test ✑ Production ✑ Development You have a dataset that uses the development database as a data source. You need to configure the dataset so that you can easily change the data source between the development, test, and production database servers from powerbi.com. Which should you do?
Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function.
You have the following three versions of an Azure SQL database:-> Test-> Production-> Development. You have a dataset that uses the development database as a data source. You need to configure the dataset so that you can easily change the data source between the development, test, and production database servers from powerbi.com.Which should you do?
Set the data source privacy level to organizational and use the replace value power query m function
Navigator Window (Power BI Desktop)
Shows you the data that is available in your data source. You can select a table or entity to preview its contents, to ensure that the correct data is loaded into the Power BI model. Select the check box(es) of the table(s) that you want to bring in to Power BI. This selection activates the Load and Transform Data buttons as shown in the following image.
You have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate. Date contains one column named Date. The tables have the following relationships: -> Sales[DueDate] and Date[Date] -> Sales[ShipDate] and Date[Date] -> Sales[OrderDate] and Date[Date] The active relationship is on Sales[DueDate]. You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.
Solution: You create two copies of the Date table named ShipDate and OrderDateGet. You create a measure that uses the new tables.
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. example.abc123.xml Which Transform function will dissect the filename values into two separate columns, one for the filename and the other for the file extension e.g. XML
Split column, split at right most delimiter
You have a large dataset that contains more than 1 million rows. The table has a DateTime column named Date. You need to reduce the size of the data model. What should you do?
Split the date column into two columns. One that contains only the time and another that contains only the date
You are modeling data in a table named SalesDetail by using Microsoft Power BI. You need to provide end users with access to the summary statistics about the SalesDetail data. The users require insights on the completeness of the data and the value distributions. Which three actions should you perform in sequence?
Step 1: Create a blank query as a data source Step 2: Specify the following query, then close and apply. -Table.Profile(#ֲ¨SalesDetail") Step 3: Create a visual for the query table.
Which Q&A feature allows you to specify alternate terms for business concepts?
Synonyms
Which feature is used to create alternative name for fields in tables. It will make it much easier for users to ask questions on dashboards?
Synonyms
When connecting to an Access database, which entities are shown in the Navigator window?
Tables from the access database. - When you connect to Access database, you will see list of tables that you can import into Power BI Desktop as queries
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 the current level until the oldest or topmost parent level. What would be the PATHLENGTH value for the third row below (values 3 and 14)? Employee Key Parent Employee Key
The PATH expression reveals that the third row has a path of: 112|14|3, i.e. the path has a depth of 3, or a PATHLENGTH of 3
Which of the following is a requirement for a user with a Power BI Pro license to share a dashboard with users who have the Power BI Free license?
The dashboard must be created in a workspace that is running with a premium capacity Without premium capacity, dashboard cannot be shared with the users who have free license
What happens when you click on Focus mode of a tile in Power BI dashboard?
The tile you selected expands and takes the full space
Consider the following custom column formula: = if [Country] = "USA" then "United States" else [Country] Which of the following statements describes the value of the custom column formula?
The value will either be United States or whatever the value of the country column is
Which component embeds a Power BI Report in Share Point Online?
The web part
What happens to the data types of Query Editor that are not available in data model while loading data?
These types will be converted to related data types
Which three properties of a tile can be edited in the Power BI dashboard from the Tile details window?
Title Subtitle Destination link
What is the primary purpose of the on-premises data gateway?
To allow refreshing on-premises data sources from the pbi service
What is the purpose of Power BI Personal Gateway?
To provide quick and secure data transfer between pbi service and on premises data sources that support refresh
Is Q&A, Cortana and External Sharing is disabled for Power BI Reports that use Row Level Security (RLS)?
True
Power BI doesn't have an R engine built into it, you need to have an installation of R on your machine in order to generate/use R visuals. Is this statement TRUE ?
True
In order to use the Power BI web part in SharePoint Online, the report author as well as all the report consumers require a Power BI Pro license. Is this statement true ?
True. All Sharepoint report viewers must have a Power BI Pro License.
The CEO wants to share the environmental report created in Power BI with external users. The report does not contain any sensitive data. How can this be achieved? Your solution must involve minimal effort.
Use publish to web feature The answer is C. Because the users with whom the report should be shared with might change over time, it is best to publish the report to web. Adding external users to ActiveDirectory or an app workspace would involve extra costs
Why use DirectQuery
Useful when you do not want to save local copies of your data because your data will not be cached. Instead, you can query the specific tables that you will need by using native Power BI queries, and the required data will be retrieved from the underlying data source. Suited for when you have large datasets to pull data from.
What could be the reason of error "This content isn't available. Learn more about Power BI" in Power BI?
User doesn't have access to report User doesn't have pbi pro license Pbi report was deleted after embedding
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.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 true about using variables in DAX?
Variables are created only one time in the current context
1. View and interact with an item 2. Read data stored in workspace dataflows
Viewer
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 analyze cash-flow during the ups and downs of all months in a financial year. Which visualization is this?
Waterfall chart A waterfall chart shows a running total as values are added or subtracted. It's useful for understanding how an initial value (for example, net income) is affected by a series of positive and negative changes.
Select the feature that allows you to navigate through HTML tags to get the data from website?
Web scraping Web Scraping is the new feature of Power bi that allows to navigate through HTML tags to get data from website
What feature would you use to specify different values for theoretical scenarios, such as different values for annual interest
What-if parameters
Connect to Data in an Application
When connecting to data in an application, you would begin in the same way as you would when connecting to the other data sources: by selecting the Get data feature in Power BI Desktop. Then, select the option that you need from the Online Services category. In this example, you select SharePoint Online List. After you have selected Connect, you'll be asked for your SharePoint URL. This URL is the one that you use to sign into your SharePoint site through a web browser. You can copy the URL from your SharePoint site and paste it into the connection window in Power BI. You do not need to enter your full URL file path; you only need to load your site URL because, when you are connected, you can select the specific list that you want to load. Depending on the URL that you copied, you might need to delete the last part of your URL, as illustrated in the following image.After you have entered your URL, select OK. Power BI needs to authorize the connection to SharePoint, so sign in with your Microsoft account and then select Connect.
You publish a report from Power BI Desktop to Power BI service. You then make changes to your local report in Power BI Desktop by adding some visualizations. When do these changes synchronize to Power BI service?
When you republish the powerbi desktop report
Which of the following is true for creating a column from an example?
When you type 1 or more values in a column, pbi analyzes if that value matches the data from selected columns or all columns in the table then it will fill remaining column values according to the matched criteria You can add new columns of data to your model using by simply providing one or more sample values for your new column. New column can be created from a current selection or provide input based on all (or selected) columns in a given table.
When is it preferable to use a calculated column instead of a measure?
When you want to use the value in a filter or slicer
Which two field options are available when creating a waterfall chart?
Y-axis and category
You are asked to create a report with the population data from a public website. To get population data in Power BI from public website: Select Get Data -> Web -> Paste Website Link -> Click connect in Anonymous tab. Then in the navigation pane select table that you want to import to Power BI. Do these steps fulfill the requirement to import data from a public website to Power BI?
Yes
You have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate.Date contains one column named Date.The tables have the following relationships:-> Sales[DueDate] and Date[Date]-> Sales[ShipDate] and Date[Date]-> Sales[OrderDate] and Date[Date]The active relationship is on Sales[DueDate]. ou need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.Solution: You create a calculated table. You create measures that use the CALCULATE, COUNT, and FILTER DAX functions. Does this meet the goal?
Yes
You have a user named User1. User1 is a member of a security group named Contoso PowerBI.User1 has access to a workspace named Contoso Workspace.You need to prevent User1 from exporting data from the visualizations in Contoso Workspace. Solution: From the Microsoft Office 365 Admin center, you modify the properties of Contoso PowerBI. Does this meet the goal?
Yes
Change Data Source Settings in Power Query
You can also change your data source settings from within Power Query. Select the table, and then select the Data source settings option on the Home ribbon. Alternatively, you can go to the Query Settings panel on the right side of the screen and select the settings icon next to Source (or double Select Source). In the window that displays, update the server and database details, and then select OK.
Which of the following statements are true with regard to designing Power BI content for mobile devices?
You can improve the experience of viewing reports in the mobile apps by creating a portrait layout. In Power BI Desktop and the Power BI service, you rearrange and resize report visuals for an optimal experience in portrait mode.
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
Which page view types are available in the Power BI Report View?
a. Fit to page b. Fit to width c. Actual size
Which native languages can be used when designing a query in Power BI Desktop?
a. M b. SQL c. MDX
Which types of datasets can be used to create a dashboard with streaming data tiles?
a. Streaming datasets b. Hybrid datasets
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?
b) Edit the interactions form the Format menu.
All office 365 Global admins and Power Bi admins are automatically capacity admins of both Power Bi premium capacity and Power BI embedded capacity
true
Which of the following cardinalities are available in Power BI Table?
One to One One to Many Many to Many
1. Update and delete workspace 2. Add/remove people (including admins) 3. Allow Contributors to update the app for the workspace
Only Admin
Which setting would you customize to add a "PRIVATE" watermark to your report?
Page background
By default, which filter level behaves the same as a slicer in a report in Power BI Desktop?
Page level filter. When you create a slicer in a report, the slicer will filter the visualizations on the current page, similar to a page level filter
You create a report in the Power BI service that displays the following visualizations:-> A KPI that displays the count of customers-> A table that displays the count of customers by country-> A line chart that displays the count of customers by year. You need to receive an alert when the total number of customers reaches 10,000. What should you do first?
Pin the report to a dashboard
Which data category setting is used to geographically map column values to United States cities when the column contains values in city, state format such as Dallas, TX and Tampa, FL?
Place
Ways to Change Source File
Power Query provides a number of ways for you to accomplish this task, so that you can make this type of change when needed.- Data source settings- Query settings- Advanced Editor
To create a DAX formula that automatically calculates the previous month number as calculated column, for example if the month is 6 then formula should return 5. Month columns is calculated from date with formula below. Month = MONTH (Sales[Date]). Which of the following DAX formulas returns correct number for previous month?
Previous month = IF(sales[month] = 1 , 12, Sales[month]-1) You can use an IF condition in DAX function to check if Month is 1 then return 12 else subtract one from the month column. Sales[Month]-1and STARTOFMONTH(Sales[Month])-1 returns correct numbers except Month 1. For month 1 it will return 0 which is wrong. PREVIOUSMONTH is incorrect because it returns a table that contains a column of all dates from the previous month.
Import JSON File
- After you have connected to the database account, the Navigator window opens, showing a list of databases under that account. Select the table that you want to import. In this example, you will select the Product table. The preview pane only shows Record items because all records in the document are represented as a Record type in Power BI.Select the Edit button to open the records in Power Query.-In Power Query, select the Expander button to the right side of the Column1 header, which will display the context menu with a list of fields. Select the fields that you want to load into Power BI Desktop, clear the Use original column name as prefix checkbox, and then select OK.
Getting data from Azure Analysis Services cubes
- Authenticate to the server.- Pick the cube you want to use.- Select which tables you need.
Which of the following visualization types support configuring dashboard alerts?
- Card - KPI - Gauge Alerts can only be set on tiles pinned from report visuals, and only on gauges, KPIs, and cards. Alerts can be set on visuals created from streaming datasets that you pin from a report to a dashboard. Alerts can't be set on streaming tiles created directly on the dashboard using Add tile > Custom streaming data
What are the advantages of installing Power BI Desktop from the Microsoft Store instead of installing it using the standard MSI download?
-Installation does not require administrative privileges -Monthly updates for pbi are automatically installed in the background -It lowers the total cost of ownership for an organiztions IT department Access to content in Office 365 and Microsoft Azure does not differ if application is installed from Store or with MSI downloaded file. Installation does not require administrative privileges because Microsoft Store is authentic source for installing applications. Microsoft Store automatically install updates when new update arrives. It also reduces to cost of ownership for IT department to distribute applications through MS Store to a large number of devices.
What is the file size limit to publish a .PBIX file to Power BI Service Free?
1 GB
Live Connection is very similar to DirectQuery in the way that it works with the data source. It will not store data in Power BI, and it will query data source every time. However, it is different from DirectQuery. Live Connection is only supported for these data sets;
1) SQL Server Analysis Services (SSAS) Tabular 2) SQL Server Analysis Services (SSAS) Multi-Dimensional 3) Power BI Service
Which three options allow you to hide a field from the Report view in Power BI Desktop?
1, In pbi desktop data view right click the column header on the respective table and click hide in report view 2. In pbi desktop relationship view right click the field and then click hide in report view 3. In pbi desktop report view right click the field in the fields list and click hide i. If you delete a field from the fields list, the field will be permanently deleted from the Power BI model instead of Report View only.
You have a table named Sales. Sales contains the data shown in the following table. You have the following measure. Total Sales This Year = SUM([Total Sales])You plan to create a KPI to compare the current yearly sales to the previous year as shown in the exhibit. You need to create the measure for the goal. How should you complete the DAX formula?
1. CALCULATE ([Total Sales This Year], DATEADD ('Date[Date],-1,YEAR))
Which of the following operations can be accomplished by a developer using the Power BI Service API (aka Power BI REST API)?
1. Create a new dataset 2. Adding rows to an existing dataset 3. Refreshing an existing dataset --- Not allowed to retrieve data from data set using Power BI Service API
Which of the following Power BI report settings can be customized using a report theme?
1. Default properties for new visuals that are added to the report 2. Sequence of custom colors that are used by the visuals in the report
In which of the following scenario refreshing an imported data set in the Power BI Service require either the on-premises gateway or a personal gateway?
1. Refreshing a dataset which imports its data from an on premise SQL database 2. Refreshing an imported dataset that relies on an r input script as its source - SharePoint and Azure SQL database are online Microsoft technologies that doesn't require On-Premise gateway but if your database or any other source is hosted on-premises(local or remote server) it would require Power Bi Gateway to be installed on that Server or any other server or PC with the network access to that server. Power BI refresh will connect to that server using Power BI Gateway to refresh the data(Server to Power BI Service Report).
From which two options can you customize your report page layout?
1. Report ribbon, page view option 2. Visualizations, format option
What are the types of row context?
1. Row context 2. Filter context
Which two cross filter directions are available?
1. Single 2. Both (cross-filter)
Which of the following report page details are recorded when you create a bookmark?
1. The sort order of rows in a table visual 2. The current selections for each slicer on the page 3. Whether a visual has been spotlighted or hidden --- With bookmarks, you capture the currently configured view of a report page, including filtering and the state of visual
What are the three reasons for which you might use a calculated table?
1. To perform different types of merge join 2. To create a table based on the result of a function or formula 3. To union tables
Which of the following statements are true with respect to setting the Cross-filter Direction property for a table relationship based on two tables with a one-to-many relationship?
1. When the cross filter direction is set to single the tabular database engine will only propagate the context filter in one direction. One side to many side 2. When cross filter set to both, propagate context filter in both directions 3. Can't always set it to both because this can create ambiguity
What are the limitations of publishing a report to website?
1.R visuals are not supported 2.Row level security won't work 3.Reports that connect live to on premises analysis services tabular are not supported
Which two visualizations can you embed into the website?
2. Uses a dataset that is stored in Microsoft OneDrive for Business 3. Is a custom visual
You create the following step by using Power Query Editor.Table.ReplaceValue(SalesLT_Address,"1115","1116",Replacer.ReplaceText,{"AddressLine1"})A row has a value of "21115 Hollywood Street" in the AddressLine1 column.What will the value be when the step is applied
21116 hollywood street
When you share a dashboard with people outside your organization, they get an email with a link to the shared dashboard, and they need a Power BI Pro license to access 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 dashboard outside of your organization? A. They can change any 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 row-level security is implemented on on-premises Analysis Services tabular models
A
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 pre-filtered 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 eq NC B. ?filter=Store/Territory = NC C. ?filter=Store[Territory] eq NC D. ?filter=Store/Territory == NC
A
Which transformation option converts rows to columns and columns to rows? A. Transpose B. Pivot Column C. Unpivot Columns D. Unpivot Other Columns
A
What si the file format used to define a theme for a Power BI Report?
A Javascript Object Notation (JSON)
The definitions and attributes of products, departments, and business units must be consistent across all reports.The board must be able to get the following information from the quarterly reports: Revenue trends over time. Ending balances for each account. A comparison of expenses versus projections by quarter. Changes in long-term liabilities from the previous quarter. A comparison of quarterly revenue versus the same quarter during the prior year. What is the minimum number of Power BI datasets needed to support the reports?
A. two imported datasets
Your company plans to use Power BI for 20 users in the sales department. The users will perform the following tasks: - Access a published Power BI app. - Modify reports in an app workspace. - Share dashboards created in My Workspace. You need to identify which Power BI licenses are required for the tasks. The solution must use the Power BI (free) license, whenever possible. Which license should you identify for each task?
Access a published Power BI App: Power BI (free) Modify reports in an app workspace: Power BI Pro Share dashboards created in My Workspace: Power BI (free)
You plan to use Power BI Desktop to import 100 CSV files.The files contain data from different stores. The files have the same structure and are stored in a network share.You need to import the CSV files into one table. The solution must minimize administrative effort.What should you do?
Add a folder data source and use the Combine Files command.
All capabilities available in PowerBI Service
Admin
Which DAX function removes filters from a table?
All
WHERE Clause
All queries should also have a WHERE clause. This clause will filter the rows to pick only filtered records that you want.
You have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate.Date contains one column named Date.The tables have the following relationships:-> Sales[DueDate] and Date[Date]-> Sales[ShipDate] and Date[Date]-> Sales[OrderDate] and Date[Date]The active relationship is on Sales[DueDate]. ou need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.Solution: You create a calculated table. You create a measure that uses the new table. Does this meet the goal?
B. No
You have a custom connector that returns ID, From, To, Subject, Body, and Has Attachments for every email sent during the past year. More than 10 million records are returned. You build a report analyzing the internal networks of employees based on whom they send emails to. You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size. What should you do?
B. Remove the Subject and Body columns during the import.
You have an Azure SQL database that contains sales transactions. The database is updated frequently. You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five minutes of an update. How should you configure the data connection?
B. Set Data Connectivity mode to DirectQuery.
Your company has training videos that are published to Microsoft Stream. You need to surface the videos directly in a Microsoft Power BI dashboard. Which type of tile should you add?
B. custom streaming data
A business user reports that Q&A does not work on a certain dashboard. Which of the following is NOT a possible reason for this? A. Q&A is disabled for this dashboard. B. The only data source uses DirectQuery. C. There is a custom visual in one of the tiles. D. Row-level security is enabled for all datasets used in the dashboard.
C
In Query Editor, what will be the result if you combine a text string with null value? A. Text String B. Text String + null C. null D. Blank Value
C
UNION function in DAX works similar to which function in Query Editor? A. JOIN B. DUPLICATE C. APPEND D. MERGE
C
What do you need to do to prepare an Excel workbook before uploading it to Power BI? A. Format all numeric data with two decimal points. B. Ensure that the data is following the star schema. C. Format the data as an Excel table. D. Name the Excel worksheet appropriately.
C
What happens when you connect to an Excel workbook that is hosted at OneDrive for Business from Power BI? A. The Excel workbook will be uploaded to Power BI. B. The Excel data model will be uploaded to Power BI. C. The Excel Report will be created in Power BI. D. The Excel Power View report will be converted to a Power BI report.
C
Which function can be used in inactive relationship? A. RELATED B. RELATEDRELATIONSHIP C. USERELATIONSHIP
C
Which of the following DAX functions does not return a table? A. Filter B. AllExcept C. Calculate D. Values
C
Which one is not a time intelligence function? A. DATESYTD B. DATEMTD C. DATEDTD D. DATEQTD
C
You need to append Query B to query A. But there is one extra column in query B. What should you do ? A. Delete extra column in Query B. B. Create column with same name in Query A. C. No need to do anything. Appending query will create that column where null will be the value for all the rows that are from query A.
C
You need to filter out totals from target figures. Which function does that? A. Table.Filter B. CALCULATETABLE C. Table.SelectRows D. Table.FilterRows
C
The model has the following relationships:-> Store to District based on DistrictIDSales to Store based on LocationID-> Sales to Date based on PeriodID-> Sales to Item based on ItemIDYou configure row-level security (RLS) so that the district managers of the stores only see the sales from the stores they manage. When the district managers view the Store by Items report, they see items for all the stores. You need to ensure that the district managers can see items for the stores they manage only. How should you configure the relationship from Sales to Item?
C. Change the Cross filter direction to Both
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in shared capacity of powerbi.com. The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The dataset is scheduled to refresh twice a day at 08:00 and 17:00. The report is a single page that contains 15 AppSource visuals and 10 default visuals. Users say that the report is slow to load the visuals when they access and interact with the report. You need to recommend a solution to improve the performance of the report. What should you recommend?
C. Change the imported dataset to DirectQuery.
You have a Microsoft SharePoint Online site named Sales. Your company has 1,000 sales users. All the sales users can access Sales. You create a report in an app workspace in the Power BI service. You embed the report into a page on the Sales site by using the Power BI web part. You need to ensure that all the sales users can view the report from the Sales site. What should you do?
C. Configure the app workspace for Premium capacity.
You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59. You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy. What should you do?
C. Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date.
You create a report in the Power BI service.You plan to provide external users with access to the report by publishing the report to a public blog.You need to ensure that the report in the blog post will be updated as the data is refreshed.What should you do in the Power BI service?
C. Publish the report to the web. In the blog post, use the embed code URL.
In the Power BI service, you create an app workspace that contains several dashboards.You need to provide a user named [email protected] with the ability to edit and publish dashboards.What should you do?
C. Share the dashboard, and then modify the Access settings of the dashboard.
You have an app workspace that contains two datasets named dataset1 and dataset2. Dataset1 connects to a Microsoft Azure SQL database. Dataset2 connects to a Microsoft Excel file stored in Microsoft OneDrive for Business.You create a report named Report1 that uses dataset1.You pin Report1 to a dashboard named Dashboard1.You publish the app workspace to all the users in your organization.You need to delete dataset2 from the app workspace.What should you do first?
C. Unpublish the app
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in shared capacity of powerbi.com.The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The dataset is scheduled to refresh twice a day at 08:00 and 17:00.The report is a single page that contains 15 AppSource visuals and 10 default visuals.Users say that the report is slow to load the visuals when they access and interact with the report.You need to recommend a solution to improve the performance of the report.What should you recommend
Change the imported dataset to direct query
You created a line chart that shows Quantity by Month and noticed month values are sorted alphabetically. How can you rectify this? Your solution should involve minimal effort.
Create a month number column from date or month name then use the sort by column feature in modeling tab and sort month by month number. - When you sort Month column by Month Number then month names will appear in the right order in all visuals. -The ellipsis menu option in answer A only allows you to change the sort order from ascending to descending or sort by Quantity instead of Month. - Creating a measure for each month, like in answer C, will not solve the problem, because using multiple measures is like using a legend, you will see multiple dots instead of a line
The Impressions table contains approximately 30 million records per month.You need to create an ad analytics system to meet the following requirements:-> Present ad impression counts for the day, campaign, and Site_name. The analytics for the last year are required.-> Minimize the data model size.Which two actions should you perform? Each correct answer forms part of the solution
Create a one to many relationship between the tables Group the impressions by Ad id , site name, and impression date. Aggregate by using the count rows function
To create a column chart that displays Quantity by Color and can be drilled down to show Quantity by Stock Item. Which of the following solves the problem? More than one answer can be correct.
Create a stock item hierarchy that includes color and stock item and use the hierarchy on axis Put color and stock item on axis The correct answers are B and C. With either option, you will be able to drill down in the hierarchy. Answer D is wrong because you cannot use the legend to drill down. If you follow the option A, you will not see stock item names in the visual.
You want to create a line chart showing Quantity by the invoice date. You notice that there is a relationship between the Date from the Date table and the Invoice Date Key from the Sale table, but the relationship is inactive. All other visuals in your report will be analyzing values by delivery date. How should you approach this problem?
Create the following measure: Quantity by Invoice Date = CALCULATE(SUM( Sale[quantity]), userelationship('date'[date], sale[invoice date key])). The answer is To create a new measure, Because this is going to be a one-off visual, creating a measure that activates the relationship at query time solves the problem and involves the least effort. If you delete the relationship based on delivery date, other visuals that use the Date table will display the wrong values. Activating one more relation is not possible because no more than one relationship can be active at a time. Using TREATAS will result in the wrong figures.
A business user wants to distribute a set of reports with certain users. The reports were created in an app workspace. What is the best way to share the reports? Your solution must involve minimal effort for all parties involved. A. Share each report with each user. B. Create a security group containing all necessary users and share reports with the security group. C. Invite all users in the app workspace with the reports. D. Create an app and install it automatically.`
D
Assume you have just completed designing a report in Power BI Desktop in which you have imported a custom visual such as the Hierarchy Slicer. What are the extra steps required when publishing the PBIX file containing the Power BI Desktop report to an app workspace to ensure that your report can properly display the custom visual in the browser? A. You must import the custom visual into the target workspace before publishing the Power BI Desktop project. B. The tenant administrator must install the custom visual at tenant-level scope. C. The tenant administrator must mark the custom visual as Safe within the current tenant using the GUID-based ID of the custom visual. D. There are no extra steps because custom visual distribution is transparent once you have imported the custom visual into a Power BI Desktop project.
D
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
To which dashboard a visual created by the natural language query is pinned? A. The last dashboard you visited B. The last dashboard you created C. A new dashboard will be created D. The dashboard you selected in the Pin to dashboard window
D
What is the extension of a Power BI custom visual file downloaded from the Power BI visual's gallery? A. .pbix B. .pbivis C. .pbiv D. .pbiviz
D
You import the list into Power Query Editor. You need to ensure that the list contains records for each State/Region to which you want to target a marketing campaign. Which two actions should you perform? Each correct answer presents part of the solution.
D. Select Column distribution. E. Select Column profile.
You have a large dataset that contains more than 1 million rows. The table has a datetime column named Date. You need to reduce the size of the data model without losing access to any data. What should you do?
D. Split the Date column into two columns, one that contains only the time and another that contains only the date.
Which of the following automatically includes a Q&A box to explore your data?
Dashboards
Which of the following are advantages that Power BI dashboards have over Power BI reports?
Dashboards provide support for data driven alerts Dashboards update automatically whenever the data in the underlying data sets is updated dashboard and report both offer Q&A functionality. dashboards are not interactive. Only a case of interaction is if you pin a live page. Even in that case that visual that contains live report page will be interactive but it cannot interact with other tiles.
Which of the following is not a valid type when programming in the M language?
Dataframe
Which option directly affects the quality of Q&A?
Dataset
The management requested two Power BI reports to be produced: one that shows all sales data, including transactions that happened in the past 10 minutes, and another report that tracks historical targets versus actual figures for the past 12 months. Historical report is prepared at the beginning of every month. Based on background information and business requirements, answer the following questions: Which data connectivity mode should you use for each report?
Direct query for first, import historical data for second Direct query is the best option for sales data because data is required to be updated all the time. Historical data shows month figures as compared to last 12 months. Historical report is prepared every month, so it's better to import data for historical and schedule it to refresh weekly or monthly. In this way data won't be loaded every time user opens or interact with the report and it will make the report faster
What should you do if you want data to automatically refresh as you interact with visuals?
Direct query. No data is imported to Power BI Desktop. As you create or interact with a visualization, Power BI Desktop queries the underlying data source, i.e. you're always viewing current data.
You use Power BI Desktop to create a visualization for a Microsoft SQL Server data source. You need to ensure that you can use R visualization. Which two actions should you perform?
E. Download and install Microsoft R on the computer that has Power BI Desktop installed. D. Enable R Scripting on the computer that has Power BI Desktop installed.
Which of the following options modifies the interaction between visuals in a Power BI Report?
Edit interactions
Which three options can be added as a group member?
Email addresses Security groups Distribution lists
Which of the following statement best describes the use case for Power Bi embedded?
Embedding pbi tiles/ reports into your company portal / external facing website customers / external users to access
DirectQuery Mode
Essentially, you are creating a direct connection to the data source. Using this model ensures that you are always viewing the most up-to-date data, and that all security requirements are satisfied.
Which feature of query editor splits a query into parts based on the steps?
Extract previous
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?
FORMAT( [Registration Date] , "MMM yyyy")
The DAX FORMAT function converts a value to text according to the specified format provided as the second argument to the function. Which of the following DAX expression formats the number to display 20.00%?
FORMAT(.2, "Percent")
Using the bookmarking features in the report designer in Power BI Desktop, there is a limitation of creating only one bookmark per page in a report. Is this statement correct?
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. Is this statement true?
False
You are creating a Power BI Desktop report that has several bar charts and a date slicer.You need to create a slide show that can be viewed from the Power BI service. Each slide must display the charts filtered for a different year.What should you do before you publish the report?
Filter the bar charts by using the slicer, and then create bookmarks.
What happens when you delete a relationship between two tables in the DAX data model
Filters will no longer propagate between those two tables
You have a workspace that contains 10 dashboards. A dashboard named Sales Data displays data from two datasets. You discover that users are unable to find data on the dashboard by using natural language queries. You need to ensure that the users can find data by using natural language queries.
From the properties of the dataset modify the Q&A and cortana settings
Which visual shows the progress toward a particular target?
Gauge
Get Data from NoSQL Database
Get data feature in Power BI Desktop. However, this time you will select the More... option to locate and connect to the type of database that you use. In this example, you will select the Azure category, select Azure Cosmos DB, and then select Connect. On the Preview Connector window, select Continue and then enter your database credentials. In this example, on the Azure Cosmos DB window, you can enter the database details. You can specify the Azure Cosmos DB account endpoint URL that you want to get the data from (you can get the URL from the Keys blade of your Azure portal). Alternatively, you can enter the database name, collection name or use the navigator to select the database and collection to identify the data source.If you are connecting to an endpoint for the first time, as you are in this example, make sure that you enter your account key. You can find this key in the Primary Key box in the Read-only Keys blade of your Azure portal.
Which of the following visuals is not included as part of Power BI and must be added as a custom visual?
Histogram
What must you consider before you republish a dataset from Power BI Desktop?
If you rename or delete a column or measure any visualizations that you have with that filed could be broken
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?
If(or((([hire date] - [term date])*-1 >= 61 , isblank([term date])),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.
How can you make sure that each category manager can see sales of their category only and CEO to see all sales in the same report? Your solution must involve minimal effort.
Implement dynamic row level security The answer is B. Creating separate reports for each manager will duplicate the reports and make it difficult to maintain them. Creating a slicer in the report is not going to secure data at all. Dynamic row-level security is the most appropriate choice given that there is a table that can be used to filter categories based on the active username. The CEO can have a separate role to view everything.
Into which of the following scopes can you import a custom visual created for Power BI?
Into the scope of a pbi desktop project
What does the option "Assume Referential Integrity" in Power BI Desktop mean(When creating relationships between tables)?
It enables queries on the data source to use inner join rather than outer join. Improves efficiency
What is the use of "Select Related Table" option while importing data from SQL server?
It will import tables that have physical relationships with selected tables Select related tables will import all other tables that have relation with this table.
What is the purpose of the TRIM function?
It will remove white space from start and end of a string
JSON Records
JSON type records must be extracted and normalized before you can report on them, so you need to transform the data before loading it into Power BI Desktop.
You have an API that returns more than 100 columns. The following is a sample of column names. - client_notified_timestamp - client_notified_source - client_notified_sourceid - client_notified_value - client_responded_timestamp-client_responded_source -client_responded_sourceid - client_responded_value You plan to include only a subset of the returned columns. You need to remove any columns that have a suffix of sourceid.
Let Source = .., rawData = Source {[tableID = "clientData"]}[Data], removeSources = "Table.RemoveColumns(List.Select(Table.ColumnNames(rawData),eachText.Contains(_,"sourceid")in removeSources
You have a user named User1. User1 is a member of a security group named Contoso PowerBI.User1 has access to a workspace named Contoso Workspace. You need to prevent User1 from exporting data from the visualizations in Contoso Workspace. Solution: From the PowerBI setting, you modify the Developer Settings. Does this meet the goal?
No
You have a user named User1. User1 is a member of a security group named Contoso PowerBI.User1 has access to a workspace named Contoso Workspace.You need to prevent User1 from exporting data from the visualizations in Contoso Workspace. Solution: From the Microsoft Office 365 Admin center, you remove User1 from the All Users security group. Does this meet the goal?
No
You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.Solution: You create two copies of the Date table named ShipDate and OrderDateGet. You create a measure that uses the new tables. Does this meet the goal?
No
Your company wants you to prepare a dashboard that shows the reports for 10 years of sales data. These reports will be used by company directors to see the monthly sales performance. You need to get data of 10 years from SQL server database. Is Direct Query best option to use in this scenario?
No
Which of the following statements are true with respect to designing a Power BI Desktop project in DirectQuery mode?
No support for parent child DAX functions When in DirectQuery mode, it's not possible to use the family of DAX PATH() functions that generally handle Parent-Child structures, such as chart of accounts, or employee hierarchies.
You are the BI developer at Adventure Works responsible for enabling report creation and sharing by business users. Currently, there is an on-premises data warehouse built with SQL Server 2017, and there are some files stored on a shared drive that contain sales targets. The files are updated manually on a weekly basis. All reports need to be refreshed automatically at-least once a day. Everyone in the organization has a Power BI Pro license. Adventure Works has a hot-desking policy, and all employees must lock their laptops in allocated cabinets. Some business users would like to be able to create their own reports and share them internally. Only a select group of users must have the rights to share reports externally. Adventure Works has a separate manager for each product category. One of the reports must be secured in such a way that each manager must be able to see the products of the category they manage only, while the CEO must be able to see all sales. An HR analyst maintains a table that maps the category name to the manager email address. The management requested your assistance in making sure that business users can create and share their Power BI reports within the organization. Based on background information and business requirements, answer the following question. Business users ask your guidance on how they should configure automatic refresh. Which gateway installation mode is appropriate for Adventure Works?
On-premises data gateway
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?
c) DATESYTD
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?
c) Deploy an on-premises data gateway. Import the data by using the DirectQuery Data Connectivity mode.
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?
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?
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?
d) From the Office 365 Admin center, click Groups
In the Power BI service, you create an app workplace that contains several dashboards. You need to provide a user named [email protected] with the ability to edit and publish dashboards. What should you do?
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?
d) breadcrumbs e) R visuals
After you have pinned a report visual to create a dashboard tile, you can later modify the visual's properties and the Power BI service will automatically update the dashboard tile with the new visual property values.
false
Duplicating a query increases the size of dataset and referencing a query doesn't increase. Is this statement correct ?
yes