COURSE 8 – MICROSOFT PL-300 EXAM PREPARATION AND PRACTICE

Module 2: Modeling Data

MICROSOFT POWER BI DATA ANALYST PROFESSIONAL CERTIFICATE

Complete Coursera Study Guide

Enroll in Coursera Microsoft Power BI Data Analyst

Last updated:

TABLE OF CONTENT

INTRODUCTION – Modeling Data

In this module, you will engage in a comprehensive review of key concepts related to preparing and loading data in Power BI, all aimed at equipping you for the PL-300 exam. This process will involve revisiting fundamental principles as well as advanced techniques essential for efficient data handling within Power BI. By focusing on these critical aspects, the module ensures that you develop a solid understanding and the ability to apply these concepts effectively in practical scenarios. This thorough preparation will help build your proficiency and confidence, ultimately positioning you for success in the PL-300 exam.

Learning Objectives

  • Prepare for the PL-300 exam by revising how to model data in Power BI

SELF-REVIEW: WHAT DID YOU LEARN? 1

1. You are formatting the column properties of your data tables within your data model. In which section of Properties can you change the data type of the column?

  • Formatting (CORRECT)
  • General
  • Advanced

That’s correct! The Formatting section allows you to change the data type of the column as per your analytical needs

2. How many table storage modes does Microsoft Power BI Desktop support?

  • 3 (CORRECT)
  • 1
  • 4

That’s correct. Power BI allows you to choose any of the three data storage types it supports, namely, Import, DirectQuery, and Dual mode.

3. True or False: The DAX CALENDAR function automatically detects the data range of your dataset and creates a calculated date column

  • True
  • False (CORRECT)

That’s correct! The DAX CALENDARAUTO function automatically detects the date range from your dataset, while CALENDAR needs you to provide a date range.

4. Which of the following components of Microsoft Power BI allow you to add a description about your table? Select all that apply.

  • Report view
  • Model view (CORRECT)
  • Power Query Editor (CORRECT)
  • Data view

That’s correct! Model view in Power BI Desktop provides plenty of options to configure and format both table and column properties.

That’s correct! In Power Query Editor, you can rename the table as query and add a description about the query (table).

5. True or False: If your data model does not contain a date table, you will not be able to perform time intelligence calculations using DAX.

  • True
  • False (CORRECT)

That’s correct! Power BI’s Auto date/time functionality allows you to create time intelligence calculations with DAX even if your data model does not have a common date dimension table.

6. Which language can you use to create a common date table in Microsoft Power BI? Select all that apply.

  • DAX (CORRECT)
  • Python (CORRECT)
  • M (CORRECT)
  • C++

That’s correct! DAX is the most common way to generate a common date table within your data model.

That’s correct! You can create a date table using Pandas in Python and import it to a Power BI data model.

That’s correct! M and Query Editor is another way to create a universal date table, especially by implementing parameters so you can use the same date table for a variety of different data models.

7. True or False: You can only manage the relationships between data tables in the Model view of Power BI Desktop?

  • True
  • False (CORRECT)

That’s correct! Power BI’s Data view also allows you to select Manage relationship to open a dialog box where you can create and configure relationships.

8. True or False: To activate multiple inactive relationships within your data model, you need to write separate DAX code using the USERELATIONSHIP function in Power BI.

  • True
  • False (CORRECT)

That’s correct! You can nest up to 10 USERELATIONSHIP functions in a single expression to activate multiple inactive relationships.

9. Which of the following elements of a DAX formula is optional while writing a CALCULATE expression?

  • Column Name
  • Table Name
  • Filter (CORRECT)

That’s correct! In the short form of the CALCULATE syntax, you can omit the use of FILTER. The DAX engine executes the filter at the back end.

10. With a many-to-many relationship, how many options for cross-filter direction do you have?

  • One
  • Three (CORRECT)
  • Two

That’s correct! Many-to-many relationships can have single cross filter direction with two variations (that is Table A filters table B or Table B filters Table A) and both cross filter directions. Therefore, you have 3 options to choose from.

SELF-REVIEW: WHAT DID YOU LEARN? 2

1. Adventure Works’ US sales manager wants insights into the sales of a few states, and she asked you to present the sales and profit for California and Arizona only. What DAX functions can you use to compute the measures? Select all that apply.

  • FILTER (CORRECT)
  • CALCULATE (CORRECT)
  • SUM
  • SUMX (CORRECT)

That’s correct! The FILTER function applies the filter context within the CALCULATE function. For instance, FILTER narrows down the sales amount for the requested states.

