INTRODUCTION – Modeling Data
The module will explore all critical details related to preparing and loading data into Power BI, which are part of the PL-300 exam preparation. Topics will include fundamental principles and advanced techniques that can be applied for efficient data handling in Power BI. This will ensure that you understand and can apply important principles in practice. Hence, the preparation is up to the hilt that will add sharpening to skill and confidence to succeed in the PL-300 exam.
Objective of Learning
- It revisits the power business modeling of data for preparation towards the PL-300 examination.
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! This formatting section allows you to change the type of data in a column as per your requirements for analysis.
2. How many table storage modes does Microsoft Power BI Desktop support?
- 3 (CORRECT)
- 1
- 4
That’s correct. Power BI lets you choose between the three data storage types, which include Import, DirectQuery, as well as 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 book has taught me the DAX CALENDARAUTO function, which automatically detects the date range from your dataset, whereas the CALENDAR function expects you to provide the starting and ending dates.
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! Here in the Power Query Editor, we can rename a table to the name of its query and add a description giving context 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! Auto date/time in Power BI allows you to do time intelligence calculations in DAX, even if you don’t have a shared date dimension table in your data model.
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++
Yes! DAX is the most common way to create a standard date table in your data model.
Indeed! You also can create a date table using Pandas in Python and pull it into your Power BI data model.
True! M and the Query Editor offer an alternate means of building a universal date table, again with parameters for flexibility across 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! A view of data in Power BI also enables users to select the option for “Manage relationships,” which opens up the ability to create and configure relationships among tables using a dialog box.
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 have a maximum of 10 USERELATIONSHIP functions nested in a single expression to activate several inactive relationships at once.
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 a short form of CALCULATE syntax, you can skip the use of FILTER, since the DAX engine will apply the filter automatically behind the scenes.
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 in Power BI can only be either of one cross-filter direction (i.e., either Table A filters Table B or Table B filters Table A) at a time as well as cross-filtering both tables. Thus, the three choices are available to you.
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)
Aha! It is the FILTER function which sets the filtering context in the CALCULATE function. For instance, it narrows down the sales amount considering the particular states.
Of course! CALCULATE is to be applied in order to set the filter context, in this case, for the two states.
Right! The SUMX function is used to calculate the total sales measure, which is then used as part of other measures to determine sales for the selected 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 metric that is non-additive, meaning it simply cannot be summed through any dimension. To deal with this, one needs to use specialized DAX functions meant for such calculations.
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! The FIRSTDATE function calculates the current opening stock balance inside the CALCULATE statement.
Exactly! LASTNONBLANK puts you into the appropriate context in calculating the last stock balance even if the last date of the month happens to be public holiday.
Right! CALCULATE is the most important and basic function of DAX; it is needed whenever you want to create a filter context for the calculations you are basing on that 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, it is possible to throw out the blank values that arise from holidays so that one may have an accurate opening balance value.
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! The aggregations that Power BI applies to the implicit measures can be directly used in 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! Calculated columns can be created in either 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! In DAX, you can reference any measure to calculate another measure. Once you create a Total Sales measure, it can be used in any DAX calculations that include 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! This DAX function is written in such a way that you can input any two dates to calculate the sales figure between them.
9. Which DAX function helps you to handle semi-additive measures?
- LASTDATE (CORRECT)
- DIVIDE
- AVERAGE
- SUMX
That’s correct! In other words, one can calculate the semi-additive measure production of stock on hand for the most recent date by utilizing the LASTDATE function in DAX.
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! It is possible to create a maximum of five levels in a hierarchy within Power BI.
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)
Exactly! You can transform the parent field into a hierarchy and then add the child fields accordingly.
True! All hierarchy levels can thus be arranged along a visual axis in a prescribed order. In such cases, the visual would illustrate that data in a smooth hierarchy, allowing easy drill down and drill-up according to corresponding analysis.
Correct! DAX can be used to define hierarchy paths, which is particularly useful for building larger hierarchies like an organizational chart.
“That is! It is even possible to convert the parent field into a hierarchy to add child fields accordingly.”
Indeed! In this way, all the library levels can be aligned spatially on a visual axis in a prescribed order. The visual would then illustrate such data as an almost seamless hierarchy through easy drill down or drill up according to the analysis it conducts.
Correct and does DAX define hierarchy paths, very beneficial in defining more complex hierarchies like those for developing an organization-wide chart.
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 the M language in the Query Editor to build 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)
Really! You can link tables and fields to the interpretation of questions about your data through Q&A.
That’s sure! Q&A can indeed be fine-tuned with custom terms, though. For instance, it could understand top salesperson as someone who sells over ten products per month using DAX expressions.
Sure! You can define synonyms to a data field that contribute to increasing user interaction. You could enhance findability by defining “item” as a synonym with “product” and “location” as a synonym with “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! The Q&A suggestions can be more tailored by providing synonyms, developing relationships, and DAX measures for enhancing query interpretation and user use experience with Power BI.
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! Pretty much in Power Query Editor, use the M language to clone existing tables or produce new computed tables that will suit the requirements of your analysis.
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 the UNION function in DAX, you can join two tables into one. This makes it perfect for merging two color tables into one seamless table.
8. Which of the following DAX functions is important when working with hierarchical data?
- UNION
- PATH (CORRECT)
- ADDCOLUMNS
- SUMMARIZE
That’s correct! A delimited string is returned by the PATH function in DAX, which lists all of the parent identifiers of the current identifier. It makes the data navigation more useful for hierarchically structured data.
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)
You can select a column from the Data pane and then use the Column tools tab to change data types in the Report view.
Column tools of the Data view give the user an option to change the data type of any columns in Power BI.
The Properties pane of the Model view allows you to configure and update the data types for the specific columns in your data table.
In the Query Editor, you could change the data types of individual columns during the data transformation process.
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! Performance Analyzer is a useful tool in Power BI as it tracks the time taken for every visual to load and interact with it to spot performance bottlenecks in reports.
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! You are perfect! In Power BI, when you change the data category of columns indicative of location (say country, state, city, and/or zip code) to an appropriate geographical category, one is sure that the analysis and mapping would be accurate.
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! In this case, the Fixed Decimal Number Data Type specifies a definite number of digits after the decimal point; thereby eliminating any performance issues during calculations.
CONCLUSION – Modeling Data
This module, therefore, aims at providing a complete guide entirely into all aspects required to prepare one’s self for data loading using Power BI. The underpinning motivation has been to get you adequately prepared for the course PL-300. You will have a good deal of an understanding right alongside practical application on basic and advanced techniques in handling data within Power BI as a result of energized preparation done to improvement of skill and boosts confidence in ensuring you do well in the PL-300 exam.