DA-100-Questions (1)

1
DA-100 Analyzing Data with Microsoft Power BI
Version 1.0
Topic 1, Prepare the Data

• QUESTION NO 1
• You have a Microsoft Excel workbook with a
  spreadsheet named Sales.
• You want to use the Sales spreadsheet in a Power
  BI dashboard report. How should you prepare the
  data on Sales spreadsheet?
• In Power BI service, import the data from the
  Sales spreadsheet.
• In Microsoft Excel, publish the Sales spreadsheet
  to the Power BI service.
• In Microsoft Excel, convert the data in the Sales
  spreadsheet to a flat table.
• In the Power BI service, upload the Excel
  workbook.
• Answer C Explanation
• Before you can load your Excel file into Power
  BI, you must organize your data in a flat table.
  In
• a flat table, each column contains the same data
  type for example, text, date, number, or
  currency. Your table should have a header row,
  but not any columns or rows that display totals.
• References
• https//docs.microsoft.com/en-us/power-bi/create-r
  eports/service-from-excel-to-stunning- report

QUESTION NO 2 You use Power BI Desktop to
create a visualization using a database on SQL
database server named SQL01. You want to be
able to use Microsoft R scripts in your
visualization.
2

• You install SQL Server R Services on SQL01.
• Does this action allow you to use Microsoft R
  scripts in your visualization?
• Yes, it does
• No, it does not
• Answer B Explanation
• By default, Power BI Desktop doesn't include,
  deploy, or install the R engine. To run R scripts
  in Power BI Desktop, you must separately install
  Microsoft R on your local computer.
• References
• https//docs.microsoft.com/en-us/sql/machine-learn
  ing/install/sql-r-services-windows-
  install?viewsql-server-2016

• QUESTION NO 3
• You use Power BI Desktop to create a
  visualization using a database on SQL database
  server named SQL01.
• You want to be able to use Microsoft R scripts in
  your visualization.
• You download and install Microsoft R Open on the
  computer that has Power BI Desktop installed.
  Does this action allow you to use Microsoft R
  scripts in your visualization?
• Yes, it does
• No, it does not
• Answer A Explanation
• By default, Power BI Desktop doesn't include,
  deploy, or install the R engine. To run R scripts
  in
• Power BI Desktop, you must separately install
  Microsoft R on your local computer.
• References
• https//docs.microsoft.com/en-us/power-bi/desktop-
  r-visuals
• QUESTION NO 4

3

• You use Power BI Desktop to create a
  visualization using a database on SQL database
  server named SQL01.
• You want to be able to use Microsoft R scripts in
  your visualization.
• You download and install Visual Studio on the
  computer that has Power BI Desktop installed.
  Does this action allow you to use Microsoft R
  scripts in your visualization?
• Yes, it does
• No, it does not
• Answer B Explanation
• By default, Power BI Desktop doesn't include,
  deploy, or install the R engine. To run R scripts
  in Power BI Desktop, you must separately install
  Microsoft R on your local computer.
• References
• https//docs.microsoft.com/en-us/power-bi/desktop-
  r-visuals

QUESTION NO 5 You have a table in Power BI
Desktop that contains the following data

• You want to create a custom column that only
  displays the last four digits of the SSN column
  and masks all the other digits.
• Which Power Query M formula should you use?
• Text.Replace(SSN, Text.Start(SSN,6),
  "xxx-xx")
• Text.ReplaceRange(SSN, 1, 1, "xxx-xx")
• Replacer.ReplaceText(SSN, SSN, "xxx-xx")

4
D. Text.TrimStart(SSN, 6) Answer A
References https//docs.microsoft.com/en-us/power
query-m/text-replace https//docs.microsoft.com/e
n-us/powerquery-m/text-start

• QUESTION NO 6
• You have a Power BI model that has a date table
  with a date column that displays the date in the
  format 2019-12-01
• You want the date column to display the date in
  the format of December 01, 2019. Which DAX
  formula should you use?
• FORMAT('Date'Date, _at_"MMM " " "DD" ", "
  "YYYY")
• FORMAT('Date'Date, "MMM") " "
  FORMAT('Date'Date, "DD") ", "
  FORMAT('Date'Date, "YYYY")
• FORMAT('Date'Date, "M") " "
  FORMAT('Date'Date, "D") ", "
  FORMAT('Date'Date, Year)
