data modeling in power bi coursera week 2 answers

Self-review: Adding a calculated table and column

In the exercise Adding a calculated table and column, you added calculated tables and columns to an existing data model in Power BI.

You imported the Sales, Products, Region, Date, and Salesperson tables into Power BI, removed all duplicate values, and ensured the model relationships were set correctly. You then created the required calculated tables and columns.

A screenshot of the final data model is included for reference:

Now it’s time to review your understanding of the tasks you complete by answering the following questions. Don’t forget that you can refer to previous lesson items to recap your process steps.

1. What does the DAX QUARTER function do when executed in the Date table?

  • It creates a column from the Year column of the Date table.
  • It converts the selected Date column data into a year’s quarter.
  • It returns a year’s quarter as a number extracted from the Date column.

2. You have created a new table called Yearly Sales by Color. What is the total number of columns in the table?

  • 11
  • 9
  • 12

3. What role did the RELATED function perform when you created the Yearly Sales calculated table? Select all that apply.

  • It referenced a column from the date table within the data model to retrieve the year information.
  • It referenced a column from the Sales table to retrieve the year information.
  • It referenced a column from an unconnected table within the data model to retrieve the year information.
  • It referenced a column from the product table within the data model to retrieve the color information.

Knowledge check: Using Data Analysis Expressions (DAX) in Power BI

4. You are a data analyst working with a large sales dataset for an e-commerce company. You are employing Power BI as an analytical tool. In which of the following scenarios would it be beneficial to use DAX functions? Select all that apply.

  • Calculating the average sales per region.
  • Filtering the data to display only this year’s sales.
  • Importing a new sales dataset.
  • Creating a column that flags high-value customers.

5. True or False: You can create a calculated table in DAX to generate a table based on the defined expression.

  • True
  • False

6. What is the purpose of the RELATED function in DAX?

  • To retrieve data from a related table.
  • To establish relationships between tables.
  • To filter data based on specific criteria.

7. Why should you clone a table in Power BI for use as a calculated table?

  • To perform calculations that are not possible on the original dataset.
  • To keep a backup of your original dataset.
  • To reduce the size of your data model.

8. Regarding DAX, what does the term "evaluation context" refer to?

  • The visualization and charts created by DAX formulas.
  • The connection between Power BI and external data sources.
  • The set of filters and context that influence DAX calculations.

Knowledge check: Introduction to measures

9. Which of the following statements about the measures is correct?

  • Measures can reference columns directly.
  • Measures store values in the model.
  • Measures can reference other measures directly.

10. Which DAX function can summarize the number of entries in a table?

  • SUM
  • COUNTROWS

  • AVERAGE

11. True or False: In DAX, a semi-additive measure can accumulate values over some dimension but typically not over time.

  • True

  • False

12. Which of the following DAX functions modifies the evaluation context of a specific calculation? Select all that apply.

  • CALCULATETABLE

  • MEDIAN
  • DIVIDE
  • CALCULATE

13. You want to calculate the total revenue for each product category in an Adventure Works sales dataset, but only for products over $1000. You can use the ___________ function to narrow down the products with a specified amount. You can then use the ____________ function to compute the final measure.

  • RELATED, FILTER
  • FILTER, CALCULATE

  • AVERAGEX, CALCULATE

Self-review: Adding a measure

In the exercise Adding a measure, you learned how to add a measure in Microsoft Power BI.

You downloaded the Adventure Works Power BI project to your desktop. You then created a quick measure to calculate the total sales data from the Sales table. You then created an additional measure to calculate total revenue using a DAX query. These measures helped Adventure Works to analyze its data based on its analytical and business requirements.

A screenshot of the data model is included for reference:

Now it’s time to review your understanding of the tasks you completed by answering the following questions. Don’t forget that you can refer to previous lesson items to recap your process steps.

14. In the exercise, you created a quick measure running total. Which of the following fields did you add as your base value?

  • The Order date column from the Sales table.
  • The Year column from the Date table.
  • The Total Sales column from the Sales table.

