There are many DAX functions for covering day-to-day business-related calculations using measures and calculated columns. However, there is also a set of functions that can be helpful to the BI team and developers in gaining insights from the Power BI model itself. The insights can include things such as the number of both-directional relationships, the dependency of the calculations, the list of columns in tables, etc. These functions are in the category of INFO functions in DAX. Let’s see what they are and how they work.
Video
INFO Functions
INFO functions are a category of functions in DAX that give you information about the semantic model itself. Not the data, but the metadata of the semantic model. For example. Info.Columns() gives you a list of all columns in the semantic model. “Info.Relationships()” provides the information about the relationship between tables, such as which two tables are connected, the type of relationship, and the direction of it, etc. Or you can use Info.CalcDependency() to understand how the calculations are dependent on each other and the columns of the data model.
INFO functions are the DAX way of retrieving data model information, similar to how DMVs (Dynamic Management Views) provide it in MDX.
Below is a list of INFO functions in DAX;
| Function | Description |
| INFO.ALTERNATEOFDEFINITIONS | |
| INFO.ANNOTATIONS | Returns a list of all annotations in the current model with columns matching the schema rowset for annotation objects. |
| INFO.ATTRIBUTEHIERARCHIES | Represents the TMSCHEMA_ATTRIBUTE_HIERARCHIES DMV query function. |
| INFO.ATTRIBUTEHIERARCHYSTORAGES | |
| INFO.CALCDEPENDENCY | Returns information about the calculation dependency for a DAX query. |
| INFO.CALCULATIONGROUPS | |
| INFO.CALCULATIONITEMS | |
| INFO.CATALOGS | Represents the DBSCHEMA_CATALOGS DMV query function. |
| INFO.CHANGEDPROPERTIES | Represents the TMSCHEMA_CHANGED_PROPERTIES DMV query function. |
| INFO.COLUMNPARTITIONSTORAGES | |
| INFO.COLUMNPERMISSIONS | Returns a list of all column permissions in the current model with columns matching the schema rowset for column permissions objects. |
| INFO.COLUMNS | Returns a list of all columns in the current model with columns matching the schema rowset for column objects. |
| INFO.COLUMNSTORAGES | Returns a list of all column storages in the current model with columns matching the schema rowset for column storage objects. |
| INFO.CSDLMETADATA | Returns information about database metadata in XML format. |
| INFO.CULTURES | Returns a list of all cultures in the current model with columns matching the schema rowset for culture objects. |
| INFO.DATACOVERAGEDEFINITIONS | |
| INFO.DATASOURCES | Represents the TMSCHEMA_DATASOURCES DMV query function. |
| INFO.DELTATABLEMETADATASTORAGES | |
| INFO.DEPENDENCIES | Returns information about the calculation dependency for a DAX query. |
| INFO.DETAILROWSDEFINITIONS | Returns a list of all detail rows definitions in the current model with columns matching the schema rowset for detail rows definitions objects. |
| INFO.DICTIONARYSTORAGES | |
| INFO.EXCLUDEDARTIFACTS | Represents the TMSCHEMA_EXCLUDED_ARTIFACTS DMV query function. |
| INFO.EXPRESSIONS | Returns a list of all expressions in the current model with columns matching the schema rowset for expressions objects. |
| INFO.EXTENDEDPROPERTIES | Returns a list of all extended properties in the current model with columns matching the schema rowset for extended properties objects. |
| INFO.FORMATSTRINGDEFINITIONS | |
| INFO.FUNCTIONS | Returns information about the functions that are currently available for use in the DAX programming language. |
| INFO.GENERALSEGMENTMAPSEGMENTMETADATASTORAGES | |
| INFO.GROUPBYCOLUMNS | |
| INFO.HIERARCHIES | Represents the TMSCHEMA_HIERARCHIES DMV query function. |
| INFO.HIERARCHYSTORAGES | |
| INFO.KPIS | Returns a list of all KPIS in the current model with columns matching the schema rowset for KPI objects. |
| INFO.LEVELS | Returns a list of all levels in the current model with columns matching the schema rowset for level objects. |
| INFO.LINGUISTICMETADATA | Represents the TMSCHEMA_LINGUISTIC_METADATA DMV query function. |
| INFO.MEASURES | Returns a list of all measures in the current model with columns matching the schema rowset for measure objects. |
| INFO.MODEL | Represents the TMSCHEMA_MODEL DMV query function. |
| INFO.OBJECTTRANSLATIONS | Returns a list of all object translations in the current model with columns matching the schema rowset for object translation objects. |
| INFO.PARQUETFILESTORAGES | |
| INFO.PARTITIONS | Represents the TMSCHEMA_PARTITIONS DMV query function. |
| INFO.PARTITIONSTORAGES | Returns a list of all partition storages in the current model with columns matching the schema rowset for partition storage objects. |
| INFO.PERSPECTIVECOLUMNS | Returns a list of all perspective columns in the current model with columns matching the schema rowset for perspective columns objects. |
| INFO.PERSPECTIVEHIERARCHIES | Returns a list of all perspective hierarchies in the current model with columns matching the schema rowset for perspective hierarchies objects. |
| INFO.PERSPECTIVEMEASURES | Returns a list of all perspective measures in the current model with columns matching the schema rowset for perspective measures objects. |
| INFO.PERSPECTIVES | Returns a list of all perspectives in the current model with columns matching the schema rowset for perspectives objects. |
| INFO.PERSPECTIVETABLES | Returns a list of all perspective tables in the current model with columns matching the schema rowset for perspective tables objects. |
| INFO.PROPERTIES | Represents the DISCOVER_PROPERTIES DMV query function. |
| INFO.QUERYGROUPS | |
| INFO.REFRESHPOLICIES | |
| INFO.RELATEDCOLUMNDETAILS | |
| INFO.RELATIONSHIPINDEXSTORAGES | |
| INFO.RELATIONSHIPS | Represents the TMSCHEMA_RELATIONSHIPS DMV query function. |
| INFO.RELATIONSHIPSTORAGES | |
| INFO.ROLEMEMBERSHIPS | Returns a list of all role memberships in the current model with columns matching the schema rowset for role memberships objects. |
| INFO.ROLES | Returns a list of all roles in the current model with columns matching the schema rowset for roles objects. |
| INFO.SEGMENTMAPSTORAGES | Returns a list of all segment map storages in the current model with columns matching the schema rowset for segment map storage objects. |
| INFO.SEGMENTSTORAGES | |
| INFO.STORAGEFILES | Returns a list of all storage files in the current model with columns matching the schema rowset for storage file objects. |
| INFO.STORAGEFOLDERS | Returns a list of all storage folders in the current model with columns matching the schema rowset for storage folder objects. |
| INFO.STORAGETABLECOLUMNS | Returns statistics about the columns of in-memory tables. |
| INFO.STORAGETABLECOLUMNSEGMENTS | Returns information about the column segments used for storing data for in-memory tables. |
| INFO.STORAGETABLES | Returns statistics about in-memory tables. |
| INFO.TABLEPERMISSIONS | Returns a list of all table permissions in the current model with columns matching the schema rowset for table permissions objects. |
| INFO.TABLES | Returns a list of all tables in the current model with columns matching the schema rowset for table objects. |
| INFO.TABLESTORAGES | Returns a list of all table storages in the current model with columns matching the schema rowset for table storage objects. |
| INFO.VARIATIONS | Returns a list of all variations in the current model with columns matching the schema rowset for variations objects. |
| INFO.VIEW.COLUMNS | Returns a list of all columns in the current model. |
| INFO.VIEW.MEASURES | Returns a list of all measures in the current model. |
| INFO.VIEW.RELATIONSHIPS | Returns a list of all relationships in the current model. |
| INFO.VIEW.TABLES | Returns a list of all tables in the current model. |
INFO functions are all tabular functions. This means that their response comes in a table format. If you want to learn what Tabular functions are and how they are different than scalar functions, read my article below;
How to use INFO functions
As these functions are tabular functions, the best way to use them is either by using the DAX Query View tab in Power BI Desktop or by creating a calculated table for their use. The DAX Query view will be a better place because you can test the results more easily before generating the output as a table.
In Power BI Desktop, Open DAX Query View, and then type in the below code;
Evaluate
Info.CalcDependency()Here is how the results will show up;
Evaluate is a function that retrieves the output of a tabular function in DAX. “Info.CalcDependency() will return all the calculations in the data model plus their dependency details.
That’s it. This is how simply you can use INFO functions; they can all be used in this manner. Because they are tabular functions, you can combine them with functions such as FILTER, SELECTColumns, and other functions to generate the output in the way you want.
The code below, for example, returns only dependency details for measures in the model;
As you can see, the output of INFO.CalcDependency is used as the input for the FILTER function. To learn more about the Filter function, read my article here.
INFO VIEW functions
There are numerous INFO functions, and they are not necessarily all new. However, a few simplified views are available for DAX functions, all of which start with INFO.VIEW, and they provide a simpler and more straightforward result set to work with.
For example, the response from INFO is shown below.Tables()
However, the same information is also available in an easier-to-read format using INFO.View.Tables()
As you can see, this output has labels and titles instead of IDs, which is simpler to understand.
There are four info functions available at the time of writing this article;
The list of functions above is also produced using a DAX query that I wrote as below;
Using INFO functions inside Calculated Tables
The INFO functions can be used in the DAX query view to help you build your queries. However, if you want to create reports and visualizations from it, then it is best to add it to the model as a calculated table.
To do this, go to the Data tab, create a New table, and then write the DAX expression. The screenshot below displays a table that combines information from all tables. You can see that even hidden tables (which are there because of the detailed Power BI data table are visible.
Once the table is created, you can easily use it in a visualization like below;
The visual above shows all the tables in the model and is filtered to show only the hidden tables.
Note that not all INFO functions can be used in this way; you may sometimes get the error below;
In cases like this, DAX Query View remains your central place to use the function.
A real use case example
A simple real-world use-case example of INFO functions is to check and see which columns consume more space in your semantic model. You can achieve this by using the INFO command.StorageTableColumns() function as below;
If you want to learn a simpler way to get this information, check out the Power BI Helper method I explained here:
Summary
DAX INFO functions are a simple method to get metadata information about your data model. They can be used in a DAX query view as their result is in the form of a table. In this article, you saw some example usages for them and a simple way to get how much space each column consumes in the data model.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.