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

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

The INFO.MEASURES 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 wanted to query those $System schema DMVs we used external tools like Tabular Editors or DAX Studio to query them:

The INFO.MEASURES function is one of the system functions.  It employs the following syntax:

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 Measure object represents a value that is calculated based on an expression.  It is a child of a Table object.

We can write this INFO.MEASURES function on DAX query view to get the same information on the TMSCHEMA_MEASURES:

It will query the $SYSTEM.TMSCHEMA_MEASURES and return an entire table with 19 columns:

• ID: this is a reference to the object
• TableID: this is an ID-based reference to a Table object
• Name: this is the name of the object
• Description: this is the description of the object
• DataType: this is the data type of the measure.  The possible values are as follows:
• String (2)
• Int64 (6)
• Double (8)
• DateTime (9)
• Decimal (10)
• Boolean (11)
• Binary (17)
• Unknown (19) - a measure in an error state
• Variant (20) - a measure with varying data type
• Expression: this is the DAX expression that is evaluated for the calculated measure
• FormatString: this is a string that specifies the format of the measure contents
• IsHidden: this is a Boolean that indicates whether the measure is treated as hidden by client visualisation tools.  If the measure is treated as hidden by client visualisation tools, it is "true"; otherwise, it is "false"
• State: this is a value that provides information about the state of the measure.  The possible values and their interpretation are as follows:
• Ready (1) – the measure is queryable and has up-to-date data
• NoData (3) – not applicable to Measure
• CalculationNeeded (4) – not applicable to Measure
• SemanticError (5) – the measure expression has a semantic error
• EvaluationError (6) - not applicable to Measure
• DependencyError (7) – a dependency associated with this measure is in an error state (SemanticError, EvaluationError, or DependencyError)
• Incomplete (8) - not applicable to Measure
• SyntaxError (9) – the measure has a syntax error in its expression.
• ModifiedTime: this is the time that the object was last modified
• StructureModifiedTime: this is the time that the structure of the object was last modified
• KPIID: this is an ID-based reference to a KPI object
• IsSimpleMeasure: this is a Boolean that indicates whether the measure is an implicit measure that is automatically created by client tools to aggregate a field.  Client applications can hide measures that have this flag set
• ErrorMessage: this is 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
• DisplayFolder: this is a string that defines the display folder in which the measure is displayed by the client applications
• DetailRowsDefinitionID: this is an ID-based reference to a DetailRowsDefinition object.  This property defines the DAX expression to apply when drilling through to the detail rows of the measure.  Compatibility level 1400 or higher is required
• DataCategory: this specifies the kind of data that is contained in the measure so that a user can add custom behaviours based on the data type of the measure.  Compatibility level 1500 or higher is required
• LineageTag: this is an optional tag that can be used to define the lineage of a measure across different versions of a model.  Compatibility level 1600 or higher is required
• SourceLineageTag: this is an optional tag that can be used to define the lineage of a referenced measure across different versions of a model.  As opposed to LineageTag, SourceLineageTag can be used to define the lineage of a referenced measure rather than a measure itself.  SourceLineageTag is useful when a model references other models by using a Direct Query connection.  Compatibility level 1600 or higher is required.

It should be noted that:

• it is used for querying the DMV (Dynamic Management Views) from the $System schema called TMSCHEMA where TM stand for ‘Tabular model’ and TMSCHEMA provides information from the tabular model
• sometimes querying DMVs may fail if we do not have the appropriate permission
• there is also a MDSCHEMA for MEASURES call MDSCHEMA_MEASURES but its output is not the same as TMSCHEMA_MEASURES and INFO.MEASURES.

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.