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.

Best Practice Analyzer recommendations
Best Practice Analyzer recommendations

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

Resolve Best Practice Analyzer recommendations
Resolve Best Practice Analyzer recommendations
BPA Generated script
BPA Generated script

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.

Custom Action setup

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.

Custom Actions in action

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:

NameDesciption
Hide all relationship columnsHide all columns on many side of a join
Hide fact table numeric columnsHide all numeric columns to avoid report creators using them instead of measures
Format string to numeric columnsFormats all numeric columns that do not have a format already
Format measuresFormat all measures that do not have a format already
Do not summarize numeric columnsChange all numeric columns to do not summarize to avoid dimension columns to be treated as implicit measures
Format DAXRun DAX formatter on all the measures
Default Measure DescriptionAdds DAX expression to the description
ID columns into folder and hidePuts 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.

Resources

Summary

By creating custom actions from BPA recommendation enables automated data model refinement for any future projects.

Hope you found this blog post useful. If you want the get notified about new posts, you can subscribe to a newsletter.

Please follow and like us:

About the author

Mihaly Kavasi

Data-driven Decision-Making Enthusiast.
Power BI Expert and Trainer. Helping organizations and communities to use data effectively.
Big fan of RE and Electric cars.
FTRSA, MCSE, MCT
Working @ Avanade

View all posts