• FORMAT('Date'Date, "MMMM DD, YYYY") Answer D
• Explanation
• The DAX FORMAT function converts a value to text
  according to the specified format.
• Syntax FORMAT(ltvaluegt, ltformat_stringgt)
  References
• https//docs.microsoft.com/en-us/dax/format-functi
  on-dax

QUESTION NO 7
5

• You have the two Power Query M queries in Power
  BI Desktop. The one query retrieves a table
  named Customers from a Microsoft SQL Server
  database while the other query retrieves a table
  named _Customers from an Oracle database
• Both the Customers and the _Customers tables have
  the same columns. You need to combine the data
  from the two tables.
• Which Power Query function should you use?
• Combine Tables
• Merge Queries
• Merge Columns
• Append Queries
• Answer D References
• https//docs.microsoft.com/en-us/power-query/appen
  d-queries

• QUESTION NO 8
• You have a table named Sales. Sales has a column
  named CustomerID. The CustomerID column contains
  a few non-numeric values.
• You want to replace the non-numeric values in the
  CustomerID column with the number 0.
• From Query Editor, select the CustomerID column
  and click Replace Values. Then enter null in the
  Value To Find dialog box and 0 in the Replace
  With dialog box.
• From Query Editor, open Advanced Editor and add
  the following query step Table.ReplaceNullValues
  (Sales, "Customer_ID", 0)
• From Query Editor, select the CustomerID column
  and click Sort ascending. Then replace the null
  values with 0.
• From Query Editor, select the CustomerID column.
  Then select Fill gt Down.
• Answer A References
• https//docs.microsoft.com/en-us/power-query/repla
  ce-values

6

• QUESTION NO 9
• You want to use Power Query Editor to quickly
  ascertain the percentage of empty values in each
  column.
• You open the Column profile Data Preview option
• Does this action allow you to ascertain the
  percentage of empty values in each column?
• Yes, it does
• No, it does not
• Answer B Explanation
• Column quality In this section, we can easily
  see valid, Error and Empty percentage of data
  values associated with the Selected table.
• Note In Power Query Editor, Under View tab in
  Data Preview Section we can see the following
  data profiling functionalities
• Column quality
• Column distribution
• Column profile
• References

• QUESTION NO 10
• You want to use Power Query Editor to quickly
  ascertain the percentage of empty values in each
  column.
• You open the Column distribution Data Preview
  option
• Does this action allow you to ascertain the
  percentage of empty values in each column?
• Yes, it does
• No, it does not

7

• Answer B
• Explanation
• Column quality In this section, we can easily
  see valid, Error and Empty percentage of data
  values associated with the Selected table.
• Note In Power Query Editor, Under View tab in
  Data Preview Section we can see the following
  data profiling functionalities
• Column quality
• Column distribution
• Column profile
• References
• https//community.powerbi.com/t5/Community-Blog/Da
  ta-Profiling-in-Power-BI-Power-BI-
  Update-April-2019/ba-p/674555
• QUESTION NO 11
• You want to use Power Query Editor to quickly
  ascertain the percentage of empty values in each
  column.
• You open the Column quality Data Preview option

8
https//community.powerbi.com/t5/Community-Blog/Da
ta-Profiling-in-Power-BI-Power-BI-
Update-April-2019/ba-p/674555

• QUESTION NO 12
• You are creating a Power BI report that connects
  to a Microsoft Excel data source. You create a
  parameter named DS that specifies the file name
  and path for the data source.
• You want your query to use the DS parameter
  instead of a hard-coded copy of the location
  within your query definitions.
• What should you do?
• In the Power Query M code, replace references to
  the Excel file with the DS parameter.
• In your query definitions, modify the source step
  to use the DS parameter as the file path.
• Create a new query that use the DS parameter.
• Import the Microsoft Excel data to Power BI.
• Answer B Explanation
• Parameterising a Data Source could be used in
  many different use cases. From connecting to
  different data sources defined in Query
  Parameters to load different combinations of
  columns.
• Reference
• https//www.biinsight.com/power-bi-desktop-query-p
  arameters-part-1/

Topic 2, Model the Data
QUESTION NO 13 You have a Power BI model that
contains tables named Sales and Date. The Sales
and Date tables are shown in the exhibit
9

