Custom Actions from Best Practice Analyzer recommendations

This post is a follow on from the DOCUMENTING POWER BI – DATA MODEL, where I looked at how you can use Tabular editor to document your data model. In this post we are going to look at Best Practice Analyzer and Custom Actions.

Creating automated solution for generic data modelling tasks based on the Best Practice Analyzer recommendations.

Let start first with introducing these two components of Tabular Editor.

Best Practice Analyzer

Best Practice Analyzer in Tabular Editor is an incredibly powerful tool to optimize and clean up Power BI Data models.

It helps by highlighting common best practices and in some cases gives you one click fixes.

Resolve Best Practice Analyzer recommendations

As you can see the generated fix script generates one line of code for each entity it would like to change.

Custom Actions

Custom Actions are an effective way to create generic scripts to preform data model modifications. Scripts a written in C#.

When you created your script, you can save them as Custom Actions. Here you can define the context where you want the action to be available.

Now you just need to select and right click to see the custom actions appearing. This way you can use them on not just the actual model you are working on but on any data model in the future.

BPA Recommendations to Custom Actions

Let’s get back to the current topic. When I encounter a task with a data model, I usually try to think through if this task might appear with future data models. If the answer is yes, then I try to find a generic and preferably automated/scripted solution for it to become more efficient with every project.

So, I thought to create generic scripts from the BPA recommendations and a few others to help me achieve a good quality and report creator friendly data model.

Dax formatting and default measure descriptions

Here is the list:

Name	Desciption
Hide all relationship columns	Hide all columns on many side of a join
Hide fact table numeric columns	Hide all numeric columns to avoid report creators using them instead of measures
Format string to numeric columns	Formats all numeric columns that do not have a format already
Format measures	Format all measures that do not have a format already
Do not summarize numeric columns	Change all numeric columns to do not summarize to avoid dimension columns to be treated as implicit measures
Format DAX	Run DAX formatter on all the measures
Default Measure Description	Adds DAX expression to the description
ID columns into folder and hide	Puts ID columns in an id folder and hides them.

Here are some examples:

//Name: Organize ID Columns
//Tooltip: Puts ID columns in an id folder and hides them.
//Enable: Model
//Created by Mihaly Kavasi
//Tabular Editor version 2.16.0

foreach(var c in Model.AllColumns)
{
    if(c.Name.ToLower().Contains("id"))
    {
        c.DisplayFolder = "_IDs";
        c.IsHidden = true;
    }
   
}

//Name: Default Measure Description
//Tooltip: Adds expression to the description
//Enable: Model
//Created by Mihaly Kavasi
//Tabular Editor version 2.16.0

//It is better to format DAX before adding it into the descriptions
foreach(var m in Model.AllMeasures)
{
    if(m.Description == "")
    {    
        m.Description = "Expression: " + m.Expression;
    }
    else if (!m.Description.Contains("Expression"))
    {
        m.Description = m.Description + " Expression: " + m.Expression;
    }
}

//Name: Numeric column to Do not summarize
//Tooltip: Change all numeric columns to do not summarize
//Enable: Model
//Created by Mihaly Kavasi
//Tabular Editor version 2.16.0

foreach (var c in Model.AllColumns)
{
    if(c.DataType == DataType.Decimal ||  c.DataType == DataType.Double || c.DataType == DataType.Int64)
    {  
    c.SummarizeBy = AggregateFunction.None;
    }
}

You can find the latest version of all the scripts in the blogs GitHub repo: https://github.com/KavasiMihaly/SelfServiceBIBlog/tree/main/TabularEditor/CustomActions

I plan to constantly add and improve upon the scripts there.