Model the data

By Daniil Maslyuk
11/5/2022

Contents

Skill 2.1: Design a data model
Skill 2.2: Develop a data model
Skill 2.3: Create model calculations by using DAX
Skill 2.4: Optimize model performance
Chapter summary
Thought experiment
Thought experiment answers

Thought experiment answers

The answer is C. Splitting a date/time column into a date and a time column will keep the original data and reduces the number of distinct values in columns, resulting in a smaller data model. Changing the data type to text, as answer A suggests, won’t change the number of distinct values, and therefore won’t reduce the size of the file. Answer B, cleaning a column, removes nonprintable characters, which does not reduce the number of distinct values when applied to a column of type date/time. If you change the data type of the DateTime column to date in accordance to answer D, you’ll see the reduction in the size of the model, and you’ll lose the time portion, which goes against the requirements.
The answer is B. Creating an active physical relationship is the best solution because it requires no DAX, allows the Calendar table to be used with tables other than Sale, and you can use other measures from the Sale table together with fields from the Calendar table. While using the TREATAS function that answer A suggests may work, it requires using unnecessarily complex DAX, especially considering that you may be interested in analyzing measures other than Revenue. The merged table from answer C will either prevent the Calendar table from being used with other tables, or it’ll duplicate data from the Calendar table unnecessarily. Answer D would fix the graph, but it won’t solve the problem when other measures are analyzed by year.
The answer is A. Power BI supports multiple roles for a single user, and they are combined by using the union logic, so the user will see all departments within the Central region and all regions within the Appliances department.
The answer is B. If you use the EOMONTH function, you’ll get a calculated column that contains the end-of-month dates, and you can then apply a custom format string to show the values in the desirable format. Since the values will still be of type date, they will be automatically sorted in the correct order. If we use the FORMAT function, you’ll get the values in the format that you want, though they will be text values that require a sorting column—otherwise the values will be sorted alphabetically. A sorting column will use extra storage. If you apply a custom format string to a duplicated Date column, the values will look the way you want, though underneath they will still be dates, so there will be more than one value for each month-year combination. By creating a new calculated table as in answer D, you are increasing the data model size and adding unnecessary complexity.
The answer is A. SUM will correctly aggregate inventory balances for all dimensions except Date, since ENDOFMONTH will pick the last date of month to show the monthly balance. In answer B, MAX is used as a filter in CALCULATE, and it won’t work because it returns a scalar value instead of a table. Answer C will provide incorrect values in cases where you have daily or weekly inventory balances. Answer D won’t aggregate the balances correctly, since it will pick the maximum balance across the available values.
The answer is B. Using the RELATED function in a calculated column often means duplicating data. If columns need to be in the same table—for example, to build a hierarchy—then it may be preferable to merge tables into one. Appending tables, as in answer A, would stack them vertically and wouldn’t provide the desired output. Separating tables in several data models, as answer C suggests, will reduce the size of the model, though it won’t allow you to have the same data. Hiding unused columns as suggested in answer D doesn’t reduce the size of the model.
The answer is C. Since the security is managed by Active Directory groups, the user should be removed from their old department security group and added to their new department security group. If you add them to a new role in the Power BI service without affecting their group membership, as suggested in answer A, they’ll see sales of both old and new departments. Changing roles in Power BI Desktop (answer B) won’t help because role membership is managed outside of Power BI Desktop.