Power BI Skills

Power BI DA-100 / PL-300 Free Practice Exam

Tuesday, December 14th, 2021

Welcome to your Power BI DA-100 Free Practice Exam

This practice DA-100 practice exam covers the same topics and uses the same question format as the official DA-100 exam. The only difference is that this exam does not include case study questions.

The DA-100 exam will be replaced by the PL-300 exam in March 2022. The PL-300 should be very similar to the DA-100. See the announcement here.

Exam duration is 180 Minutes. The exam DA-100 format includes multiple choice and multi-response questions. The exam has 50 questions. You should not use any outside resources while taking the practice exam.

1. You have a database that contains stock market transactions. The database is updated every five minutes, and you’d like these updates to be reflected in your Power BI report as soon as possible. Which data source and storage mode do you think will be applicable here?

Excel file and Import mode

Azure SQL database and DirectQuery mode

Local CSV file with DirectQuery mode

SQL Server and Import mode

2. Which of these statements best describe the Import storage mode in Power BI?

It does not include the data model

Import mode can cache data from multiple sources

In Import mode, we can connect with live data, get the data model of the live data and also import offline Excel/CSV files

With Import storage mode, the maximum size offor a dataset published to the Power BI service is 1 GB, while DirectQuery can be used for larger datasets

3. You have imported a data in Power Query but the correct column headers are not being shown. You examine your imported table and realize that the correct headers are in the second row of the imported table, as shown in the image. In order to correct this and given the steps below, what would be right order of steps?

On the transform tab, select Use First Row as Headers and from drop down select Use First Row as Headers
In the Remove Top Rows window, enter 2 in the Number of Rows box
In the Remove Top Rows window, enter 1 in the Number of Rows box
Select Home from the Power Query ribbon, find Reduce Rows, and then select Remove Rows -> Remove Top Rows
On the transform tab, select Use First Row as Headers and from drop down select Use Headers as First Row

1, 3, 5

3, 2, 4

5, 1, 2

4, 3, 1

4. Suppose you have created all your visuals in Power BI desktop and would like to publish it on the Power BI service. You previously used a CSV file to setup the report but now you want to connect the same report to an Excel source. What are some of the ways to update the data source before publishing? Mark all the correct options.

Go into File > Options and Settings > Change data source settings > Click on a data source > Change Source

Go to File > Options and Settings > Change data source settings > Click on a data source > Delete the data source > Import Excel file as a new data source

Open the Power Query Editor > Click on new source > Select Excel workbook > Load the new data with the same name as old data and replace the old data

In Power BI desktop, click on drop down of transform data > Select data souce settings > Click on a data source > Change Source

5. Which of the options are correct if we have to deal with errors in the table below before we calculate the sum of the column named “Num”?

Remove the errors

Replace error with the value of 0

Replace error with “null”

Let the errors remain if they do not affect anything

6. Suppose you are building a report for a retail company and you have imported some tables in your data model. You have two tables as shown below along with their columns: Refunds and the Date table. You need to create a valid relationship between the Refunds table and the Date table. How would you go about doing this?

In the Refunds table, create a calculated column using Day, Month and Year and use the calculated column to create a relationship with the Date table.

Create an index column in the Date table and join with the Item_ID column in Refunds table

Create a relationship using the Day column in the refunds table with the Date column of the Date table

Create a measure and use the Date column of the Date table using a RELATED DAX function

7. You are working with a dataset in Power BI containing millions of rows of data. The Date column is in the date/time format as shown below. What can you do to improve performance of the Date column when importing and filtering data in Power BI?

Remove the Date column

Change the column type from datetime to only date

Remove time and only keep the date

Split the date/time column into 2 distinct Date and Time columns

8. You have imported data from a CSV file. During the transformation process, you discover that some columns need to fixed:

Column A contains names that are all lower case
Column B contains a hyphen at the beginning of each row value
Column C contain spaces at the beginning and end of the row values

What commands would you use to fix all of the errors above?

Column A – Capitalize each word, Column B – Extract text after delimiter & Column C – Trim

Column A – Trim, Column B – Extract text after delimiter & Column C – Capitalize each word

