6. Add calculated tables and columns to pbi

row context

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, 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.

related work better than lookupvalue wrt performance (indexed and stored)

e.g. ordered (many) : product price (1)

Row context is used when calculated column formulas are evaluated. It’s also used when a class of functions, known as iterator functions, are used. Iterator functions provide you with flexibility to create sophisticated summarizations. Iterator functions are described in a later module.

Choose a technique to add a column

3 techniques

• 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.

recommend only use a calculated column when the calculated column formula:

• Depends on summarized model data.

• Needs to use specialized modeling functions that are only available in DAX

  • i.e. RELATED and RELATEDTABLE functions. Specialized functions can also include the DAX parent and child hierarchies, path pathitem to find recursive relationship
    • e.g. in an employee table where each row stores a reference to the row of the manager (who is also an employee).

• Calculated tables aren’t evaluated in any context.

• Calculated columns are evaluated in row context.

• for usecase of recursive analysis of hierarchy.

  • The columns require naturalizing the parent-child relationship into columns.
  • M doesn’t have built-in functions to complete this operation.