15. What methods could you have used to access the Quick Measure feature in Power BI? Select all that apply.

  • Access the feature through Power Query editor.
  • Select the New measure option from the Model view of Power BI desktop.
  • Access the feature from the drop-down menu using the ellipses under the Data pane.
  • Select the Quick Measure option from the Power BI Home tab.

16. True or False: You can use the SUM function instead of SUMX to calculate the Total Sales measure in DAX.

  • True
  • False

Knowledge check: Working with measures

17. How can measures help with data analysis in Power BI?

  • By combining data from various external data sources.
  • By performing calculations and aggregations for data visualization and analysis.
  • By creating new tables and relationships between data tables in your models.

18. In which scenario might the CROSSFILTER function be useful?

  • When establishing relationships within the tables.
  • When creating visualizations with custom filtering behavior.
  • When applying complex calculations within a measure.

19. True or False: Measures in Power BI are calculated columns that store results based on a specific DAX expression.

  • True
  • False

20. Which of the following are the DAX modifier functions? Select all that apply.

  • KEEPFILTERS
  • FILTER
  • CROSSFILTER
  • SUMX

21. You are analyzing a data model comprised of a Customers and an Orders tables. You want to analyze the number of customer orders from a specific region. Which of the following functions, combined with CROSSFILTER, can you use to generate these insights?

  • SUMMARIZE
  • CALCULATE
  • COUNTROWS

Self-review: Adding a role-playing dimension

Introduction

In the exercise Adding a role-playing dimension, you were provided with Adventure Works’ data and asked to analyze the company’s sales data based on shipping dates for a specific month.

You downloaded and connected to the Adventure Works dataset, reviewed the data model and established relationships, created a measure by writing a DAX expression and saved the Power BI project.

A screenshot of the results is included for reference:

Now it’s time to review your understanding of the tasks you complete by answering the following questions. Don’t forget that you can refer to previous lesson items to recap your process steps.

22. You added the date table as a role-playing dimension to analyze the August sales. What alternative method could you have used to perform the same analysis in Power BI?

  • Create a calculated table by extracting data from the Sales and Date tables.
  • Clone the B table in DAX and name it Shipping date table.
  • Create a calculated column in the Date table.

23. What was the purpose of the USERELATIONSHIP function in creating the August sales by shipping date measure for Adventure Works?

  • To establish custom relationships between the Sales and the Date table.
  • To filter the data based on specific conditions.
  • To define default relationships between the Sales and Date table.

24. True or False: In your Adventure Works data model, the default relationship between the Sales and Date tables is based on the order date. Could you make this relationship inactive? You can convert the default relationship between the sales and date table to inactive relationship in Power BI for a specific calculation?

  • True
  • False

Knowledge check: DAX and table relationships

25. How can you use an inactive relationship in a single measure?

  • Use the USERELATIONSHIP function in DAX.
  • Change the active relationship in the modeling view of Power BI.
  • Delete one relationship from the modeling tab of Power BI.

26. How does the USERELATIONSHIP function impact the relationship between tables in a data model?

  • It automatically generates relationships based on the column names.
  • It creates a new relationship between tables.
  • It overrides the existing relationship state between tables.

27. True or False: Role-playing dimensions in Power BI are multiple instances of the same dimension used within a single fact table.

  • True
  • False

28. True or False: The CROSSFILTER function in DAX allows users to establish custom relationships between tables.

  • True
  • False

29. An e-commerce company has a data model with an inactive relationship between the Product table and the Sales table. The active relationship is based on the Products sold column of the Product table, while the inactive relationship is based on the Products purchased column. You want to analyze sales data based on both Products sold and Products purchased. What are the possible solutions to analyze the data? Select all that apply.

  • You can delete the inactive relationship and use the Product dimension table for both analyses.
  • You can use the USERELATIONSHIP function.
  • You create a separate calculated table based on the Product table and rename it as Products Purchased.
  • You can use the CROSSFILTER function to get both analyses.

Self-review: Using time intelligence to compare to previous year

