3F. Add calculated tables and columns to Power BI Desktop models Flashcards
What is a calculated table?
You can write a Data Analysis Expressions (DAX) formula to add a calculated table to your model. The formula can duplicate or transform existing model data to produce a new table.
A calculated table formula must return a table object. The simplest formula can duplicate an existing model table.
A calculated table can’t connect to external data; you must use Power Query to accomplish that task.
What is a cost of calculated tables, and why?
They increase the model storage size and they can prolong the data refresh time. The reason is because calculated tables recalculate when they have formula dependencies to refreshed tables.
When are calculated tables useful?
Calculated tables are useful to work in scenarios when multiple relationships between two tables exist, to create a role-playing dimension without having to rely on explicit DAX calls of inactive relationships (for example when a table contains two separate columns with date, like order and ship, or start and end).
They can also be used to add a date table to your model. Date tables are required to apply special time filters known as time intelligence.
What is the optional argument in the CALENDARAUTO function, and how is it used?
The CALENDARAUTO DAX function takes a single optional argument, which is the last month number of the year, and returns a single-column table. If you don’t pass in a month number, it’s assumed to be 12 (for December). For example, at Adventure Works, their financial year ends on June 30 of each year, so the value 6 (for June) is passed in.
How is the timespan in a date table decided by CALENDARAUTO?
The function scans all date and date/time columns in your model to determine the earliest and latest stored date values. It then produces a complete set of dates that span all dates in your model, ensuring that full years of dates are loaded. For example, if the earliest date that is stored in your model is October 15, 2021, then the first date that is returned by the CALENDARAUTO function would be July 1, 2021. If the latest date that is stored in the model is June 15, 2022, then the last date that is returned by the CALENDARAUTO function would be June 30, 2022.
What must be true for a table to be eligible for being marked as a date table?
- The table must include a column of data type Date.
- The column must contain complete years.
- The column must not have missing dates.
What is duplicated when create a calculated duplicate table?
A calculated table only duplicates data; it doesn’t duplicate any model properties or objects like column visibility or hierarchies. You’ll need to set them up for the new table, if required.
What is a calculated column?
You can write a DAX formula to add a calculated column to any table in your model. A calculated column formula must return a scalar or single value.
What is a cost of calculated columns, and why?
Calculated columns in import models have a cost: They increase the model storage size and they can prolong the data refresh time. The reason is because calculated columns recalculate when they have formula dependencies to refreshed tables.
What is a typical example of calculated columns?
Creating year, month, fiscal, etc, columns from a date column.
What is row context in the context of calculated columns?
The formula for a calculated column is evaluated for each table row. Furthermore, it’s evaluated within row context, which means the current row. When the formula is evaluated for each row, the ‘Due Date’[Due Date] column reference returns the column value for that row. You’ll find that Microsoft Excel has the same concept for working with formulas in Excel tables.
Row context does not extend beyond the current table, so how can formulas reference columns in other tables?
You have two options:
- If the tables are related, directly or indirectly, you can use the RELATED or RELATEDTABLE DAX function. The RELATED function retrieves the value at the one-side of the relationship, while the RELATEDTABLE retrieves values on the many-side. The RELATEDTABLE function returns a table object.
- When the tables aren’t related, you can use the LOOKUPVALUE DAX function.
Generally, try to use the RELATED function whenever possible. It will usually perform better than the LOOKUPVALUE function due to the ways that relationship and column data is stored and indexed.
What are the three ways of adding columns to a model table?
- Add columns to a view or table (as a persisted column), and then source them in Power Query. This option only makes sense when your data source is a relational database and if you have the skills and permissions to do so. However, it’s a good option because it supports ease of maintenance and allows reuse of the column logic in other models or reports.
- Add custom columns (using M) to Power Query queries.
- Add calculated columns (using DAX) to model tables.
What is the preferred way of adding columns?
The preference is to add custom columns in Power Query, whenever possible, because they load to the model in a more compact and optimal way.
When is it recommended to add columns by adding a calculated column with DAX (instead of as a custom column in Power Query)?
- When you need to add a column to a calculated table
- When the calculated column formula depends on summarized model data.
- When the calculated column formula needs to use specialized modeling functions that are only available in DAX, such as the RELATED and RELATEDTABLE functions. Specialized functions can also include the DAX parent and child hierarchies, which are designed to naturalize a recursive relationship into columns, for example, in an employee table where each row stores a reference to the row of the manager (who is also an employee).