Column A – Trim, Column B – Capitalize each word & Column C – Extract text after delimiter

Column A – Extract text after delimiter, Column B – Trim & Column C – Capitalize each word

9. You have received data from MS SQL Server. During the cleaning process, you realize that many columns have some portion of the data consists of empty values. How will identify which percentage of empty values exist in each column?

Select Transform data. Then in the Power Query editor, select the “View” tab on the toolbar, Click on Monospaced checkbox

Select Transform data. Then in the Power Query editor, select the “View” tab on the toolbar, Click on Column Profile checkbox

Select Transform data. Then in the Power Query editor, select the “View” tab on the toolbar, Click on Column Quality checkbox

Select “Transform data.” In the Power Query editor select the “View” tab on the toolbar, Click on Show Whitespace checkbox

10. Consider a Power BI table containing the following columns and values:

Now you need to calculate whether a number in the source is above a certain threshold so that you can mark it as Pass or Fail. For example, any number above 75 is pass, rest are fail as in the formula shown below:

= if [Source] >= 75 then "Pass" else "Fail"

Now you get this image as result:

Which option(s) will you select in the Query Editor to accomplish the above task?

Custom Column

Measure

Index Column

Conditional Column

11. You have created the table visual and added a country column from a table and a measure which calculates the total sales of each country for the 3 months (January 2022 – March 2022). How will you calculate sales 3 months prior from the total sales of the first measure that is October 2021 - December 2021, supposing March 2022 is the Max Date?

Total Sales Previous Period = CALCULATE([Total Sales], DATESBETWEEN(Table[Date], MAX(Table[Date]) – 182, MAX(Table[Date]) – 90))

Total Sales Previous Period = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Table[Date]))

Total Sales Previous Period = CALCULATE([Total Sales], (Table[Date]) – 3)

Total Sales Previous Period = CALCULATE([Total Sales], DATESINPERIOD(Table, MAX(Table[Date]), -6, MONTH)

12. Which DAX expression is being used to format the date column in the image below?

FORMAT(‘Products’[Date], “MMM YY”)

FORMAT(‘Products’[Date], “MMMM YYYY”)

FORMAT(‘Products’[Date], “MM YYYY”)

FORMAT(‘Products’[Date], “M YY”)

13. Creating a separate date table is a very common practice when dealing with time intelligence functions in Power BI. Which of the following statements are true for a date table?

The date table should have a column of data type date or date/time

The date column can contain duplicate values

The date column can have missing dates

The date column can not have any blank values

14. You are tasked to build a database model for a large retail company. You are asked to build a model where you can track the daily and monthly sales, dates, products sold and customers list. How will you create the model keeping in mind that the tables can have millions of rows, the retrieval needs to be as fast as possible and the business users should be able to explore the data from many different perspectives? Choose all that apply.

Build a dimensional model with a fact table for sales and dimensional tables for the dates, products and customers

Create one table and put all the fields in one table

Create a dimensional model with sales, dates, customers and products table but also branch out customer table to location and country tables and product table to product category table

Build a dimensional model with one fact table for sales and the other table containing dates, customers and products list

15. When do we use the table visual in Power BI?

When we want to compare detailed data and exact numerical and textual values

When we want to see a general trend and plot future forecasts in our data

When we want to see visual patterns within our data like outliers, the biggest value or the smallest value

When we want to visualize the data as pictures and icons and do not care about precise values

16. You are building a report for a media company which runs ads in different cities. You are asked to make a report where the users can view the data based only on their locations. For example, a user from New York can only see data of New York but not Los Angeles. There are over 1000 users and there are hierarchies (managers, developers etc.) among users, so you have to figure out a way to automate this. How will you set this up?

Use a single username and password for each city

Create dynamic Row Level Security (RLS) using UserPrincipleName() DAX function

Create seperate reports for each user

From the “Modeling Tab”, select Manage Roles. Then select “Create” and add a role for each user

17. A media company has asked you to build an analytics dashboard, called “Ad_Impressions”, which shows the number of clicks an ad gets per user. Q&A is enabled for the dashboard. When users try to get a count of impressions, you get expected results. But when they try to get a count of clicks, the query fails. You want to ensure that both queries will return the same results, since ad impressions and clicks mean the same thing. Which action will you perform?

Edit synonyms in the Power BI service

Edit synonyms in Power BI desktop

Create a calculated column called Clicks which is a duplicate of the column named Impressions

Edit the original column name from Impressions to Clicks

18. Suppose you work for a media agency and have a Power BI model which contains the following tables:

Customer (Cust_ID, Cust_Name, Address, Date_Added); Date (Date, Month, Day, Year)

There is a relationship between Customer and Date table using the columns Date_Added and Date respectively. You need to create an annual customer acquisition growth percentage. This is calculated by counting the number of customers acquired in the current year over total customers. Which DAX expression will you use making sure that the current year is always choosen dynamically?

CALCULATE(COUNT(Customer[Cust_ID]), FILTER(Date, Date[Year] = MAX(YEAR(Customer[Date_Added]))) / COUNT(Customer[Cust_ID])

CALCULATE(COUNT(Customer[Cust_ID]), SAMEPERIODLASTYEAR(Date[Date])) / COUNT(Customer[Cust_ID])

COUNT(Customer[Cust_ID]) - CALCULATE(COUNT), [Number_of_Items]))