In the exercise Using time intelligence to compare to the previous year, you used DAX time intelligence functions to compare Adventure Works’ year-over-year sales growth.

You imported the Adventure Works Power BI data model, formed measures for an appropriate data type, and created a matrix to view the results of your time intelligence comparison.

A screenshot of the results is included for reference:

Now it’s time to review your understanding of the tasks you complete by answering the following questions. Don’t forget that you can refer to previous lesson items to recap your process steps.

30. In Power BI, time intelligence DAX functions require which of the following to work properly? Select all that apply.

  • Properly defined relationships between the tables.
  • A date dimension table with a column of dates.
  • Presence of calculated columns in the data model.
  • Data imported from multiple sources.

31. What is the value of change in the sales measure (YoY%) for August 2018?

  • -2.9%
  • -23.42%
  • 23.42%

32. True or False: To calculate the Sales previous year measure in DAX, you used the Revenue measure for your calculation. Is it possible to create the Sales previous year measure without using the Revenue measure?

  • True
  • False

Knowledge check: Time intelligence calculations in DAX

33. Why is it important to have a properly defined date column in the data model to execute time intelligence functions?

  • To ensure data accuracy and consistency in time-based calculations.
  • To enable the use of advanced data visualization techniques.
  • To create calculated columns and measures in the data model.

34. Which of the following features of Power BI can you use to add a date or common-dimension table to the data model? Select all that apply.

  • The Data modeling tab
  • Power Query and M language
  • Data Analysis Expressions
  • Manual data entry

35. True or False: DAX time intelligence functions can be used with Power BI’s autogenerated date/time dimension.

  • True
  • False

36. You are tasked with evaluating a company’s year-to-date sales. You need to analyze YTD (year-to-date) values by integrating additional filter context to the calculations like the Product category, Region, and Reseller datasets. Which of the following DAX functions can you use to complete this task?

  • DATEADD
  • DATESYTD
  • TOTALYTD

37. A business recently launched a marketing campaign for its product line and wants to determine its impact on sales within a specific period. What time intelligence function can you employ to evaluate the campaign’s impact on sales? Select all that apply.

  • DATESINPERIOD
  • DATEADD
  • DATESBETWEEN

Module quiz: Using DAX in Power BI

38. A marketing company wants to compare its daily website traffic. Which functions can it use to compare today’s web traffic with the day before?

  • CALCULATE with PREVIOUSDAY.

  • SUM with PREVIOUSDAY.
  • CALCULATE with DATEADD.

39. Which of the following functions in DAX can you use to alter the evaluation context of a calculation? Select all that apply.

  • USERELATIONSHIP
  • CALCULATE
  • FILTER

40. What is the outcome of the COUNTROWS function when deploy on a table with no filters applied?

  • It returns the total number of unique rows in a table.
  • It returns the total number of rows in a table.
  • It returns the total number of distinct values in a column.

41. True or False: The CROSSFILTER function can only establish a one-to-one relationship between tables.

  • True
  • False

42. How can you use an inactive relationship to compute a single measure?

  • By using the USERELATIONSHIP function.
  • By selecting Delete on the active relationship.
  • By changing the active relationship in the Modeling tab of Power BI.

43. An e-commerce company wants to analyze its sales, profit, and profit margin data. Which of the following scenarios should it use measures instead of calculated columns?

  • To calculate a percentage based on values from multiple columns in a table.
  • To add a new column to a table that performs aggregations on a numeric column.
  • To perform row-level calculations on a single column of a table.

44. Adventure Works has multiple stores located in different regions. Its dataset contains sales data for the past year stored in columns that include storeID, date, productID, quantity, and revenue. Which DAX function can you use to calculate the running total of revenue for each store over time? Select all that apply.

  • SUMX
  • RANKX
  • TOTALYTD

45. In the context of data model calculations, which statement best describes time intelligence?

  • Modified calculations in a filter context involving a date table.
  • Modified calculations involving COUNT, SUM and AVERAGE functions.
  • Modified calculations involving time-related dimensions.