• The tables have the following relationships
• SalesDue_Date and DateDate
• SalesShip_Date and DateDate
• SalesOrder_Date and DateDate
• The active relationship is on SalesDue_Date.
• You want to create measures to calculate the
  number of orders by Ship_Date and the number of
  orders by Order_Date.
• Your solution must not result in duplicated data
  and must not require the loading additional data.
  Which two actions could you take? Each correct
  selection is a complete solution.
• Create a calculated table and then create a
  measure that uses the calculated table.
• Create measures that use the CALCULATE, COUNT,
  and USERELATIONSHIP DAX functions.
• Create two copies of the Date table named
  ShipDate and OrderDate. The create a measure
  that uses the ShipDate and OrderDate tables
• Create measures that use the CALCULATE, COUNT,
  and FILTER DAX functions.
• Answer BD References
• https//docs.microsoft.com/en-us/dax/calculate-fun
  ction-dax
• https//docs.microsoft.com/en-us/dax/count-functio
  n-dax https//docs.microsoft.com/en-us/dax/userel
  ationship-function-dax

QUESTION NO 14 Your company has a Power BI
dashboard that contains different visualizations
of sales. You enable QA on the dashboard.
10

• You want to provide users with a sample of
  questions that they can ask when using QA.
• What should you do?
• In Power BI Settings, configure Subscriptions
• In Power BI Settings, configure Workbooks
• In Power BI Settings, configure Dashboards
• In Power BI Settings, configure Datasets
• Answer D References
• https//docs.microsoft.com/en-us/power-bi/service-
  q-and-a-create-featured-questions

QUESTION NO 15 You have a Power BI model that
contains tables named Sales and Customers. The
Sales and Customers tables are shown in the
exhibit
There is a relationship between the Sales and
Customers tables. You want to rank the customers
based on their total sales amount. You run
the RANKX(ALL(Customers), SUMX(RELATEDTABLE(Sales)
, Sales_amount)) DAX formula. Does this
action produce the desired outcome? A. Yes, it
does
11
B. No, it does not Answer B References https//
msdn.microsoft.com/query-bi/dax/rankx-function-dax
QUESTION NO 16 You have a Power BI model that
contains tables named Sales and Customers. The
Sales and Customers tables are shown in the
exhibit

• There is a relationship between the Sales and
  Customers tables.
• You want to rank the customers based on their
  total sales amount. You run the
• RANK.EQ(SalesSales_amount, CustomersCustomer_ID
  )
• DAX formula.
• Does this action produce the desired outcome?
• Yes, it does
• No, it does not
• Answer B References
• https//msdn.microsoft.com/query-bi/dax/rankx-func
  tion-dax

12
QUESTION NO 17 You have a Power BI model that
contains tables named Sales and Customers. The
Sales and Customers tables are shown in the
exhibit

• There is a relationship between the Sales and
  Customers tables.
• You want to rank the customers based on their
  total sales amount. You run the
• RANKX(ALL(Sales), SUMX(RELATEDTABLE(Customers),
  Sales_amount)) DAX formula.
• Does this action produce the desired outcome?
• Yes, it does
• No, it does not
• Answer A References
• https//msdn.microsoft.com/query-bi/dax/rankx-func
  tion-dax
• QUESTION NO 18
• You have a Microsoft Excel workbook that has a
  Power Pivot model with the tables show in the
  following exhibit

13

• The model has the following relationships
• Sales to Product
• Sales to Salesperson
• You create a new Power BI file and import the
  Power Pivot model.
• You want to create a report that displays the
  count of products sold by each salesperson. What
  should you do before you can create the report?
• Create a one-to-one relationship between Product
  and Salesperson.
• For each relationship, change the Cross filter
  direction to Both.
• For each relationship, change the Cardinality to
  One to one (11).
• Create a many-to-one relationship between Product
  and Salesperson.
• Answer B References
• https//docs.microsoft.com/en-us/power-bi/transfor
  m-model/desktop-create-and-manage-
• relationships

QUESTION NO 19 DRAG DROP You have a Microsoft
SQL Server database with the tables show in the
following exhibit
14

• You create a Power BI model with the following
  relationships
• Sales to Product
• Sales to Salesperson
• You plan to add a table named Date to the model.
  The table will have columns for the date, year,
  month, and end of the last month, and will
  include data from January 1, 2018 to December 31,
  2020.
• The Date table and the Sales table will have a
  relationship. You need to create the columns for
  the Date table.
