DA-100 practice test

1. Question 1 · 1

   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?

   Choose one answer.

   • Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent.
   • Remove the Subject and Body columns during the import.
   • From Model view, set the Subject and Body columns to Hidden.

2. Question 2 · 1

   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?

   Choose one answer.

   • Change the data type of the Logged column to Date.
   • Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.
   • Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date.
   • Add a conditional column that outputs 2018 if the Logged column starts with 2018 and set the data type of the new column to Whole Number.

3. Question 3 · 1

   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?

   Choose one answer.

   • Add a SQL statement.
   • Set Data Connectivity mode to DirectQuery.
   • Set the Command timeout in minutes setting.
   • Set Data Connectivity mode to Import.

4. Question 4 · 1

   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?

   Choose one answer.

   • Split the model into multiple models.
   • Hide unused columns in the model.
   • Merge tables by using Power Query.
   • Transpose.

5. Question 5 · 1

   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?

   Choose one answer.

   • Round the hour of the Date column to startOfHour.
   • Change the data type of the Date column to Text.
   • Trim the Date column.
   • Split the Date column into two columns, one that contains only the time and another that contains only the date.

6. Question 6 · 1

   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?

   Choose one answer.

   • 1318
   • 1319
   • 21318 Lasalle Street
   • 21319 Lasalle Street

7. Question 7 · 1

   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?

   Choose one answer.

   • Increase the number of times that the dataset is refreshed.
   • Split the visuals onto multiple pages.
   • Change the imported dataset to DirectQuery.
   • Implement row-level security (RLS).

8. Question 8 · 1

   Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. 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?

   Choose one answer.

   • Yes
   • No

9. Question 9 · 1

   Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. 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?

   Choose one answer.

   • Yes
   • No

10. Question 10 · 1

    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? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

    Choose all answers that apply.

    

    • Group the impressions by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function.
    • Create one-to-many relationships between the tables.
    • Create a calculated measure that aggregates by using the COUNTROWS function.
    • Create a calculated table that contains Ad_id, Site_name, and Impression_date.

11. Question 11 · 1

    Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. 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. Solution: In the Power Query M code, you replace references to the Excel file with DataSourceExcel. Does this meet the goal?

    Choose one answer.

    • Yes
    • No

12. Question 12 · 1

    Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. 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. Solution: You create a new query that references DataSourceExcel. Does this meet the goal?

    Choose one answer.

    • Yes
    • No

13. Question 13 · 1

    Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. 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?

    Choose one answer.

    • Yes
    • No

14. Question 14 · 1

    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?

    Choose one answer.

    • video
    • custom streaming data
    • text box
    • web content

15. Question 15 · 1

    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?

    Choose one answer.

    • Show whitespace
    • Column profile
    • Column distribution
    • Column quality

Paid access to all 55 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.