That’s correct! You must use the CALCULATE function to apply any filter context, in this case, two states.

That’s correct! You must use SUMX to compute the total sales measure to use in other measures to compute the sales for required states.

2. The profit margin is an example of a/an _____________ measure.

  • Additive
  • Semi-additive
  • Non-additive (CORRECT)

That’s correct! Profit margin is a non-additive measure that cannot be summed across any dimension. You need to handle this using special DAX functions.

3. You are handling a semi-additive measure in Microsoft Power BI. What DAX functions are useful in managing a semi-additive measure? Select all that apply.

  • DATEADD
  • FIRSTDATE (CORRECT)
  • LASTNONBLANK (CORRECT)
  • CALCULATE (CORRECT)

That’s correct! FIRSTDATE computes the opening balance of stock in hand within the CALCULATE function.

That’s correct! The LASTNONBLANK function allows you the flexibility to compute the last stock balance even if the last day of the month is a holiday.

That’s correct! CALCULATE is the backbone of DAX. You must use this function whenever you need to integrate any filter context

4. Adventure Works wants a visualization displaying the opening balance of its products in stock for each month. Which DAX function is most suitable to use in computing your measure?

  • LASTDATE
  • FIRSTNONBLANK (CORRECT)
  • FIRSTDATE

That’s correct! With FIRSTNONBLANK, you can eliminate the blank values originated by holidays, thus providing you with the most accurate opening balance values.

5. True or False: In Microsoft Power BI, you do not need to write DAX script to visualize implicit measures on the report canvas

  • True (CORRECT)
  • False

That’s correct! Power BI automatically applies aggregations on implicit measures that you can use for your visualizations.

6. True or False: In Microsoft Power BI you can create a calculated column using DAX in the Power Query Editor.

  • True
  • False (CORRECT)

That’s correct! You can create calculated columns in the Report view, Data view or Model view of Power BI Desktop.

7. Which statement about measures in Microsoft Power BI is correct?

  • Measures can only be created using DAX.
  • Measures can be referenced by other measures. (CORRECT)
  • Measures are stored in the data model.
  • Measures are stored information in the data model.

That’s correct! You can reference any measure to compute other measures in DAX. For example, once created, you can use a Total Sales measure in a range of DAX calculations to integrate filter context.

8. Which DAX function can be used to compute the sales values between two specified dates?

  • DATESBETWEEN (CORRECT)
  • DATESINPERIOD
  • DATEADD
  • DATESYTD

That’s correct! You can specify any two dates within this DAX function to compute the sales values.

9. Which DAX function helps you to handle semi-additive measures?

  • LASTDATE (CORRECT)
  • DIVIDE
  • AVERAGE
  • SUMX

That’s correct! By using LASTDATE function in DAX you can compute semi-additive measures like inventory on hand.

SELF-REVIEW: WHAT DID YOU LEARN? 3

1. What is the maximum number of levels you can create in a hierarchy in Microsoft Power BI?

  • 4
  • 5 (CORRECT)
  • 3

That’s correct! In Power BI, you can create a maximum of five levels in a hierarchy.

2. How can you create a data hierarchy in Microsoft Power BI? Select all that apply.

  • Creating a new hierarchy and adding fields to the hierarchy (CORRECT)
  • By using the Power Query editor
  • By adding fields to the visual axis well (CORRECT)By using DAX (CORRECT)

That’s correct! You can convert the parent field to a hierarchy followed by adding the child fields.

That’s correct! You can bring all levels of a hierarchy to the visual axis in a particularn order. The visual then present presents the data in a hierarchical structure, allowing you to drill down and drill up as per your analysis needs.

That’s correct! You can use DAX to define the hierarchy paths. This is particularly useful when defining complex hierarchies like an organizational structure.

3. True or False: In Microsoft Power BI, you can only create calculated tables using DAX.

  • True
  • False (CORRECT)

That’s correct! You can also use M language in the Query editor to create calculated tables.

4. Microsoft Power BI’s Q&A visual lets users ask user-defined questions about the data. To help them swiftly analyze information and draw insights, what features of the Q&A visual can you configure? Select all that apply.

  • Relationships (CORRECT)
  • Teach Q&A (CORRECT)
  • Visual formatting
  • Synonyms (CORRECT)

That’s correct! You can define relationships between tables and fields to help Q&A understand questions about your data.

That’s correct! You can also teach Q&A to define custom terms. For example, if you want to identify the best salesperson, you can define the best salesperson as selling more than ten10 products per month by using DAX.