• Which DAX functions should you use? To answer,
  drag the appropriate DAX function to the answer
  area. You may need to drag the split bar between
  panes or scroll to view content.
• NOTE Each correct selection is worth one point.
  Answer D
• Incorrect Answers
• CALENDARAUTO uses fiscal date ranges
• ENDOFMONTH is not a DAX date function
• References
• https//docs.microsoft.com/en-us/dax/calendar-func
  tion-dax https//docs.microsoft.com/en-us/dax/yea
  r-function-dax

15
https//docs.microsoft.com/en-us/dax/month-functio
n-dax https//docs.microsoft.com/en-us/dax/eomont
h-function-dax https//docs.microsoft.com/en-us/d
ax/calendarauto-function-dax

• QUESTION NO 20
• You have a Power BI model for your company's
  sales data. You have a measure that calculates
  the year-to-date sales.
• You want to compare the year-to-date sales with
  the previous year for the same time period.
  Which DAX function should you use?
• TOTALYTD
• PARALLELPERIOD
• SAMEPERIODLASTYEAR
• PREVIOUSYEAR
• DATEADD
• Answer C References
• https//docs.microsoft.com/en-us/dax/sameperiodlas
  tyear-function-dax

QUESTION NO 21 HOTSPOT You have a Microsoft SQL
Server database that contains the tables as show
in the exhibit
16

• You want to modify the model to report on the
  number of orders.
• How would you calculate the number of orders?
• Create a calculated measure that uses the
  COUNTA('Orders'Order_id) DAX formula.
• Create a calculated column that uses the
  COUNTA('Orders'Order_id) DAX formula.
• Create a calculated column that uses the
  SUM('Orders'Order_id) DAX formula.
• Create a calculated measure that uses the
  SUM('Orders'Order_id) DAX formula.
• Answer A

QUESTION NO 22 HOTSPOT You have a Microsoft SQL
Server database that contains the tables as show
in the exhibit
17

• You want to create the following measure.
• Measure1 DISTINCTCOUNT(SalesProduct_id) You
  must create the following relationships
• Sales to Product
• Sales to Salesperson
• You want to be able to use Measure1 to display
  the count of products sold by each salesperson.
• How would you configure the relationships? To
  answer, select the appropriate options in the
  answer area.
• NOTE Each correct selection is worth one point.
  References
• https//docs.microsoft.com/en-us/power-bi/transfor
  m-model/desktop-create-and-manage- relationships
• QUESTION NO 23
• You have run two queries that have produced two
  different result sets. You want to concatenate
  the result sets into one query with all the
  combined rows.
• Which operation should you perform?

18

• Append
• Merge
• Combine columns
• Answer A Explanation
• Append will take two tables and combine it into
  one query.

QUESTION NO 24 You have a Power BI model that
contains tables named Sales and Customers. The
Sales and Customers tables are shown in the
exhibit

• There is a relationship between the Sales and
  Customers tables. There are no reporting
  requirements on Customer_ID.
• You need to optimize the data model.
• What should you configure for Customer_ID
  column?
• Delete the Customer_ID column.
• Hide the Customer_ID column.
• Sort the Customer_ID column.
• Change the datatype on the Customer_ID column.
• Answer B Explanation

19
Hide the Customer_ID column. A hidden column
can still be used in a calculation. So if you
dont use the field directly, you can make it
hidden, and use the calculation that generates
from it in the visualization. Incorrect
Answers A We cannot delete the Customer_ID
column as it is used in the relationship. Referen
ce https//radacad.com/what-fields-to-hide-in-you
r-power-bi-solution
QUESTION NO 25 You plan to create a report in
Power BI Desktop. You have the following tables.

• The Date table is marked as a date table. DateID
  is the date data type.
• Your report must include an annual sales growth
  percentage measure. Which DAX expression should
  you use?
