Model the data

Thought experiment

In this thought experiment, demonstrate your skills and knowledge of the topics covered in this chapter. You can find the answers in the section that follows.

You are a data analyst at Contoso responsible for creating Power BI reports.

Management has requested a report based on the historical data available. Based on background information and business requirements, answer the following questions:

1. A data model has a fact table that has over 15 million rows. There is a date/time column called DateTime, which contains both date and time. You need to reduce the size of the data model. Your solution must preserve as much of the original data as possible. Which solution should you implement?

   1. Change the data type of the DateTime column to Text.

   2. Clean the DateTime column.

   3. Split the DateTime column into two separate columns: one column that contains dates, and one column that contains the time portion.

   4. Change the data type of the DateTime column to Date.

2. You create a visual that is supposed to show revenue by year. You use the Year column from the Calendar table and the Revenue measure from the Sale table. The formula of the Revenue measure is as follows:

   Revenue = SUM(Sale[Total Including Tax])

   The result is shown in Figure 2-31. After checking data, you can see that in 2021, revenue was $60 million. How can you fix the visual? The solution must use the minimum amount of DAX and ensure that the Calendar table can be used with other fact tables. The solution must also take into account that you may be interested in analyzing other measures based on the Sale table.

   

   FIGURE 2.31 Revenue by Year

   1. Use the TREATAS function in DAX.

   2. Create an active relationship between the Calendar and the Sale tables.

   3. Merge the Sale and Calendar tables.

   4. Create a calculated table that calculates revenue for each year.

3. There are two roles in a data model: CentralRegion, which filters the Region table to only show the Central region, and AppliancesDepartment, which filters the Department table to only show the Appliances department. A user is a member of both roles. What will they see in a sales report?

   1. Sales from the Central region or the Appliances department; they will see all departments in the Central region and all regions in the Appliances department.

   2. Sales from the Central region and the Appliances department; they will only see the Appliances department within the Central region.

   3. Only one role will be applied, whichever was configured first.

   4. The user will see an error message.

4. Your Date table currently consists only of one column called Date, which contains dates. You need to add a column to the Date table that shows month and year in the MMMM YYYY format, e.g., May 2022. What should you do? Your solution must require the minimum amount of effort and storage, and the solution must ensure that the values are sorted chronologically.

   1. Create a calculated column that uses the FORMAT function.

   2. Create a calculated column that uses the EOMONTH function format as MMMM YYYY.

   3. Duplicate the Date column and apply a custom format string.

   4. Create a new calculated table called Date – MMMM YYYY and format as MMMM YYYY.

5. You need to write a measure that calculates the monthly balance. Which formula should you use?

   1. CALCULATE(SUM(Inventory[Balance]), ENDOFMONTH('Date'[Date]))

   2. CALCULATE(SUM(Inventory[Balance]), MAX(Inventory[Date]))

   3. CALCULATE(SUM(Inventory[Balance]), DATESMTD(Inventory[Date]))

   4. CALCULATE(MAX(Inventory[Balance]), LASTDATE(Inventory[Date]))

6. You inherit a Power BI data model that contains several tables, one of which has many calculated columns that all use the RELATED function. You would like to reduce the size of the model. What should you do?

   1. Append tables.

   2. Merge tables.

   3. Separate tables into several data models.

   4. Hide unused columns.

7. You created a sales report and enabled row-level security on it. There are multiple roles, each filtering the dataset to one department. Each role has a corresponding Active Directory group. The report is primarily used by sales managers, each of whom can view their department only. One sales manager has recently moved from one department to another. What should you do?

   1. Update role membership in the Power BI service.

   2. Change roles in Power BI Desktop.

   3. Raise a request to remove the user from their old Active Directory group and add them to the new one.