top of page
MK

Advanced Filtering Examples in Tabular Editor

Updated: Oct 31, 2022

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

View KPIs


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 Calculated Tables


This shows all the calculation groups in your model.

:ObjectType = "CalculationGroupTable"

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.

:string.IsNullOrWhiteSpace(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"


Columns using a particular Translated Name


This allows you to search for columns with a particular translated name. Simply update the translation language (en-EN is used in the example below) and translated name text.


:ObjectType = "Column" and TranslatedNames["en-EN"] = "translated name"


Conclusion


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.

2件のコメント


jasperstarrenburg
2022年8月26日

Hi Michael,


This helps me a great deal! However, there is one question for me left unanswered:


Sometimes I see a translated column/measure in Power BI, and I want to look it up in Tabular Editor. Often, it is hard to find the original, untranslated column, so I am trying to search through translated names with Dynamic LINQ. However, I have difficulty in finding the right function. I have an idea I have to use ObjectTranslation, but I cannot seem to get the function right (unfortunately I don't have experience with API programming).


Could you provide a function how to search through translations?


Kind Regards,


Jasper

いいね!
MK
2022年8月28日
返信先

Hi Jasper,


You can use this code in the C# script window. Just fill in the parameter with the translated column name.


string trcolName = "";
foreach (var c in Model.AllColumns.Where(a => a.TranslatedNames.Any(b => b == trcolName)))
{
    string tableName = c.Table.Name;
    Model.Tables[tableName].Columns[c.Name].DaxObjectFullName.Output();
}
いいね!
bottom of page