That’s correct! You can define synonyms for the data fields. For example, an item can be used as a synonym for product, and location can be used as a synonym for country.

5. True or False: When you write your question in the Q&A visual text box, Microsoft Power BI provides you with autocomplete suggestions. Can you modify the suggestions that appear in the Q&A visual?

  • True (CORRECT)
  • False

That’s correct! Power BI allows you to configure the suggestions that appear in the Q&A visual by defining synonyms, relationships, and DAX measures.

6. In addition to using DAX to create calculated tables, what is an alternative way to create calculated tables without using any external tool?

  • Python
  • M language (CORRECT)
  • SQL

That’s correct! You can duplicate tables and create new calculated tables by using M language in the Power Query editor.

7. Adventure Works received two separate tables representing the color of its various products. For seamless analysis and optimizing the data model, you only need a single table for colors. Which DAX table function can you use to combine data from two tables?

  • SUMMARIZE
  • ADDCOLUMNS
  • UNION (CORRECT)

That’s correct! With UNION you can combine two tables into a single unified table, therefore the best option is to combine two color tables.

8. Which of the following DAX functions is important when working with hierarchical data?

  • UNION
  • PATH (CORRECT)
  • ADDCOLUMNS
  • SUMMARIZE

That’s correct! With the PATH function you can return a delimited string containing the identifiers of all parents of the current identifier.

SELF-REVIEW: WHAT DID YOU LEARN? 4

1. You have Sales and Cost columns in your data table. The data type of the columns is set to Text. Which Microsoft Power BI element allows you to update the data types of the columns to Currency? Select all that apply

  • Report view (CORRECT)
  • Data view (CORRECT)
  • Model view (CORRECT)
  • Query Editor (CORRECT)

That’s correct! You can also update the data types from the Report view. You need to select the column from the Data pane, and the Column tools tab appears where you can apply changes.

That’s correct! You can change the data type of any column from the column tools available from the Data view of Power BI.

That’s correct! The Properties pane of the Model view lets you configure and update the data types of individual columns of the data table.

That’s correct! The Query Editor lets you update the data types of individual columns of the data table.

2. How does the Performance Analyzer assist in analyzing user interactions in Microsoft Power BI reports?

  • By capturing the impact of user interactions on report elements. (CORRECT)
  • By suggesting a better visual to use in the report.
  • By recommending the alteration in data refresh frequency.

That’s correct! The Performance Analyzer captures the impact of user interaction on various report elements, aiding in the optimization of report design for better responsiveness.

3. What is the primary benefit of correctly categorizing the location-based columns of your data model in Microsoft Power BI?

  • To eliminate data redundancy
  • To enable accurate geographical analysis and mapping (CORRECT)
  • To enhance the visual appearance of map visuals

That’s correct! Changing the data category of location-based columns to the appropriate category from the list of options available in Power BI enables accurate geographical analysis and mapping.

4. What type of insights does the Performance Analyzer provide? Select all that apply.

  • Information about field parameters. (CORRECT)
  • Information about the visual rendering time. (CORRECT)
  • Information about DAX query execution. (CORRECT)
  • Information about the columns used from the Data mode.

That’s correct! You can also observe information about the field parameters of your data mode in the Performance Analyzer.

That’s correct! The Performance Analyzer provides insights into the rendering of any visual elements of the report in the Performance Analyzer pane.

That’s correct! The Performance Analyzer provides the execution time of all DAX queries and thus can be optimized if necessary.

5. Which of the following data types would offer the optimal performance, reducing cardinality, for a currency field?

  • Text
  • Decimal Number
  • Fixed Decimal Number (CORRECT)

That’s correct! The Fixed Decimal Number data type specifies the exact number of decimal digits, mitigating performance issues in calculations.

CONCLUSION – Modeling Data

In conclusion, this module is meticulously designed to offer an extensive review of crucial concepts related to preparing and loading data in Power BI, with the ultimate goal of preparing you for the PL-300 exam. By delving into both fundamental and advanced techniques, you will gain a robust understanding and practical skills necessary for effective data handling within Power BI. This targeted preparation will not only enhance your proficiency but also boost your confidence, ensuring you are well-equipped to succeed in the PL-300 exam.

Previous Module
Next Module

Quiztudy Top Courses

Popular in Coursera

Liking our content? Then, don’t forget to ad us to your BOOKMARKS so you can find us easily!

Expanded Thumbnail Promo1

Mood Zone for Studying & Relaxing