Power Pivot Principles: The A to Z of DAX Functions – INFO.CALCULATIONITEMS

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at INFO.CALCULATIONITEMS. 

The INFO.CALCULATIONITEMS function

Dynamic Management Views (DMVs) are specialised queries provided by SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and Power BI that offer an administrative view into the internal state of these systems.  DMVs are used to retrieve metadata, monitor health and performance, and diagnose problems within the database or data model.  They serve as a powerful tool for administrators and developers to gain insights into the workings of the database engine and the tabular data model, covering aspects like performance metrics, configuration settings and the structure of database objects.

The $System schema DMVs in SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and Power BI are categorised into four [4] types, each serving specific purposes:

• DISCOVER: requires admin privileges and provides information about the model, including details on connected sessions and environment configuration
• DMSCHEMA: focused on data mining, offering insights for predictive analytics and pattern recognition, mainly used in SSAS/AAS
• MDSCHEMA: targets multidimensional models, delivering metadata and structure from an MDX perspective, relevant for OLAP cubes and dimensions
• TMSCHEMA: designed for tabular models, it provides detailed metadata about tables, columns, measures, etc., using Tabular Model Scripting Language (TMSL) information, crucial for Power BI and tabular SSAS/AAS models.

In the past, if we want to query those $System schema DMVs we use external tool like Tabular Editors or DAX Studio to query them:

Now, we just need to write a simple DAX syntax to query those TMSCHEMA directly in DAX query view.  In this instance, we are using INFO.CALCULATIONITEMS function to query the TMSCHEMA_CALCULATION_ITEMS. 

The INFO.CALCULATIONITEMS function is known  as one of the system functions.  It employs the following syntax:

i.e. this function has no parameters.

Based upon the ‘[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol’ from Microsoft (which you may access here), the CalculationItem object represents a value that is calculated based upon an expression.  It is a child of a CalculationGroup object and requires compatibility level 1500 or higher.

We can write this INFO.CALCULATIONITEMS function in DAX query view to get the same information on the TMSCHEMA_CALCULATION_ITEMS:

It will query the $SYSTEM.TMSCHEMA_CALCULATION_ITEMS and return an entire table with ten  [10] columns:

• ID: this represents a reference to the object
• CalculationGroupID: this represents an ID-based reference to a CalculationGroup object
• FormatStringDefinitionID: this represents an ID-based to a FormatStringDefinition object
• Name: this presents the name of the object
• Description: this represents the description of the object
• ModifiedTime: this is the time that the object was last modified
• State: this is a value that provides information about the state of the calculation item.  The possible values are as follows:
  • Ready (1): the calculation item is able to be queried and has up-to-date data
  • NoData (3): not applicable to CalculationItem
  • CalculationNeeded (4): not applicable to CalculationItem
  • SemanticError (5): the CalculationItem expression has a semantic error
  • EvaluationError (6): not applicable to CalculationItem
  • DependencyError (7): a dependency associated with this calculation item is in an error state (SemanticError, EvaluationError or DependencyError)
  • Incomplete (8): not applicable to CalculationItem
  • SyntaxError (9): the CalculationItem has a syntax error in its expression
• ErrorMessage: this represents the string that explains the error state associated with the current object.  It is set by the engine only when the state of the object is one of these three values: SemanticError, DependencyError or EvaluationError
• Expression: this represents the DAX expression that is evaluated for the calculation item
• Ordinal:  this represents the zero-based ordinal value that is associated with a calculation item.  This value is meant to be used as the ordering column in the DAX query.

It should be noted that:

• it is used for querying the DMV (Dynamic Management Views) from the $System schema called TMSCHEMA where TM stands for ‘Tabular Model’ and TMSCHEMA provides information from the tabular model
• sometimes querying DMVs may fail if we do not have the appropriate permission.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.