Studied by 16 people
The data has the following refresh requirements:
✑ Customer must be refreshed daily.
✑ Date must be refreshed once every three years.
✑ Sales must be refreshed in near real time.
✑ SalesAggregate must be refreshed once per week.
You need to select the storage modes for the tables. The solution must meet the following requirements:
✑ Minimize the load times of visuals.
✑ Ensure that the data is loaded to the model based on the refresh requirements.
Which storage mode should you select for each table? To answer, select the appropriate options in the answer area.
Direct Query
Import
Dual
Import
You have a project management app that is fully hosted in Microsoft Teams. The app was developed by using Microsoft Power Apps.
You need to create a Power BI report that connects to the project management app.
Which connector should you select?
Dataverse
For the sales department at your company, you publish a Power BI report that imports data from a Microsoft Excel file located in a Microsoft SharePoint folder.
The data model contains several measures.
You need to create a Power BI report from the existing data. The solution must minimize development effort.
Which type of data source should you use?
Power BI dataset
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 ID
Address contains the following columns:
✑ Address ID
✑ Address Line 1
✑ Address Line 2
✑ City
✑ State/Region
✑ Country
✑ Postal Code
Each Customer ID represents a unique customer in the Customer table. Each Address ID represents a unique address in the Address table.
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.
You have two Azure SQL databases that contain the same tables and columns.
For each database, you create a query that retrieves data from a table named Customer.
You need to combine the Customer tables into a single table. The solution must minimize the size of the data model and support scheduled refresh in powerbi.
What should you do?
Append Queries as New
Disable loading the query to the data model
In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product.
Every Product has a ProductSubCategory.
Not every ProductsubCategory has a parent ProductCategory.
You need to merge the three queries into a single query. The solution must ensure the best performance in Power Query.
How should you merge the tables?
Inner join
Left outer join
You need to perform the following analyses:
✑ Orders sold over time that include a measure of the total order value
Orders by attributes of products sold. The solution must minimize update times when interacting with visuals in the report.
What should you do first?
From Power Query, merge the Orders query and the Order Line Items query.
You have a Microsoft SharePoint Online site that contains several document libraries.
One of the document libraries contains manufacturing reports saved as Microsoft Excel files. All the manufacturing reports have the same data structure.
You need to use Power BI Desktop to load only the manufacturing reports to a table for analysis.
Get data from a SharePoint folder and enter the site URL Select Transform, then filter by the folder path to the manufacturing reports library.
You need to use Power Query Editor to combine the products from Table1 and Table2 into the following table that has one column containing no duplicate values. Which three actions should you perform in sequence?
Import From Excel
Append Table 2 to Table 1
Remove Duplicates
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?
Split the Logged column by using at as the delimiter.
You have a Microsoft Excel file in a Microsoft OneDrive folder.
The file must be imported to a Power BI dataset.
You need to ensure that the dataset can be refreshed in powerbi
Which two connectors can you use to connect to the file?
Sharepoint folder
Web
You have two CSV files named Products and Categories.
The Products file contains the following columns:
✑ ProductID
✑ ProductName
✑ SupplierID
✑ CategoryID
The Categories file contains the following columns:
✑ CategoryID
✑ CategoryName
✑ CategoryDescription
From Power BI Desktop, you import the files into Power Query Editor.
You need to create a Power BI dataset that will contain a single table named Product. The Product will table includes the following columns:
✑ ProductID
✑ ProductName
✑ SupplierID
✑ CategoryID
✑ CategoryName
✑ CategoryDescription
How should you combine the queries, and what should you do on the Categories query?
Merge
Disable the query load.
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.
Set Data Connectivity mode to DirectQuery.
You have a folder that contains 100 CSV files.
You need to make the file metadata available as a single dataset by using Power BI. The solution must NOT store the data of the CSV files.
Which three actions should you perform in sequence.
Get data then select folder
Remove content
Colum Expand Attribute Colum
A business intelligence (BI) developer creates a dataflow in Power BI that uses DirectQuery to access tables from an on-premises Microsoft SQL server. The
Enhanced Dataflows Compute Engine is turned on for the dataflow.
You need to use the dataflow in a report. The solution must meet the following requirements:
✑ Minimize online processing operations.
✑ Minimize calculation times and render times for visuals.
✑ Include data from the current year, up to and including the previous day.
Create a dataflows connection that has Import mode selected and schedule a daily refresh.
You publish a dataset that contains data from an on-premises Microsoft SQL Server database.
The dataset must be refreshed daily.
You need to ensure that the Power BI service can connect to the database and refresh the dataset.
Which four actions should you perform in sequence?
Configure an on-premises data gateway
Add a data source
Add the dataset owner to the data source
Configure a scheduled refresh
You attempt to connect Power BI Desktop to a Cassandra database.
From the Get Data connector list, you discover that there is no specific connector for the Cassandra database.
You need to select an alternate data connector that will connect to the database.
Which type of connector should you choose?
ODBC
You receive annual sales data that must be included in Power BI reports.
From Power Query Editor, you connect to the Microsoft Excel source shown in the following exhibit.
You need to create a report that meets the following requirements:
• Visualizes the Sales value over a period of years and months
• Adds a slicer for the month
• Adds a slicer for the year
Which three actions should you perform in sequence?
Select the Month and MonthNumber columns
Select Unpivot other columns
Rename the Attribute column as Year and the Value column as Sales
You are using Power BI Desktop to connect to an Azure SQL database.
The connection is configured as shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
10 minutes
Only tables that contain data
You have the Azure SQL databases shown in the following table.
You plan to build a single PBIX file to meet the following requirements:
• Data must be consumed from the database that corresponds to each stage of the development lifecycle.
• Power BI deployment pipelines must NOT be used.
• The solution must minimize administrative effort.
What should you do?
Create: One parameter
Parameter type: Text
You are creating a query to be used as a Country dimension in a star schema.
A snapshot of the source data is shown in the following table.
You need to create the dimension. The dimension must contain a list of unique countries.
Which two actions should you perform?
Delete the City column
Remove duplicates from the Country column
You use Power Query Editor to preview the data shown in the following exhibit.
You need to clean and transform the query so that all the rows of data are maintained, and error values in the discount column are replaced with a discount of 0.05. The solution must minimize administrative effort.
Which three actions should you perform in sequence?
Select the discount column
Select Replace Errors to replace each error value with 0.05
For the discount column, change Data Type to Decimal Number
You attempt to use Power Query Editor to create a custom column and receive the error message shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
The error is caused by mismatched data types
The desired outcomes of the custom column is A1
From Power Query Editor, you attempt to execute a query and receive the following error message.
Datasource.Error: Could not find file.
What are two possible causes of the error?
You do not have permissions to the file
The referenced file was moved to a new location.
You have data in a Microsoft Excel worksheet as shown in the following table.
You need to use Power Query to clean and transform the dataset. The solution must meet the following requirements:
• If the discount column returns an error, a discount of 0.05 must be used.
• All the rows of data must be maintained.
• Administrative effort must be minimized.
What should you do in Power Query Editor?
Select Replace Errors
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?
Split the Logged column by using at as the delimiter.
You have two Microsoft Excel workbooks in a Microsoft OneDrive folder.
Each workbook contains a table named Sales. The tables have the same data structure in both workbooks.
You plan to use Power BI to combine both Sales tables into a single table and create visuals based on the data in the table. The solution must ensure that you can publish a separate report and dataset.
Which storage mode should you use for the report file and the dataset file?
Report file: Import
Dataset file: DirectQuery
You use Power Query to import two tables named Order Header and Order Details from an Azure SQL database. The Order Header table relates to the Order Details table by using a column named Order ID in each table.
You need to combine the tables into a single query that contains the unique columns of each table.
What should you select in Power Query Editor?
Merge queries
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?
Split the Logged column by using at as the delimiter
You are creating a report in Power BI Desktop.
You load a data extract that includes a free text field named coll.
You need to analyze the frequency distribution of the string lengths in col1. The solution must not affect the size of the model.
What should you do?
From Power Query Editor, change the distribution for the Column profile to group by length for col1
You have a collection of reports for the HR department of your company. The datasets use row-level security (RLS). The company has multiple sales regions.
Each sales region has an HR manager.
You need to ensure that the HR managers can interact with the data from their region only. The HR managers must be prevented from changing the layout of the reports.
How should you provision access to the reports for the HR managers?
Publish the reports in an app and grant the HR managers access permission
You need to provide a user with the ability to add members to a workspace. The solution must use the principle of least privilege.
Which role should you assign to the user?
Member
You have a Power BI query named Sales that imports the columns shown in the following table.
Users only use the date part of the Sales_Date field. Only rows with a Status of Finished are used in analysis.
You need to reduce the load times of the query without affecting the analysis.
Which two actions achieve this goal?
Removing uninteresting rows will increase query performance.
Splitting the Sales_Date column will make comparisons on the Sales date faster.
You build a report to analyze customer transactions from a database that contains the tables shown in the following table.
You import the tables.
Which relationship should you use to link the tables?
one-to-many from Customer to Transaction
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?
Remove the Subject and Body columns during the import
You create a Power BI dataset that contains the table shown in the following exhibit.
You need to make the table available as an organizational data type in Microsoft Excel.
How should you configure the properties of the table?
Row label: Name
Key column: ID
Is featured table: Yes
You have the Power BI model shown in the following exhibit.
A manager can represent only a single country.
You need to use row-level security (RLS) to meet the following requirements:
✑ The managers must only see the data of their respective country.
✑ The number of RLS roles must be minimized.
Which two actions should you perform?
Create a single role that filters Country[Manager_Email] by using the USERNAME DAX function
For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions
You have a Power BI imported dataset that contains the data model shown in the following exhibit.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
Changing the cross filter direction setting
The data model is organized into a star schema
You have a Power BI model that contains a table named Sales and a related date table. Sales contains a measure named Total Sales.
You need to create a measure that calculates the total sales from the equivalent month of the previous year.
How should you complete the calculation?
CALCULATE SAMEPERIODLASTYEAR 'DATE'[DATE]
You plan to create a report that will display sales data from the last year for multiple regions.
You need to restrict access to individual rows of the data on a per region-basis by using roles.
Which four actions should you perform in sequence?
Import data
Create the roles on power bi
Publish the report
Assign Users to the role.
You create a data model in Power BI.
Report developers and users provide feedback that the data model is too complex.
The model contains the following tables.
The model has the following relationships:
✑ There is a one-to-one relationship between Sales_Region and Region_Manager.
✑ There are more records in Manager than in Region_Manager, but every record in Region_Manager has a corresponding record in Manager.
✑ There are more records in Sales_Manager than in Sales_Region, but every record in Sales_Region has a corresponding record in Sales_Manager.
You need to denormalize the model into a single table. Only managers who are associated to a sales region must be included in the reports.
Which three actions should you perform in sequence?
Merge [Region_Manager] and [Manager] by using an inner join.
Merge [Sales_Region] and [Sales_Manager] by using an inner join.
Merge [Sales_Region] and [Region_Manager] by using an inner join.
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?
Split the visuals onto multiple pages
You are creating a Microsoft Power BI imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories.
You import a fact table named Sales as shown in the exhibit. (Click the Exhibit tab.)
The related dimension tables are imported into the model.
Sales contains the data shown in the following table.
You are evaluating how to optimize the model.
The SalesRowID and AuditID columns can be removed: True
The OrderDateKey and OrderDate columns are neccessary: False
The TaxAmt column must retain the current number of decimal places to perform: False
You have a Microsoft Power BI data model that contains three tables named Orders, Date, and City. There is a one-to-many relationship between Date and
Orders and between City and Orders.
The model contains two row-level security (RLS) roles named Role1 and Role2. Role1 contains the following filter.
City[State Province] = "Kentucky"
Role2 contains the following filter.
Date[Calendar Year] = 2020 -
If a user is a member of both Role1 and Role2, what data will they see in a report that uses the model?
The user will see data for which the State Province value is Kentucky or where the Calendar Year is 2020
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.
Solution: From Power Query Editor, you import the table and then add a filter step to the query.
Does this meet the goal?
No
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.
Solution: You write a DAX expression that uses the FILTER function.
Does this meet the goal?
No
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.
Solution: You add a WHERE clause to the SQL statement.
Does this meet the goal?
Yes
You are preparing a financial report in Power BI.
You connect to the data stored in a Microsoft Excel spreadsheet by using Power Query Editor as shown in the following exhibit.
You need to prepare the data to support the following:
✑ Visualizations that include all measures in the data over time
✑ Year-over-year calculations for all the measures
Which four actions should you perform in sequence?
You are preparing a financial report in Power BI.
You connect to the data stored in a Microsoft Excel spreadsheet by using Power Query Editor as shown in the following exhibit.
You need to prepare the data to support the following:
✑ Visualizations that include all measures in the data over time
✑ Year-over-year calculations for all the measures
Which four actions should you perform in sequence?
Use first row as header
Unpivot all columns other than "Measure"
Rename "Attribute" to "Year"
Change data type of "Year" to date (Date > Year)
You are creating an analytics report that will consume data from the tables shown in the following table.
There is a relationship between the tables.
There are no reporting requirements on employee_id and employee_photo.
You need to optimize the data model.
What should you configure for employee_id and employee_photo?
Employee_id: Hide
Employee_photo: Delete
You plan to create Power BI dataset to analyze attendance at a school. Data will come from two separate views named View1 and View2 in an Azure SQL database.
View1 contains the columns shown in the following table.
View2 contains the columns shown in the following table.
The views can be related based on the Class ID column.
Class ID is the unique identifier for the specified class, period, teacher, and school year. For example, the same class can be taught by the same teacher during two different periods, but the class will have a different class ID.
You need to design a star schema data model by using the data in both views. The solution must facilitate the following analysis:
✑ The count of classes that occur by period
✑ The count of students in attendance by period by day
✑ The average number of students attending a class each month
In which table should you include the Teacher First Name and Period Number fields?
Teacher's dim
Class dim
You have the Power BI model shown in the following exhibit.
There are four departments in the Departments table.
You need to ensure that users can see the data of their respective department only.
What should you do?
Create a row-level security (RLS) role for each department, and then define the membership of the role
In Power BI Desktop, you are building a sales report that contains two tables. Both tables have row-level security (RLS) configured.
You need to create a relationship between the tables. The solution must ensure that bidirectional cross-filtering honors the RLS settings.
What should you do?
Create an active relationship between the tables and select Apply security filter in both directions
You have a column named UnitsInStock as shown in the following exhibit.
UnitsInStock has 75 non-null values, of which 51 are unique.
There will be 75 rows in the table
Changing the settings will reduce the number of rows
You have a Power BI report.
You have the following tables.
You have the following DAX measure.
Accounts :=
CALCULATE (
DISTINCTCOUNT (Balances[AccountID]),
LASTDATE ('Date'[Date])
FALSE
FALSE
TRUE
You have the tables shown in the following table.
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?
Create one-to-many relationships between the tables
Group the Impressions query in Power Query by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function
You are creating a Microsoft Power BI data model that has the tables shown in the following table.
The Products table is related to the ProductCategory table through the ProductCategoryID column. Each product has one product category.
You need to ensure that you can analyze sales by product category.
How should you configure the relationship from ProductCategory to Products?
Cardinality: one-to-many
Cross-filter direction: single
You import a Power BI dataset that contains the following tables:
✑ Date
✑ Product
✑ Product Inventory
The Product Inventory table contains 25 million rows. A sample of the data is shown in the following table.
The Product Inventory table relates to the Date table by using the DateKey column. The Product Inventory table relates to the Product table by using the
ProductKey column.
You need to reduce the size of the data model without losing information.
What should you do?
Remove MovementDate
You are enhancing a Power BI model that has DAX calculations.
You need to create a measure that returns the year-to-date total sales from the same date of the previous calendar year.
Which DAX functions should you use?
CALCULATE
SUM
DATESBETWEEN
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.
Solution: You add a report-level filter that filters based on the order date.
Does this meet the goal?
No
ou have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: For each date foreign key, you add inactive relationships between the sales table and the date table.
Does this meet the goal?
No
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query twice to make the queries for Order Date and
Delivery Date.
Does this meet the goal?
No
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables.
Does this meet the goal?
Yes
You receive revenue data that must be included in Microsoft Power BI reports.
You preview the data from a Microsoft Excel source in Power Query as shown in the following exhibit.
You plan to create several visuals from the data, including a visual that shows revenue split by year and product.
You need to transform the data to ensure that you can build the visuals. The solution must ensure that the columns are named appropriately for the data that they contain.
Which three actions should you perform in sequence?
Select Use First Row as Headers
Select Department and Product and Unpivot Other Column
Rename the Attribute column to YEAR and the Value column to REVENUE
You have a Power BI report named Orders that supports the following analysis:
✑ Total sales over time
✑ The count of orders over time
✑ New and repeat customer counts
The data model size is nearing the limit for a dataset in shared capacity.
The model view for the dataset is shown in the following exhibit.
The data view for the Orders table is shown in the following exhibit.
The Orders table relates to the Customers table by using the CustomerID column.
The Orders table relates to the Date table by using the OrderDate column.
No
No
Yes
You are building a financial report by using Power BI.
You have a table named financials that contains a column named Date and a column named Sales.
You need to create a measure that calculates the relative change in sales as compared to the previous quarter.
How should you complete the measure?
Calculate
Dateadd
Divide
You are creating a Power BI model and report.
You have a single table in a data model named Product. Product contains the following fields:
✑ ID
✑ Name
✑ Color
✑ Category
✑ Total Sales
You need to create a calculated table that shows only the top eight products based on the highest value in Total Sales.
How should you complete the DAX expression?
TOPN
DESC
You are creating a sales report in Power BI for the NorthWest region sales territory of your company. Data will come from a view in a Microsoft SQL Server database. A sample of the data is shown in the following table:
The report will facilitate the following analysis:
✑ The count of orders and the sum of total sales by Order Date
✑ The count of customers who placed an order
✑ The average quantity per order
You need to reduce data refresh times and report query times.
Which two actions should you perform?
Remove the TaxAmt and Freight columns
Filter the data to only the NorthWest region sales territory
You are creating a Power BI model that contains a table named Store. Store contains the following fields.
You plan to create a map visual that will show store locations and provide the ability to drill down from Country to State/Province to City.
What should you do to ensure that the locations are mapped properly?
Set the data category of City, State/Province, and Country
You are building a data model for a Power BI report.
You have data formatted as shown in the following table.
You need to create a clustered bar chart as shown in the following exhibit.
What should you do?
From Power Query Editor, split the Machine-User column by using a delimiter
You need create a date table in Power BI that must contain 10 full calendar years, including the current year.
How should you complete the DAX expression?
Year
Today
Calendar
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
✑ Due Date
✑ Order Date
✑ Delivery Date
You need to support the analysis of sales over time based on all the date foreign keys.
Solution: You create measures that use the USERELATIONSHIP DAX function to filter sales on the active relationship between the sales table and the date table.
Does this meet the goal?
No
You have a Power BI report that contains a measure named Total Sales.
You need to create a new measure that will return the sum of Total Sales for a year up to a selected date.
How should you complete the DAX expression?
TOTALTYD
‘Date’[Date]
You are modifying a Power BI model by using Power BI Desktop.
You have a table named Sales that contains the following fields.
You have a table named Transaction Size that contains the following data.
You need to create a calculated column to classify each transaction as small, medium, or large based on the value in Sales Amount.
How should you complete the code?
Filter
And
Calculate
You have a Power BI report for the procurement department. The report contains data from the following tables.
There is a one-to-many relationship from Suppliers to LineItems that uses the ID and Supplier ID columns.
The report contains the visuals shown in the following table.
You need to minimize the size of the dataset without affecting the visuals.
What should you do?
Remove the LineItems[Description] column
You have a Power BI report for the marketing department. The report reports on web traffic to a blog and contains data from the following tables.
There is a one-to-many relationship from Posts to Traffic that uses the URL and URL Visited columns.
The report contains the visuals shown in the following table.
The dataset takes a long time to refresh.
You need to modify Posts and Traffic queries to reduce load times.
Which two actions will reduce the load times?
Remove the rows in Traffic in which Traffic[URL Visited] does not contain ג€blogג€
Remove Posts[Full Text] and Posts[Summary]
ou are creating a quick measure as shown in the following exhibit.
You need to create a monthly rolling average measure for Sales over time.
How should you configure the quick measure calculation?
Total Sales
Date
Months
You have the Power BI data model shown in the following exhibit.
The Sales table contains records of sales by day from the last five years up until today’s date.
You plan to create a measure to return the total sales of March 2021 when March 2022 is selected.
Which DAX expression should you use?
Calculate (SUM(Sales[Sales]), SAMEPERIODLASTYEAR(dimDate[Date] ))
You use Power BI Desktop to load data from a Microsoft SQL Server database.
While waiting for the data to load, you receive the following error.
You need to resolve the error.
What are two ways to achieve the goal?
Reduce the number of rows and columns returned by each query
Split log running queries into subsets of columns and use Power Query to merge the queries
From Power Query Editor, you profile the data shown in the following exhibit.
The IoT GUID and IoT ID columns are unique to each row in the query.
You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You split the IoT DateTime column into a column named Date and a column named Time.
Does this meet the goal?
Yes
From Power Query Editor, you profile the data shown in the following exhibit.
The IoT GUID and IoT ID columns are unique to each row in the query.
You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You remove the IoT GUID column and retain the IoT ID column.
Does this meet the goal?
Yes
From Power Query Editor, you profile the data shown in the following exhibit.
The IoT GUID and IoT ID columns are unique to each row in the query.
You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You change the IoT DateTime column to the Date data type.
Does this meet the goal?
No
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?
Remove unused columns from tables in the data model
You have a Power BI data model that contains two tables named Products and Sales.
A one-to-many relationship exists between the tables.
You have a report that contains a report-level filter for Products.
You need to create a measure that will return the percent of total sales for each product. The measure must respect the report-level filter when calculating the total.
How should you complete the DAX measure?
CALCULATE
ALLSELECTED.
You have a Power BI data model that analyzes product sales over time. The data model contains the following tables.
A one-to-many relationship exists between the tables.
The auto date/time option for the data model is enabled.
You need to reduce the size of the data model while maintaining the ability to analyze product sales by month and quarter.
Which two actions should you perform?
Create a relationship between the Date table and the Sales table
Create a Date table and select Mark as Date Table
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?
Remove unused columns from tables in the data model
You have a Power BI data model that contains a table named Stores. The table has the following columns:
• Store Name
• Open Date
• Status
• State
• City
You need to create a calculated column named Active Store Name that meets the following requirements:
• When the value of the Status column is “A”, the value in the Store Name column must be returned.
• When the value of the Status column is NOT “A”, the value in the Store Name column that is prefixed with "Inactive - " must be returned.
How should you complete the DAX expression?
IF
&
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?
Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date
From Power Query Editor, you profile the data shown in the following exhibit.
The IoT GUID and IoT ID columns are unique to each row in the query.
You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You create a custom column that concatenates the IoT GUID column and the IoT ID column and then delete the IoT GUID and IoT ID columns.
Does this meet the goal?
No
You have a Power BI model that contains a table named Employee. The table contains the following data.
Each employee has one manager as shown in the ParentEmployeeID column.
All reporting paths lead to the CEO at the top of the organizational hierarchy.
You need to create a calculated column that returns the count of levels from each employee to the CEO.
Which DAX expression should you use?
PATHLENGTH(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]))
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?
Split the Logged column by using at as the delimiter
You have the Power BI data model shown in the following exhibit.
You need to create a measure to count the number of product categories that had products sold during a selected period.
How should you complete the DAX expression?
Distinctcount('Product'[product category]
'sales'
You have the Power BI data model shown in the following exhibit.
The Sales table has the following columns.
The data model must support the following analysis:
• Total sales by product by month in which the order was placed
• Quantities sold by product by day on which the order was placed
• Number of sales transactions by quarter in which the order was placed
Yes
No
Yes
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?
Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date
You have a Power BI data model that contains a table named Employees. The table has the following columns:
• Employee Name
• Email Address
• Start Date
• Job Title
You are implementing dynamic row-level security (RLS).
You need to create a table filter to meet the following requirements:
• Users must see only their own employee data.
• The DAX expression must work in both Power BI Desktop and the Power BI service.
Which expression should you use?
[Email Address] = USERPRINCIPALNAME()
You have the Power BI data model shown in the following exhibit.
The Country table contains the following data.
You create two row-level security (RLS) roles named Manager and CFO.
You plan to publish the dataset to the Power BI service.
You need to create DAX expressions for the RLS filters. The solution must meet the following requirements:
• Each manager must see only the data in the Sales and Human Resources tables for their own country.
• The CFO must be prevented from seeing the data in the Human Resources table.
• The CFO must see the sales data of all countries.
How should you complete the DAX expressions to meet the requirements? 
Human Resources > False ()
Country > [Email] = USERPRINCIPALNAME ()
You have a Power BI model that contains a table named Sales. The Sales table contains the following columns:
• Order Line ID
• Product ID
• Unit Price
• Order ID
• Quantity
Orders are uniquely identified by using the order ID and can have multiple order lines. Each order line within an order contains a different product ID.
You need to write a DAX measure that counts the number of orders.
Which formula should you use?
DistinctCount('Sales' [Order ID])
You are creating a Power BI model in Power BI Desktop.
You need to create a calculated table named Numbers that will contain all the integers from -100 to 100.
How should you complete the DAX calculation?
GENERATESERIES
(-100,100,1)
You have a Power BI model that contains a table named Date. The Date table contains the following columns:
• Date
• Fiscal Year
• Fiscal Quarter
• Month Name
• Calendar Year
• Week Number
• Month Number
• Calendar Quarter
You need to create a calculated table based on the Date table. The calculated table must contain only unique combinations of values for Calendar Year, Calendar Quarter, and Calendar Month.
Which DAX function should you include in the table definition?
SUMMARIZE
You have a Power BI model that contains the following data.
The Date table relates to the Sales table by using the Date columns.
You need to create a calculated table that will contain the following:
• A row for each year
• A column that contains the total sales per year
How should you complete the DAX calculation?
SUMMARIZE
Date[Year]
Note 
Flashcard (41)