46. Which of the following actions can you take to create time intelligence measures? Select all that apply.

  • Create a Date table using M and Power Query.
  • Use Power BI’s automatically generated date/time dimension for time intelligence functions.
  • Create a Date table using DAX.
  • Import a Date table from an Excel sheet.

47. When using the CALCULATE function, which of the following contexts can influence the overall evaluation context of the measure? Select all that apply.

  • Column context
  • Filter context
  • Row context

48. Which syntax can you use to compute sales data for the previous month?

  • Sales = CALCULATE
    (
    SUM ( Sales[Total Sales] ),
    PREVIOUSMONTH ( Date[DateKey] )
    )
     
  • Sales =SUM
    (
    Sales[Total Sales] ),
    PREVIOUSMONTH ( Date[DateKey] )
    )
     
  • Sales = CALCULATE
    (
    Sales[Total Sales] ),
    PREVIOUSMONTH ( Date[DateKey] )
    )

49. A car company is evaluating sales of its latest model. It changes the default evaluation context of its calculation to compute the total sales for a specific color. Which of the following functions can the company deploy to compute this value?

  • CALCULATE
  • FILTER
  • CROSSFILTER

50. You have a table containing information about customer orders and another table with customer details. Considering the relationship between the tables, how can you count the number of orders each customer places?

  • By using the COUNTROWS function with FILTER function.
  • By using the COUNTX function in combination with RELATED function.
  • By using the COUNTROWS function with the RELATED function.

51. The syntax of CROSSFILTER function is:

  • CROSSFILTER(____________________________)
     
  • Table1[Column1], [Column2], Direction
     
  • Table1[Column1], Table2[Column2], Direction

     
  • Table1, Table2[Column2], Direction

52. Why is the role-playing dimension useful in Power BI?

  • It allows for different views of the same dimension in a report.
  • It simplifies data modeling by eliminating the need for relationships.
  • It enables the creation of custom visuals in reports.

53. Which of the following is an advantage of using measures over calculated columns in DAX?

  • Measures can be used in calculated columns to create complex calculations.
  • Measures can be used for data transformation operations.
  • Measures are more efficient and require less memory in the data model.

54. Adventure Works has multiple stores located in different regions. Its dataset contains sales data for the past year stored in columns that include storeID, date, productID, quantity, and revenue. Which DAX functions can the company use to identify the date with the highest revenue for each store? Select all that apply.

  • TOPN
  • FILTER
  • RANKX
  • MAX

55. Which steps are required for creating a Date table using DAX in a data model? Select all that apply.

  • Establish a relationship between the data model’s Date table and other tables.
  • Create a calculated table in DAX.
  • Populate the table with related columns.
  • Add new columns to existing tables in Power Query editor.

56. A hospital’s emergency department wants to calculate the number of visitors it has received this year compared to the previous year. How can it compare the values in Power BI using DAX?

  • Using CALCULATE with the DATESBETWEEN function.
  • Using CALCULATE with the PREVIOUSYEAR function.
  • Using SUM with the PREVIOUSYEAR function.

57. A Sales table lists each transaction as a separate row. Which of the following DAX functions can summarize the total number of transactions in the table?

  • COUNTROWS
  • DISTINCTCOUNT
  • SUM

58. Your data model contains a role-playing dimension based on a Date table that analyzes each transaction's order and delivery dates. What solutions can you use to distinguish between the analyses based on the order and delivery dates? Select all that apply.

  • Create separate measures for the order date and delivery dates by using the USERELATIONSHIP function.
  • Use the same date hierarchy for the order and delivery dates.
  • Rename the role-playing dimension as Delivery date.
  • Clone the Date table and use it to create measures based on the Delivery date.

59. Which of the following statements describes the similarity of measures and calculated columns in an import model?

  • Both can be used to store values in a data model.
  • Both are evaluated during the data refresh.
  • Both can be used to summarize data.

60. True or False: Row context refers to the current row that DAX is processing. It helps to calculate formulas for individual rows in a table.

  • True
  • False

Leave a Reply