Updated: May 4
Have you ever had the idea of finding which measures in your model use a particular function? Or a quick view of all the calculated columns in a model? These are relatively simple tasks but it can be quite tedious to actually find these specific objects in your model.
The basics of such advanced filtering techniques are officially documented here. In this post, I will discuss additional filtering scenarios which tend to come up over and over again. I really think every tabular developer should know these patterns as they will save you lots of time in the long run.
These filtering techniques are done in Tabular Editor, so make sure you have it installed. Within Tabular Editor, the filtering commands below are entered into the highlighted 'Filter' box.
Now...let's get started!
View only calculated columns
Since calculated columns should be avoided, here is an easy way to find them (and hopefully turn them into data columns!).
:ObjectType = "Column" and Type = "Calculated"
View Bi-Directional Relationships
This filters the object list to show only relationships that are enabled for bi-directional crossfiltering.
:ObjectType = "Relationship" and CrossFilteringBehavior = "BothDirections"
View Many-to-Many Relationships
This filters the object list to show only many-to-many relationships.
:ObjectType = "Relationship" and FromCardinality = "Many" and ToCardinality = "Many"
View Measures that use a particular DAX function
If you want to search which measures use a particular function - just replace where it says 'SWITCH' below with the function you want to search.
:ObjectType = "Measure" and RegEx.IsMatch(Expression,"(?i)SWITCH\s*\(")
View Multi-Partitioned Tables
This shows all tables in your model that have more than 1 partition.
:ObjectType = "Table" and Partitions.Count > 1
This shows all the measures in your model which are also KPIs.
:ObjectType = "Measure" and KPI != null
View Columns Which Use Sort-By Columns
This shows all columns in your model which are sorted by another column within the model.
:ObjectType = "Column" and SortByColumn != null
View Sort-by Columns
This shows all columns in your model which are used in order to sort other columns.
:ObjectType = "Column" and Model.AllColumns.Any(SortByColumn.Name == current.Name)
View Calculated Tables
This shows all the calculated tables in your model.
:ObjectType = "Table" and SourceType = "Calculated"
View Direct Query Tables/Partitions
This shows all the partitions in your model which are in Direct Query mode.
:ObjectType = "Partition" and (Mode = "DirectQuery" or (Model.DefaultMode = "DirectQuery" and Mode = "Default" || Mode = "DirectQuery"))
View Import mode Tables/Partitions
This shows all the partitions in your model which are in Import mode.
:ObjectType = "Partition" and (Mode = "Import" or (Model.DefaultMode = "Import" and Mode = "Default" || Mode = "Import"))
View Relationship columns
This shows all the columns in your model which are used in relationships
:ObjectType = "Column" and UsedInRelationships.Any()
View foreign key columns
This shows columns used on the 'from' side of relationships (generally foreign keys).
:ObjectType = "Column" and UsedInRelationships.Any(FromTable.Name == current.Table.Name and FromColumn.Name = current.Name)
View Primary key columns
This shows columns used on the 'to' side of relationships (generally primary keys).
:ObjectType = "Column" and UsedInRelationships.Any(ToTable.Name == current.Table.Name and ToColumn.Name = current.Name)
VIEW Objects with blank descriptions
This shows all (relevant) objects with no description.
VIEW Measures with no format string
This shows all measures in your model that are not formatted (i.e. currency, whole number).
:ObjectType = "Measure" and string.IsNullOrWhiteSpace(FormatString)
SEARCH FOR Text within Partition Queries
This allows you to search for partition queries which contain a specified text string. Simply replace 'SEARCH TEXT' with the text you want to search.
:ObjectType = "Partition" and RegEx.IsMatch(Query,"(?i)SEARCH TEXT")
Partitions used by a specific data source
This allows you to search for all partitions which use a specific data source.
:ObjectType = "Partition" and Datasource.Name = "SqlServer MyServerName"
Tabular Editor has some slick ways of filtering your model to see exactly the objects on which you want to focus. The key is to know how to use it. These examples give common scenarios which may help you immediately or at least should give you a better idea of how to do advanced filtering using Dynamic LINQ expressions.