Count Measures in Power BI Datasets with Tabular Editor
GET A QUICK OVERVIEW OF A MODEL
…with a pop-up of the number of objects like measures, tables, relationships, and more
GETTING A HELICOPTER OVERVIEW
When you first open a model made by someone else, or one that you made a long time ago, it’s easy to feel lost. Clicking through the tables and display folders, it’s hard to get a quick overview of the overall model complexity. A number of tools in Tabular Editor 3 help you do this, such as the VertiPaq Analyzer, which reveals the total model size, and the number of tables & columns.
But what if you want the number of measures, or the number of Calculated Tables or Calculated Columns, specifically? This might be valuable information. For example, when auditing a model, a high number of Calculated Columns may indicate the original author performed a lot of data transformations in DAX rather than in Power Query or the back-end data source. Additionally, a high number of one-to-one and/or bi-directional relationships also might suggest you need to watch out for model ambiguity. Even performing a quick assessment of the total number of measures and the length of their DAX expressions can yield valuable insights.
Getting this information is easy to do with C# Scripts in Tabular Editor. With a single line, you can quickly count any object and output the result to a pop-up “info” window:
A Pop-up in Tabular Editor that gives a quick overview of what’s in the model.
C# SCRIPTS & MACROS IN TABULAR EDITOR
One of the most powerful features in Tabular Editor, C# scripts allow programmatic access to the Tabular Model Metadata. We can use them to read, interact with and write model metadata changes with some C# written inside the Tabular Editor “C# Script” window. Further, they allow us to use rich functionalities like dynamic LINQ expressions or API calls to web services for more advanced use-cases.
C# Scripts enable automation and extreme productivity enhancements. In this case, we can fully automate Power Query formatting, applying it to either all M Expressions in our model, or a single M Expression that we’ve selected. The below script is an example which formats Power Query of a selected partition, replacing the unformatted Power Query code with the formatted code.
C# SCRIPT: COUNT MODEL OBJECTS
If the below script is copied into a Tabular Editor 3 C# Script window, you can execute it to return a pop-up that will count the number of objects by type. This is a nice, quick way to get an overview of what is in the model. You can delete any objects that are not relevant to you.
By default, the pop-up will resemble the one in the image, above.
// Count calculation groups & calculation items
int _calcgroups = 0;
int _calcitems = 0;
foreach ( var _calcgroup in Model.CalculationGroups )
{
_calcgroups = _calcgroups + 1;
foreach ( var _item in _calcgroup.CalculationItems )
{
_calcitems = _calcitems + 1;
}
}
// Count partitions and DAX parameters
int _partitions = 0;
int _whatifparameters = 0;
int _fieldparameters = 0;
foreach ( var _table in Model.Tables )
{
foreach ( var _partition in _table.Partitions )
{
string _type = Convert.ToString(_partition.SourceType);
string _exp = Convert.ToString(_partition.Expression);
if ( _type == "M" )
{
_partitions = _partitions + 1;
}
else if ( _type == "Calculated" && _exp.Contains("NAMEOF") )
{
_fieldparameters = _fieldparameters + 1;
}
else if ( _type == "Calculated" && _exp.Contains("GENERATESERIES") )
{
_whatifparameters = _whatifparameters + 1;
}
}
}
// Average measure length
decimal _numLines = 0;
decimal _numChars = 0;
int _measures = Model.AllMeasures.Count();
foreach ( var _measure in Model.AllMeasures )
{
_numLines = _numLines + _measure.Expression.Split("\n").Length;
_numChars = _numChars + _measure.Expression.Length;
}
_numLines = Math.Round(_numLines / _measures, 1);
_numChars = Math.Round(_numChars / _measures, 1);
// Return the pop-up
Info ( "In the model, we see the below objects:\n\n"
+ "-----------------------------------------\n"
+ "Data Objects\n"
+ "-----------------------------------------\n"
+ " ├─ PQ Expressions: " + Convert.ToString(Model.Expressions.Count()) + "\n"
+ " │\n"
+ " └─ Tables: " + Convert.ToString(Model.Tables.Count()) + "\n"
+ " ├─ Incremental Refresh Tables: " +
Convert.ToString(Model.Tables.Where(
_ir =>
Convert.ToString(_ir.EnableRefreshPolicy)
==
"True").Count()) + "\n"
+ " │\n"
+ " ├─ Calculated Tables: " +
Convert.ToString(
Model.Tables.Where(
_tables =>
Convert.ToString(_tables.Columns[0].Type)
==
"CalculatedTableColumn").Count()) + "\n"
+ " │ ├─ What if parameters: " +
Convert.ToString(_whatifparameters) + "\n"
+ " │ └─ Field parameters: " +
Convert.ToString(_fieldparameters) + "\n"
+ " │\n"
+ " ├─ M Partitions: " +
Convert.ToString(_partitions) + "\n"
+ " │\n"
+ " └─ Total Table Columns: " +
Convert.ToString(Model.AllColumns.Count()) + "\n\n"
+ "-----------------------------------------\n"
+ "DAX Objects\n"
+ "-----------------------------------------\n"
+ " ├─ Relationships: " +
Convert.ToString(Model.Relationships.Count()) + "\n"
+ " │ ├─ Bi-directional: " +
Convert.ToString(Model.Relationships.Where(
_relationships =>
Convert.ToString(_relationships.CrossFilteringBehavior)
==
"BothDirections").Count()) + "\n"
+ " │ ├─ Many-to-Many: " +
Convert.ToString(Model.Relationships.Where(
_relationships =>
Convert.ToString(_relationships.FromCardinality)
==
"Many"
&&
Convert.ToString(_relationships.ToCardinality)
==
"Many").Count()) + "\n"
+ " │ ├─ One-to-One: " +
Convert.ToString(Model.Relationships.Where(
_relationships =>
Convert.ToString(_relationships.FromCardinality)
==
"One"
&&
Convert.ToString(_relationships.ToCardinality)
==
"One").Count()) + "\n"
+ " │ └─ Inactive: " +
Convert.ToString(Model.Relationships.Where(
_relationships =>
Convert.ToString(_relationships.IsActive)
==
"False").Count()) + "\n"
+ " │\n"
+ " ├─ Calculation Groups: " +
Convert.ToString(_calcgroups) + "\n"
+ " │ └─ Calculation Items: " +
Convert.ToString(_calcitems) + "\n"
+ " │\n"
+ " ├─ Calculated Columns: " +
Convert.ToString(Model.AllColumns.Where(
_columns =>
Convert.ToString(_columns.Type)
==
"Calculated").Count()) + "\n"
+ " │\n"
+ " └─ Measures: " +
Convert.ToString(_measures) + "\n"
+ " └─ Avg. Lines of DAX: " +
Convert.ToString(_numLines) + " Lines \n"
+ " └─ Avg. Chars of DAX: " +
Convert.ToString(_numChars) + " Characters \n\n"
+ "-----------------------------------------\n"
+ "Other Objects\n"
+ "-----------------------------------------\n"
+ " ├─ Data Security Roles: " +
Convert.ToString(Model.Roles.Count()) + "\n"
+ " ├─ Explicit Data Sources: " +
Convert.ToString(Model.DataSources.Count()) + "\n"
+ " ├─ Perspectives: " +
Convert.ToString(Model.Perspectives.Count()) + "\n"
+ " └─ Translations: " +
Convert.ToString(Model.Cultures.Count()));
Notes & Limitations:
- You can remove any objects from the script that aren't in scope for your model.
- If no measures are in the model, it may return an error when determining the avg. DAX length (divide by 0).
- The script is provided as-is without warranty or guarantees. It has not been tested on Tabular Editor 2.
Save as a macro and map to custom keyboard shortcuts so you can quickly get a model overview
TO CONCLUDE
To count the number of objects in your model, you can make use of C# scripts in Tabular Editor. These counts can be structured and output to pop-up “Info” windows for a quick, glanceable overview of what’s in the model.
NEXT UP
Read about how C# scripts like this can be saved as macros and bound to keyboard shortcuts or custom toolbar buttons. With a push of a button or keyboard hotkey, you can get this overview!