• CALCULATE(SUM(SalesSalesAmount),
  SAMEPERIODLASTYEAR('Date'DateID)
• SUM(SalesSalesAmount) - CALCULATE(SUM(SalesSale
  sAmount), SAMEPERIODLASTYEAR('Date'DateID)) /

20
CALCULATE(SUM(SalesSalesAmount),
SAMEPERIODLASTYEAR ('Date'DateID) C.
CALCULATE(SUM(SalesSalesAmount)),
DATESYTD('Date'DateID)) D. SUM(SalesSalesAmoun
t) - CALCULATE(SUM(SalesSalesAmount),
SAMEPERIODLASTYEAR('Date'DateID) Answer B
Reference https//docs.microsoft.com/en-us/dax/sa
meperiodlastyear-function-dax

• QUESTION NO 26
• You have five sales office. Each office has a
  single sales person.
• You have an imported dataset that has a dynamic
  row-level security (RLS) role named Sales. The
  Sales role filters sales transaction data by the
  sales person. Each sales person must see only the
  data from their office.
• You publish the dataset to the company website,
  set RLS role membership, and distribute the
  dataset and related reports to the sales persons.
• A sales person reports that data is missing from
  the dataset that she can view. You need to
  verify what data the salesperson currently sees.
• What should you do?
• Use the Test as role option to view data as the
  salesperson's user account.
• Filter the data in the reports to match the
  intended logic in the filter on the sales
  transaction table.
• Instruct the salesperson to open the report in
  Microsoft Power BI Desktop.
• Use the Test as role option to view data as the
  Sales role.
• Answer D Reference
• https//www.sqlshack.com/introduction-to-row-level
  -security-in-power-bi/

21
Topic 3, Visualize the Data

• QUESTION NO 27
• You have several Power BI visualizations that you
  want to embed in a public website. Which two
  visualizations can you embed into the website?
• NOTE Each correct selection is worth one point.
• Visualizations that use row-level security (RLS)
• Visualizations that use datasets stored in
  Microsoft OneDrive for Business
• Custom visualizations
• Visualizations that contain reports shared to
  your user account
• QA for Power BI visuals
• Visualizations that use report-level DAX
  measures.
• Answer BC References
• https//docs.microsoft.com/en-us/power-bi/service-
  publish-to-web

• QUESTION NO 28
• You have a workspace that contains a dashboard
  named Sales Data. The Sales Data dashboard
  displays data from two datasets.
• Users complain that natural language queries on
  the dashboard often returns on results. You want
  users to be able find data by using natural
  language queries.
• You open the settings of the workspace and modify
  the Language Settings. Does this action allow
  users to find data by using natural language
  queries?
• Yes, it does
• No, it does not
• Answer B References

22
https//docs.microsoft.com/en-us/power-bi/create-r
eports/service-q-and-a-direct-query

• QUESTION NO 29
• You have a workspace that contains a dashboard
  named Sales Data. The Sales Data dashboard
  displays data from two datasets.
• Users complain that natural language queries on
  the dashboard often returns on results. You want
  users to be able find data by using natural
  language queries.
• You open the properties of the datasets and
  modify the QA and Cortana settings. Does this
  action allow users to find data by using natural
  language queries?
• Yes, it does
• No, it does not
• Answer A References
• https//docs.microsoft.com/en-us/power-bi/create-r
  eports/service-q-and-a-direct-query

• QUESTION NO 30
• You have a workspace that contains a dashboard
  named Sales Data. The Sales Data dashboard
  displays data from two datasets.
• Users complain that natural language queries on
  the dashboard often returns on results. You want
  users to be able find data by using natural
  language queries.
• You open the properties of the Sales Data
  dashboard, modify the QA settings. Does this
  action allow users to find data by using natural
  language queries?
• Yes, it does
• No, it does not
• Answer B

23
References https//docs.microsoft.com/en-us/power
-bi/create-reports/service-q-and-a-direct-query

• QUESTION NO 31
• You have a Microsoft Excel spreadsheet that
  contains a table named Sales. You want to add
  the Sales table to a Power BI dashboard as a
  tile.
• How should you configure the tile?
• From the Power BI service, import the data from
  the Excel workbook.
• From Excel, publish the workbook to the Power BI
  service.
• From the Power BI tab in Excel, pin the table.
• From the Power BI service, upload the Excel
  workbook.
• Answer C References
• https//docs.microsoft.com/en-us/power-bi/collabor
  ate-share/service-analyze-in-excel

• QUESTION NO 32
• You have a dashboard that has QA enabled.
• When a user runs the query "count of clients" in
  QA, the user does not receive any results.
  However, when the user runs the query "count of
  subscribers", the user receives the expected
  results.
• You want the user to receive the same results
  when using either query. Which action should you
  take?
• In the dashboard, enable and configure Data
  classification.
• In your data model, add synonyms to tables and
  columns.
• In Power BI, create featured questions.
• Add a description of "subscribers" to the
  dashboard.
• Answer B Explanation

24
You can add synonyms to tables and
columns. Note This step applies specifically to
QA (and not to Power BI reports in general).
Users often have a variety of terms they use to
refer to the same thing, such as total sales, net
sales, total net sales. You can add these
synonyms to tables and columns in the Power BI
model. This step applies specifically to QA
(and not to Power BI reports in general). Users
often have a variety of terms they use to refer
to the same thing, such as total sales, net
sales, total net sales. You can add these
synonyms to tables and columns in the Power BI
model. References https//docs.microsoft.com/en-
us/power-bi/natural-language/q-and-a-best-practice
s

• QUESTION NO 33 DRAG DROP
• You have a Power BI model that contains a table
  named Sales. The Sales table has the three
  measures named Sales Last Year, Sales This Year,
  and Sales Difference
• Sales Last Year displays the sales from the
  previous calendar year. The current value is
• 32.89 million.
• Sales This Year displays the sales from the
  current calendar year. The current value is
• 11.69 million.
• Sales Difference uses the DAX formula of
  SalesLast Year SalesThis Year.
• You need to create the following visualization.

How should you configure the visualization? To
answer, move the measures to match the fields in
the answer area. Each measure can be used once,
more than once, or not at all. NOTE Each correct
selection is worth one point. References https/
/docs.microsoft.com/en-us/power-bi/visuals/power-b
i-visualization-radial-gauge-charts
25

• QUESTION NO 34
• You are creating a visual that will rank product
  categories by sales revenue. Your solution must
  ensure that data is not sent outside of your
  Microsoft Power BI tenant.
• What should you do?
• Use default or any custom visuals from the
  marketplace.
• Use default or certified custom visuals.
• Use default visuals or custom visuals uploaded
  from a .pbiviz file.
• Use only default visuals.
• Answer B Reference
• https//datasavvy.me/2019/02/28/what-data-is-being
  -sent-externally-by-power-bi-visuals/

• QUESTION NO 35
• You have a report that contains several
  visualizations. One of the visualizations is a
  KPI visualization.
• You need to filter all the visualizations in the
  report except the KPI visualization. Which two
  actions should you perform? Each correct answer
  is part of the solution. (Choose two.)
• NOTE Each correct selection is worth one point
• Add the same slicer to each page and configure
  Sync slicers.
• Edit the interactions of the KPI visualization.
• Configure a page-level filter.
• Edit the interactions of the slicer that is on
  the same page as the KPI visualization.
• Configure a report-level filter.
• Answer AB Reference
• https//docs.microsoft.com/en-us/power-bi/create-r
  eports/service-reports-visual-interactions

QUESTION NO 36
26

• You are using Microsoft Power BI. You want to add
  multimedia objects that are published to
  Microsoft Stream to the Power BI dashboard.
• What action should you take?
• Add a Video tile
• Add a Custom streaming data tile
• Add a Web content tile
• Add an Image tile
• Answer B Explanation
• The only way to visualize a streaming dataset is
  to add a tile and use the streaming dataset as a
  custom streaming data source.
• References
• https//docs.microsoft.com/en-us/power-bi/create-r
  eports/service-dashboard-add-widget
  https//docs.microsoft.com/en-us/power-bi/connect-
  data/service-real-time-streaming

• QUESTION NO 37
• You are developing a report page in Microsoft
  Power BI Desktop.
• Some of your users will use a screen reader to
  consume the report. You want these users to
  consume report in a chronological order. What
  action should you take?
• In Microsoft Power BI Desktop, configure the tab
  order
• In Microsoft Power BI Desktop, configure the
  layer order
• In Microsoft Power BI Desktop, configure the
  bookmark order
• In Microsoft Power BI Desktop, configure the sort
  order
• Answer A Explanation
• Report authors can set the tab order for objects
  and visuals.
• References
• https//powerbi.microsoft.com/en-us/blog/power-bi-
  desktop-december-2018-feature- summary/

27
https//docs.microsoft.com/en-us/power-bi/create-r
eports/desktop-accessibility-consuming- tools
Topic 4, Analyze the Data
QUESTION NO 38 You have a Power BI model that
contains tables named Sales and Customers. The
Sales and Customers tables are shown in the
exhibit

• There is a relationship between the Sales and
  Customers tables.
• The Sales table contains more than one million
  records.
• You want to import a sample of the data from the
  Sales table. What action could you take?
• In the Power BI model, create a calculated table.
• In the Power Query Editor, transform the Sales
  table.
• In the Power Query Editor, filter the Sales table
  by Sales_Date.
• In the Power Query Editor, create a column by
  using Column From Examples.
• Answer C

QUESTION NO 39
28
You have the tables show in the following exhibit

• More than 10 million Sales records are generated
  every month.
• You want to create an analytics system displays
  the number of sales per day, per product, and
  per sales person. You also want to ensure that
  the data model is as small as possible.
• You create the following many to one
  relationships
• Sales to Product
• Sales to Salesperson
• What should you do next?
• For each relationship, change the Cross filter
  direction to Both.
• Group the sales by Order_Date, Product_id, and
  Salesperson_id. Then aggregate by using the
  CountRows function.
• Create a new group on the Sales_Amount column and
  set the Group type to Bin.
• Create a new hierarchy that summarizes the data
  by the Sales_Amount column.
• Answer B References
• https//docs.microsoft.com/en-us/power-bi/transfor
  m-model/desktop-create-and-manage-
• relationships

29

• QUESTION NO 40
• Your company has a Microsoft Excel file that
  stores purchase data. The Excel file has a date
  and time column named Date_Time. The Date_Time
  column contains data in the format
• 2020-08-13 134683
• You want to use a built-in date hierarchy to
  analyze the purchase data by the date. How
  should you prepare the data in the Date_Time
  column?
• Create a script that removes the time portion of
  the Date_Time column.
• Add a new column named Date and use regular
  expressions to trim the data to the first 10
  characters.
• Add a new column named Date using the following
  formula TransformColumnTypes(Source,"Date_Time
  ", type datetime)
• Add a new column by example that starts with
  2020-08-13. Set the data type of the new column
  to Date.
• Answer D Explanation
• To use a built-in-date hierarchy, you need to set
  the data type of the new column to Date.
• Reference
• https//docs.microsoft.com/en-us/power-bi/create-r
  eports/desktop-add-column-from-example
  https//www.exceljetconsult.com.ng/home/blog/power
  -query-split-date-and-time-into-
  separate-columns/

• QUESTION NO 41
• You have a dataset that contains sales data by
  sales person.
• You want to create a visual that shows the top 5
  performing sales persons based on their total
  sales value.
• You create a visual with a Top N filter.
• Does this action show the top 5 performing sales
  persons based on sales value?
• Yes, it does
• No, it does not

30
Answer A Explanation Power BI Top N Filters
are useful to display the top performing records,
and Bottom N filters are helpful to display the
least performing records. It works the same as
the TOP clause in SQL Server. References https/
/powerbidocs.com/2020/01/21/power-bi-top-n-filters
/

• QUESTION NO 42
• You have a table maned Sales that contains sales
  data by sales person.
• You want to create a visual that shows the top 5
  performing sales persons based on their total
  sales value.
• You use the following DAX function
• TOPN(5, Sales, orderBy (SalesPerson, TotalSales))
• Does this action show the top 5 performing sales
  persons based on sales value?
• Yes, it does
• No, it does not
• Answer B Explanation
• Syntax is incorrect.
• References
• https//docs.microsoft.com/en-us/dax/topn-function
  -dax

QUESTION NO 43 Your company has a data model for
its sales data. The sales data has a column named
Date. You want to view the data by week. What
action should you take to quickly view sales data
by week?
31

• Create a new aggregation that summarizes by date.
• Create a new group on the Date column and set the
  Group type to List.
• Create a new group on the Date column and set the
  Group type to Bin.
• Create a new hierarchy that summarizes by date.
• Answer C Explanation
• You can set the bin size for numerical and time
  fields in Power BI Desktop.
• Incorrect Answers
• D To add week hierarchy you would need to create
  a separate date table using CALENDARAUTO and
  then add a column using WEEKNUM to that table.
  You would also need to add columns using YEAR,
  FORMAT(Date,"mmmm" and a quarter function using
  SWITCH. Then you could build a hierarchy
  manually using these columns. Binning would be
  easier and quicker.
• Reference
• https//docs.microsoft.com/en-us/power-bi/create-r
  eports/desktop-grouping-and-binning
  https//www.mssqltips.com/sqlservertip/4720/binnin
  g-and-grouping-data-with-power-bi/
  https//community.powerbi.com/t5/Desktop/Adding-we
  ek-to-date-hierarchy/m-p/427762

Topic 5, Deploy and Maintain Deliverables QUESTI
ON NO 44 You have a user named MiaHamm. MiaHamm
is a member of a security group named PowerBI
Users. MiaHamm has access to a workspace. You
want to prevent MiaHamm from exporting data from
the visualizations in the workspace. You open
Microsoft 365 Admin center and remove MiaHamm
from the All Users security group. Does this
action prevent MiaHamm from exporting data from
the visualizations in the workspace? A. Yes, it
does
32
B. No, it does not Answer B

• QUESTION NO 45
• You have a user named MiaHamm. MiaHamm is a
  member of a security group named PowerBI Users.
• MiaHamm has access to a workspace.
• You want to prevent MiaHamm from exporting data
  from the visualizations in the workspace.
• You open Microsoft 365 Admin center and modify
  the properties of the PowerBI Users security
  group.
• Does this action prevent MiaHamm from exporting
  data from the visualizations in the workspace?
• Yes, it does
• No, it does not
• Answer A References
• https//docs.microsoft.com/en-us/power-bi/collabor
  ate-share/service-manage-app-workspace-
  in-power-bi-and-office-365

• QUESTION NO 46
• You have an app workspace named Sales Analysis in
  the Power BI service. You have the following
  requirements
• Manage users that have access to the app
  workspace
• Your solution must require the least amount of
  administrative effort. What should you do?
• A. In the Microsoft 365, select Users.

33

• In the Power BI, select Tenant settings.
• In the Power BI, select Usage metrics.
• In the Microsoft 365, select Members.
• Answer D Explanation
• Access to workspace is managed on the Members tab
  in either Microsoft 365 or Power BI
• Incorrect Answers
• B, C In terms or workspace, you can use Power BI
  to
• Add or remove workspace members, including making
  a workspace member an admin.
• Edit the workspace name.
• Delete the workspace, which also deletes the
  Microsoft 365 group.
• References
• https//docs.microsoft.com/en-us/power-bi/collabor
  ate-share/service-manage-app-workspace-
  in-power-bi-and-office-365

• QUESTION NO 47
• You have Power BI report that is published to
  your company's public website.
• You want the report on the website to update
  automatically when the data is refreshed. What
  should you do in the Power BI service?
• Publish the app workspace to the entire
  organization. In the website, use the URL of the
  app workspace.
• Share the report. In the website, use the URL of
  the dashboard.
• Publish the report to the web. In the website,
  use the embed code URL.
• In the website, use the URL of the report.
• Answer C References
• https//docs.microsoft.com/en-us/power-bi/collabor
  ate-share/service-publish-to-web

QUESTION NO 48 Your company has a Power BI
workspace with several shared dashboards.
34

• You need to provide a user named
  user1_at_company.com with the permissions to edit
  and publish dashboards.
• What should you do?
• Modify the members of the app workspace.
• Configure security for the dataset used by the
  app.
• Modify the Access settings of the dashboards.
• In workspace, select Update, and then configure
  the Access settings.
• Answer C

• QUESTION NO 49
• You are creating a Power BI report based on data
  from an Azure Analysis Services cube. You want
  the Power BI report to update as soon as data in
  the cube is refreshed.
• How should you connect to the Azure Analysis
  Services cube?
• Import
• Connect Live
• Direct query
• Answer B Explanation
• Azure analysis service can be connected with
  Power BI using direct query and import mode. But
• here in this question user wants to see the data
  immediately in the power bi report once data
  refresh done so we should use Connect live
  connectivity mode.
• References
• https//docs.microsoft.com/en-us/power-bi/connect-
  data/desktop-analysis-services-tabular- data
• QUESTION NO 50
• Your company has a Microsoft Power BI workspace.

35

• You are required to grant a user that has a user
  account named MiaHamm with the permissions
  required to create and publish Power BI apps.
• You must not provide the MiaHamm account with
  excessive permissions. What role should you
  assign to the MiaHamm account?
• Admin
• Member
• Contributor
• Viewer E Owner
• Answer B Incorrect Answers
• C Contributors can update the app metadata but
  not publish a new app or change who has
  permission to the app.
• Reference
• https//docs.microsoft.com/en-us/power-bi/collabor
  ate-share/service-new-workspaces