TOPN (ALL(Customer[Cust_ID]), DATESYTD(Date[Date])) / COUNT(Customer[Cust_ID])

(COUNT(Customer[Cust_ID]) - CALCULATE(COUNT(Customer[Cust_ID]), FILTER(Date, Date[Year] = MAX(YEAR(Customer[Date_Added])))) / COUNT(Customer[Cust_ID])

19. A media agency asks you to create a Power BI report. You need to create a calculated table to return the 50 highest spending customers. How will you complete the DAX expression below?

Top 50 Customers = ______________ (50, ______________ (Transaction, Transaction[Customer_ID], “Spending”, SUM(Transaction[Sale])), [Sales], ______________)

FILTER, TOPN, SUMMARIZE

TOPN, SUMMARIZE, DESC

SUMMARIZE, FILTER, TOPN

ASC, TOPN, FILTER

20. You have created a report which has a large data model and is quite slow. You would like to identify and correct poorly performing measures and visuals. Which of these options are correct if performance is to be improved?

Use of variables in measures to avoid repeating calculations

Checking the Performance Analyzer to figure out the poorly performing visuals and DAX measures

Merging the smaller tables into one large table for easy filtering

Enabling auto date/time

Removing unnecessary columns and rows from the data model

21. A company wants to show the percentages of the total amount of products that have been sold in its latest fiscal year. Which chart(s) can you use to visualize percentages as a sum total of all its parts?

Pie Chart

Scatter Plot

Donut chart

Table

22. Which of the following are ways to add a visual to a report page? [select all that apply.]

Select a visual icon on the Visualizations pane

Drag a visual icon from the Visualizations pane onto the canvas

Drag a field from the Fields pane onto a visual icon on the Visualizations pane

Drag a field from the Fields pane onto the canvas

23. A car company has asked you to come up with a Power BI report. Part of the requirement is to show data in a visual called Bullet Chart. See the image below.

You realize that a suitable one is not present in the Power BI’s visualizations list. How will you accomplish this task?

Create a custom visual from scratch that resembles the Bullet Chart

Use a bar chart instead

Select Get More Visuals in the Visualizations pane > Search for Bullet Chart in the window that displays and then click on the ‘Get It Now’ button

Close Power BI and re-open it again since the visualizations may not have loaded well

Download a PBIVIZ file from Microsoft AppSource and import it locally into Power BI

24. A survey was taken regarding the weight and height comparison of a sample population. You are tasked to make a scatter plot (shown below) and figure out a way to show outliers, circled in red, so that they are clearly visible to the users.

How will you accomplish this task?

Label all the data points and show the labels in the plot

Apply conditional formatting to show a red dot whenever there is an outlier

Create a best fit trend line so that points farthest away from the line can be seen as outliers in the plot

Use a line chart instead of a scatter plot

25. You use Power BI in a factory environment and want to keep check on the temperature of the machines. You want to be notified whenever the temperature goes above or below a given threshold. What can you do to accomplish this?

Setup a data alert in Power BI desktop

Create a subscription in the Power BI service

Set the report to refresh every 5 minutes and be on the look-out for any drastic temperature change

Configure a data alert in the Power BI service

26. You have built a report for a large retail company and are now receiving messages from end users who are asking questions about the data and asking if you can build visuals for their needs. You do not have time to make a new visual every time a user asks a question and since there are so many requests, you are getting overwhelmed. What can you do so that the users can get ad hoc answers to their queries?

Use the AI Builder visual

Create a Q&A visual

Use the Smart Narrative visual

Create a Decomposition Tree visual

27. You are working for an analytics company and are building a report that will be shared with the entire organization. They have provided you with some design guidelines for customizing the reports. You are given instructions on which color to use for the organization and a set of icons. How can you meet this goal using the least amount of effort and time?

Import a blank theme from the Power BI Theme Gallery and customize it from scratch

Customize the color properties of each visual from the Format pane

Create a JSON file and import it as a custom theme

Create an XML file and import it as a custom theme

28. You have created a Power BI report with two visuals (Bar Chart, Pie Chart) along with a slicer on the same page. Whenever you change the value in the slicer, both the visuals change as a result. You want only the bar chart to change when changing slicer values and not the pie chart. How will you make it possible?

Edit the interactions from the Format menu

Set a visual filter on the bar chart visual

Set a report level filter

Set a visual filter on the pie chart visual

29. You have a line chart containing sales on the y-axis and month names on the x-axis, as shown in the image below.

As you can see the months in the x-axis are not sorted properly. Looking at the table schema below, which column will you choose to sort the month names by?

30. You are building a report that contains many different pages. The slicers used on all the pages are the same and use the same fields. You receive feedback from the users that when they select slicers in one page, the selection is not reflected on all the other pages. You need to provide a solution so that when one value is selected, the filter is applied on all the pages. How will you solve this problem?

Replace slicers with report level filters

Sync slicers across all the pages

Replace slicers with visual level filters

Replace slicers with page level filters

31. The media department of organization wants to show the top 10 ads with the highest penetration numbers. Which of these methods can you use to accomplish this?

Top N Filter

Matrix

Scatter Plot

DAX Formula

32. A multi-national company has asked you to create a table showing revenue per region and its percentage, as shown below.

The color of the ‘% revenue region’ will be based on the revenue column. How will you achieve this?

33. You are asked to create a report that can be filtered by region using a hierarchy of country, state and city. What are some ways to accomplish this?

Create a slicer and use drill-down to go from country to state and then to city

Add the country, state and city fields in the page filters pane

Create a hierarchy in slicer using the three fields

Create three slicers and use a separate slicer for each field

34. A company wants to calculate average total sales per region. You have imported an Excel file, containing the table ‘Sales’, as shown in the image below. Table name is ‘Sales’.

Which DAX formula would you use to accomplish this?

DIVIDE (SUM(State), DISTINCTCOUNT(State), 0)

DIVIDE (SUM(State), COUNT(Region), 0)

DIVIDE (SUM(Revenue), COUNTROWS(Sales), 0)

AVERAGE(Revenue)

35. Which query will you use in the Q&A visual to get the result shown in the image below?

Top products by total sales

Top 5 products by total sales

Total sales over top 5 products

Total sales for products

36. You have a column chart showing the price of items in a store. The managers have asked you to mark average on the chart like below.

Go to insert, select shapes and choose line. Select average from the dropdown.

Insert average line from the chart’s format options

Choose average line from the analytics pane

Right click on the chart and click “Show Average Line”

37. How can personalization of the visuals be enabled for the end users?

Right click on a visual and select “allow users to personalize this visual”

Enabling “Allow report readers to personalize visuals” in the report settings of Power BI Desktop and then enable personalization on the service

Users can personalize a visual by clicking on the format options of a visual in the Power BI service

By adding a drill-down for each visual in the Power BI report

38. What are the best visuals to use when conducting a time series analysis?

Matrix

Scatter Plot

Line Chart

Pie Chart

39. Which of these statements are true for a decomposition tree visual?

It does not allow us to do ad hoc analysis of data

We can drill down a dimension based on a certain criteria

We cannot do root cause analysis for errors in our data

We can use AI splits to find the highs and the lows in the data

40. Which of these problems can you solve using AI insights in Power BI?

We can converse with AI bots and get answers regarding our data

We can do sentiment analysis and evaluate a text input as negative or positive

We can tag images and classify the objects present in an image

We can create sentences using a bunch of key phrases as input

41. Which role cannot create, edit and delete content in the Power BI workspace?

Admin

Contributor

Viewer

Member

42. You are working on a report that shows stock market data. The data needs to be refreshed every 15 minutes. Which data refresh strategy would you use to achieve this?

Setup scheduled refreshes to update data when new data is available

Connect the data with DirectQuery

Use import mode to import data and refresh from Power BI desktop

Connect the report to an online excel file

43. You are an administrator of a workspace where your organization’s Power BI reports are published. The reports contain highly-sensitive data. You hire an outsider to build some more reports but you want them to use the existing data models. What would be the best solution for this scenario?

Give build permissions to reuse the dataset of your reports

Send them guidelines on how to build the data models from scratch

Give them your own Power BI files so they can build their reports

Copy the report in another workspace and give them access to that report

44. You are working on a report that has a very large dataset (greater than 100 gigabytes) and needs to be updated frequently but not all data has to be refreshed every time. The management has asked you to make sure that you refresh at least 1 year of previous sales data every day. How will you accomplish this?

Invest in a super fast infrastructure that can update all data quickly

Setup incremental refresh to update one year of previous data

Use report level filters to only show one year of sales data

Only keep one year of data in the dataset

45. You work for a company as a Power BI administrator and have created reports which have met the organization’s quality standards and they want to highlight content in the report that they consider as valuable. What should you do to make it possible?

Create new reports containing only the most valuable data or visuals

Share reports with everyone as soon as they are created

Extract data from the reports and create PowerPoint presentations on it

Promote and certify valuable data that meets the company’s guidelines in the Power BI service

Write an email to let everyone know that the data is correct

46. A new team member has joined your Power BI team and would like to update reports in the Power BI service for your organization. Which roles can you assign to the new member to enable this?

Admin

Viewer

Contributor

Member

47. You work with a media agency and have created many Power BI reports for them. They want you to group specific reports and dashboards so that they can see all the reports related to a particular subject in one place. What is the most efficient way to make this happen?

Create an app in the Power BI workspace and pin reports and dashboards to it

Bundle the reports together and send them as an email

Make a new Power BI report and copy all the related reports in it

Make a new workspace in the Power BI service related to the subject and publish reports to that workspace

48. You are working on a Power BI report and have made changes to some visuals. You have already published this report before in the Power BI service. What will happen when you try to publish the updated report with the same name?

It will be published and updated without any problems

Power BI will publish the updated report as a new report with new name

Power BI will ask if you would like to update the existing report

You will be denied to publish the report with the same name

49. You are working for a financial firm and they have asked you to create a report on the assets they manage for a large portfolio of clientele. They want this information to remain confidential and private from anyone outside the organization even among their clientele. Which of these sensitivity options can you apply in order to make this happen?

Highly confidential

General

Confidential for Finance

Public

50. You are a user of a Power BI report created by your organization. You want to get updates to the changes that occur in the report and you want them weekly. What can you do to enable this?

Ask the Power BI developer to email the report every week

Subscribe to the report in Power BI service and set it as weekly

Ask the administrator to inform you every week that the report has been updated

Make a calendar entry to remind yourself each week to check the report

Time is Up!

Time's up

2 thoughts on “Power BI DA-100 / PL-300 Free Practice Exam”

Awais Aziz says:

December 27, 2021 at 8:53 am

Test is great

Reply
1. Lukas Halim says:
  
  December 30, 2021 at 3:43 pm
  
  Thanks Awais!
  
  Reply

Learn Power BI

Learn Power BI

Power BI DA-100 / PL-300 Free Practice Exam

2 thoughts on “Power BI DA-100 / PL-300 Free Practice Exam”

Leave a Reply Cancel reply